Tag Archives: Hyperion Essbase
Oracle BI EE 10.1.3.4.1 & Essbase Connectivity – Repository Design – Enabling drills on Evaluate Columns – Part 1
One of the common questions that generally come up in the BI EE and Essbase integration is, how do we enable drills on Evaluate based columns. I have covered in detail here with regard to the usage of Evaluate based MDX functions. As of the current release in BI EE, most of the reporting requirements on Essbase sources cannot be met out of the box without using Evaluate. Though Evaluate functions can be very useful to solve many reporting requirements, the major flipside in using them though is the fact that one cannot use repository based drills out of the box. Today we shall look at a technique that would enable drills on such Evaluate based columns.
For illustration purposes, i would use the Demo->Basic cube. The idea behind this technique is in making the BI Server to jump across multiple logical table sources while doing the drills. For example, lets take a look at the report below.
As you see, its a report containing all the members of the Markets dimension, Generation 2 of Year dimension and the corresponding Sales. The All Markets column has been obtained using the Evaluate function below
EVALUATE('%1.dimension.members',
"localhost"."Demo".""."Basic"."Gen1,Market")
The All Markets column contains all the members from all the generations of the Markets Dimension. How do we make sure that clicking on a member at any level automatically takes us to the lowermost level without using Navigation and of course without getting any errors? In order to do this, the first step is to create a hierarchy as shown below
Essentially what we are doing is, whenever we click on any member in the All Markets column, it will drill down to the Level-0 column. Ensure that All Markets column is part of the drill key. Now, go to the logical table source of the Markets dimension and remove all the mappings for all other columns except the All Markets column.
The next step is in determining the number of generations that contribute to the new column. For example, you might have an evaluate function which would produce a subset of Generation 2 alone. In that case you would need only one extra logical table source. In our case above, the All Members column produces members from all the generations. So, we would need 3 additional logical table sources as shown below.
Each logical table source would have a corresponding mapping to the All Members column. This is shown below.
Ensure that each logical table source has the corresponding level assigned to ensure that BI Server does that jump. So, whenever anyone clicks on any member in the All Markets column, the BI Server would do an in-memory union on the remaining 2 logical sources. But since one member can be found in only one source, there would not be any duplicates in your report. When you click on say Market Member, it would take you to the Gen3, Market column with proper filters applied on the corresponding All Markets mapping column in that logical table source. From then on it would be normal drills for the end user. For example, if we click on the East member (which is actually in Gen2), we would get the below
In the same way one can drill on any member at any level. An example screenshot below after drilling on the Market member(Gen1).
This technique can be used for any kind of member returning evaluate function. We shall see more use cases in the future blog entries.
Hyperion Essbase 11.1.1.2 – XOLAP – MDX to SQL Conversion – Part 1
One of the interesting aspects of XOLAP is the fact that it exposes Essbase as a dynamic relational aggregation engine. XOLAP to an extent is pretty good in determining what kind of sql should be pushed back to the relational sources and what requires custom in-memory calculations. For example, lets first start with a simple XOLAP cube that we built the other day using the Global Schema.
Lets import this XOLAP cube into BI EE and without changing any default aggregation settings.
After this import lets first create a very simple report containing the Generation 1 of Channel and Product dimensions.
To generate the above report BI EE basically generates an MDX that is fired back to Essbase.
With
set [Channel Hierarchy1] as '[Channel Hierarchy].Generations(1).members'
set [Product Hierarchy1] as '[Product Hierarchy].Generations(1).members'
select
{ [Units Hierarchy].[UNITS]
} on columns,
NON EMPTY {crossjoin ({[Channel Hierarchy1]},{[Product Hierarchy1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [GlobXola.GlobXola]
MDX is used to tell Essbase on what is needed in the report. Then Essbase converts the MDX to the physical sql. Unfortunately, XOLAP does not give you an automated way of finding the physical sql fired back to Essbase. The main reason for this is, it just does not fire the sql alone in some kind of reports. But for the above report, to determine the sql query, i had to use the v$sql views to find out what was getting fired by XOLAP(i would recommend DBMS_MONITOR package if you are on 10g). The converted sql from MDX looked like the one shown below
SELECT
cp_1108."TOTAL_CHANNEL_DSC",
cp_2106."TOTAL_PRODUCT_DSC",
SUM(cp_107."UNITS"),
count(*)
FROM
(("GLOBAL"."CHANNEL_DIM" cp_1108 join "GLOBAL"."PRODUCT_DIM" cp_2106 on (1=1))
join "GLOBAL"."UNITS_FACT" cp_107 on (cp_1108."CHANNEL_ID" = cp_107."CHANNEL_ID")
AND
(cp_2106."ITEM_ID" = cp_107."ITEM_ID"))
WHERE
( (cp_1108."TOTAL_CHANNEL_DSC" IN ('All Channels')))
AND
( (cp_2106."TOTAL_PRODUCT_DSC" IN ('Total Product')))
GROUP BY cp_1108."TOTAL_CHANNEL_DSC", cp_2106."TOTAL_PRODUCT_DSC"
The sql above is pretty straight forward. But one interesting point to note is the filters on the topmost node of the outline (though i have not explicitly applied the filters). So, to an extent XOLAP simply is not a conversion engine. It is an outline aware SQL conversion/aggregation engine. To put this under more tests, lets now include one more column in the report. But this column would contain an EVALUATE MDX expression to pull in all the Time dimension members.
EVALUATE('%1.dimension.members',"Time Hierarchy"."Gen1,Time Hierarchy")
Again, XOLAP has basically converted our BI EE report into its corresponding MDX given below
With
set [Channel Hierarchy2] as '[Channel Hierarchy].Generations(2).members'
set [Product Hierarchy2] as '[Product Hierarchy].Generations(2).members'
set [Evaluate0] as '{[Time Hierarchy].Generations(1).dimension.members }'
select
{ [Units Hierarchy].[UNITS]
} on columns,
NON EMPTY {crossjoin ({[Channel Hierarchy2]},crossjoin ({[Product Hierarchy2]},{[Evaluate0]}))} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [GlobXola.GlobXola]
But the corresponding sql is what would be of interest to us as the above report cannot be produced using a normal sql. Either it would have to be a union all request or it should use the MODEL clause. But its actually neither of them.
SELECT
cp_1108."TOTAL_CHANNEL_DSC",
cp_2106."TOTAL_PRODUCT_DSC",
cp_3109."MONTH_DSC",
SUM(cp_107."UNITS"),
count(*)
FROM
((("GLOBAL"."CHANNEL_DIM" cp_1108 join "GLOBAL"."PRODUCT_DIM" cp_2106 on (1=1))
join "GLOBAL"."TIME_DIM" cp_3109 on (1=1))
join "GLOBAL"."UNITS_FACT" cp_107 on (cp_1108."CHANNEL_ID" = cp_107."CHANNEL_ID")
AND (cp_2106."ITEM_ID" = cp_107."ITEM_ID") AND
(cp_3109."MONTH_ID" = cp_107."MONTH_ID"))
WHERE ( (cp_1108."TOTAL_CHANNEL_DSC" IN ('All Channels')))
AND ( (cp_2106."TOTAL_PRODUCT_DSC" IN ('Total Product')))
GROUP BY cp_1108."TOTAL_CHANNEL_DSC", cp_2106."TOTAL_PRODUCT_DSC", cp_3109."MONTH_DSC"
All that the sql does is, it produces level-0 data at the month level. Every other outline based calculation/aggregation is done by the Essbase at run-time. The next question would be, how are we sure that Essbase is doing the Aggregation instead of the BI Server. In order to eliminate this possibility, lets fire the MDX directly in Essbase.
As you see, the MDX itself has produced the year and quarter level data even though the sql fired got the month level. So, every outline based operation is done at the Essbase layer using the Essbase aggregation engine. This opens up a lot of possibilities for reporting. We now have 3 levels of aggregation for relational sources.
1. Relational Database itself.
2. Essbase
3. BI Server
The major advantage of having this flexibility is that we can determine what is best depending on the system scalability etc. Also, it provides Essbase specific calculation capabilities to relational sources. I would cover more advanced reporting use cases in the coming blog entries.
Hyperion Essbase 11.1.1.2 – XOLAP – Reporting on Relational and Essbase sources together – Transparent Partitions
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.
Hyperion Essbase 11.1.1.2 – Varying Attributes
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.
Hyperion Essbase 11.1.1.2 – Altering Measure Formats – Format Strings and Text Measures
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.
