I'm baffled by this.
For reasons I won't go into, We have an island dimension, which has the same values as some column names in the fact table.
The simple reason being the dataset is 65,000,000 rows and having this dimension as an additional column would potentially increase this by 20 times!!
Therefore under the advice of qliktech we created the island dimension, and renamed the amount columns with the same values.
Consider this as a basic example:
Island Table:
|
| BLOOD |
| DRUGS |
| WARDS AND NURSING |
| IMPLANTS |
Fact Table:
|
| HRG | BLOOD | DRUGS | WARDS AND NURSING | IMPLANTS |
| a | 100 | 20 | 40 | 23 |
| b | 200 | 10 | 50 | 456 |
We did have this set up with a huge IF statement but the performance was appalling so on some very good advice here we used a pick match formula.
This works brilliantly... except when we built in a new variable divisor into the table when it all went a bit wrong.
The only way I've seen to calculate the right value is to use an aggr formula... this works fine for the first line within the pick / match but then fails??
The attached probably makes more sense.
