Using Aggregates
  • Share this blog:

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.


Table Alias
SAMP_REVENUE_FA2 F2 Revenue Aggregate
SAMP_TIME_QTR_D D1 Time Quarter Grain


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:

You liked the article?

Like : 0

Vote for difficulty

Current difficulty (Avg): Medium

Recommended Courses


About Author
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.