In the October 2006 Newsletter

This Newsletter gives you some updates on the Oracle OLAP option, specifically about the new 10.2.0.3 release that will be released towards the end of October 2006.

SQL access to AWs is our special note this time. A tip on how you can easily analyze server performance, including OLAP is also given.

Finally, we feature a great customer story from Ford, in Genk, Belgium, who have a new Energy Monitoring application comprising of Oracle OLAP and OracleBI Spreadsheet Add In amongst other Oracle products.

Product Management OLAP Update

Oracle Database 10g

• What is the latest version of Oracle Database 10g available for OLAP users? 10.2.0.3 should be available for most platforms by the end of October.
• What is the latest version of AWM available for OLAP users? AWM 10.2.0.2A is available on MetaLink or OTN and can be used with 10.1x or 10.2x releases.
• What is the next version of AWM ? There will be an AWM 10.2.0.3 which will be an OTN release at the same time the 10.2.0.3 patch set is released to MetaLink.
• What are the new features of AWM 10.2.0.3?
  • New Sparsity Advisor to help in Implementation Tab
  • Measure Sub folders to assist in measure folder organization
  • Additional Calculation support and more features in calculation wizard
  • Non Gregorian (non standard calendar) time dimension support
  • Improved Data Viewer
  • Java Plug-Ins to extend the functionality of AWM to support programs written in AWXML, OLA API, Beans, JDBC, Swing API as well as allow use of OLAP DML and SQL
• What patches are currently available for OLAP? There is a 10.2.0.2 'B' patch, patch # 5146470, which is available on MetaLink for most platforms (not Win32 at the moment for example). Users are recommended to install this patch if using 10.2.0.2.
• When will 11g be available? 11g is planned for the summer of 2007
• Refer to Recommended versions on OTN for more information

Oracle Database 9.2

• What's the next version of the RDBMS in the 9.2 code stream? The RDBMS 9.2.0.8 patch set, the terminal release for 9.2, is now released as patch # 4547809 on MetaLink.

View OLAP collateral on OTN here.

OLAP 10g Corner

OLAP data in an AW can be accessed by simple SQL. This means that SQL emitting tools can access data in an AW and take advantage of the performance, calculations and scalability benefits that Oracle OLAP offers. Tools that have been proven to work well with Oracle OLAP are Business Objects, MicroStrategy, Cognos Reportnet and Oracle's own BI EE (ex Siebel tools). Below are some tips on how to best set up SQL access to AWs.

1. Relational views should be created to enable VERY simple SQL for a query e.g. select x from x.

2. Relational views may be best to be denormalized 'embedded total' style views for a cube (with columns for dimension information, and for all levels of fact data). This means that a single view can be used for whatever level of data a user wants to see; the SQL is simple since the AW stores the aggregate data and no joins or group bys are needed. All measures for a cube should be in the single view.

3. Dimension views can be created which include columns for every dimension level to improve performance if simply querying dimension information.

4. Most of these SQL emitting tools are the same in that you need to map the physical view columns to the logical layer in the client tool. There will be nuances for each tool that you will need to set up.

Some tips for the underlying OLAP model are:

1. Each dimension in the AW needs at least 1 hierarchy.

2. LOOP keyord in OLAP_TABLE or the use of the MODEL clause improves performance when lots of records are being returned. LOOP doesn't work if you have a compressed cube, or partitioned cubes.

3. There is a sample AWM 10203 Plug In which will create relational views planned. Please watch for this to be posted on OTN or ask your Oracle Consultant for this or other accelerator tools that may be available to you.

4. Use a text variable inside the AW to store limitmap information since it can get very big, and there is a limit of 4000 characters in a single PL/SQL function parameter

An example relational fact view for accessing AWs in OLAP 10g are below.

CREATE OR REPLACE VIEW units_cube_fact_view2 AS
select
  time_id,
  customer_id,
  product_id,
  channel_id,
  sales,
  units,
  extended_cost,
olap_expression(olap_calc,'lag(units_cube_sales,1,time,status)') as SALES_PRIOR_PRIOD
FROM TABLE(OLAP_TABLE('global DURATION SESSION',
  '',
  '',
  'DIMENSION time_id as varchar2(20) from time
   DIMENSION customer_id as varchar2(20) from customer
   DIMENSION product_id as varchar2(20) from product
   DIMENSION channel_id as varchar2(20) from channel
   MEASURE sales as number FROM units_cube_sales
   MEASURE units as number FROM units_cube_units
   MEASURE extended_cost as number FROM units_cube_extended_cost
   ROW2CELL olap_calc'
));

An example relational dimension view is below.

CREATE OR REPLACE VIEW time_cal_view AS
SELECT time_id, cal_short_label, cal_end_date, cal_timespan
FROM TABLE(OLAP_TABLE(
'global_aw.global duration session',
null,
null,
'DIMENSION time_id AS varchar2(32) FROM time WITH
HIERARCHY time_parentrel
INHIERARCHY time_inhier
ATTRIBUTE cal_short_label AS VARCHAR2(32) from time_short_description
ATTRIBUTE cal_end_date AS DATE from time_end_date
ATTRIBUTE cal_timespan AS NUMBER(6) from time_time_span'));

DBA 10g Corner

To monitor and improve server performance (including OLAP of course), familiarize yourself with new 10g utilities, the Advanced Workload Repository (AWR) and Automated Database Diagnostic Monitor (ADDM) reports.

The AWR collects, processes and stores statistics for self-tuning and problem detection in a set of data dictionary tables.

An ADDM report is produced automatically from the AWR, and provides an analysis of problems, symptoms and information and gives recommended action. All are easily viewed in a script format, or in Enterprise Manager. This allows you to easily monitor and control server and indeed OLAP performance.

Customer Feature

View all Oracle OLAP Customer References here.

Let us know if you'd like to take part in a reference activity or present at an event with Oracle.

Product Management BI Update

• What is the most current release of OracleBI?  OracleBI 10.1.2.2 (part of the IAS 10.1.2.2 release) which includes Data Write Back in SSA, and custom member functionality will be available in mid October and will work with OLAP 10.2.0.2 with 'B' or 'C' patch. The most recent version of OracleBI can still be found here

• What does OracleBI Enterprise Edition (EE) includes? Enterprise Edition contains the new generation of BI tools (ex Siebel)
• Does OracleBI Enterprise Edition (EE) work with Oracle OLAP? Yes, to use EE with Oracle OLAP you generate SQL views against the OLAP AW. Refer to the OLAP 10g Corner for the special feature on this.

• View BI collateral on OTN here.

ORP Newsletter Archives

Access past editions of the ORP Newsletter here:

Feb 05 | May 05 | July 05 | Sep 05 | Nov 05 | Jan 06 | Mar 06 | May 06 | July 06

Oracle BIDW Events

Oracle OpenWorld is taking place this year during Oct 22-26, 2006 in San Fransisco. Check out the Oracle OpenWorld website for agenda and up to date coverage of daily events.

Downloads

Oracle Education

Click this link to view all BI & DW courses available. Or, access Oracle Education at http://education.oracle.com and choose country of choice.

BIDW Training/Assistance

Use Oracle By Example Hands On tutorials for learning a range of BIDW topics.


Access the Oracle OLAP Discussion Forum for answers from the community.

Copyright © 2004, 2006 Oracle. All rights reserved.

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