Creating Cubes with Simple SQL

If you didn't look carefully at some of the Oracle OLAP 11.2 collateral there's a very interesting new feature that you've probably missed, the ability to create Oracle cubes and dimensions using only SQL. This feature is implemented at the CREATE_MVIEW program in the DBMS_CUBE package.

Here's how it works. You define SQL dimension objects and a table-based materialized view and then run the DBMS_CUBE.CREATE_MVIEW program. This program will (1) create an analytic workspace, (2) create OLAP dimensions from the SQL dimensions, (3) create a cube from the table-based materialized view and (4) create a cube-organized materialized view on the cube to enable query rewrite into the cube.

This feature is primarily designed to make it very easy to use the cube as a summary management solution for applications that query relational tables. For example, you might have an Oracle Business Intelligence (or Business Objects, Cognos or MicroStrategy) application that queries tables. If the application queries summary level data, can benefit from a performance boost and queries tables that tables represent data that is dimensional in nature (e.g., a star or snowflake schema), a cube is a great way to manage that summary data.

Let's look at a scenario. There's a star schema with time, product, customer and channel dimension tables and a sales fact table. The hierarchies are as follows:

Time: Day > Month > Quarter > Year
Product: Item > Subtype > Type > Category > Department
Customer: Customer > City > State > Country > Region
Channel: Channel > Class

The fact table contains data at the Day, Item, Customer and Channel levels for measures Sales and Quantity.

The objective is to create a cube and cube-organized materialized view that manages all summary data beginning at Month, Item, City and Channel levels. Note that in this example the detail of the cube will be a summary of the fact table.

Step 1 is to create SQL dimension objects for each of the dimension tables. (If you want to try this script yourself, download and install the OLAPTRAIN schema from the OLAP page on OTN. http://www.oracle.com/technology/products/bi/olap/11g/samples/schemas/readme.html .)

--
-- Create SQL dimension objects for hierarchies and attributes. The cube will
-- use these as the source definitions for cube.
--
-- Time dimension.
--
CREATE DIMENSION times
LEVEL day IS (times.day_key)
LEVEL month IS (times.month_id)
LEVEL calendar_quarter IS (times.calendar_quarter_id)
LEVEL calendar_year IS (times.calendar_year_id)
HIERARCHY calendar (
day CHILD OF
month CHILD OF
calendar_quarter CHILD OF
calendar_year)
ATTRIBUTE day DETERMINES (day_description)
ATTRIBUTE month DETERMINES (month_name, month_end_date)
ATTRIBUTE calendar_quarter DETERMINES (calendar_quarter_name, calendar_quarter_end_date)
ATTRIBUTE calendar_year DETERMINES (calendar_year_name, calendar_year_end_date);
--
-- Product dimension.
--
CREATE DIMENSION products
LEVEL item IS (products.item_key)
LEVEL subtype IS (products.subtype_key)
LEVEL type IS (products.type_key)
LEVEL category IS (products.category_key)
LEVEL department IS (products.department_key)
HIERARCHY departments (
item CHILD OF
subtype CHILD OF
type CHILD OF
category CHILD OF
department)
ATTRIBUTE item DETERMINES (item_name)
ATTRIBUTE subtype DETERMINES (subtype_name)
ATTRIBUTE type DETERMINES (type_name)
ATTRIBUTE category DETERMINES (category_name)
ATTRIBUTE department DETERMINES (department_name);
--
-- Customer dimension.
--
CREATE DIMENSION customers
LEVEL customer IS (customers.customer_key)
LEVEL city IS (customers.city_key)
LEVEL state IS (customers.state_province_key)
LEVEL country IS (customers.country_key)
LEVEL region IS (customers.region_key)
HIERARCHY regions (
customer CHILD OF
city CHILD OF
state CHILD OF
country CHILD OF
region)
ATTRIBUTE customer DETERMINES (customers.customer_number)
ATTRIBUTE city DETERMINES (customers.city_name)
ATTRIBUTE state DETERMINES (customers.state_province_name)
ATTRIBUTE country DETERMINES (customers.country_name)
ATTRIBUTE region DETERMINES (customers.region_name);
--
-- Channel dimension.
--
CREATE DIMENSION channels
LEVEL channel IS (channels.channel_key)
LEVEL class IS (channels.class_key)
HIERARCHY classes (
channel CHILD OF
class)
ATTRIBUTE channel DETERMINES (channels.channel_name)
ATTRIBUTE class DETERMINES (channels.class_name);

The next step is to create a table-based materialized view that summarizes data to the level you want to load into the cube. Note that you do not need load data into this materialized view (use the BUILD DEFERRED option). The DBMS_CUBE.CREATE_MVIEW program will simply use the table-based MV to understand how to query the source fact table and to find to the measures to be added to the cube.

--
-- Create the table-based materialized view. In this example, the table-based
-- materialized view aggregates data from the day to month levels in time
-- and the customer to city levels in customer.
--
-- MV is build deferred because the cube doesn't need data from the MV (it just
-- uses the MV's definition.
--
CREATE materialized VIEW sales_mon_ite_cit_cha_mv build deferred
AS
SELECT t.month_name,
p.item_name,
cu.city_name,
ch.channel_name,
SUM(f.sales) sales,
SUM(f.quantity) quantity
FROM times t,
products p,
customers cu,
channels ch,
sales_fact f
WHERE t.day_key = f.day_key
AND p.item_key = f.product
AND cu.customer_key = f.customer
AND ch.channel_key = f.channel
GROUP BY t.month_name,
p.item_name,
cu.city_name,
ch.channel_name;

Finally, run the DBMS_CUBE.CREATE_MVIEW program.

--
-- Create and load the cube-organized materialized view.
--
declare
myCubeMv varchar2(32);
begin
myCubeMv :=
dbms_cube.create_mview(
mvOwner =>user,
mvName =>'sales_mon_ite_cit_cha_mv',
sam_parameters=>'logDest=serverout,build=immediate' );
end;
/

There's several parameters to this program, but I found the defaults to be very reasonable. It built my cube as compressed, partitioned by quarter and pre-aggregation settings of 0 for the top partition and 35 for the bottom partition. You can control any of these settings with the sam_parameters argument. You can also defer the loading of the cube and load the cube later with the DBMS_CUBE.BUILD or DBMS_MVIEW.REFRESH programs.

The cube can be viewed and edited in Analytic Workspace Manager, so you can use it to check the design or make changes such as adding custom measures.

As with any other materialized view, you will need to make sure all the necessary constraints are in place on the base tables. I used AWM’s Relational Access Advisor feature (in the Cube / Materialized View tab) to generate constraint recommendations.

Automatically Create Oracle Business Intelligence Repositories for Oracle11g Cubes

An OBIEE Plug-in for Oracle11g OLAP was released on the OLAP Option page on the Oracle Technical Network today. The plug-in allows you to automatically create OBIEE repositories that can be used to query Oracle11g cubes. It's really, really easy. The OBIEE repository created by the plug-in allows OBIEE to query all content of the cube, including summary data and advanced calculations. This is a great way to demonstrate how the Oracle cube can enhance the performance and analytic content of Oracle Business Intelligence Enterprise Edition.

The OBIEE Plug-in is used with Analytic Workspace Manager (AWM), the administrative tool of the Oracle OLAP Option. Only the most basic understanding of the OBIEE repository is required to use this AWM plug-in. The plug-in creates fully configured a physical database, business model and mapping layer and presentation catalog in the OBIEE repository.

Here are some useful links:

The Oracle OLAP OTN page.
The OBIEE Plug-in instruction sheet.
A demonstration video.
Download the plug-in.

Call for OpenWorld 2009 papers has started...

This years Oracle OpenWorld conference will be held on October 11-15 at the Moscone Center in San Francisco. A few days ago the OpenWorld website opened for customers and partners to submit proposals for presentations to be included in this years conference. This year there will be more sessions allocated to customer and partner papers which increases your chances of having your paper selected. The OpenWorld conference is a great opportunity to present your ideas to the Oracle Community and to add to increase your own knowledge by attending other presentations. Most importantly,all the Oracle OLAP Blog team will be onsite at the demo grounds so come and say "Hi" and checkout the latest cool 11g OLAP demos.

Presenting at OpenWorld is an amazing experience and I would recommend it to anyone and everyone. There is nothing quite like it. The deadline for submission is April 19 so act fast!

Are there any benefits to presenting? Of course - but the benefits/goody bags change every year and I am not sure what we will be offering this year. Obviously you get a complimentary Oracle OpenWorld Full Conference pass which means you can get into the vast majority of events during the conference. You get access to the whole presentation catalog via the OpenWorld website. On top of the Oracle goodies, most of the vendors in the demo grounds hand out lots of other goodies (it pays to bring an extra suitcase) and most evenings someone, somewhere, is usually offering free beer. What more could you ask for!

What should I present on? The topic is completely up to you. You can talk about a recent project or a specific database feature and how you used it. Just pick any topic you think will be of use and interest to other people.

The 2009 Conference web page is here. Look for the red link "Now Live" and good luck.

Excel Update - Beta Version of MDX Provider Now Available

A quick update on using Excel to query an Oracle11g cube ....

Yesterday Simba announced availability beta 1 MDX provider for Oracle OLAP. I've been using prerelease version of the provider for a few months now and have been very happy with it. There's still some features to be added and some performance work to be done, but I think the beta 1 version is well worth trying. In my experience, the MDX provider is pretty stable and I very much like Excel as a front end to Oracle cubes.

To sign up, visit the MDX Provider for Oracle page at http://simba.com/MDX-Provider-for-Oracle-OLAP.htm . You can also find a video demo on this page.

Using Excel with Oracle OLAP

I did two webcasts about using Excel with Oracle OLAP this week. They were incredibly sucessful. If you've never looked at Excel 2007 pivot tables, I'll tell you that they are very good. Using the MDX Provider for Oracle OLAP, Excel makes a direct/native connection to the Oracle database and has the ability to query OLAP cubes in a multidimensional context.

I won't every try do to justice to this solution in this blog. It's really best to just see it in action. I've posted a video on simba.com (go to http://simba.com/MDX-Provider-for-Oracle-OLAP.htm and click on the product demo link).

You can also sign up for the beta test at simba.com. Do yourself a favor - try it out.



Querying an Oracle OLAP Cube using Excel.

New! Oracle OLAP Overview Video

A new fifteen minute Oracle OLAP video has been released - describing the benefits of Oracle OLAP in the context of the Oracle Database, Data Warehouse and Business Intelligence platform.

You can either watch this video online, or download for replay on an iPod:
• Click here to view the video now
• Click here to download the iPod video

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.

New Tutorial - Creating Interactive APEX Reports Over OLAP 11g Cubes

The latest in a recent series of 11g OLAP tutorials has been added to the Oracle OLAP product page on OTN.

The tutorial is called 'Creating Interactive APEX Reports Over OLAP 11g Cubes' and shows how you use Oracle Application Express (APEX) to create an interactive sales analysis report that runs against OLAP 11g data.



You learn how to query and create analytic reports of OLAP 11g cubes, including both stored and calculated measures. You also learn how to apply query techniques that leverage unique characteristics of OLAP 11g cubes.

The other tutorials already published in this series are:

• Building OLAP 11g Cubes
• Querying OLAP 11g Cubes
• Creating BI EE Metadata for OLAP 11g Cubes
• Querying OLAP 11g Data Using Oracle BI Answers

Oracle OLAP Newsletter - February 2009

The latest Oracle OLAP newsletter, February 2009, has been posted onto OTN and is available by clicking here

The customer feature this time is R.L. Polk who have used 11g OLAP to simplify their delivery of aggregate data through the use of cube organised materialised views. This is a fantastic case study which captures the true value of this functionality (note the dramatic improvements in both build and query times), and of having Oracle OLAP embedded in the Oracle Database.

The highlights of the Product Update section this time are the release of the latest version of AWM 11g (11.1.0.7B), and also a new version of the BI Spreadsheet Add-in (10.1.2.3.0.1 - enough digits?!) which now includes support for Excel 2007.

Gartner identifies Oracle OLAP as a key strength in Oracle's DW proposition

You may have already seen elsewhere that Gartner recently published an updated magic quadrant for Data Warehouse Database Management Systems.

It was a little bit surprising to see that the HP Oracle Exadata Storage Server and the HP Oracle Database Machine hadn't provided a bigger boost to Oracle's rating (although, as you would expect, Oracle is still firmly entrenched in the leaders quadrant). Perhaps it is still a little early for their significance to be fully appreciated. There is likely to be a more pronounced improvement in Oracle's rating the next time this magic quadrant is published.

Of particular interest to this blog is the fact that Oracle OLAP is identified as a key strength of Oracle's Data Warehousing proposition. The research highlights the enhanced materialized view and cube management features added in 11g, along with transparent SQL access and incremental update.

It is really great to hear such a significant endorsement of Oracle's strategy to embed the Oracle OLAP engine inside the Oracle Database.

If you want to read more about what 11g OLAP could do for your Oracle Data Warehouse, then check out this white paper

Get hands-on with 11g OLAP & Oracle Business Intelligence Enterprise Edition

Happy New Year to everyone!!

Following the announcement last month of two new 'Oracle By Example' tutorials on building and querying 11g OLAP cubes, here are the details of a further two tutorials on working with 11g OLAP and Oracle Business Intelligence Enterprise Edition (OBIEE).

The first tutorial shows how to create OBIEE metadata over 11g OLAP cubes

(if you are using 10g OLAP, use this tutorial to create OBIEE metadata instead)

The second new tutorial shows how to query 11g OLAP cubes using OBIEE Answers - using the metadata repository created during the first tutorial

While OBIEE Answers is a widely used query tool for Oracle OLAP (for an example, see the article on Micros Systems), it would be interesting to hear from people using some of the other components of OBIEE, especially some of the newly integrated 'plus' components like Smart View which appears to be receiving a lot of development effort from the BI/EPM folks.

Please feel free to share any experiences you might have in our comments section.