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.
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
STATE level and also it contains higher levels as well, which is the
REGION level. You can see the sample view ...