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 10.1.3.2 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
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.
The data is fetching from the Year_Sales_Agg table which is aggregate table.
Posts
