Oracle Business Intelligence and Data Warehousing Practice Notes and Knowledge Repository

  • About Me

    Hello Friends,

    This is Santosh Kumar Gidadmani, a Business Intelligence and Data Warehouse Enthusiast passionate about blogging articles in the BI, Data warehousing, space. This is my attempt to share my experience and knowledge on Oracle BI & Data Warehousing Subjects.

  • OBIEE 11g Certified Implementation Specialist

  • Oracle Partner Network Certified Specialist

  • Visit My Profiles

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 73 other followers

  • Blog Hits Since Nov 2010

    • 364,321 Hits
  • Live Traffic

  • Visitors Country

    Since-Mar'12Free counters!

Archive for the ‘Aggregate Wizard’ Category

Aggregate Persistence Wizard

Posted by Santosh Kumar Gidadmani on March 8, 2011

Aggregate tables store pre-computed results that are aggregated measures (typically summed) over a set of dimensional attributes. Using aggregate tables is a typical technique used to improve query response times in decision support systems. This feature came with OBIEE version.

Steps for creating Aggregate tables using Aggregate Persistence Wizard
Create dimensional hierarchies to use in the aggregate persistance wizard.
Ensure there is at least one measure value which is aggregated on the properties, aggregation tab.
Open the Admin tool in online mode
Go to Tools – Utilities
Click on Aggregate Persistence Wizard and execute it

Type the name of the SQL file to be generated and locate anywhere on the system and click next.

Select the Database, Measure on which aggregates have to be created and click next.

Select the dimensional hierarchy and click on surrogate key options. You can also select other hierarchies.

Select the same database, connection pool and click next.

This will create the UDML scrip which is understood by the BI Server only.

Select “I’m done” and click next

Click on Finish, Save the RPD and Close it.

The SQL file will be created, go to the location path and open the file to check the script.

Run this script in the command prompt to create the SQL statement to create the actual tables to use in Admin tool.

We will use NQCMD.exe file to create the aggregates table. This exe file is located in OracleBI -> Server -> BIN

Now, type the below shown command to create the tables, It will take some time to create the tables.

Now, open the Admin tool, you would see the aggregate table created in the physical layer and added to Sales Fact TLS.

Let us take a look at the Year_Sales_Agg table properties column mapping tab, the data is coming from year_sales_agg table which is aggregate table and on the content tab, it is showing at year level.

Save the RPD and let us check the Query.

Login to the Answers and select the below columns in the SH Subject area.

Go to Settings -> Administrations -> Manage Sessions -> click on the View log link

The data is fetching from the Year_Sales_Agg table which is aggregate table.

– Santosh

Posted in Aggregate Wizard, OBIEE 10g, OBIEE 10g RPD | 2 Comments »

%d bloggers like this: