As Published In May/June 2012

TECHNOLOGY: Business Intelligence

Working with the Summary Advisor

Create in-memory aggregates for better performance on Oracle Exalytics In-Memory Machine.

If you create analyses and dashboards with Oracle Business Intelligence Enterprise Edition 11g, you know that users expect short response times even when querying datasources many terabytes in size. How do you provide short, consistent response times when querying such large amounts of data?

Oracle Exalytics In-Memory Machine, one of Oracle’s latest additions to its engineered systems products, works alongside Oracle Exadata and Oracle Exalogic to provide a “speed of thought” business intelligence platform. Oracle Exalytics brings in-memory analysis to Oracle Business Intelligence solutions and provides a platform for analyzing terabytes of information with lightning-fast query responses.

Under the covers, Oracle Exalytics works by gathering statistics on your query workload and then recommending in-memory aggregates to improve query response time. To make this possible, Oracle Business Intelligence Enterprise Edition provides a set of cache management tools that are available only when you license Oracle Exalytics. One such management tool is the Summary Advisor, a utility within the Oracle Business Intelligence administration tool. Let’s take a look now at how you can use the Summary Advisor with your Oracle Exalytics system to first analyze your query statistics and then to recommend in-memory aggregates, which you will then implement to improve the performance of queries on your system.

Using the Summary Advisor to Generate Recommendations

The following example uses the SampleAppLite repository and catalog that come preinstalled with Oracle Business Intelligence Enterprise Edition 11g to demonstrate the Summary Advisor at work. This article assumes that your Oracle Exalytics system has been installed and set up with a standard configuration: query caching is disabled, usage tracking is set up for your system, the required tables are registered in your repository, and the standard TT_AGGR_STORE Oracle TimesTen In- Memory Database connection is enabled. 

1. Let’s start by looking at the performance of the SampleAppLite repository and catalog. Using your Web browser, navigate to the Oracle Business Intelligence Web page, typically at http://[machine_name]:9704/analytics, and log in with your user ID and password— biadmin/welcome1, for example.

   From the Dashboards menu in the header area, select Sample Lite -> QuickStart. When the dashboard opens, click the Overview tab and then locate the History Overview pivot table on the dashboard page, as shown in Figure 1.
    

    

   Figure 1: The History Overview pivot table

    

   In this pivot table, drill into the All Products hierarchy to get a feel for the responsiveness of the analysis.

2. Now let’s get ready to use the Summary Advisor to recommend aggregates to place in Oracle TimesTen In-Memory Database. Start by opening the Oracle Business Intelligence administration tool, and then connect to the repository running on your Oracle Exalytics server (File -> Open -> Online). When prompted, enter the credentials for the SampleAppLite repository and your Oracle Exalytics server. For example 

   Repository Password: Admin123
   User: biadmin
   Password: welcome1 

3. To start using the Summary Advisor, select Tools -> Utilities -> Oracle BI Summary Advisor from the Oracle Business Intelligence administration tool. Note that this option will be listed on the Utilities menu only if you are using Oracle Exalytics.

4. The Summary Advisor first uses log activity to present aggregate recommendations. 

   Because you want to consider all activity on your system for these recommendations, click Next on the first three screens—Filter Logs - Logical Fact Tables, Filter Logs - Time Window, and Filter Logs - Execution Time Threshold—to accept the default settings, which will instruct the Summary Advisor to consider all log activity when making its recommendations.

5. The Targets screen enables you to select which physical database in your repository will be used to store the aggregates recommended by the Summary Advisor. Because you want to use Oracle TimesTen In-Memory Database, select the details that correspond to your Oracle TimesTen In-Memory Database instance, such as 

   Database Schema: "20 - RCU".. "BIRCU_BIPLATFORM"
   Connection Pool: "20 - RCU"."RCU CP"
   Capacity (MB): 200000

   Click Add Target to register the target database, and then click Next.

6. On the Select File Location screen, click Browse to specify a filename and a file system location for the script the Summary Advisor will create for you—C:\TEMP\summary_advisor.sql, for example—and click Next.

7. The Stopping Criteria screen enables you to place limits on how long the Summary Advisor will spend generating recommendations. Again, click Next to accept the default values, which place no limits on the recommendation generation process.

8. On the Miscellaneous screen, leave the Max size of any single aggregate setting at its default maximum value and click Browse to specify a location for the script that can be used to rerun the recommendation process—c:\TEMP\rerun_advisor.xml, for example—and click Next.

9. On the Run screen, click Run to generate these recommendations. 

   Depending on the settings you chose previously, the recommendation process will take a few moments to run, and then you will be presented with a list of aggregate recommendations, as shown in Figure 2.
    

    

   Figure 2: The aggregate recommendations

    

10. On the Filter Aggregates screen, you can select which aggregates will be included in the script that the Summary Advisor will generate.

    For this example, leave all the aggregates selected and then click Next to proceed to the final step.

11. On the final screen, Finish Script, click Finish to direct the Summary Advisor to create the specified scripts and close the wizard.

Creating the Aggregates

At this point, the Summary Advisor has created two scripts for you:

• C:\TEMP\summary_advisor.sql contains the aggregate definitions that you will need to run through the nqcmd utility to create or re-create the aggregate definitions in Oracle TimesTen In-Memory Database and the Oracle Business Intelligence Enterprise Edition 11g repository.

• C:\TEMP\rerun_advisor.xml contains the parameters you specified on the various Summary Advisor screens.

Let’s now use the first script created by the Summary Advisor to create the aggregates it has recommended. To do this, close the Oracle Business Intelligence administration tool; open a command-prompt session (Start -> Run -> cmd.exe); and run the nqcmd (nqcmd.exe) utility, using the following parameters:

• -d : an Open Database Connectivity (ODBC) datasource name for the Oracle Business Intelligence Server to which you want to connect
• -u : username of an administration user, such as biadmin
• -p : password of the user account
• -s : path to the aggregate definitions script created by the Summary Advisor

For example, for a default installation and the file, account, and password naming suggested in this article, the command-line entry is 

C:\>"C:\Program Files\Oracle Business
Intelligence Enterprise Edition Plus
Client\oraclebi\orahome\bifoundation\
server\bin\nqcmd.exe" -d exalytics -u
biadmin –p welcome1 -s C:\TEMP\summary_
advisor.sql

After the script runs, you should see a message indicating that several statements have been executed successfully. You can now reopen the Oracle Business Intelligence administration tool, connect to your repository, and navigate to the Business Model and Mapping layer to check out the new aggregate logical table sources that will have been automatically mapped in for you. For example, Figure 3 shows the F0 Revenue Base Measures logical table with seven new logical table sources mapped to it, representing the seven in-memory aggregates that were created by the Summary Advisor script.
 

Figure 3: Aggregate tables mapped into the Business Model and Mapping layer
 

Later on, if the volume of data loaded into your source database changes or new dashboards and analyses are created, you can generate a fresh set of aggregate recommendations by using the same Summary Advisor settings. Click Load Parameters from File on the first Summary Advisor screen to load your settings from the c:\TEMP\rerun_advisor.xml file you created in the previous steps.

Testing the In-Memory Aggregates

So now that you have generated a set of in-memory aggregate recommendations and implemented them for your repository, you can test the impact on your dashboards and analyses. Using your Web browser, log in to the Oracle Business Intelligence Web page again, navigate to the Overview dashboard, and try drilling into the History Overview analysis. You should now notice a much shorter response time as you drill into the hierarchies in your data.