Business Intelligence – Oracle

Archive for April, 2009

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.

Posted in All Posts, EPM, Hyperion Essbase, OBI EE Plus | Leave a Comment »

Oracle BI EE 10.1.3.4.1 – Hyperion Essbase Security Integration – Resolved

Posted by Venkatakrishnan J on April 27, 2009

Now that 10.1.3.4.1 is out, i thought i would test out the security integration of BI EE and Essbase. If you have read my blog entries here and here, you would have noticed that there was a known bug around the 10.1.3.4 version of BI EE. Though there was a patch that was out earlier to resolve this, i never got around to install the patch. 10.1.3.4.1 is a patch release, so all the intermediary patches should have been bundled with this release as well.

To see how this has been resolved, lets use the Global cube that we had built in one of the blog entries before. We shall be testing it with 2 users. One is the admin and the other would be admin1. admin would be the super user. admin1 would just have access to the Global cube. And a filter would be assigned to this user. So to start lets first create a filter in the Global Cube as shown below

create filter 'Global'.'Global'.'Sample'
read on '"1999","Units","All Channels","Total Product"';

Once this filter is created, assign the filter to the admin1 user.

       

So basically, admin1 has access to only one intersection. Every other intersection would produce #NoAccess in Excel addin. As a next step lets create a report out of the Global cube as the admin user.

       

Now lets create a report in BI EE out of this global with admin user.

       

Lets switch this user to admin1 in the connection pool and try viewing the same report.

       

Thats so much better. So, by default BI EE does displays the #NoAccess intersections as null. The MDX generated still remains the same.

With
  set [Channel2]  as '[Channel].Generations(2).members'
  set [Product2]  as '[Product].Generations(2).members'
  set [Time2]  as '[Time].Generations(2).members'
select
  { [Measures].[Units]
  } on columns,
  NON EMPTY {crossjoin ({[Channel2]},crossjoin ({[Product2]},{[Time2]}))} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Global.Global]

       

Lets change the filter a little bit as shown below to include more intersections for admin1’s access.

create or replace filter 'Global'.'Global'.'Sample'
read on '@IDESCENDANTS("Time"),"Units",@IDESCENDANTS("All Channels"),"Total Product"';

 

With
set [Channel2] as '{[Channel].[All Channels]}'
set [Product2] as '{[Product].[Total Product]}'
set [Product3] as 'Generate({[Product2]}, Descendants([Product].currentmember, [Product].Generations(3),SELF), ALL)'
set [Time2] as '{[Time].[1998]}'
select
{ [Measures].[Forecast Units], [Measures].[Units] } on columns,
NON EMPTY {crossjoin ({[Channel2]},crossjoin ({[Product3]},{[Time2]}))} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Global.Global]

       

It does look like this indeed works. Well this makes it more interesting. We now have a complete solution and sure opens up lot more opportunities for integration.

Posted in All Posts, EPM, Hyperion Essbase, OBI EE Plus | 2 Comments »

Oracle BI EE 10.1.3.4.1 – Running Hyperion Essbase Data Loads/Calculations from BI EE – Using JAPI and JSPs

Posted by Venkatakrishnan J on April 26, 2009

As consultants one of the major hurdles that we would face with customers on a daily basis is the fact that how much ever features are available in any BI tool, almost upto 10 to 30% of the requirements(especially on big implementations) require some kind of custom work(sometimes this might even go upto 60% depending on the project). For example, last week i had a customer who basically wanted to know of a method for kick starting essbase allocation/calculations/data loads directly from BI EE. Now that they are using BI EE/Essbase connectivity, they wanted to provide the capability to some users to kick start the calculations directly from BI EE. Unfortunately, currently there is no out of the box way of doing this. But with some amount of work, the integration can be achieved. Today, lets look at how to go about achieving this integration. This would serve as a pre-cursor to another blog series wherein i would walk you through some complex integration techniques between BI EE and Essbase.

The basic aim is to have a link in BI EE, clicking on which should run a calculation in Essbase and then refresh a report to show the report with the new calculated data. To do this, the first step is to create a simple JSP page which would be used as a placeholder for firing the Essbase calculation. This JSP page would in turn refer the JAPI of Essbase and fire the calculation. In my case, i have used JDeveloper 10.1.3.2 to create the JSP page.

In order to create the JSP page, create a sample project and package called AllocEssbase.

       

Create a custom JSP page within this application. Include the Essbase jar files Ess_japi.jar and Ess_es_server.jar (these are the 2 main jar files that contribute to Essbase JAPI) in the project classpath.

       

Now, create the JSP using the code below. The code basically executes a calculation script CalcAll.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"<a href="http://www.w3.org/TR/html4/loose.dtd">http://www.w3.org/TR/html4/loose.dtd</a>">
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ page import="java.io.*" %>
<%@ page import="java.util.Map" %>
<%@ page import="java.util.Map.Entry" %>
<%@ page import="java.util.jar.Attributes" %>
<%@ page import="java.util.Iterator" %>
<%@ page import="com.essbase.api.base.*" %>
<%@ page import="com.essbase.api.dataquery.*" %>
<%@ page import="com.essbase.api.session.*" %>
<%@ page import="com.essbase.api.datasource.*" %>
<%@ page import="com.essbase.api.domain.*" %>
<%@ page import="com.essbase.api.metadata.*" %>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
    <title>AllocEssbase</title>
  </head>
    <body>
  <font size="5"><%="Essbase Allocation Process Started" %></font>
<%
        String s_userName = "admin";
        String s_password = "password";
        String s_olapSvrName = "localhost";
        String s_provider = "<a href="http://localhost:13080/aps/JAPI">http://localhost:13080/aps/JAPI</a>";
       
        try
        {       
        IEssbase ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);
        IEssDomain dom = ess.signOn(s_userName, s_password, false, null, s_provider);
        IEssOlapServer olapSvr = (IEssOlapServer)dom.getOlapServer(s_olapSvrName);
        olapSvr.connect();
       
        IEssCube cube = olapSvr.getApplication("Global").getCube("Global");
        String maxLstat = "import database 'Global'.'Global' data connect as 'global' identified by 'global' using server rules_file 'UnitsLd' on error abort;";
        //cube.loadData(IEssOlapFileObject.TYPE_RULES,"UnitsLd",0,"SH",false,"global","global");
        cube.calculate(false,"CalcAll");
        //cube.beginDataload("UnitsLd",IEssOlapFileObject.TYPE_RULES,"global","global",0);
        //IEssMaxlSession maxLsess = (IEssMaxlSession)dom.getOlapServer(s_olapSvrName);
        //maxLsess.execute(maxLstat);
        }catch (EssException x){
            System.out.println("ERROR: " + x.getMessage());
        }
%>
               
  <font size="5"><%="Essbase Allocation Process Ended" %></font>
        </body>
</html>
<%
    response.sendRedirect("<a href="http://localhost:9704/analytics">http://localhost:9704/analytics</a>");
%>

       

As you see this is a very basic JSP which runs a calculation script. You can use the same for doing data loads or just about anything in Essbase. As a next step execute this JSP from JDeveloper itself to be sure that this JSP works.

       

This JSP would then have to deployed on the OC4J (or app server) of BI EE. Since this JSP uses a couple of external jar files, the only way to deploy this is by bundling it as a WAR file. In order to do that, create a new deployment profile and include both the jars

       

       

Deploy the war file in the OC4J of BI EE.

       

       

Now you should be able to access the JSP using the url given below

http://localhost:9704/AllocEssbase/AllocEssbase.jsp

Embed this URL in the BI EE dashboards.

       

This should get you started. Now there are lots of different applications that one can put this into use. Like Planning load start, ODI integration, BI Scheduler integration etc. I would cover all of these use cases in detail in the coming blog entries.

Posted in All Posts, EPM, Hyperion Essbase, OBI EE Plus | 1 Comment »

Oracle to acquire Sun Microsystems

Posted by Venkatakrishnan J on April 20, 2009

I believe for news as big as this, even 2 to 3 hours seems long enough. The entire blogosphere is now full with this acquisition news. My good friend Christian puts it well here. For me personally though, this could not have come at a better time as i heard this while giving finishing touches to a custom Swing based application for the Brighton conference here. Now, some good news for readers of this blog. I will roll this application out for anyone to try after the conference. Its a very simple application but has some very good features that Essbase Administration Console does not offer out of the box. It can be reused in any instance having BI EE and Essbase installations. I will post more details after the conference.

Posted in All Posts, General | Leave a Comment »

Oracle BI EE 10.1.3.4 – Content Accelerator Framework V1 – Free Utility released – Migrating Reports/Dashboards/RPD

Posted by Venkatakrishnan J on April 16, 2009

Update: As of today, this utility would not work against any of your existing installations as this is certified to work only with 10.1.3.4.1 patch release which has not been released yet. Thanks Phillipe Lions, the PM for CAF. This would work with 10.1.3.4 as well. Ensure that after deleting the jar files, there is no lingering in-memory older catalog manager processes.

I happened to stumble upon a new utitlity in OTN today. Not sure when this was released. But i am blogging about it as not a lot of people are aware of this.

One of the major disadvantages of BI EE has been the fact that there was no out of the box utility to migrate Dashboards/Reports from one environment to another. Also, the other major issue has been the fact that any changes made to the RPD are not propagated automatically to the Reports/Dashboards. To circumvent that a new free public utlity called Content Accelerator Framework (CAF) has been released. It has excellent features which i would list below

1. Ability to use a report or a dashboard as a template to create further reports. This is an excellent feature which basically helps end users to reuse a report across multiple subject areas (the utility will help in reverse engineering the RPD to an extent).
2. Propagating RPD changes to the Reports/Dashboards.
3. Easy cloning of Development and Production instances (helps in seamless migration of Reports/Dashboards from one environment to another).
4. Paves way for BI EE Answers templates (similar to BI Publisher Templates) which can be re-used by a lots of users. I can foresee a BI EE Template depot like what has been done for BI Publisher.

The above are just the high level advantages. There are even more excellent advantages which you would realize once you start using the utility. This utility can be downloaded here and the documentation is available here. To be honest, this looks like a precursor to the kind of capabilities 11g can offer. This utility basically uses the nqudmlexec and nqudmlgen to reverse engineer and rebuild the RPD.

       

As the name of utility suggests, i believe it is meant as a framework over which many similar such applications can be built (i am just guessing). The utility uses the catalog manager jar file to add custom properties within the catalog manager.

Posted in All Posts, OBI EE Plus | 3 Comments »

Oracle BI EE 10.1.3.4 and Essbase Connectivity – Understanding Aggregations – Part 4

Posted by Venkatakrishnan J on April 13, 2009

Today i would basically summarize the observations that i had mentioned over the last couple of blog entries. As a generic rule, while designing BI EE reports with Essbase as the data source, it is important to consider the 2 points mentioned below

1. Physical layer aggregation and BMM layer aggregation both determine how the MDX is fired.
2. Each one of them should not be looked at in isolation or should not be changed in isolation without considering the end to end impact.

Lets look at the results of each and every combination one by one.

Physical Layer: Aggr_External and BMM: Aggr_External

1. If a report requests data that exists directly in Essbase, then the MDX generated would go only against those intersections.
2. If a report requests data that does not exist directly in Essbase ( can be retrieved through a custom aggregation), then the aggregation would be pushed back to Essbase. The MDX would only go against the necessary intersections to get the aggregated/calculated data. The Essbase aggregation would use the AGGREGATE function to calculate the summarized data.
3. This does not support relational database functions and hence everything would have to converted into MDX.

Physical Layer: Aggr_External and BMM: SUM

1. If a report requests data that exists directly in Essbase, then the MDX generated would go only against those intersections.
2. If a report requests data that does not exist directly in Essbase, then the MDX fired would be pushed against the Essbase layer. The major difference in this scenario and the one above is the fact that Aggregation is the fact that the aggregation in this case is done using the SUM function. So, this would generate wrong results if the outline contains Time Based properties like TBAverage, TBLast etc. This sometimes can vary based on the outlines.
3. This supports relational calculation like concatenation etc. But the aggregation would happen only at the BI Server layer. The MDX fired would go against all the level-0 intersections. So, this is generally not recommended. Also, the numbers produced might be wrong if you have lots of Label Only or Time Based properties in your outline.

Physical Layer: SUM and BMM: Aggr_External

1. This combination requires all the dimensions in the report layout.
2. This combination does not have any specific advantage over other combinations.
3. This always does a level-0 roll up using AGGREGATE function. This can be at times pretty slow and is not recommended unless you want the aggregation to happen dynamically all the time.

Physical Layer: SUM and BMM: SUM

1. This is exactly similar to Aggr_External aggregation set for both Physical Layer and BMM.
2. If a report requests data that exists in Essbase, then the MDX generated would go only against the necessary intersections.
3. If a report requests data that does not exist directly in Essbase, then the aggregation would be pushed back to Essbase. The MDX would go only those necessary intersections to calculate the aggregated/calculated data. The Essbase aggregation would use the SUM function to calculate the summarized data.
4. The major advantage of this is the fact that this supports relational calculations as well. When relational calculations are introducted then, the relational manipulations are done in BI Server memory. The MDX generated would go only against those necessary intersections rather than doing a level-0 dynamic sum.

Of course, some of the behavior above can change as new releases of BI EE come out. But currently the above summarizes the behavior of various aggregation combinations when introduced in the BMM and physical layer. There are other combinations that are possible as well. I have not covered them as they are not used that much.

Posted in All Posts, EPM, Hyperion Essbase, OBI EE Plus | 2 Comments »

Oracle BI EE 10.1.3.4 and Essbase Connectivity – Understanding Aggregations – Part 3

Posted by Venkatakrishnan J on April 9, 2009

Lets look at one more interesting aggregation combination today which would result in different MDX queries and hence would result in different data being displayed. The combination that we would be using is given below

BMM: SUM and Physical Layer: SUM

Again to test this combination we shall use two forms of a similar report. The data for one report can be obtained from Essbase directly. The data for the other report would have to be derived from the existing Essbase data.

       

       

The MDX for this report is given below. As you see, this aggregation combination goes directly against the Essbase intersections.

With
set [Channel1] as '[Channel].Generations(1).members'
set [Product1] as '[Product].Generations(1).members'
select
{ [Measures].[Units] } on columns,
NON EMPTY {crossjoin ({[Channel1]},{[Product1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Global.Global]

This is very similar to the Aggr_External aggregation set to both the physical layer and the BMM. Now lets test a report wherein the data to be displayed would have to be derived.

       

       

If you look at the MDX below, you would notice that SUM function is shipped instead of AGGREGATE. So, what this means is that, if you use this combination you need to be sure your Essbase outline does not contain any Time Balance properties. If it contains Time balance properties then the numbers generated would be wrong, if the above combination is used.

With
set [Channel1] as '[Channel].Generations(1).members'
set [Product1] as '[Product].Generations(1).members'
set [Time2] as '{[Time].[1998], [Time].[1999], [Time].[2000]}'
member [Measures].[MS1] as 'SUM({[Time2]},Measures.[Units])'
select
{ [Measures].[MS1] } on columns,
NON EMPTY {crossjoin ({[Channel1]},{[Product1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Global.Global]

So far we have seen all the relevant combinations which would be commonly used. But again there are other combinations like Evaluate_Aggr, None etc which again can generate completely different set of MDX queries. I would not be covering these combinations as they are seldom useful. Neverthless, this wraps up my aggregation series. In the next blog entry i would summarize the observations so that anyone using BI EE on Essbase can refer to it while doing their implementation.

Posted in All Posts, EPM, Hyperion Essbase, OBI EE Plus | 1 Comment »

Oracle BI EE 10.1.3.4 and Essbase Connectivity – Understanding Aggregations – Part 2

Posted by Venkatakrishnan J on April 7, 2009

Yesterday we saw the 3 types of aggregations that are possible currently in BI EE (while using Essbase as a data source). We had discussed the first type of aggregation which is the default. Now, today we shall go more into details of how BI EE generates the MDX and how the aggregations set in the BMM and the physical layer affect the MDX generated. As you might have already noticed, both BMM layer and physical layer level aggregations can be specified for Essbase data sources. For simplicity and for easy understanding we shall take only 3 types of aggregation in both the BMM and the physical layer. The list is given in the below table

BMM Aggr_External
BMM SUM
BMM None
Physical Layer Aggr_External
Physical Layer SUM
Physical Layer None

The important part to note here is the fact that different combinations of aggregations set in the BMM and the physical layer can produce different MDX queries. Let us start with the first combination shown below (we shall use the same report that we had used yesterday – One wherein data is directly available in Essbase and the other wherein its not available in Essbase).

BMM: Aggr_External and Physical Layer: SUM

       

       

You would notice that this would produce an error due to the wrong MDX being generated. Thats because whenever in physical layer, if the aggregation is set other than Aggr_External, the reports that we create should have a dimension member from every dimension. Though this has produced an error, lets take a look at the MDX.

With set [Channel1] as '[Channel].Generations(1).members'
set [Product1] as '[Product].Generations(1).members'
member [Measures].[MS1] as 'AGGREGATE(crossjoin (Descendants([Channel].currentmember,[Channel].Generations(3)),crossjoin (Descendants([Product].currentmember,[Product].Generations(5)),{[Time4]})),Measures.[Units])', SOLVE_ORDER = 100
select
{ [Measures].[MS1] } on columns,
NON EMPTY {crossjoin ({[Channel1]},{[Product1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Global.Global]

Now, lets go back to the report and add Gen2, Year to bypass the above error.

       

The MDX generated is given below

With
set [Channel1] as '[Channel].Generations(1).members'
set [Product1] as '[Product].Generations(1).members'
set [Time2] as '[Time].Generations(2).members'
member [Measures].[MS1] as
'AGGREGATE(crossjoin (Descendants([Channel].currentmember,[Channel].Generations(3)),crossjoin (Descendants([Product].currentmember,[Product].Generations(5)),
Descendants([Time].currentmember,[Time].Generations(4)))),Measures.[Units])', SOLVE_ORDER = 100
select
{ [Measures].[MS1] } on columns,
NON EMPTY {crossjoin ({[Channel1]},crossjoin ({[Product1]},{[Time2]}))} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Global.Global]

As you see, due to the aggregation property (SUM) in the physical layer, the MDX generated does a level-0 dynamic aggregation using the AGGREGATE function (though the intersection for the above members actually exist in Essbase). So, what this means is that, you do not necessarily have to always have aggregated data in Essbase. Now, as a next step lets change the report to produce a report for which data does not even exist inside Essbase.

       

       

Even this has produced an error. So, in effect what this means is with a combination of BMM: Aggr_External and Physical Layer: SUM it is not possible to do in memory or custom aggregations on Essbase. But by default in the report if we have generations from every dimension then a level-0 roll up happens using the AGGREGATE function.

BMM: SUM and Physical Layer: Aggr_External

To test this combination lets first create the simple report without the Time dimension and then we shall look at the MDX query.

       

       

The MDX query generated would be

With
set [Channel1] as '[Channel].Generations(1).members'
set [Product1] as '[Product].Generations(1).members'
select
{ [Measures].[Units] } on columns,
NON EMPTY {crossjoin ({[Channel1]},{[Product1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Global.Global]

As you see, this behaves like the first case discussed above. If we are requesting a report for which data already exists in Essbase, then the MDX generated would go directly against those intersections. Now, lets add a year filter to generate a report for which data does not exist in Essbase (can be obtained only through calculations/aggregation).

       

       

Now, if you at the MDX below you would notice that the aggregation is not pushed back to Essbase. There is no AGGREGATE function. But there is a SUM function. So even in this case the aggregation is pushed back to Essbase. But if you have Time Based Properties in the outline like TBLast, TBAverage etc, then this would generate wrong results. You need to be extremely careful in such cases as the numbers reported might be wrong.

With
set [Channel1] as '[Channel].Generations(1).members'
set [Product1] as '[Product].Generations(1).members'
set [Time2] as '{[Time].[1998], [Time].[1999], [Time].[2000], [Time].[2001]}'
member [Measures].[MS1] as 'SUM({[Time2]},Measures.[Units])'
select
{ [Measures].[MS1] } on columns,
NON EMPTY {crossjoin ({[Channel1]},{[Product1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Global.Global]

Now the question is what is the difference between this and the default setting. What difference does SUM aggregation on the BMM layer make to the report. There are 2 main differences (one is noted above).

1. When a report requests some data that is not there in Essbase (which would have to be calculated on the fly) then in this case the aggregation is pushed back to Essbase but with the SUM function. In the default case (both aggregations set to Aggr_External) the aggregation is pushed back to Essbase with AGGREGATE function.
2. The other difference is that when we set SUM as the aggregation in the BMM layer, then in Answers we can use relational functions to manipulate the data. For example, in the report above, lets assume we need to do a relational calculation like concatenation to the dimension members in the report. So, all we need to do is to append “Test” to the Product dimension as shown below.

       

       

As you see the number above is outright different than the actual numbers. The reason for this is the MDX generated.

With
set [Channel3] as '[Channel].Generations(3).members'
set [Product5] as '[Product].Generations(5).members'
set [Time4] as '[Time].Generations(4).members'
set [Axis1Set] as 'crossjoin ({[Channel3]},crossjoin ({[Product5]},{[Time4]}))'
select
{[Measures].[Units]} on columns,
NON EMPTY {[Axis1Set]} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Global.Global]

As you see, just because of the relational calculation, BI EE has generated an MDX that goes against Level-0 members. If you fire this directly in Essbase you would notice all Non Empty tupules being generated as shown below.

       

So what this means is BI Server itself does the aggregation. Again, one would have to be extremely careful while using this as this would turn out to be very slow depending on the amount of data getting extracted. Not many people are aware of these minute differences and this is where i believe understanding Essbase and the BI EE RPD would help a lot while creating Essbase specific BI EE reports. I would document(in the form of a tabular chart) various aggregation combinations in the coming blog entries so that everyone can refer to that while using Essbase as a data source.

Posted in All Posts, EPM, Hyperion Essbase, OBI EE Plus | 1 Comment »

Oracle BI EE 10.1.3.4 and Essbase Connectivity – Understanding Aggregations – Part 1

Posted by Venkatakrishnan J on April 6, 2009

One of the least explored/documented part of the BI EE and Essbase Connectivity is the ability of BI EE to do aggregations at 3 different layers. Whenever Essbase is used as a data source in BI EE, there are 3 types of aggregations that are possible. All the 3 can produce different results and hence warrants a correct understanding of how they exactly work. I have seen quite a few customer cases wherein the report results are outright wrong due to wrong aggregations or due to lack of understanding of how BI EE treats Essbase. So, lets try to understand the 3 types of aggregation that BI EE and Essbase connectivity provides to the end user.

1. Aggregation by going directly against the aggregated intersections in Essbase.
2. Aggregation in Essbase by using MDX. Essbase would fire Aggregate/Sum functions to calculate the data from Level-0.
3. Aggregation at the BI EE layer

All the 3 above are possible in BI EE currently. Remember that by default, when you create the BMM for an Essbase cube by a simple drag and drop, the first type of aggregation would be used. There are certain caveats that one would have to understand while using this type of aggregation. As always, wherever possible try to include a dimension member from every dimension in the report. MDX as well as BI EE does not require us to qualify a report with all the dimensions. So, if you are creating a report with only a subset of all the dimensions in the outline, then you need to be aware of the fact that topmost dimension member for the other dimensions are implicitly assumed by the MDX query. For example, consider the outline shown below

       

       

As you see, this is a very simple outline containing 4 dimensions. Now lets create a simple report(assuming the default BMM created by the drag and drop option) as shown below

       

This report contains the topmost member of the channel and product dimension reporting against the Units measure. Since, we have not included the Time dimension in this report, we would typically assume that the report would be a sum of all the years data. Apparently that is not the case since MDX implicitly assumes the topmost node of the missing dimension. Since Time is a label only member, the value that you would see in the report would be of the year 1999 (first immediate child).

       

The MDX query for this report is given below

With
set [Channel1] as '[Channel].Generations(1).members'
set [Product1] as '[Product].Generations(1).members'
select
{ [Measures].[Units] } on columns,
NON EMPTY {crossjoin ({[Channel1]},{[Product1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Global.Global]

Now, since all the data that we requested in the report is present in Essbase, the MDX would go directly against the data source and extract the intersecting data values. But what if we want to get a sum of all the years. Then to achieve this include all the years as a filter in your report.

       

       

So basically, in the above report we are requesting data which does not even exist in Essbase. We only have the individual year values and not the summed up year values. So, If you look at the MDX now, you would notice that BI EE would have automatically fired an AGGREGATE function to retrieve the data back.

With
set [Channel1] as '[Channel].Generations(1).members'
set [Product1] as '[Product].Generations(1).members'
set [Time2] as '{[Time].[1998], [Time].[1999], [Time].[2000], [Time].[2001], [Time].[2002], [Time].[2003], [Time].[2004]}'
member [Measures].[MS1] as 'AGGREGATE({[Time2]},Measures.[Units])', SOLVE_ORDER = 100
select
{ [Measures].[MS1] } on columns,
NON EMPTY {crossjoin ({[Channel1]},{[Product1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Global.Global]

The question is how did BI EE determine that AGGREGATE function needs to be used here. What if we let the BI Server to do the aggregation? How do we make BI EE to generate SUM function based MDX instead of AGGREGATE? These are questions that would be answered in the next couple of blog entries.

In the same fashion, whenever you are creating a custom Evaluate MDX function based logical column, be aware of the intersecting dimension members while creating the report. This is absolutely critical for the data correctness in the report. We will go more into detail in the next couple of blog entries.

Posted in All Posts, EPM, Hyperion Essbase, OBI EE Plus | 1 Comment »

Mr Winker has arrived!!!

Posted by Venkatakrishnan J on April 3, 2009

It is very rare that i post something personal in this blog. But this is an occassion that i just cannot afford to miss.

It is with pleasure that i announce the arrival of Mr. Winker into the V family. He has been officially named Vrishav though. He was born on April the 2nd at 7.33 AM and weighed around 3.04 Kgs. Mother and the baby are doing well, though the father is way too famished after being the moral support for over 4 hours in the labour room!!! Father is under constant observation by the new mother, for his weight(baby)-lifting skills are being constantly tested/tuned.

Well, now that the news has trickled in, all goodwill funds for Vrishav Graduate and Post-Graduate Education are more than welcome. Credit Cards, Debit Cards, Cash, TC’s etc accepted. Just kidding :-).

       

       

       

New Mother and the new father,
Vandana and Venkat

Posted in All Posts, General | 33 Comments »