Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Using Aggregate Tables – Aggregate Persistence Wizard

Posted by Venkatakrishnan J on November 20, 2007

One of the users of BI EE had sent me an email yesterday asking about the various options for improving performance of reports in BI EE. There are lots of options available in BI EE to improve the performance of reports in general. One of the widely used ones is the use of Aggregate Tables. So, lets try to understand what are Aggregate tables and how they can be used. As the name suggests, Aggregate tables are Physical tables that store Aggregates of measures across multiple levels of a Hierarchy. So, for example, say if you have the following hierarchy

      

and say you are analyzing Sales Measure across this hierarchy, you can store the aggregated values of this measure across all the above levels so that the BI Server can use this aggregate table instead of querying against the transactional database. Now, lets see how to go about creating Aggregates. There are 2 ways of doing this. One way is to manually create an Aggregate table and then associate that table with the dimension levels. The other way (recommended one) is to use the Aggregate Persistence Wizard available within BI EE. So, if you go to the Admin Tool -> Utilties you would find a utility called as the Aggregate Persistence Wizard.

      

The first step in using this wizard is to specify an output file name for the resultant script. This script is what we will execute later to create the Aggregate tables and their corresponding mapping to the various levels of the hierarchy

      

The next step in creating the Aggregate script is to specify the measures for which you would like to create the aggregate tables. Remember, you can only use those measures that have some predefined aggregation set.

      

Once that is done, choose the levels across the dimensions which you would like to associate with the aggregation. In our case we shall choose the lowest country level.

      

Once the levels are chosen, choose the names and the connection pool for the target aggregate table.

      

Then complete the wizard. This would create the script in the output directory you specified.

      

If you open the script you would see some UDML based commands that can be recognized only by the BI Server. So, lets see how we can go about executing them. Go to command prompt and navigate to {OracleBI}/Server/Bin and there you would find an executable called as nqcmd.exe. This is what we will use to execute the above script. Use the command below to execute this

nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s “D:\AggregateCountry.sql” -o “D:\Outputfile1.txt”

      

Once this script is executed open the Admin tool and the repository. If you navigate to the connection pool you would find the aggregate tables created.

      

Also, if you double click on any of the levels above the Country level (say SubRegion_Name), you would find that the logical mapping has been automatically created by the script.

      

Before proceeding further lets just go back to the database and see how the data of the aggregate table looks like. Remember, there will be 2 tables. One is for storing the level hierarchy and the other is for storing the aggregates.

      

And, at last lets create a simple report in BI Answers containing the Region, SubRegion and Country Columns. After that look at the query that is fired in the backend. You would notice that the aggregate table will be used instead of the actual transaction table.

      

      

Advertisements

11 Responses to “Oracle BI EE 10.1.3.3/2 – Using Aggregate Tables – Aggregate Persistence Wizard”

  1. Manas said

    Venkat,

    Thanks for this article. I am trying to use this wizard and I cannot create the agg table. It gives me an error saying the ” A general error has occurred. Error while processing aggregates.” I have 5 dimensions and one fact. I gave the detail level hierarchy for the aggregates. Could you help me with this?

    Thanks,
    manas

  2. caimino said

    Hi Venkat,
    I tried to create an aggregate table on a Time Dimension which a time dimension hierarchy corresonds to. The structure of the hierarchy is: Total, Year, Quarter, Month, Day with the chronological key checked on Day.
    When I use the Aggregate Persistence Wizard, it doesn’t display the Time Dimension in the “Selection Dimensions & Levels” screen.
    Is the Aggregate Persistence Wizard able to create an aggregate table on a Time Dimension?
    How can I do?

    Thanks in advance.

    Regards

    Cosimo

  3. G said

    Dear Venkat,

    This is working well for me, but i was wondering if this is widely use in production environments ?

    Aggregates are often critical and don’t you think that this is better to build our own aggregates.
    Visibility restrictions, performance, RPD customization. We cannot adapt those aggregates when they are deleted and build by the wizard.

    What is your point of view about this ?

    Many thanks.
    Regards,
    G.

  4. vasu said

    Hi Venkat,

    How can we refresh the data in the Aggregate tables daily?
    Can you please guide me with steps….

    Thank you in advance..

  5. jayasankar said

    Dear Venkat,

    This is working well for me, but i was wondering if this is widely use in production environments ?

    Aggregates are often critical and don’t you think that this is better to build our own aggregates.
    Visibility restrictions, performance, RPD customization. We cannot adapt those aggregates when they are deleted and build by the wizard.

    What is your point of view about this ?

    Could you please tell us your feed back?

    Regds
    JayasankarJ

  6. kkaki said

    i can’t execute
    nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s “D:\AggregateCountry.sql” -o “D:\Outputfile1.txt”

    my computer don’t known commande nqcmd ?? what can i do ??

  7. sreekar said

    Hi,

    When I try to use the same procedure mentioned above, I am getting this error when I execute the following command.

    nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s “D:\AggregateCountry.sql” -o “D:\Outputfile1.txt”

    I am getting the below error in the output file. I am copying the entire output file..

    create aggregates

    “ag_Sales_time”
    for “SALES”.”Sales”(“total_quantity”,”total_amount”)
    at levels (“SALES”.”Year_Dimension”.”Month” using_surrogate_key )
    using connection pool “SALES_DWH”.”Sales_Data_Warehouse”
    in “SALES_DWH”..”SALES”
    create aggregates

    “ag_Sales_time”
    for “SALES”.”Sales”(“total_quantity”,”total_amount”)
    at levels (“SALES”.”Year_Dimension”.”Month” using_surrogate_key )
    using connection pool “SALES_DWH”.”Sales_Data_Warehouse”
    in “SALES_DWH”..”SALES”
    [10058][State: S1000] [NQODBC] [SQL_STATE: S1000] [nQSError: 10058] A general error has occurred.

    [nQSError: 84008] [Aggregate Persistence] Error while processing aggregates (refer previous errors in log).
    Statement preparation failed

    Processed: 1 queries
    Encountered 1 errors

    can you please help me on this…

    Regards
    Sreekar Suri

  8. Satish said

    Hi

    I have created the Agrregate tables and its working fine but my question is how do we refresh the aggregate tables Daily.

    Is there a way ….if so please send me to my email ASAP…

    Thank You guys in advance..

    Satish

  9. Emel said

    Hi,

    I am interested about the aggregate tables refresh too.
    If there is a way to refresh them daily please advice.

    Thanks,

    Emel

  10. Emel said

    Hi,

    I am also interested on aggreate tables refresh.
    Please advise.

    Emel

  11. Edgars said

    So what about refresh. As I think, this could be done by Oracle Scheduler, at least I will use some Ibot to execute this command file

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: