Business Intelligence – Oracle

Archive for May, 2009

Oracle BI EE & Essbase Connectivity – Displaying Member Intrinsic & Custom Properties – Aliases and UDAs

Posted by Venkatakrishnan J on May 31, 2009

One of the common questions asked in the existing BI EE to Essbase connectivity is the ability to display member names in a report instead of the alias names. Currently BI EE does not support displaying the member names(if default alias exist already) in a direct way. So, lets take a look at an approach today which basically extends on the method described here in the modeling guide. Actually,. the method described there is correct but the explanation is partly wrong(in a different context it is right though). It is described there that Member Names are supported for only Level-0 members. It is actually supported for all the generations. But when a drill happens the alias names will return. Lets look at why this happens and of course understand the capability of BI EE in displaying other member intrinsic properties.

The most common question that generally comes up with regard to member names is, why not EVALUATE can be used to display them directly. The main reason why this is not possible is, Member Name is an intrinsic property of a member. There are no MDX functions which can actually be used to display a member name. An essbase member has many intrinsic properties. The intrinsic properties are listed below


Apart from the above there are other properties like custom UDA properties etc. Now lets take the example of the Demo Basic cube. If you look at the outline of this cube, it has aliases at the Time dimension level and also for the quarters(not for the level-0 though). Now, our aim is that, whenever we include these levels in the report, the report should automatically display the member names instead of the alias names.


The intrinsic properties are typically referred directly in the repository as the External Name.


So, in order to refer the alias, lets create a custom physical cube column as shown below.


The name for the column can be anything. Basically we are creating an Member Name column for the level that has aliases. In our case it is the quarter level.


Assign this new column to the Gen2, Year of the year dimension.


Also, make this column to be part of the BMM and the presentation layer. Now, if you create a report on this column and a measure, you would get a report only with the member names for the Quarter Level.


But the problem with this approach is that, once you drill on this column, the drills would return. That can be manipulated by re-arranging the level keys accordingly. The main reason why i introduced this was to show a very good feature that can be derived using this approach. In many cases, you would have a report containing 2 or 3 dimension levels and a fact measure. In the same report, you might want to know which dimension member has a specific UDA assigned. For example, consider the outline below


If you look at the Market dimension, the 2nd generation has multiple UDAs assigned. Assume that we have a report like the one shown below


In the above report, we need one more column stating whether that member has a “Major Market” UDA assigned or not. This is not possible using MDX as the UDA based MDX functions will filter the members based on a UDA (which is not our requirement). So, to start with lets create a custom Physical cube column. In the External name of the column enter Major Market as the External Name. UDA is a custom property of a member. Hence it can be referenced directly as an External name as shown below


Now, include this UDA in the Gen2, Region level of the Market Dimension.


Once this extra column is included, just include this in the BMM and the presentation layer. Now in the above report, pull in this column. This would basically show all the members having the Major Market UDA as 1. Remaining members that do not have this UDA assigned will be shown as 0.


The above technique can be used for other solving other interesting reporting requirements as well. Its heartening to see every aspect of MDX being available in some form or other for customization.

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

Oracle Enterprise Performance Management Architect (EPMA) – Administering Essbase, Planning and HFM – Setup

Posted by Venkatakrishnan J on May 27, 2009

One of the major advantages of Oracle acquiring so many companies is the fact that it integrates all the products in some fashion or the other, that too quickly. For example, if you look at the different possible ways of loading data into Essbase, the list of options available is sometimes absolutely daunting(to an extent flexibility as well) especially for users who are new to the product stack. The following list gives all the possible ways that i can think of for loading data into Essbase

1. Using Essbase Administration Services or EAS
2. Using Essbase Studio (from EPM 11 version)
3. Using Oracle Data Integrator (uses JAPI)
4. Using Hyperion DIM or Data Integration Management
5. Using custom Java, C & VB APIs
6. Using MaxL Scripts through an unix shell or batch script
7. Using Essbase Integration Services (Superceded now by Essbase Studio)
8. Using Oracle Hyperion Enterprise Performance Management Architect

I have covered 7 of the above 8 in prior blog entries. Today we shall see another important tool that is quite commonly used for managing the metadata of Oracle EPM products like Planning, Financial Management & Profitability and Cost Management. This tool called as Enterprise Performance Management Architect or EPMA provides a uniform platform for metadata management across most of the Hyperion components. Apart from its capabilities, one strange aspect to this product is the fact that it depends on IIS to web enable itself. So, you would need a mandatory windows server to host this. When i started using this a couple of months back, my initial impressions were far from impressive. I am not sure whether it was because of my environment or because of the product itself. Whenever EPMA was started, it seemed to be consuming the entire memory(more than the memory occupied by SGA of Oracle and the Essbase Kernel). Having said that, its features(and also the concept of common metadata management) are really good and of course with more usage i am starting to like this more and more.

Today we shall see what it takes to setup EPMA to manage Essbase Applications/Outlines. Just remember the fact that EPMA was never designed to do individual application(Essbase, Planning etc) level administration. That is meant for the respective product administration tools. EPMA is meant for managing the metadata like dimensions, hierarchies, data loads across product sets etc. So, in effect it is like ODI or Hyperion DIM but with more product specific multi-dimensional features thrown in. So, if you are managing Planning, Essbase and HFM (or any of these 2) in your organization EPMA could be a good fit when you want to use a single tool for managing the common dimensions, data loads etc.

To use EPMA for managing Essbase specific application outline members, ensure that you are using Essbase in external mode i.e shared services security should be used. By default when you install EPM in windows, Essbase will be installed with SSO configured against shared services. SSO is not sufficient to use EPMA against Essbase. Ensure that you convert Essbase from local authentication to External authentication. That is done by externalizing the users from Essbase Administration Services.

Once that is done, the next step is to create a data source for the interface tables using the EPM Configurator.

These interface tables are used for importing the pre-defined common dimension types that are available across EPMA. The idea is to have custom members defined in these interface tables and then import them into EPMA. This step is needed only if you have pre-defined members defined in the interface tables. If you are starting from scratch then this is not needed. Once the data source is created, log into EPMA and go to Application Library.

Then create a new Essbase (ASO) application.

Then edit this ASO application and create the dimensions and the corresponding members as shown below

After this step, go to shared services and ensure that the user with which you have logged in as has the necessary privileges to access Essbase. Basically one would need application and database administrator privileges.

Then you can deploy this application directly from EPMA to Essbase.

The major drawback with this is that this does not provide any restructuring options when data is loaded into a deployed cube. Also, one should not be changing the application directly within Essbase using EAS. Then the sync between EPMA and Essbase would be lost and all changes would be over-written when the sync is done from EPMA again. Typically EPMA is not recommended for Essbase applications. It is very good for managing Planning and HFM metadata. One can use shared dimensions wherein the same dimension can be reused across applications.This should give you an idea of how EPMA works. I would go into details of how one can do data synchronization across applications using EPMA in the coming blog entries.

Posted in All Posts, EPM, Hyperion Essbase, Oracle EPMA | 5 Comments »

Oracle BI EE – Writebacks to Essbase – Using JAPI and Custom HTML – Part 1

Posted by Venkatakrishnan J on May 25, 2009

Considering the amount of expectations surrounding the BI EE and Essbase connectivity, i thought it would make a lot of sense to blog about another interesting piece of the Essbase and BI EE integration i.e Writebacks to Essbase from BI EE. Actually i have seen this question being asked by customers/users in quite a few internal presentations that i have been involved recently. Writebacks to Essbase from BI EE is not supported by default. Having said that one can create a custom solution to enable a writeback to an Essbase cell. We shall see one approach of doing this today.

As you would probably know, Writebacks to a relational source from BI EE is supported through custom XML messages. Unfortunately, as of this release, this method cannot be reused for non-relational sources. Basically our requirement is pretty simple. In a BI EE report (reporting against an Essbase source), the end user should have the ability to enter custom values and update the corresponding intersections back in Essbase. The below screenshot explains the requirement pretty clearly.

The high level architecture diagram to enable these writebacks is given below

The high level flow is, for every cell update, the end user will enter the new value and then will click on the update button. That will pass on the parameters to a JSP page using the HTML Form GET method. The JSP will accept the parameters and will in turn pass on the values to the JAPI. The JAPI will then update the Essbase cell. To illustrate this, we shall use the default Demo->Basic cube. Remember the fact that one cannot use the write backs textboxes directly as currently BI EE does not provide a means of referencing the updated/entered value in a cell outside of the XML template. So, we would need to write our own custom HTML to generate textbox and the update buttons.

Import the Demo Basic cube into the repository and create the BMM and presentation layers by drag and drop. Change the physical and the BMM layer aggregations(of the all the measures for which you want to enable writebacks) to SUM instead of Aggr_External. The main reason for doing this is to ensure that we can use string manipulation functions like concatenation from Answers. For more details on each of these aggregations check my blog entries here, here, here and here.

Now, lets go to JDeveloper and create a simple JSP page. Use the below code in the JSP. You can customize this to your needs.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ page import="*" %>
<%@ 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.*" %>
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
		<font size="5"><%="WriteBack Started" %></font>


        String s_userName = "admin";
        String s_password = "password";
        String s_olapSvrName = "localhost";
        String s_provider = "http://localhost:13080/aps/JAPI";

        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);
        IEssCubeView cv = dom.openCubeView("Data Update Example",s_olapSvrName, "Demo", "Basic");

        String v_Market = request.getParameter("p_Market");
        String v_Product = request.getParameter("p_Product");
        String v_Accounts = request.getParameter("p_Accounts");
        String v_Scenario = request.getParameter("p_Scenario");
        String v_Year = request.getParameter("p_Year");
        String v_Value = request.getParameter("p_Value");

        IEssGridView grid = cv.getGridView();
        grid.setSize(2, 5);
        grid.setValue(0, 1, v_Market);
        grid.setValue(0, 2, v_Product);
        grid.setValue(0, 3, v_Accounts); ;
        grid.setValue(0, 4, v_Scenario);
        grid.setValue(1, 0, v_Year);

        System.out.println("\nData Cell at 2nd-row, 2nd-column: " + grid.getValue(1,1).toString());
        System.out.println ("Market: "+v_Market+" Product: "+v_Product+" Accounts: "+v_Accounts+" Scenario: "+v_Scenario+" Year: "+v_Year+" Value: "+v_Value);

        int row = 1, col = 1;
        if (grid.getCellContentType(row, col) ==
                IEssGridView.CELL_CONTENT_TYPE_DOUBLE) {
            IEssValueAny val = grid.getValue(row, col);
            double dblVal = val.getDouble();

            grid.setValue(row, col, Double.valueOf(v_Value).doubleValue());
        } else if (grid.getCellContentType(row, col) ==
                IEssGridView.CELL_CONTENT_TYPE_MISSING) {
            grid.setValue(row, col, Double.valueOf(v_Value).doubleValue());

        IEssOpUpdate opUpd = cv.createIEssOpUpdate();

        }catch (EssException x){
            System.out.println("ERROR: " + x.getMessage());
<font size="5"><%="WriteBack Ended" %></font>
		<font size="5"><%="WriteBack Ended" %></font>

I would not explain this in detail as the JSP is self-explanatory. But there is one aspect to writebacks using JAPI that one would have to be aware of. Whenever JAPI is used for writebacks ensure that you have a IEssGridView which basically visualizes your output as an Excel add-in grid. The rows and columns are numbered in an increasing order from zero.

The code snippet that actually does the writeback is given below

Once this is done, this JSP would have to be deployed on a web server that is accessible to BI EE. In order to accomplish that, create a custom WAR profile which would basically contain all the dependent jar files and also the manifest related information.

Then deploy this WAR file on the same application server as BI EE (or OC4J).

Once the deployment is done, test the jsp page by passing the url as shown below


Once this is done, lets create a BI EE report containing all the dimensions. Remember that for writebacks to work in Essbase, we would need a value/member from every dimension. Create a custom column and in the custom colum enter the formula as shown below

'<form name="input" action="http://localhost:9704/WriteBack/WriteBack.jsp">
<input type="text" name="p_Value" size="10"/>
<input type="submit" value="Update" />
<input type="hidden" name="p_Accounts" value="Sales" />
<input type="hidden" name="p_Year" value="'||"Year"."Gen2,Year"||'"/>
<input type="hidden" name="p_Market" value="'||Market."Gen2,Market"||'"/>
<input type="hidden" name="p_Product" value="'||Product."Gen1,Product"||'"/>
<input type="hidden" name="p_Scenario" value="'||Scenario."Gen2,Scenario"||'"/>

And change the column format to HTML.

Basically what the formula above does is, it create an HTML input field in the report itself. One parameter p_Value would be obtained from the value of the textbox and the remaining hidden form parameters will be used for passing the dimension member attributes to the url.

If you go to the report now, you can alter the existing values and on submit, these values would be submitted back to Essbase.

The same methodology can be used for writing data back into Oracle OLAP as well. This is one method of doing writebacks. There is one more method as well wherein one can use the Writeback template itself. I will discuss that in future. But for now, the above should give an idea of how Writebacks can be enabled in BI EE against Essbase data sources. There are quite a few moving parts. But again these are required due to the nature of the connectivity as of today.

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

Oracle BI EE – Scheduling Essbase/Planning Calculation Scripts – Action Framework & Custom Java Remote Procedure calls

Posted by Venkatakrishnan J on May 21, 2009

In the blog entry here, i had shown you how to run an essbase calculation script from the BI EE dashboards. To make it even more complete, lets look at a means of calling a calculation script through BI Scheduler. So, our idea is to use BI EE as a scheduler to schedule Essbase/Planning calculations. In order to do that there are certain pre-requisites. I have covered this in my blog entry before here. Once the setup is done, lets open the JDeveloper and create a new project. Include the schedulerrpccalls.jar, Ess_japi.jar and Ess_es_server.jar as part of the project properties.

The idea is to basically write a custom java program which in turn will run an Essbase calculation script through the Java API. The java program would then be bundled in the form of a jar file and called from an iBot. Use the below code to login to Essbase and then to execute the calculation.

package bischeduler;



import com.essbase.api.base.*;
import com.essbase.api.dataquery.*;
import com.essbase.api.session.*;
import com.essbase.api.datasource.*;
import com.essbase.api.domain.*;
import com.essbase.api.metadata.*;

public class BIScheduler implements SchedulerJavaExtension{
    public BIScheduler() {   

        String s_userName = "admin";
        String s_password = "password";
        String s_olapSvrName = "localhost";
        String s_provider = "http://localhost:13080/aps/JAPI";

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

        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;";
        //IEssMaxlSession maxLsess = (IEssMaxlSession)dom.getOlapServer(s_olapSvrName);
        }catch (EssException x){
            System.out.println("ERROR: " + x.getMessage());

    public void run(SchedulerJobInfo jobInfo) throws SchedulerJobException{
        new BIScheduler();
        System.out.println("JobID is:" + jobInfo.jobID());
        System.out.println("Instance ID is:" + jobInfo.instanceID());
        System.out.println("JobInfo to string is:" + jobInfo.toString());
    public void cancel(){};

        public static void main(String[] args) {
        new BIScheduler();


Once this is done, create a deployment profile to bundle the code as a jar file. Ensure that the deployment also bundles the 3 jar files mentioned above. Then deploy the jar file.

Now copy the deployed jar file to the {OracleBI}\web\javahost\lib directory (or a directory that you have mentioned as the lib path in the Java host config.xml). Now create an ibot and then in the advanced tab call the deployed jar file as shown below.

You can create ibots in such a way that once the java class run is completed, it would trigger another ibot to deliver a report based on an Essbase cube. This will ensure that the administrator is sure of the fact that the calculation has run successfully.

Now you should be able to run essbase calculations from BI Scheduler directly.  One can even run ODI packages using BI EE. I would cover that as well in future. Keep watching this space next week for a method to do write backs into Essbase. I would present a couple of methods and both of them can be used effectively in a prod like environment.

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

Oracle BI EE and Essbase Connectivity – Report Based and Essbase based Grand Totals – Answers Based Aggregation

Posted by Venkatakrishnan J on May 20, 2009

This post here by Christian prodded me to write about another interesting feature in the BI EE and Essbase Connectivity. As you would probably know BI EE supports report based grand totals/sub-totals in a table view. There are 2 types of totals. One is BI Server based totals wherein BI Server would do the totalling on a result set. The other is data source specific totalling wherein the query is fired back to the underlying data source to obtain the totals. For example, lets just quickly import the Demo->Basic cube in to the repository and build a very simple report as shown below.

As you see, it is a very simple report containing a report based total and a sub-total at the market level. Before going further, lets look at the outline of the Basic cube first. As you see, every dimension top member is set as a stored only member.

If you look at the MDX of the above report, you would notice that 3 MDX queries would be fired. One for the base report , one for the grand total and the other for the market sub-total.

The aggregation for the Measure Sales is Aggr_External in both the physical and logical layer. In Answers, the aggregation is set as default. Now, lets go to the outline and convert the Year Dimension top member to be a label only member as shown below.

Now, try running the same report above. You would notice that the report level totals and sub-totals are totally wrong as shown below.

The main reason for this is the fact that since we have converted the topmost member of the outline to be label-only. So for the Year dimension, it will always pick the Qtr1 value instead of totalling all the quarters. For report developers, this would turn out to be an absolute nightmare considering the fact a report based total is created under the assumption that, the totalling is done on the report and not at the data source level. Now, from Answers lets change the aggregation os the sales measure to SUM.

And look at the report.

Basically, a report level SUM ensures that all the custom aggregations/totalling occuring in a pivot table/table are done at the report level instead of at the Essbase layer. So by default, ensure that you always have SUM at the report level to ensure that you do not get wrong answers especially for totals and sub-totals.

This should have given you an idea of how the aggregations at 3 layers (Physical, BMM and the Answers) can affect a report. I would cover the usage of Report Aggregations across different BMM and Physical Layer aggregations in the future.

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

Oracle BI EE – Consuming Web Services in BI EE – Displaying Live Stock Quotes – Using UTL_HTTP and EVALUATE – Part 1

Posted by Venkatakrishnan J on May 19, 2009

When i was at the BI Forum in Brighton, one of forum participants had a question on using Web Services in BI EE. BI EE currently does not support direct WSDL response consumption by passing a request. So, in order to achieve this one way is to write a pl/sql wrapper which would basically call the web service and then use EVALUATE within BI EE to display them. There are other approaches as well like writing a pipelined table function etc. We shall see the other approaches in a later blog entry.

Assume that we have a table as shown below containing the companies and their corresponding Stock Symbols.

The requirement is to display the live stock feeds(Day High as well as Day Low) from a live Web Service feed at within BI EE. One good thing about this service is that they offer a variety of web service feeds along with their statuses. In order to consume the stock feed, lets first create a pl/sql function which is nothing but a wrapper, that sends a request to the WSDL above and gets the response back. The request sent would pass the Stock Symbol as a parameter. The Pl/Sql function would basically extract the Day High and Day close (or any other parameter) values directly from the stock feed.

create or replace function stock_quote
( p_symbol in varchar2 default 'ORCL',
p_tag in  varchar2  default 'High'
return varchar2
  s_request varchar2(32000);
  s_response varchar2(32000);
  h_request utl_http.req;
  h_response utl_http.resp;
  s_request:= '<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="" xmlns:xsd="" xmlns:soap="">
    <GetQuote xmlns="http://www.webserviceX.NET/">
 h_request:= utl_http.begin_request
            ( ''
            , 'POST'
            , 'HTTP/1.1'
  utl_http.set_header(h_request, 'Content-Type', 'text/xml');
  utl_http.set_header(h_request, 'Content-Length', length(s_request));
  utl_http.write_text(h_request, s_request);
  h_response := utl_http.get_response(h_request);
  utl_http.read_text(h_response, s_response);
  return substr(s_response,instr(s_response,'<'||p_tag||'>')+length(p_tag)+8,instr(s_response,'</'||p_tag||'>')-instr(s_response,'<'||p_tag||'>') - length(p_tag) - 8);

As you see the above function basically does a string manipulation to extract the parameter values. Typically this is not a recommended approach. The recommended approach is to use an XMLType function or a Regex function to extract the parameter values. But in our case, the string is sufficient. If you are behind a proxy, ensure that you set the UTL_HTTP.set_proxy before the begin request function. To test this out, lets fire the function from command line.

select stock_quote('ORCL','Low') from dual;

As you see we get the Oracle stock quote high and low for the day directly from this function. Now, lets go to BI EE and call this function through EVALUATE.

As you see we have now displayed the response of a WSDL directly from BI EE. We shall look at other approaches in future.

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

Oracle BI EE – Time Based Aggregation – Essbase Equivalence – Part 1

Posted by Venkatakrishnan J on May 17, 2009

Right now i am in the process of comparing certain Essbase related functionalities in BI EE and vice versa. One of the main reasons why i am doing this is to understand how the feature of one product can be leveraged while working with the other, now that more and more customers seem to have both of them together. While doing a quick comparison, one of the key Essbase functionalities that came to the forefront was the ability to do Time based aggregations. Any Essbase user would be aware of the fact that both Block Storage and Aggregate Storage (to an extent) essbase cubes support time based aggregations. Time based aggregations are nothing but aggregations of measures wherein the aggregation rule varies for the time dimension alone. There are 3 types of time based aggregations

1. Time Based Last – In some cases, when we are at a year level, we would like to see the amount sold for the last month(December) alone instead of a summation of all the months. But the aggregation for all the other dimensions (and their levels) would be SUM.
2. Time Based First – In some cases, when we are at a year or a quarter level, we would like to see the amount sold for the first month of that quarter or year instead of a summation of the all the child months for that quarter/year. But the aggregation for all the other dimensions would be SUM.
3. Time Based Average – In some cases, when we are at a year or a quarter level in the Time dimension, we would like to see the amount sold as an average of all the corresponding child months for that quarter/year. But the aggregation for all the other dimensions would be SUM.

Just to explain this a bit further, lets consider the screenshot below

As you see, we basically have 2 dimensions. One is Product and the other is Year. In the report containing Top Level of Product and Year Level of Time, the report has produced a number which is nothing but sum of amount sold for all the products in the last month of that Year (December). So, basically there are 2 kinds of aggregation for the same measure. The method for achieving this though is pretty simple, i wanted to introduce this here so that this can act as a pre-cursor to even more complex dimension based aggregations which i would show in the coming blog entries. To achieve this, open up the repository and go to the aggregation tab of the measure that you are reporting against.

Now choose the based on dimensions option. In the aggregation list first choose Other Dimensions and enter the aggregation as SUM. After that choose the Time dimension and enter the aggregation as LAST.

Ensure that the order of the dimensions listed above are the same. So basically what this does is, it sets the aggregation of the measure for all the dimensions apart from Time as SUM. For Time alone it would do a LAST aggregation. The order of the dimensions ensure that the LAST operation is carried out after the SUM.

Now create a report on Time and Product. You would notice that whenever upper levels of Time dimension is queried upon, it would result in LAST aggregation.

If you look at the actual query fired, you would notice that TIME based LAST aggregation would be performed on a Summed up product dimension.

select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3
     (select distinct D1.c2 as c1,
               D1.c3 as c2,
               D1.c4 as c4,
               D1.c5 as c5
               (select T3663.TOTAL_PRODUCT_DSC as c2,
                         T3683.YEAR_DSC as c3,
                         T3663.TOTAL_PRODUCT_ID as c4,
                         T3683.YEAR_ID as c5,
                         sum(T3720.UNITS) as c6,
                         T3683.MONTH_ID as c7
                         PRODUCT_DIM T3663,
                         TIME_DIM T3683,
                         UNITS_FACT T3720
                    where  ( T3663.ITEM_ID = T3720.ITEM_ID and T3663.TOTAL_PRODUCT_DSC = 'Total Product' and T3683.MONTH_ID = T3720.MONTH_ID )
                    group by T3663.TOTAL_PRODUCT_DSC, T3663.TOTAL_PRODUCT_ID, T3683.MONTH_ID, T3683.YEAR_DSC, T3683.YEAR_ID
               ) D1
     ) D1
order by c1, c2

The above was pretty straightforward. But there is one more similar use case of Time based properties in Essbase which is more tougher to simulate in BI EE against a relational source. Time based aggregation can vary on different products. For example, for one product we would need Time Based aggregation of LAST, the other FIRST and the other AVERAGE. In such cases the above cannot be used as the aggregation is constant across a dimension(in the example above). In the above case, we had the same TB property for all members within a dimension. We shall see how to go about achieving such complex members based other dimension Time Based Aggregations in a future blog entry( in Part 2)

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

Oracle BI EE – Report SUM and Pivot Calculations – An alternative SQL on Oracle 10g and above – Equivalence to MDX – MODEL Clause – Part 2

Posted by Venkatakrishnan J on May 16, 2009

One of the few things that was discussed in the BI Forum was the fact that BI EE sometimes generates absolutely un-necessary queries while using a custom Report Sum. For example, if you look at the report below, you would notice that we have added a custom summation row after every Year to get the Year wise sub-totals

There are 2 ways of producing this summation. One is by making the BI Server to fire a query back to the database (which would generate the sum) and the other is by letting the BI Server itself to produce the sum. Lets first choose the first option by disabling the report based totals(making BI EE to generate the sub-total query as well).

If you look at the query you would notice that the query generated by BI EE does not actually produce the result in the actual desired format. The query is given below

distinct D1.c2 as c1,
D1.c3 as c2, D1.c1 as c3,
sum(D1.c1) over () as c4
select sum(T10976.UNITS) as c1,
T10939.FISCAL_YEAR_DSC as c2,
T10881.CHANNEL_DSC as c3
TIME_DIM T10939,
UNITS_FACT T10976 where ( T10881.CHANNEL_ID = T10976.CHANNEL_ID and T10939.MONTH_ID = T10976.MONTH_ID )
group by
order by c1, c2

This query would not generate the data in the desired format(instead of new summation row, this would generate a new column that BI Server would have to manipulate). So, this begs the question of how do we generate a sql which produces an output exactly same as the above report. This in itself is a nice sql challenge as it is not possible out of the box using normal sql constructs. This is where MODEL clause can come to our rescue. In the last blog entry on this topic, i had shown you how to go about generating AGO and ToDate functions using direct sql on the summed up result set instead of the base tables. In the same fashion, MODEL clause can be used to generate custom sub totals. For example, the same query above can be re-written as shown below

               T10939.FISCAL_YEAR_DSC as FISCAL_YEAR_DSC,
               T10881.CHANNEL_DSC as CHANNEL_DSC
               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)

In order to use MODEL, we would have to understand certain basics of how it can be used and in what situations it can be used. MODEL was introduced to generate sql queries that enables an end user to visualize the data in a multi-dimensional format. One can generate Multi MODEL references to do currency restatements etc. To understand this lets look at the BSO architecture diagram of Essbase. As you would probably know, BSO option consists of 2 parts

1. Sparse Dimensions that are stored as an index – So, these dimensions are typically stored in a normal relational row-wise format in Essbase.
2. Dense Dimensions that are stored as an Multi-Dimensional Array – These dimensions form a multi-dimensional array. The pointer to the array is denoted by the corresponding sparse dimension combination

In the same way, a MODEL clause consists of an index denoted by the Partition By Clause. The multidimensional array or a Block is denoted by the DIMENSION clause. The only difference between the 2 is the fact that you would have multiple measure blocks per partition by clause dimension combination. In the case of essbase, an index entry points to only one block. The reason for this is, MODEL clause does not treat the measure values as a seperate dimension. So, a MODEL clause treats a summarized sql query as shown below

If you understand this analogy, it would be easier to understand the analogy between Essbase member formulas/calculation scripts to the MODEL RULES clause. I would go into detail of how these 2 are related in a future blog entry. For now, lets take another pivot table example as shown below.

As you see, the above is a very simple pivot table containing a calculated row (DIrect Sales+Catalog). If you look at the query generated by BI EE, you would notice, that BI Server would be doing this calculation in memory. Now, our aim is to generate the pivot table report with the calculation too in a single query using MODEL clause

select T10939.FISCAL_YEAR_DSC as c1,
     T10881.CHANNEL_DSC as c2,
     sum(T10976.UNITS) as c3
     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
order by c1, c2

In order to do that, we would have to generate a rule that would produce a new set of rows in addition to the existing rowset.

               T10939.FISCAL_YEAR_DSC as FISCAL_YEAR_DSC,
               T10881.CHANNEL_DSC as CHANNEL_DSC
               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)
RULES (UNITS['Calculated'] = SUM(UNITS)[ANY])

As you see, MODEL clause has quite a few advantages whenever we have data manipulations on a summarized result. We shall see more complex examples in a future blog entry.

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

Brighton BI Forum – Updates – Day 1

Posted by Venkatakrishnan J on May 15, 2009

I just woke up after a very bad nauseating night yesterday. I believe the time lag finally got to me. In the retrospect, when i think about yesterday’s presentation that i did, i thought there were a few things(in fact a lot) that i could have done better. Mark was kind enough to say that this was one of the best sessions so far, due to the number of questions. But again i think i could have done more justice to it by being well prepared. For my own memory following are the key aspects of any presentation that one would have to focus on

1. Get the flow correct – I missed that completely yesterday. For the 1st 15 mins, i did not get any single question which meant that i was not coherent enough to make the audience understand.
2. Get the content to match the flow – Well, i thought i had this right as well. But when i was going through the slides (during the presentation which was another mistake) i realized the fact that it had more words than actual helpful content
3. Rehearse atleast once before you present – Well, i thought this was not needed at all. To my utter dismay, i was found lacking in this aspect as i could not get the audience glued to the presentation.

Christian was kind enough to fill in and after that the Q&A session went very well. Unfortunately, i could not get any photos to post here. Let me get try to take them today. All in all the conference was good so far. There were key presentations from Craig Stewart who presented the Roadmap on ODI. Today we have a session from Mike which i believe should be interesting as he would be presenting the Roadmap on 11g. All in all the conference has been excellent so far and i should thank Mark & Jon and their team of Borkur, Peter, Ragnar made us feel at home. It should be another interesting day of presentations today.

Posted in All Posts | 1 Comment »

Oracle BI Forum – Brighton Conference – Update

Posted by Venkatakrishnan J on May 14, 2009

As you might probably know already, i am right now at the Brighton – Oracle BI Forum for one of my presentations on BI EE and Essbase Security integration. More than the conference, i believe this has given me an excellent opportunity to actually interact face to face with the fellow active bloggers like Christian, Andreas, fellow forumers, conference presenters and of course with Mark, who was pivotal in organizing this entire conference. Now i know with whom i have been interacting over emails :-). I arrived yesterday pretty late and just about missed Mark’s keynote. Should not be missing any presentation today. I am not sure how much of an interest my presentation would invoke as its more tilted towards with the under-pinnings of Java API of Essbase. Well lets see. Will keep everyone posted about the conference updates here. Of course, do follow Mark’s twitter feed if you want frequent updates.

The above was taken today morning from my room. The view is breathtaking though i have not done justice to it with the above picture. I would post more later.

Posted in All Posts, Conference, General | Leave a Comment »