Visualizing relational data as Essbase/OLAP cubes – Partition Outer Joins and MODEL Clauses

In a prior blog entry here, i had shown the advantages of using the MODEL clause. I also showed how the MODEL clause can be used to visualize the relational data in a multi-dimensional format. In most cases, using the MODEL clause alone can be sufficient. Just to recap, the MODEL clause divides your resultant data into 3 parts

Partition By set of Dimensions – Similar to Index entries in Essbase (sparse dimensions)
Dimension By set of Dimensions – Similar to Blocks of Essbase (dense dimensions)
Measure Dimension

So, whenever you use MODEL, Oracle constructs a multidimensional array for each partition by the dimension(s) combination value. The multi-dimensional array would be dimensioned by the dimensions specified in the DIMENSION BY clause. Though a multi-dimensional array is created, the created array contains only the valid intersections present in the fact table and not every intersections that are possible. For example, consider the sql query below

SELECT FISCAL_YEAR_DSC,CHANNEL_DSC,

UNITS

FROM

(

select sum(T10976.UNITS) as UNITS, T10939.FISCAL_YEAR_DSC as FISCAL_YEAR_DSC, T10881.CHANNEL_DSC as CHANNEL_DSC

from

CHANNEL_DIM T10881,

TIME_DIM T10939,

UNITS_FACT T10976

where

( T10881.CHANNEL_ID = T10976.CHANNEL_ID and T10939.MONTH_ID = T10976.MONTH_ID )

group by T10881.CHANNEL_DSC, T10939.FISCAL_YEAR_DSC)

MODEL

PARTITION BY (FISCAL_YEAR_DSC)

DIMENSION BY (CHANNEL_DSC)

MEASURES (UNITS) RULES (UNITS['Total'] = SUM(UNITS)[ANY])

ORDER BY 1,2

As you see, the above query produces Sub-Totals for every Fiscal Year. But if you notice in the output of the query, we would be missing the year FY-06 which has no transactions in the fact table. Similarly even the Channel Television is missing in the output since it does not have any fact level transactions. In some cases, though there are no transactions we need the capability to show the non-existent (in fact tables) dimension values in the output(data densification). This is where Partition Outer Joins come in very handy. One can visualize partition outer join as a Cartesian product of dimensions. The major difference between a Cartesian product and the partition outer join is, one can have a control on Cartesian product using Partition Outer Join. Stewart has already covered Partition Outer Joins in a prior blog entry here.

For example, lets say we have a requirement to show all the dimension values of Channel and only the transaction values of Fiscal year in our above report i.e. we need to bring in the Television Channel as well into our above report for Year.

Usually we would approach this using a normal outer join with the Channel Dimension as shown below.

SELECTA.FISCAL_YEAR_DSC FISCAL_YEAR_DSC,B.CHANNEL_DSC CHANNEL_DSC,NVL(A.UNITS,0) UNITS
FROM
( select sum(T10976.UNITS) as UNITS, T10939.FISCAL_YEAR_DSC as FISCAL_YEAR_DSC, T10881.CHANNEL_DSC as CHANNEL_DSC
from
CHANNEL_DIM T10881,
TIME_DIM T10939,
UNITS_FACT T10976
where ( T10881.CHANNEL_ID = T10976.CHANNEL_ID and T10939.MONTH_ID = T10976.MONTH_ID )
group by T10881.CHANNEL_DSC, T10939.FISCAL_YEAR_DSC) A
RIGHT OUTER JOIN
(SELECT DISTINCT CHANNEL_DSC FROM CHANNEL_DIM) B ON (A.CHANNEL_DSC = B.CHANNEL_DSC)

But again the above outer join only provides a single row for Television channel. But our requirement is to have the same Television Channel for every Fiscal Year that has transactions. This is where Partition Outer Join would be of great help. Lets modify the above query to the one shown below

SELECTA.FISCAL_YEAR_DSC FISCAL_YEAR_DSC,B.CHANNEL_DSC CHANNEL_DSC,NVL(A.UNITS,0) UNITS
FROM
(
select
sum(T10976.UNITS) as UNITS,
T10939.FISCAL_YEAR_DSC as FISCAL_YEAR_DSC,
T10881.CHANNEL_DSC as CHANNEL_DSC
from
CHANNEL_DIM T10881,
TIME_DIM T10939,
UNITS_FACT T10976 where
( T10881.CHANNEL_ID = T10976.CHANNEL_ID and T10939.MONTH_ID = T10976.MONTH_ID )
group by T10881.CHANNEL_DSC, T10939.FISCAL_YEAR_DSC) A
PARTITION BY (A.FISCAL_YEAR_DSC)
RIGHT OUTER JOIN
(SELECT DISTINCT CHANNEL_DSC FROM CHANNEL_DIM) B
ON (A.CHANNEL_DSC = B.CHANNEL_DSC)

Now, if you notice we now have non-existent(in the fact table) all channel dimension values for every transactional Year. We can extend the same concept to show both non-existent year (FY-06) as well as channel dimension values using the query below.

SELECTA.FISCAL_YEAR_DSC,B.CHANNEL_DSC,NVL(A.UNITS,0)
FROM
(
SELECT B.FISCAL_YEAR_DSC FISCAL_YEAR_DSC, A.CHANNEL_DSC CHANNEL_DSC, NVL(A.UNITS,0) UNITS
FROM
(
select sum(T10976.UNITS) as UNITS, T10939.FISCAL_YEAR_DSC as FISCAL_YEAR_DSC, T10881.CHANNEL_DSC as CHANNEL_DSC
from
CHANNEL_DIM T10881,
TIME_DIM T10939,
UNITS_FACT T10976
where ( T10881.CHANNEL_ID = T10976.CHANNEL_ID and T10939.MONTH_ID = T10976.MONTH_ID )
group by T10881.CHANNEL_DSC, T10939.FISCAL_YEAR_DSC) A
PARTITION BY (A.CHANNEL_DSC)
RIGHT OUTER JOIN
(SELECT DISTINCT FISCAL_YEAR_DSC FROM TIME_DIM) B
ON (A.FISCAL_YEAR_DSC = B.FISCAL_YEAR_DSC)) A
PARTITION BY (A.FISCAL_YEAR_DSC)
RIGHT OUTER JOIN (SELECT DISTINCT CHANNEL_DSC FROM CHANNEL_DIM) B
ON (A.CHANNEL_DSC = B.CHANNEL_DSC)

This can further be modified to include the sub-totals using the MODEL clause.

SELECTFISCAL_YEAR_DSC,CHANNELS,NVL(UNITS,0)
FROM
(SELECT B.FISCAL_YEAR_DSC FISCAL_YEAR_DSC, A.CHANNEL_DSC CHANNEL_DSC, NVL(A.UNITS,0) UNITS
FROM
(
select
sum(T10976.UNITS) as UNITS, T10939.FISCAL_YEAR_DSC as FISCAL_YEAR_DSC, T10881.CHANNEL_DSC as CHANNEL_DSC
from
CHANNEL_DIM T10881, TIME_DIM T10939, UNITS_FACT T10976
where
( T10881.CHANNEL_ID = T10976.CHANNEL_ID and T10939.MONTH_ID = T10976.MONTH_ID )
group by T10881.CHANNEL_DSC, T10939.FISCAL_YEAR_DSC) A
PARTITION BY (A.CHANNEL_DSC)
RIGHT OUTER JOIN
(SELECT DISTINCT FISCAL_YEAR_DSC FROM TIME_DIM) B
ON (A.FISCAL_YEAR_DSC = B.FISCAL_YEAR_DSC)) A
PARTITION BY (A.FISCAL_YEAR_DSC)
RIGHT OUTER JOIN (SELECT DISTINCT CHANNEL_DSC FROM CHANNEL_DIM) B
ON (A.CHANNEL_DSC = B.CHANNEL_DSC)
MODEL
PARTITION BY (FISCAL_YEAR_DSC)
DIMENSION BY (B.CHANNEL_DSC CHANNELS)
MEASURES (UNITS) RULES (UNITS['Total'] = SUM(UNITS)[ANY])
ORDER BY 1,2

The primary advantage of using the Partition By Outer Join and MODEL clause together is the fact that one can now do custom member based allocations, calculations etc directly on a relational data source. If you do not have Essbase/Oracle OLAP and would still like to do the complex data manipulation on a relational source, MODEL and Partition By Outer Joins can come in very handy.