Rittman Mead - Delivered Intelligence

In the last 2 blog entries, i had covered 2 new features of EPM 11 Essbase. They were Format Strings and Varying Attributes. In today’s blog entry we shall see another good feature that was introduced in the EPM 11 release called as XOLAP. Though I have covered this before here, i  thought it would make sense if i introduce this again in the context of the BI EE – Essbase connectivity.

Prior to XOLAP, Essbase supported HOLAP (still does) wherein one can drill from an Essbase cube to a relational source(only on BSO cubes) thereby providing a drill-through. It also supported something called as LRO’s in BSO cubes wherein one can attach an artifact to a database cell. What was not possible though was visualizing relational and Essbase data together. For example, we might have Actuals loaded inside Essbase but Budget might be obtained directly from a relational source. In such cases HOLAP cannot be used directly(though some workarounds are possible). With the advent of BI EE – Essbase connectivity in the 10.1.3.3.2 release, such complex integration cases have been made possible within BI EE framework using conforming dimensions. For details on how this is done, check out the ODTUG white paper here that Mark and myself had created. But what if we want this kind of reporting in Excel-addin or smart view or any other downstream tools that use Essbase. This is where XOLAP can be very helpful.

For the sake of demonstration, i would use the Global schema here. Lets first start with building a XOLAP cube using the Essbase studio. Start with importing the data source and then creating the model.

Then build your hierarchies and deploy the cube as a XOLAP cube. Remember whenever a XOLAP cube is created, it is an ASO cube. Also, it gets created with “Duplicate Members” turned on.

Deploy this cube. Once the deployment is done, you can login to Excel-add in and view the data.

We now can report directly on a relational source through Essbase from Excel-add in. Our idea is to have a similar reporting structure but also have one more measure called Price which would be coming in directly from Essbase itself. In order to achieve this, create another ASO cube directly in EAS or in the Studio with a similar dimensional structure. It is not necessary that the ASO cube should have an exact dimensional structure as the XOLAP cube. But in our case for demonstration, we would create an exact similar structure. There would be another measure in the ASO cube called as Price.

Now load some data into Price measure alone and aggregate it.

Basically we have 2 cubes, one reporting on relational data using XOLAP and the other is a normal Essbase ASO cube. Now, in order to have a report with both Units and Price measure together, we need to create an additional ASO cube called GlobTarg which will be fed by the XOLAP and the ASO cube through transparent partition. So, lets first create the outline of GlobTarg first. Ensure that it has both Price and Units measures.

Now create 2 transparent Partitions,one with the XOLAP cube as the source and GlobTarg as the target and the other with the Price ASO cube as the source and GlobTarg as the target.

While creating the partition, map the corresponding source measures to the target measure. Once this is done, you can report directly on GlobTarg ASO cube. And you should be able to report both on the ASO as well as the relational source together.

Another excellent feature that was introduced as part of the EPM 11 release was the support for varying attributes. Varying attributes help in providing different perspectives of multiple attributes of a dimension over time. One can visualize this as SCD 2 in a relational world. For example, lets take the Sample –> Basic cube and look at the various attributes that have been defined.

As you see, this cube has 5 different attributes defined on Product and Market dimensions. As of 9.3.1 release of Essbase one can have only static attributes defined. If varying attributes had to be defined, they had to be modeled as separate individual dimensions. Static attributes are those attributes that remain constant for a specific member. For example, lets assume that a product X has a static weight of Y ounces when it was introduced. If the manufacturing company decides to repackage the product X with a weight of Z ounces, then as of 9.3.1 release all the sales data mapped to Y ounces would switch over to Z ounces(similar to SCD-1 in a relational DW world). But in most cases, we want to see the sales data corresponding to their weights so that one can analyze the sales drop or increase due to the new product packaging. This is called as varying attributes over time. Sometimes, the same product X can be packaged with Y and Z ounces depending on Market. In this case, the weight varies over Time as well as Market.

With the advent of EPM 11.1.1.0, one can enable tracking of metrics over varying attributes as well. In the above outline, lets take the example of Pkg Type attribute. This has 2 values

1. Bottle
2. Can

This packaging can vary for products across multiple Markets. Also the packaging can vary for a product in a specific market over time. To enable varying attributes, one would have to first enable this feature while creating an outline or later.

In order enable the attribute Pkg Type on the product dimension, one would have to choose the attribute and the set of independent dimensions. Independent dimensions are those dimensions over which the attribute varies like Market and Time.

Continuous dimensions(in the screenshot above) are typically those dimensions like Time where there is a chronological order and where we can specify a range. After this has been enabled, while setting the attribute for each product, the Market and Time would have to be chosen as well. For continuous attributes, one can specify a range.

For each product set the Time range over which it is planned to be sold and also the market. In the above example, the product 100-10 is sold in New York Market from Jan till Dec in Bottle Type and in the Massachusetts Market from Jan to Dec in Can type. The same kind of association would have to be done for each and every Market for the corresponding Pkg Type.

The above basically provides a relational visualization in a multi-dimensional cube. This also provides analysis of data in multiple perspectives. For example, we might be needing a report wherein we would like to analyze the sales for the list of products that were sold as Bottles in New York from Jan-Dec with a perspective of the attribute setting that we had in July. These kind of queries can be answered pretty easily by Varying attributes. In a future blog entry we shall see how these varying attributes can be leveraged from BI EE. As of the current release in BI EE, one can only use the default query context/perspective. One cannot alter the perspectives due to the limitation of modifying certain parts of the MDX query in BI EE. Having said that, it is possible to leverage the varying attributes using the default perspective. I will cover this in the coming weeks.

Well we’re back now from two weeks in the States, the first week of which was vacation in San Francisco and Yosemite, the last was at ODTUG Kaleidoscope 2009 in Monterey. Well done to the ODTUG team and to the Oracle ACE program for an exceptionally-well organized conference, as I’ve said in the past ODTUG Kaleidoscope is big enough to draw in the best speakers but small enough to get to meet everyone, and Mike, Kathleen, Crystal and the rest of the team make us all feel very welcome. Thanks again for the invite over.

As I had the family with me I spent a bit less time at actual sessions this time, but still made it to the key events. Tim Tow and Edward Roske did an excellent job of organizing the Hyperion stream and gave me a few ideas for the BI stream for next year; on reflection, although we had some good BI speakers this year the BI stream itself was a bit disjointed and didn’t really have the coherence that the Hyperion stream did. Next year our plan is to take a much more proactive approach to the BI content and try and organize it in a similar way to the Hyperion sessions, with streams dedicated to ETL + DW, OBIEE and so on. If anyone reading this is likely to go next year and wants to help shape next year’s agenda, drop me a line and we’ll make a start soon.

Rittman Mead had a great turnout at the conference, with our names on the lanyards and Stewart Bryson kicking off the sessions with a deep look into the functonality of Oracle Warehouse Builder. Stewarts’ presentation is on our Articles page and here’s a quick video clip of him presenting:

In case you’re wondering, that’s Joe Leva (our US MD) and Lewis Cunningham (Oracle ACE Director) that I pan to towards the the middle of the video.

My two sessions were on Optimizing Oracle BI Enterprise Edition, and a joint paper I wrote with Venkat on Creating Hybrid Relational/Multi-Dimensional Data Models using OBIEE and Essbase. Both papers are on are articles page, including a white paper that accompanied the Essbase paper that goes into a number of different integration use-cases. Now that Venkat has joined us you can expect him and I to be authoring a bunch of other papers together, and to start things off we’ve had an updated version of the Essbase and OBIEE paper accepted for Open World 2009 in San Francisco.

Unfortunately as Venkat has only just left Oracle and joined us he couldn’t make it in person to Monterey, however I had a good stand-in as my six year-old son Scott was able to introduce me instead … we’d talked about this the evening before and he was up for doing a short introduction, and here he is introducing me at the start of the talk:

Well done to Scott! I’ll have him doing the demos next time…

Well for now it’s a quick turnaround and repack, as I’m off to Istanbul tomorrow to deliver a two-day Oracle 11g Data Warehousing seminar for Oracle University. After that it’s back up to London for a couple of days of client work, and starting to write the presentations and papers for Open World 2009.

If you had read my blog entry here, i would have shown how to go about creating textual measures in Essbase. Though that was a new feature introduced as part of the EPM 11 release, one related feature that i did not cover was the introduction of Format Strings. This probably is one of the very important features from an end user reporting standpoint. In a couple of implementations that i was involved in on 9.3.1, i faced a situation wherein we had to control the format of the data within Essbase itself. Unfortunately, since that was not possible in 9.3 release, custom formatting had to be applied in each and every downstream tools like Excel Add-in, HFR, BI EE etc.

Format strings help in controlling the output format of the measure values. For example, if we have an Expense account and a revenue account, the normal reporting requirement is to show the variance of the current quarter Actuals with the last quarter Actuals for both the type of accounts. For Revenue accounts, when current quarter is more than last quarter, then the variance would be positive else it would be negative. For Expense accounts, when the current quarter is less that last quarter, then the variance would be positive else it would be negative. Also, all negative values would have to be shown within braces and not as negative values.

Normally to implement the above requirement, we would have to write a custom formula to change the variance formula based on the Expense/Revenue UDA in an ASO cube(BSO cube has Expense/Revenue based reporting properties. But it is very limited in its usage). Also, adding braces to negative values would have to be done in the front end. Lets see how Format Strings can help us in achieving this requirement. Lets take the example of the Sample – Basic BSO cube. The strange aspect to the Format Strings is the fact that the implementation is done using MDX even in a BSO cube (should give a hint about the strategic direction :-)).

Lets first look at the outline. There are 2 measures Sales and COGS. Sales is a revenue measure and COGS is an expense measure.

Now lets create another member called Variance as shown below in the Scenario dimension.

This variance member is actually updated through a calculation script shown below (Format strings would not work on members having a member formula in BSO cubes)

SET CACHE HIGH;SET LOCKBLOCK HIGH;

CLEARDATA "Variance";

CALC DIM ("Year");

"Variance" = "Actual"->&CurrQtr - "Actual"->&LastQtr;

CALC DIM ("Market","Product");

Run the calc script after the data load. Following would be the variance values in Excel add-in.

As you see, since there was no formatting applied on the Variance values, both COGS and Sales are getting treated the same way i.e shown as positive numbers. Now, in order to apply format strings on this, lets first convert the outline into Text measure compatible outline.

After that, go to the member properties of the Variance member and add the MDX shown below

MdxFormat(IIF(Is([Measures].dimension.CurrentMember, [COGS]),

    CASE WHEN cellvalue() > 0 then Concat(Concat("(", numtostr(CellValue())), ")" ) else numtostr(-cellvalue()) end,

    CASE WHEN cellvalue() < 0 then Concat(Concat("(", numtostr(-CellValue())), ")" ) else numtostr(cellvalue()) end

))

Now if you go to Excel add-in, and retrieve the same data, you would notice that the accounting format would have got applied automatically.

This is an excellent feature. In fact using this alone, one can mimic textual measures that i explained before. In a future blog entry i would show how this can be leveraged from other downstream tools like HFR, BI EE etc.

Whilst Mark, Stewart are Joe are enjoying Monterery, I am on the train going up to London to meet Borkur to do a joint presentation entitled High Availability in Oracle BI EE in the BI Enterprise stream of the UKOUG Conference Series Hyperion & BI 2009 event. The paper is a fairly technical one, going through the steps you need to perform to cluster OBIEE and discussing some of the pros, cons and pitfalls. If you are in the area please come along. I will post the slides after the event.