TECHNOLOGY: Business Intelligence
Working with the Summary AdvisorBy Mark Rittman
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.
Creating the Aggregates
At this point, the Summary Advisor has created two scripts for you:
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:
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.
Mark Rittman is an Oracle ACE Director and cofounder and technical director of Rittman Mead. He writes for the Rittman Mead blog at rittmanmead.com.
Send us your comments