Creating aggregate tables
Aggregate tables should be designed according to business requirements. We should monitor the usage statistics of the analysis and then we'll make a decision about the levels of hierarchies that will be stored in the aggregate tables.
How to do it...
- Let's assume that the most frequently accessed hierarchy levels are:
- Product hierarchy: Subtype level
- Time hierarchy: Year level
- Customer hierarchy: State level
So we're going to create three aggregate dimension tables and one aggregate fact table based on our sample scenario. The first table is named as
DIM_STATE_AGG. This table stores the attribute columns regarding the
STATElevel and also it contains higher levels as well, which is the
REGIONlevel. You can see the sample view ...