Data Warehouse/SSAS Cube/Reporting and date assistance
-
Hi everyone - first post and question about SSAS so be gentle :) I am attempting to build a cube with sample warehouse data of a handful of dimensions (including date) and one fact table. Popular data warehouse insertion (from what i've read - although I could be wrong) is based on an initial load of data then incremental updates based on the changes and the date of change. This would then present the problem of duplicate rows in the warehouse, and therefore the cube??? One of the reporting requirements from the cube is to report on a given date, what are the total financials for a given selection claims, grouped by area. If an initial then ongoing updated load of data is utilised, how would I be able to query via date (today or otherwise)? Because of the duplicate rows in the cube, would a claim be counting a financial value twice? Any clarification needed, please let me know. Thanks.
-
Hi everyone - first post and question about SSAS so be gentle :) I am attempting to build a cube with sample warehouse data of a handful of dimensions (including date) and one fact table. Popular data warehouse insertion (from what i've read - although I could be wrong) is based on an initial load of data then incremental updates based on the changes and the date of change. This would then present the problem of duplicate rows in the warehouse, and therefore the cube??? One of the reporting requirements from the cube is to report on a given date, what are the total financials for a given selection claims, grouped by area. If an initial then ongoing updated load of data is utilised, how would I be able to query via date (today or otherwise)? Because of the duplicate rows in the cube, would a claim be counting a financial value twice? Any clarification needed, please let me know. Thanks.
I would assume your fact table has a primary key, if you are using aggregated value you still need a unique key, just insert any records that do not exist. Or if you are working by date, query the cube for existing dates.
Never underestimate the power of human stupidity RAH
-
I would assume your fact table has a primary key, if you are using aggregated value you still need a unique key, just insert any records that do not exist. Or if you are working by date, query the cube for existing dates.
Never underestimate the power of human stupidity RAH
Hi - thanks for your reply. The fact table has a composite PK - FactDateID (the date of insertion) and DimRepairID (each repair is unique in the OLTP system). These combined will give a unique reference for a row. My issue is how I solve the issue of displaying the correct data in a cube when I choose a date. For example: RepairID 1 and date of 20140615 is inserted into the fact table. The financial value associated with this is 10. No new row is inserted for the 16th of June, as there was no activity on this repair. However on the 17th of June, the financial value was updated to 20 by a user in the OLTP system and therefore transferred to the DW. A new row of 20140617 with RepairID of 1 and value of 20 is then inserted. Bearing in mind that there will be many RepairIDs in the fact table and some will have changed recently and others not, how do I enable users to pick a date and see what rows were what financial values at a given point in time? Thanks