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
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);
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
Now we have the ROLAP cube ready we can query it using native sql
Querying ROLAP cube can be leveraged using GROUPING_ID output
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
Q) Total Sales in 2008 , for Australia and state New South Wales all city’s
GROUPING SETS
Grouping sets allow selectively group in to sets and can also be used with ROLLUP to customize result
Sqljunkieshare 