Creating Aggregate Tables in OBIEE

Steps for Creating Aggregate Tables in OBIEE

1. Create all Dimension Tables, Fact Tables & Hierarchies, which are required to be aggregated.
2. Go to ToolsàUtilities (from Administration tool menu bar), a dialog box will pop up with all available utilities.
3. Select Aggregate Persistence Wizard, and then click Execute Button. (See Figure Below).

4.       Now Select appropriate path where you need to generate Aggregate Table SQL.

Note : Click Generate DDL file for first time generation of Aggregate Table. (See Figure Below)

5. Click Next, to move to next page (Select Business Model & Measure Page)
6. In Select Business Model & Measure Page, Select Appropriate Business Model & then select associated Fact / Measure.(See Figure Below).

     7.       Click next, to move to next page (Select Dimensions & Levels).

8. Select appropriate level of dimension & check Use Surrogate Key.

9.       Click next, to move to next page (Select output Connection Pool, Container & Name).

10. Click next, to move to next page (Aggregate Definition).
11. Select I am Done (Radio Button).
12. Click Next.

13. Then Click Finish. Your Aggregate Table is Created Now & available at the path you specified in “Select File Location” Page.
14. To view generated script move to - C:\Agg\
15. Select The Aggregate Table Created & View the Code. Code for above process is mentioned below:

delete aggregates; /* Required only first time, so that any further aggregates can be deleted */

/*However if you create other Aggregate, you dont need it. */ 

create aggregates "ag_FACTINTERNETSAL"  for "SALES"."FACTINTERNETSALES"("ORDERQUANTITY")  at levels ("SALES"."Time"."Year" using_surrogate_key )  using connection pool "Adventure Works"."Agg CP"  in "Adventure Works"."AdventureWorksDW2008";

16. Now the script is ready, we run it using the “nqcmd.exe” utility in the /OracleBI/server/bin directory.

Steps for running Aggregate Script are mentioned below:

Microsoft Windows [Version 6.1.7601] Copyright (c) 2009 Microsoft Corporation.  All rights reserved. C:\Users\gaurav.mishra>cd\ C:\>cd oraclebi\server\bin (Click Enter) C:\OracleBI\server\Bin>nqcmd.exe -u Administrator -p Administrator -d analyticsweb -s \Agg\agg.sql [ Where : u (Admin User id)  p (Password)  d (analyticsweb)  s (script path) ] -------------------------------------------------------------------------------           Oracle BI Server           Copyright (c) 1997-2009 Oracle Corporation, All rights reserved ------------------------------------------------------------------------------- delete aggregates delete aggregates Statement execute succeeded create aggregates "ag_FACTINTERNETSAL"  for "SALES"."FACTINTERNETSALES"("ORDERQUANTITY")  at levels ("SALES"."Time"."Year" using_surrogate_key )  using connection pool "Adventure Works"."Agg CP"  in "Adventure Works"."AdventureWorksDW2008" create aggregates "ag_FACTINTERNETSAL"  for "SALES"."FACTINTERNETSALES"("ORDERQUANTITY")  at levels ("SALES"."Time"."Year" using_surrogate_key )  using connection pool "Adventure Works"."Agg CP"  in "Adventure Works"."AdventureWorksDW2008" Statement execute succeeded Processed: 2 queries C:\OracleBI\server\Bin>

 

After getting successful aggregate script execution, you need to restart all BI Services.

Now Open your metadata Repository & you will see that new aggregate tables created and registered, and shown in red to show they’re aggregates.

Note : No change in presentation layer, as data is executed from logical table & physical table, so presentation layer remains unaffected.