Oracle BI EE 10.1.3.4.1 – Ago and ToDate – An alternative SQL on Oracle 10g and above – Equivalence to MDX – MODEL Clause – Part 1
Posted by Venkatakrishnan J on April 30, 2009
One of the few things that i have been exploring recently is the ability to create sql queries similar to the ones created by MDX. The basic idea behind this is the fact that MDX has excellent calculation capabilities. It also has excellent member based selection queries which in most cases would be relevant to relational reporting as well. In relational reporting, we are in most cases tied to the columns and hence for some use cases like AGO and TODATE, the queries generated are actually not necessary at all. For example, lets take the Ago function provided by BI EE. For demonstration purposes, lets create a Prior Year Measure on the Global BMM as shown below
Now, lets create a report as shown below
All the above report does is, it calculates the Units sold for each year and would also display the prior year units sold as well. There are 2 ways of generating queries for such a report
1. Generate Units and Generate Prior Year Units both from the base time and fact tables.
2. Generate Units from the base time and fact tables. Then generate the Prior Year units based on Units data.
Currently BI EE generates queries using the 1st approach mentioned above. But sometimes you realize that working on the resultant dataset is generally more efficient and predictable rather than going against the base tables again for the calculated dataset. If you look at the query generated to produce the above result, you would notice that the query generated is pretty complex and in fact sometimes can generate wrong results if you dont have the proper chronological key.
select D1.c2 as c1, D1.c1 as c2, D1.c3 as c3 from (select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from (select sum(T10976.UNITS) as c1, D3.c3 as c2, D3.c2 as c3, ROW_NUMBER() OVER (PARTITION BY D3.c2 ORDER BY D3.c2 ASC) as c4 from UNITS_FACT T10976, (select distinct D1.c1 + 1 as c1, D1.c2 as c2 from (select min(D1.c1) over (partition by D1.c3) as c1, D1.c2 as c2 from (select Case when case D1.c4 when 1 then D1.c2 else NULL end is not null then Rank() OVER ( ORDER BY case D1.c4 when 1 then D1.c2 else NULL end ASC NULLS LAST ) end as c1, D1.c2 as c2, D1.c3 as c3 from (select T10939.MONTH_ID as c2, T10939.YEAR_ID as c3, ROW_NUMBER() OVER (PARTITION BY T10939.YEAR_ID ORDER BY T10939.YEAR_ID DESC) as c4 from TIME_DIM T10939 ) D1 ) D1 ) D1 ) D4, (select distinct min(D1.c1) over (partition by D1.c2) as c1, D1.c2 as c2, D1.c3 as c3 from (select Case when case D1.c5 when 1 then D1.c4 else NULL end is not null then Rank() OVER ( ORDER BY case D1.c5 when 1 then D1.c4 else NULL end ASC NULLS LAST ) end as c1, D1.c2 as c2, D1.c3 as c3 from (select T10939.YEAR_ID as c2, T10939.YEAR_DSC as c3, T10939.MONTH_ID as c4, ROW_NUMBER() OVER (PARTITION BY T10939.YEAR_ID ORDER BY T10939.YEAR_ID DESC) as c5 from TIME_DIM T10939 ) D1 ) D1 ) D3 where ( T10976.MONTH_ID = D4.c2 and D3.c1 = D4.c1 ) group by D3.c2, D3.c3 ) D1 where ( D1.c4 = 1 ) ) D1 order by c3
Sometimes the queries above are not necessary. This is where Oracle 10g’s MODEL clause can come to the rescue. MODEL clause was introduced to visualize a relational data in the form of a multidimensional data source. So, it typically works on top of a GROUP BY sub query (visualize this as a summarized cube). In the future blog entries (also in a planned whitepaper) i would cover how the MODEL clause is similar to visualizing data in a Essbase cube (both MDX as well as Calc Scripts). Today, i will just show how the above Ago query can be re-organized to a MODEL query. The query is given below
SELECT YEAR_DSC, SALES, PRIOR_YEAR FROM (SELECT YEAR_DSC, SUM(UNITS) SALES FROM TIME_DIM A, UNITS_FACT B WHERE A.month_id = b.month_id (+) GROUP BY YEAR_DSC) MODEL DIMENSION BY (YEAR_DSC) MEASURES (SALES,0 PRIOR_YEAR) RULES (PRIOR_YEAR[ANY] = SALES[cv(YEAR_DSC)-1]) ORDER BY 1
Currently BI EE does not create the MODEL clause based sql queries out of the box. Neither it is possible to hardcode them directly into the BI Server. I am just hoping that this feature would be added in the coming releases. And, I have just scratched the surface of the capabilities this MODEL clause can offer. There are quite a few use cases wherein this can be extremely useful/effecient. I would cover them in the future blog entries.