Business Intelligence – Oracle

Oracle 10g/11g – OLAP, CUBE and ROLLUP

Posted by Venkatakrishnan J on August 30, 2007

If you had gone through the list of features in Oracle 11g, one of the most important advancements/features in the BI space would be the introduction of Oracle OLAP as part of the DB optimizer (in the form of MV query rewrite). But for users who do not have the luxury of using Oracle OLAP 11g (or even 11g database) in their environment, dont lose hope yet :-). CUBE and ROLLUP functions had undergone quite a few advancements in themselves while in 10g itself and they can indeed mimic an OLAP cube starting from 10g. Let me just give an example here. All the advances done in CUBE and ROLLUP functions are documented here. If you had gone through my previous blog entry here on using OLAP 11g, i had used a specific example to create a cube. I would be using the same one here.

Dimensions:

Customers:

      Hierarchy:   All      ->      Customer Gender      ->      Customer Name

Product:

      Hierarchy:   All   ->   Product Pack Size   ->   Product Name

Times:

      Hierarchy:   All   ->   Fiscal Year   ->   Fiscal Month   -> Fiscal Day

Measures:

      Quantity Sold

      Amount Sold

Oracle 10g/11g now supports Hierarchical cubes within queries. As you would know, ROLLUP is a feature wherein one can have summed up data across a hierarchy. What is new in 10g & 11g is, one can have multiple ROLLUPs (more like hierarchies) in the group by clause which in turn would take a cartesian product of all the rollups in the group by clause. In order to get a cube like data for the above measures and dimensions, the query would look like this

SELECT CUST_GENDER, CUST_FIRST_NAME||CUST_LAST_NAME, PROD_PACK_SIZE, PROD_NAME, FISCAL_YEAR, FISCAL_MONTH_DESC, DAY_NAME, SUM(QUANTITY_SOLD) AS QUANTITY, SUM(AMOUNT_SOLD) AS SALES
FROM
SALES a, CUSTOMERS b, PRODUCTS c, TIMES d
where
a.CUST_ID = b.CUST_ID and
a.PROD_ID = c.PROD_ID and
a.TIME_ID = d.TIME_ID
GROUP BY
ROLLUP(CUST_GENDER, CUST_FIRST_NAME||CUST_LAST_NAME),
ROLLUP(PROD_PACK_SIZE, PROD_NAME),
ROLLUP(FISCAL_YEAR, FISCAL_MONTH_DESC, DAY_NAME)

               snapo1.jpg

And the plan for the above query would be like this

              snapo2.jpg

The above generates the same set of data that Oracle OLAP produces. But of course, oracle OLAP provides more features like partitioning and many more. This is just to kindle your interest on the enhancements that have been made in this space. If you want to create a MV,

CREATE MATERIALIZED VIEW SALES_ROLLUP_MV
ENABLE QUERY REWRITE AS
SELECT CUST_GENDER, CUST_FIRST_NAME||CUST_LAST_NAME as CUSTOMER_NAME, PROD_PACK_SIZE, PROD_NAME, FISCAL_YEAR, FISCAL_MONTH_DESC, DAY_NAME, SUM(QUANTITY_SOLD) AS QUANTITY, SUM(AMOUNT_SOLD) AS SALES
FROM
SALES a, CUSTOMERS b, PRODUCTS c, TIMES d
where
a.CUST_ID = b.CUST_ID and
a.PROD_ID = c.PROD_ID and
a.TIME_ID = d.TIME_ID
GROUP BY
ROLLUP(CUST_GENDER, CUST_FIRST_NAME||CUST_LAST_NAME),
ROLLUP(PROD_PACK_SIZE, PROD_NAME),
ROLLUP(FISCAL_YEAR, FISCAL_MONTH_DESC, DAY_NAME)

The above is called as a hierarchical cube. It does not store all the combinations like in a cube. Instead it stores the rollup of data in the same hierarchy across different hierarchies. For example, this cube will not generate value for sales when sales is analyzed by Customer Name, Product name and Fiscal month since their actual path in the hierarchy is not specified.

 If one wants to generate an actual cube, the query would look like this

SELECT CUST_GENDER, CUST_FIRST_NAME||CUST_LAST_NAME, PROD_PACK_SIZE, PROD_NAME, FISCAL_YEAR, FISCAL_MONTH_DESC, DAY_NAME, SUM(QUANTITY_SOLD) AS QUANTITY, SUM(AMOUNT_SOLD) AS SALES
FROM
SALES a, CUSTOMERS b, PRODUCTS c, TIMES d
where
a.CUST_ID = b.CUST_ID and
a.PROD_ID = c.PROD_ID and
a.TIME_ID = d.TIME_ID
GROUP BY
CUBE(CUST_GENDER, CUST_FIRST_NAME||CUST_LAST_NAME),
CUBE(PROD_PACK_SIZE, PROD_NAME),
CUBE(FISCAL_YEAR, FISCAL_MONTH_DESC, DAY_NAME)

The above does not take care of best practices to create MVs or the queries. But this is just to show the advancements that 10g/11g provides for an end user. Can anyone let me know whether these were available in 9i? I believe in 9i, correct me if i am wrong here, one would not be able to create MVs out of the queries using CUBE clause.

Update: CUBE and ROLLUP are features that were available from 8i. Thanks for the comments Laurent.

About these ads

2 Responses to “Oracle 10g/11g – OLAP, CUBE and ROLLUP”

  1. CUBE and ROLLUP are 8i new features
    GROUPING SETS is a 9i feature

  2. Venkatakrishnan J said

    Thanks for your comments Laurent. I have never tried these in a 9i/8i instance before. But again, one of the main reasons why i wrote this article was to explore the options CUBE and ROLLUP provide to end users. But thanks for the info again.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 151 other followers

%d bloggers like this: