I need help from you data warehouse / SSIS experts out there! I have a Transaction Fact Table with dollar amounts as the measurements. The grain is one row per transaction. I want to roll this up into a Monthly Periodic Snapshot based on 5 keys. I am having no problem where there is transaction data for each month.
However, the problem I am having is - how do I gracefully insert the Monthly rows for the five keys where there was no activity in the transaction fact table - I am sure there is a slick way to do this with SSIS but I am definitely having a mental block on how to accomplish this. Any help would be appreciated!
Hopefully you have a method of deriving all possibly combinations of the 5 key columns. Personally I would do this by extracting all possible values from the dimension tabes, unioning them together but where each column creates a new column in the output from the UNION ALL and then use the Aggregate component to produce all of the combinations.
Once you have done that you can use a MERGE JOIN component to join to your dataset and produce nulls/zeros for teh fact values for all of the missing rows.
-Jamie
|||Thanks Jamie. I will give that a shot.
-Steve
