Introduction

This article describes aggregate tables and their purpose in dimension modelling. It explains the modelling of aggregate tables and to set the number of elements for a hierarchy level

1. Aggregates

1. Business challenge in using Aggregates

• Data in fact and dimension sources is stored at the lowest level of detail.

• Data often needs to be rolled up or summarized during analysis.

• Based on the amount of data, performing calculations at the time of the query can be resource intensive and can delay results to the user.

2. Business Solution for the usage of Aggregates

Aggregate tables store pre-computed results, which are measures that have been aggregated (totally summed) over a set of dimensional attributes. Using aggregate tables is a very popular technique for increasing the speed query response time in decision support systems. This eliminates the need for run-time calculations and delivers faster results to users.

Note that the below tables are physical tables. The calculations are done ahead of time and the results are stored in the tables. The key point is that the aggregate table should have fewer rows than the non aggregate table and, therefore, processing should be quicker.

3. Oracle BI Aggregate Navigation

If you are writing SQL queries or using a tool that understands the existence of physical tables, the aggregate tables become more difficult if the number of tables increases.

The aggregate navigation capability of Oracle BI Server, however, allows queries to use the information stored in the aggregate tables automatically, without query authors or query tools having to specify aggregate tables in their queries.

Specific metadata in the repository should be configured so that the Oracle BI Server has enough information to navigate to aggregate tables.

4. Aggregated Facts

Each aggregate table column contains data at a given set of levels. For example, an aggregate sales fact table might contain a pre-computed sum of the revenue for each product type for each sales representative during each month.

The graphic indicates that there is more data at lower levels in a hierarchy. As you move higher in the hierarchy, there is less data because the results are aggregated.

5. Modelling Aggregates

The aggregate tables are modelled in the same way that you model other source data.

The aggregate tables are modified in three layers as follows:

In Physical Layer

• Create data source connection and import physical sources.

• Create physical joins.

In BMM Layer

Add sources to logical tables and specify aggregation content.

In Presentation Layer

No changes are necessary. Aggregate navigation is important of the presentation layer objects.

Example for an aggregate

• The prebuilt aggregate tables can be used to improve performance of the server.

• The matching levels of aggregation should be done for fact and dimensions.

6. Aggregate Navigation Implementation

The known techniques are used to import fact and dimension tables to the Physical layer and create aliases. Both the Aggregate and Fact tables should be imported because you need to create logical dimension sources at the same level of detail as the fact sources.

1. Creation of Join

The Physical diagram should be used to create joins between the aggregate fact table aliases and the aggregate dimension table aliases.

7. Fact LTS Mappings

The known techniques are used to create a new LTS within the current logical fact table that points to the aggregate table. For example, drag physical columns from the fact aggregate physical table to the corresponding columns in the business model to map existing logical columns to the new LTS.

In this example, the new LTS is named Fact_D1_ORDER_AGG1.

Note that these four columns now map to both the Fact_D1_ORDERS2 table and the Fact_D1_ORDER_AGG1 table. In this step you configure the model to choose the appropriate table during a query based on how content is specified in the content tab.

1. Set Aggregation Content

Use the content tab of LTS dialog box to specify the aggregation content of new aggregate LTS. You set the aggregation content for the fact table to the corresponding levels in the dimension hierarchies.

Later, when a user queries against a particular level, Oracle BI Server will automatically know that it should access the aggregate tables instead of the detail tables. In a subsequent step, you set similar levels for the dimension table aggregate sources.

For example, if a user queries for total sales by Month by Sales Rep, the server accesses the Fact_D1_ORDER_AGG1 aggregate fact table and the corresponding aggregate dimension tables Dim_MONTHS and Dim_D1_SALESREP. If a user queries for a level lower than the levels specified here, for example instead of Month, or Customer instead of Sales Rep, the Server accesses the detail tables (Fact_D1_ORDERS2, Dim_D1_CALENDAR2, and Dim_D1_CUSTOMER2).

If a user queries for a higher level (Year instead of Month, District instead of Sales Rep), the aggregate tables are used as well, because whenever a query run against a logical level or above, the aggregate tables are used.

Hint: Click the More button in LTS dialog box to have the server help determine the levels.

2. Specify content for Detail LTS

The levels of the fact detail source should be set to the lowest in the hierarchies. This is because you want the server to access the detail tables when queries are against lower levels than those specified for the aggregate tables.

The content of all sources should be specified for the documentation purposes. This helps prevent another administrator from interpreting the lack of an aggregation content statement as an inadvertent omission of information.

3. Create Dimension LTS and Mappings

A new LTS can be created within the current logical dimension tables that point to the aggregate tables.

This example shows the Dim_Customer logical dimension table. A new LTS named Dim_D1_SALESREPS is added. It maps to the Dim_D1_SALESREPS physical aggregate table. The logical columns District, Region, and Sales Rep now map to both physical tables Dim_D1_Customer2 and Dim_D1_SALESREPS.

4. Specify Dimension Aggregation

Specify the aggregation content for the aggregate LTS for Dim_Customer table so that the Oracle BI Server knows what level of data is stored in the aggregate tables. The Dim_D1_SALESREPS table contains data at the Sales Rep level within the Customer hierarchy.

5. Set Content for Dimension LTS

The levels of the dimension detail source should be set to the lowest in the hierarchies. This is because you want the server to access the detail tables when queries are against lower levels than those specified for the aggregate tables.

The content of all sources should be specified for the documentation purposes. This helps prevent another administrator from interpreting the lack of an aggregation content statement as an inadvertent omission of information.

6. Test Results

In the example below, the analysis requests data at the levels stored in the aggregates. As expected, the Dim_D1_SALESREPS, and the dollar data is retrieved from the fact aggregate, Fact_D1_ORDER_AGG1.

In the example shown below, the analysis request data at levels above or below those stored in the aggregates. When data is requested by Sales District, which is above the Sales Rep level, the DIM_D1_SALESREPS and Fact_D1_ORDER_AGG1 aggregate tables are still used. When data is requested by the customer that is below the Sales Rep level, the Dim_D1_CUSTOMER2 and Fact_D1_ORDERS2 detail tables are used.

8. Setting the Number of Elements

The number of elements is used by Oracle BI Server when selecting aggregate sources. Setting the number of elements is necessary only when there are two or more aggregate sources that could be accessed by an Oracle BI query.

Aggregate fact sources are accessed based on the combination of the fields selected as well as the number of elements of the levels in the logical dimensions to which they map. The number does not have to be exact, but ratios of numbers from one logical level to another should be accurate.

In the example shown below, there are two aggregate sources for the Fact-Sales logical table: Fact_D1_ORDER_AGG1 and Fact_D1_ORDER_AGG2.

Logical levels are set differently for each aggregate source; however, both sources have logical levels set to Month for the Time logical dimension. Despite the fact that both aggregate sources have their aggregation content set to the Month level for the Time Logical dimension, the query uses Fact_D1_ORDER_AGG1. This is because Oracle BI Server determines that it is more economical to access Fact_D1_ORDER_AGG1 instead of Fact_D1_ORDER_AGG2 based on the levels specified in the dimension hierarchies.

For example, to access Fact_D1_ORDER_AGG2, it calculates 35,712 potential rows (12 districts*6 Months*186 Generic products). To access Fact_D1_ORDER_AGG1, it calculates 11,968 potential rows (34 sales rep * 16 Months * 22 product types). Therefore, Fact_D1_ORDER_AGG1 is the more economical aggregate source.

Selvi

Add comment