In the September 2005 Newsletter
We're seeing that a number of customers are testing, developing and rolling out with OLAP 10g - releases 1 and 2. In this Newsletter, we feature how to upgrade to 10g R2 and provide some great tips on how to improve performance.
Other topics in this ORP newsletter include a feature on The Gallup Organization who have one of many OLAP applications now in production, as well as our usual OLAP and DBA tips, and OLAP and BI Product Management updates.
Let us know any other topics you'd like to see included in the newsletter.
Upgrading from 10.1 to 10.2 is simple:
Product Management OLAP Update
Oracle Database 9.2
Oracle Database 10g
Product Management BI Update
Oracle OLAP Events
OpenWorld is next week. Attending? Improve your business insight by attending these business intelligence sessions.
Not yet registered? You can still register as an exhibit visitor, or call 650.226.0812 to enquire about full passes!
Do a search for OLAP on EVENTS at oracle.com to see range of scheduled events. There's always a lot happening in EMEA!
The Gallup Organization are developing a generic OLAP application that can be easily remodeled and extended for various customers with different end user needs. The first of these applications is a healthcare offering which analyzes hospital quality. This solution is currently in production at a few sites and will be rolled out to 200 different customers by early next year. They expect up to 6000 concurrent users globally.
Ken Ewald, Senior DBA at Gallup states:
"We needed a solution that would allow us to achieve 1000 concurrent users across the globe initially; our custom application using SQL to access AW data provides us with a scalable solution which achieves these goals. This OLAP application will become extremely important in our solution offering to many customers."
About the Solution:
The solution is Oracle 9.2 with the OLAP option using SQL interface to access AW data. Response time for reports is in seconds (some reports hold 20 thumbnail graphs and 20 crosstabs per page). They currently employ a 6 dimensional cube and they are working on a 10g implementation with up to 12 dimensions. They're also extending to use Discoverer for OLAP for another application.
The Gallup Organization has studied human nature and behavior for more than 70 years and provide customers with the tools to understand statistics gathered; their performance management solutions help organizations boost organic growth by increasing customer engagement and maximizing employee productivity. Learn more about Gallup at http://www.gallup.com.
10g DBA Tip
How to improve AW performance at query time
If you're querying an AW through D4O, BI Beans, SSA or custom OLAP API and you experience poor performance, there are a number of things to consider:
1. Check the RDBMS parameters are appropriately tuned for your environment. In 10g init.ora parameters are managed dynamically, however a few parameters that may need to be changed are:
2. Check that you�re not trying to retrieve too much data � are you remembering what you have selected for every dimension? Data for every page, i.e. every cell combination, is retrieved no matter what is shown on the screen. Also, are you using calculations e.g. YTD calculations, so that although you may only see a few rows of data, what is being processed is much more? This could be a big problem for time series calculations models that go to day level for example.
3. And of course, check you�ve got sufficient hardware for what you�re trying to achieve � the faster disk and more CPUs the better (especially true for loading and aggregating since a lot of use is made of TEMP space in 10g).
How to improve AW performance at load time
Build times also need to be optimized; loads normally have a tight batch window and you should aggregate as much as possible at load time so that data is pre-aggregated and less needs to be aggregated on the fly when querying the AW.
To decrease the 'build' time you can make the following few changes:
1. Modify parameters SORTBUFFERSIZE, and OLAP_PAGE_POOL_SIZE as detailed above for query improvement.
2. Add physical partitions to AWs. The AW should be logically partitioned and modeled well and then should also be physically partitioned as it improves update performance by reducing index lob contention. For example, create a physical partition for every logical partition you have,i.e. you have 60 months of data and have logically partitioned by month in the AW. Now create 60 physical partitions as follows:
3. a. Turn off Logging ( REDO) or increase REDO Log Size to between 100M and 500M, and b. modify LOG_BUFFER parameter to 10M (for example) to allow for more efficient index lob creation, and c. move TEMP, UNDO and REDO logs to fastest disk.
4. Turn Parallel Update of AWs off by setting it to any high value e.g.
5. Change PARALLEL_MAX_SERVERS to the number of processors less 1 so that AWs use as many parallel slaves as available (normally this is set too high by default install).
10g OLAP Tips
How can I monitor what is happening during my AWM load?
You can run the following query in SQL*PLUS and this will show you the tasks that have been completed by the AWM maintain.
set pages 5000 lines 110
This will show output as follows:
13:54:00 Finished Loading Attributes.
How can I trace the OLAP DML that is run during the load?
In OLAP Worksheet in the same session type
poutfileunit = tracefileunit
A .trc file will be placed in the specified udump directory. Also type 'fileclose poutfileunit' after the load has completed to stop writing to .trc file.
Copyright © 2004, 2005, Oracle. All rights reserved.