Using Aggregates

Using Aggregates

In this set of steps, you set up and use aggregate tables to improve query performance. Aggregate tables store pre-computed results, which are measures that have been aggregated (typically summed) over a set of dimensional attributes. Using aggregate tables is a popular technique for speeding up query response times in decision support systems. This eliminates the need for runtime calculations and delivers faster results to users. The calculations are done ahead of time and the results are stored in the tables. Aggregate tables typically have many fewer rows than the non-aggregate tables and, therefore, the processing is faster.

To set up and use aggregate tables, perform the following steps:

• Import Metadata • Create New Logical Table Sources • Set Aggregate Content • Test Your Work

Import Metadata 1. Return to the Administration Tool and open the BISAMPLE repository in offline mode. 1. In the Physical layer, expand orcl. 2. Right-click Connection Pool and select Import Metadata to open the Import Wizard. 3. In the Select Metadata Types screen, select Views, and click Next.

Desired to gain proficiency on OBIEE Training? Explore the blog post on OBIEE  training to become a pro in "OBIEE Training"

4 . In the Select Metadata Objects screen, in the data source view, expand BISAMPLE. 5. In the data source view, select the following for import: SAMP_REVENUE_FA2 SAMP_TIME_QTR_D

6 . Click the Import Selected button to move the objects to the Repository View.

7. Expand BISAMPLE in the Repository View and confirm that the objects are visible.

8 . Click Finish to close the Import Wizard.

9. Confirm that the objects are visible in the Physical layer of the repository.

11 . Right-click F2 Revenue Aggregate and select View Data. F2 Revenue Aggregate stores aggregated fact information for revenue and units at the quarter and product grain.

12 . Right-click D1 Time Quarter Grain and select View Data. D1 Time Quarter Grain stores time data at the quarter grain. It stores one record for each quarter beginning with Q4 2006 and ending with Q4 2011.

13 . Use the Physical Diagram to create the following physical joins: "orcl".""."BISAMPLE"."D2 Product"."PROD_KEY" = "orcl".""."BISAMPLE"."F2 Revenue Aggregate"."PROD_KEY" "orcl".""."BISAMPLE"."D1 Time Quarter Grain"."QTR_KEY" = "orcl".""."BISAMPLE"."F2 Revenue Aggregate"."BILL_QTR_KEY"

For an in-depth understanding of OBIEE click on:

• OBIEE 11g Tutorials
• New Features in OBIEE11g
• Checking Out a Project in OBIEE 11g
• Making Changes to the Repository in OBIEE
• Setting Up a Multiuser Development Environment in OBIEE