Forum Discussion
Slowly changing dimension type 2
Not sure what to call this but I have a fact table with Category {1...N} and other sized data like Amount and Date etc. How to I properly work with this kind of dimension table ? Categor...
To make this problem easier to understand, here's my Dimension table
| Resort | Valid From | Valid To | Rooms |
| A | 1.1.2018 | 31.12.2018 | 220 |
| B | 1.1.2018 | 31.12.2018 | 150 |
| C | 1.1.2018 | 31.12.2018 | 60 |
| A | 1.1.2019 | 30.6.2019 | 225 |
| A | 1.7.2019 | 235 | |
| B | 1.1.2019 | 160 | |
| C | 1.1.2019 | 40 |
Last column (as a measure ofc ) would be my desired output
| Resort | Date | Nights | Desired Output | Formula* |
| A | 1.1.2018 | 200 | 91% | 200/220 |
| A | 1.1.2019 | 210 | 93% | 210/225 |
| A | 1.8.2019 | 235 | 100% | 235/235 |
| B | 1.1.2018 | 120 | 80% | 120/150 |
| B | 1.1.2019 | 120 | 75% | 120/160 |
| B | 1.8.2019 | 120 | 75% | 120/160 |
| C | 1.1.2018 | 30 | 50% | 30/60 |
| C | 1.1.2019 | 35 | 88% | 35/40 |
| C | 1.8.2019 | 30 | 75% | 30/40 |
*just for showcase
just 1 more question, are this always full montly periods?
No, this could be any daily date for that matter.