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...

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

Get Oracle Business Intelligence 11g R1 Cookbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.