The OLAP Extension is now available in SQL Developer 4.0

The OLAP Extension is now in SQL Developer 4.0.

See http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/sqldev-releasenotes-v4-1925251.html for the details.

The OLAP functionality is mentioned toward the bottom of the web page.

You will still need AWM 12.1.0.1.0 to

• Manage and enable cube and dimension MV's.
• Manage data security.
• Create and edit nested measure folders (i.e. measure folders that are children of other measure folders)
• Create and edit Maintenance Scripts
• Manage multilingual support for OLAP Metadata objects
• Use the OBIEE plugin or the Data Validation plugin

What is new or improved:

• New Calculation Expression editor for calculated measures.  This allows the user to nest different types to calculated measures easily.  For instance a user can now create a Moving Total of a Prior Period as one calculated measure.  In AWM, it would have required a user to create a Prior Period first and then create a Moving Total calculated measure which referred to the Prior Period measure.  Also the new Calculation Expression editor displays hypertext helper templates when the user selects the OLAP API syntax in the editor.
• Support for OLAP DML command execution in the SQL Worksheet.  Simply prefix OLAP DML commands by a '~' and then select the execute button to execute them on the SQL Worksheet.  The output of the command will appear in the DBMS Output Window if it is opened, or the Script Output Window if the user has executed 'set serveroutput on' before executing the DML command.
• Improved OLAP DML Program Editor integrated within the SQL Developer framework.
• New diagnostic reports in the SQL Developer Report navigator.
• Ability to create a fact view with a measure dimension (i.e. "pivot cube").  This functionality is accessible from the SQL Developer Tools-OLAP menu option.
• Cube scripts have been renamed to Build Specifications and are now accessible within the Create/Edit Cube dialog.  The Build Specifications editor there, is similar to the calculation expression editor as far as functionality.

Cell level write-back via PL/SQL

A topic of conversation that regularly comes up when I talk to customers and developers about the OLAP Option is write-back to OLAP cubes. The most frustrating of these conversations usually involves someone saying 'but... the OLAP Option doesn't support write-back'. This is not the case and never has been.

Since the first OLAP Option release in 9i it has always been possible to write-back to cubes via the Java OLAP API and OLAP DML. But in recent releases, a new PL/SQL package based API has been developed. My thanks go to the ever-excellent David Greenfield of the Oracle OLAP product development group for bringing this to my attention.

At the most simple level, it is possible to write to a qualified cell:

dbms_cube.build(
'PRICE_COST_CUBE USING (
SET PRICE_COST_CUBE.PRICE["TIME" = ''24'', PRODUCT = ''26''] = 711.61, SOLVE)')

In the example above, a cube solve is executed after the cell write. The objects are referenced by their logical (ie. AWM) names.

This approach is very flexible. For example you can qualify only some dimensions, in this case the assignment is for all products:

dbms_cube.build(
'PRICE_COST_CUBE USING (
SET PRICE_COST_CUBE.PRICE["TIME" = ''24''] = 711.61, SOLVE)')

You can also skip the aggregation:

dbms_cube.build(
'PRICE_COST_CUBE USING (
SET PRICE_COST_CUBE.PRICE["TIME" = ''24'', PRODUCT = ''26''] = 711.61)')

or run multiple cell updates in one call:

dbms_cube.build(
'PRICE_COST_CUBE USING (
SET PRICE_COST_CUBE.PRICE["TIME" = ''24'', PRODUCT = ''26''] = 711.61,
SET PRICE_COST_CUBE.PRICE["TIME" = ''27'', PRODUCT = ''27''] = 86.82,
SOLVE)');

You can also copy from one measure to another.

dbms_cube.build('UNITS_CUBE USING (SET LOCAL_CUBE.UNITS = UNITS_CUBE.UNITS'));

This will copy everything from the UNITS measure in UNITS_CUBE to the UNITS measure in the LOCAL_CUBE. You can put fairly arbitrary expressions on the right hand side and the code will attempt to loop the appropriate composite. You can also control status.


For more details, take a look at the PL/SQL reference documentation

Special OpenWorld 2010 Cocktail Reception

Join us! Simba Technologies and Vlamis Software Solutions are celebrating Oracle OLAP with a reception for current and prospective users. Please contact events@simba.com for exact location. Tuesday September 21, 2010 from 5-7PM. Walking distance from Moscone.

Simba product managers will also be available at the Oracle OLAP pod in Moscone West to answer questions you might have about Excel Pivot Tables and the MDX Provider for Oracle OLAP.

Experience with Oracle University OLAP Essentials Course

I've recently taught two sessions of the Oracle University Course 'OLAP Database 11g: OLAP Essentials'. The first session was in Reading UK and the next in Munich. Both sessions were highly rated by students (9.4 out of 10). As an instructor I found the course a pleasure to teach (kudos to Brian Pottle and Marty Gubar who put much of this class together).

This is a three day introductory course that is appropriate for anyone who is approaching the OLAP Option for the first time or who is upgrading to 11g from earlier releases (there is so much new in OLAP 11g that even experienced users can learn a lot in this course). The students in the class were a mix of OLAP Option newbies and upgraders. Both groups were equally pleased with the course.

Students remarked that one of the real strengths of the course is the hands-on exercises. By the end of day 1 students have defined, populated and queried their first cube. Later in the course they get hands-on experience with cube-organized materialized views, SQL query of cube, creating calculated measures, forecasting, security and even building a custom application in Application Express on the OLAP cube. One of the things about the hands-on exercises that I found remarkable was the high success rate experienced by the students. These are very well put together.

The outline of the course follows:

Day 1

1. Examining the role of the OLAP Option in the Oracle BI/DW platform.
2. Examining the OLAP Data Model.
3. Building an OLAP Cube (with 2 hands-on exercises).

Day 2

4. Examining Cube-Organized Materialized Views (with hands-on exercise).
5. Creating Calculated Measures (with hands-on exercise).
6. Using SQL to Query OLAP Cubes (with hands-on exercise).
7. Enhancing Analytic Content (with hands-on exercise). (This uses OLAP DML programs to create a forecast.)

Day 3

8. Using Oracle Application Express and Oracle Business Intelligence with OLAP Data (with hands-on exercise).
9. Cube security (with hands-on exercise).
10. Designing cubes for performance and scalability.
11. Performance tuning (this is really about things the DBA should know to tune the Database for cubes).

These three very full days. Expect to get a lot of value from this class. And, it was lots of fun.

Here is one student's perspective of the class: http://ofaworld.wordpress.com/2009/02/18/oracle-university-oracle-11g-olap-essentials-training-course/

For more information, see the Oracle University site.