I have a new project where we need to keep history on dimensions using slowly changing dimensions and keep history of the fact table. So my fact table would look something like this.
| sales_skid | sales_id | employee_id | product_id | commission | type | date |
| 1 | 1 | 1 | 1 | $2,035.00 | insert | 2/15/2012 9:20 |
| 2 | 1 | 1 | 1 | $3,034.00 | update | 2/15/2012 11:15 |
The problem being with this model is if you have to get the latest sum of commission you would have to get only the last record. You also cannot tell if the employee did multiple sales…
More to come
Filed under:
SSAS
The answer is use the same start_date end_date columns as you would a SCD slowly changing dimension. If someone deletes a sale I take the current and give it a end date. To process I use SELECT … FROM fact.sales WHERE end_date IS NULL