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.

10gR2 Upgrade

Upgrading from 10.1 to 10.2 is simple:

• Upgrade to 10.2 using the Oracle Universal Installer. OLAP will be upgraded and there are no patches at present, though there will be an A patch available soon for AWM. You need to change to 10.2 compatibility mode.

• To take advantage of 10.2 features with a 10.1 or prior version of an AW you need to upgrade the Standard Form (SF) metadata. This is done by a right mouse click on the AW in the model view in AWM, or in SQL*Plus by running the following script:
  
  execute dbms_awm.upgrade_aw_to_10_2 ('<aw_owner>', '<aw_name>');
  
  Note, if you create from a template that was generated in 10.1 you will also need to upgrade as above.  Watch out for Bug 4429253 if upgrading the metadata; a patch will be available on MetaLink in the coming week.

Product Management OLAP Update

Oracle Database 9.2

• Is 9.2.0.7 available? The RDBMS 9.2.0.7 patch set (which includes OLAP fixes) is available for most platforms now. Platforms that are unavailable now (Linux 64, LINUX Itanium and Tru 64) will follow in Oct.
• When will 9.2.0.8 be available, and what fixes will be in this version? The RDBMS 9.2.0.8 patch set is planned for release in Feb 2006 for Windows and later for other platforms.

Oracle Database 10g

• What is the latest version of Oracle Database 10g available for OLAP users? 10.2 is now available on OTN for Linux, Windows, AIX 5L, Solaris and HP UX. The download also includes AWM 10g. 10.1.0.4 plus OLAP A patch (Patch # 4457603) is recommended for other users. Note, 10.2 is not planned for release for Tru64.
• Which version of AWM is recommended for OLAP users? OLAP users should use AWM10g which comes with the installation. Please note, that there will be a new release of AWM 10.2 (10.1.0.2.0a)  which will be made available on OTN within the next month.
• When is the next patch set for Oracle Database 10.1 ? RDBMS 10.1.0.5 should be available for LINUX and Windows in Oct this year and other platforms by the end of the year.
• When is the next patch set for Oracle Database 10.2 ? RDBMS 10.2.0.2 should be available at end of the year and includes some OLAP performance enhancements.
• What version of Oracle Database 10g is Analytic Workspace Manager 10.2 certified with? 10.2. Note, AWM 10.2 will work with 10.1.0.4; any new features which need 10.2 will not be enabled if using with 10.1.0.4 RDBMS.

Product Management BI Update

• When will OracleBI 10.1.2.0.2 be available? It is available now. Download here.
• What versions of Oracle is OracleBI 10.1.2.0.2 certified with? Oracle 9206 and higher , Oracle 10.1.0.4 and higher, Oracle 10.2.0.1 and higher.
• What versions of Oracle is OracleBI 10.1.2 certified with? Oracle 9205 plus patches, 9206 and Oracle 10.1.0.4. It is supported with Oracle 10.2 though not officially certified.
• When will Oracle Warehouse Builder 10gR2 (Codename Paris) be in production? It is scheduled for 2006.

Access past editions of the ORP Newsletter here:

June 04 | Aug 04 | Oct 04 | Dec 04 | Feb 05 | May 05 | July 05

Oracle Education

	Oracle Database 10g: Using OLAP**Recommended**
	Oracle Database 10g: OLAP Enhancements Online Course
	OracleBI Discoverer Plus 10g- Analyze Relational and OLAP Data
	Oracle9i Database: Using OLAP
	Oracle9i Database: Program with OLAP DML
	Oracle9i Warehouse Builder: Perform OLAP Integration Online Course
	Introduction to Oracle�s Business Intelligence Solution Online Course
	Oracle9i JDeveloper: Explore the BI Beans Query Model Online Course

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!

Downloads

	Oracle Database 10g Release 2 for Linux x86, Solaris, AIX and HP-UX
	Oracle 10g Release 2 Developer's Release for Windows
	Oracle 10g Release 1 - 10.1.0.4 OLAP A Patch
	Analytic Workspace Manager 10g
	Analytic Workspace Manager 10g Hands On Lab
	OracleBI SpreadSheet Add In
	Ox Tool (Unsupported) New!

Customer Feature

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.

About Gallup:

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:
    a. SORTBUFFERSIZE should be increased since OLAP AWs use this parameter instead of SORT_AREA_SIZE. So, for every AW, to increase it do the following:

exec DBMS_AW.EXECUTE('aw attach SCOTT.MYAW rwx');
exec DBMS_AW.EXECUTE('shw sortbuffersize');
262,411
exec DBMS_AW.EXECUTE('SortBufferSize=10485760');
exec DBMS_AW.EXECUTE('shw sortbuffersize');
10,485,760
exec DBMS_AW.EXECUTE('aw detach SCOTT.MYAW');
        b. OLAP_PAGE_POOL_SIZE should be set to 0 or unset so that auto dynamic page pool is on and is managed by the database (will be set to 50% of PGA size). However, if you have over 8G of memory available then you should set the parameter manually � a good value for data loading is to set to 256MB and for multiple users querying concurrently, 64MB.

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).

Refer to July's newsletter for instructions on how to do a SQL trace to view retrieved data.

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:
SQL> exec dbms_aw.execute('aw create scott.product_AW partitions 60');
   

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.
SQL> alter system set "_olap_parallel_update_threshold"=2000000000
SQL> scope=spfile; shutdown immediate startup
   

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
select xml_message from olapsys.xml_load_log order by xml_loadid, xml_recordid, xml_date;

This will show output as follows:

13:54:00   Finished Loading Attributes.
13:54:00   Finished Loading Dimensions.
13:54:00   Started Updating Partitions.
13:54:01   Finished Updating Partitions.
13:54:11   Completed Build(Refresh) of SCOTT.MYAW Analytic Workspace.

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.

  oracle.com/olap | MetaLink | forums/olap | education | events | unsubscribe