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

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

lets look at the Dimensional model at staging layer

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 ROLAP example

How ROLLUP works

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

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

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);