Creating, querying, tuning hierarchical ROLAP cubes in Oracle

This post will explain how to  use  Oracle grouping functions to leverage ROLAP capability in Oracle

To begin with some introduction Multi dimensional data marts or an Enterprise data warehouse has become need than necessity as in the end data that drives the business

Having right staging area and presentation layer is very important, there are many Bi tools out there these tools helps the users to create multi dimensional OLAP cubes up on statging layer by MOLAP, ROLAP, HOLAP

indepth analysis of these storage methods are outside the scope of our discussion

ROLAP stands for Relational Online Analytical Processing

ROLAP features

  • ROLAP is considered to be more scalable in handling large data volumes, especially models with dimensions with very high cardinality (i.e., millions of members).
  • With a variety of data loading tools available, and the ability to fine tune the ETL code to the particular data model, load times are generally much shorter than with the automated MOLAP loads.
  • The data are stored in a standard relational database and can be accessed by any SQL reporting tool (the tool does not have to be an OLAP tool).
  • ROLAP tools are better at handling non-aggregatable facts (e.g., textual descriptions). MOLAP tools tend to suffer from slow performance when querying these elements.
  • By decoupling the data storage from the multi-dimensional model, it is possible to successfully model data that would not otherwise fit into a strict dimensional model.
  • The ROLAP approach can leverage database authorization controls such as row-level security, whereby the query results are filtered depending on preset criteria applied, for example, to a given user or group of users (SQL WHERE clause).

ROLAP simplifies the report and analysis development by 100 times when compared to MOLAP

Oracle Provides following grouping functions

  • ROLLUP
  • CUBE
  • GROUPING SETS

Rolap cube over view

In order to understand we will take a simple example a online retail bike selling we will have simple multidimentional data model with

Dimensions

  • dimgeog(loacation)
  • dimdate(Date)
  • dimproduct(Product)

Fact

  • Sales

oracle cube

lets look at the Dimensional model at staging layer

DATA MODELS

ROLLUP enables us to calculate multiple levels of subtotals across a specified group of dimensions(Hierarchy) , allowing us to create subtotals for different levels for hierarchy’s  in dimensions

ROLLUP creates subtotals for n+1 levels of subtotals where n is the number of grouping columns

Simple Rollup

Simple ROLAP example

rollup examples1

How ROLLUP works

rollup examples2

Understanding NULLS in ROLLUP , Readability and Querying, controlling grouping

NULLS returned by GORUP BY extensions are not always always NULL, a NULL indicate that its a subtotal, in order to differentiate against a traditional NULL value

Oracle provides two functions GROUPING and GROUPING_ID

GROUPING(Column name)  will result 1 if a row is subtotaled on that given column name  and returns 0 if its null value or if row is not subtotaled

Using DECODE and GROUPING  functions we can rewrite above query for better readability

rollup examples3

rollup examples4

In order to build a complete cube we have to also need to include other dimensions as we discussed above geog, product, So we will add to these aggregation levels to our existing query

rollup examples5

SELECT DECODE (grouping(dt.fiscalyear),1,’All years’,dt.fiscalyear) AS YEAR,
DECODE (grouping(dt.fiscalquarter),1,’All Quarters’ ,dt.fiscalquarter) AS quarter,

DECODE (grouping(dt.fiscalsemester),1,’All Semesters’ ,dt.fiscalsemester) AS semister,

DECODE (grouping(dt.weeknumberofyear),1,’All Weeks’ ,dt.weeknumberofyear) AS weeknmyr,
DECODE (grouping(dg.englishcountryregionname),1,’All’,dg.englishcountryregionname) AS country,
DECODE (grouping(dg.stateprovincename),1,’All State”s’ ,dg.stateprovincename) AS state,
DECODE (grouping(dg.city),1,’All City”s’ ,dg.city) AS city,
DECODE (grouping(dp.englishproductname ),1,’All Products’,dp.englishproductname) AS product,
DECODE (grouping(prsubcat.englishproductsubcategoryname),1,’All Categories’
, prsubcat.englishproductsubcategoryname) AS productsubcat,
SUM( fs.salesamount) AS sales,
GROUPING_ID(fiscalyear, dt.fiscalquarter,dt.fiscalsemester
,dt.weeknumberofyear,englishcountryregionname,stateprovincename,city) AS GROUPID
FROM factsales fs
INNER JOIN dimdate dt
ON fs.orderdatekey = dt.datekey
INNER JOIN dimgeog dg
ON dg.geographykey = fs.salesterritorykey
INNER JOIN dimproduct dp
ON dp.productkey = fs.productkey
INNER JOIN dimproductsubcat prsubcat
ON prsubcat.productsubcategorykey = dp.productsubcategorykey
WHERE dp.englishproductname IN (‘Road Tire Tube’)
GROUP BY rollup(fiscalyear, dt.fiscalquarter,dt.fiscalsemester,dt.weeknumberofyear),
ROLLUP( prsubcat.englishproductsubcategoryname,dp.englishproductname),
ROLLUP(englishcountryregionname,stateprovincename,city);

Now we have the query we have build a cube using Materialized views

First step is to build materialized views logs

Second build materialized view

Script can be download from here

https://www.dropbox.com/s/9eljxv6p91s8hkz/materialize%20views.sql

oralce materialized view

Now we have the ROLAP cube ready we can query it using native sql

Querying ROLAP cube can be leveraged using GROUPING_ID output

ROLAP cube 1

Using grouping_id to query to ROLAP cube

Q) Retrieve total sales for all years  and country “Australia”, all city’s all states  for “Road Tire Tube”

To find group id

GROUPING_ID(FISCALYEAR, FISCALQUARTER, FISCALSEMESTER, WEEKNUMBER,COUNTRY, STATE, CITY,PRODUCTSUBCATEGORY, PRODUCTNAME)

1                                 1                                          1                                     1                    0                     1             1                         0                                               1

groupid query 1

Q)  Total Sales in 2008 , for Australia  and state New South Wales all city’s

groupid query 2

GROUPING SETS 

Grouping sets allow selectively group in to sets and can also be used with ROLLUP to customize result

grouping sets

grouping sets equavalent

Advertisements

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