Business Intelligence – Oracle

Archive for December, 2008

Hyperion Essbase 9.3.1 – OBE Series – Designing Essbase Databases – BSO Outlines & Data Load – Global Schema – Part 1

Posted by Venkatakrishnan J on December 11, 2008

The OBE series on Oracle OLAP here inspired me to actually write down this new series for Essbase. In this article/blog series we shall see how to go about designing BSO & ASO cubes based on the data from the Global Schema. We shall see how to go about designing outlines(along with the sparse and dense settings), designing rule files, loading data and also currency conversions. All these would be based on the global schema (which you can install from the link above). I would try to cover the various aspects of outline builds and the various properties in the outlines. Let us start with identifying the database tables that we shall be using in this series. Listed below are the tables.

1.    CHANNEL_DIM
2.    CUSTOMER_DIM
3.    PRODUCT_CHILD_PARENT
4.    TIME_DIM
5.    UNITS_FACT
6.    PRICE_AND_COST_FACT

So, basically we would have 4 normal dimensions(Channel, Customer, Product & Time) and an Accounts dimension (or a measure dimension) containing 3 measures (UNITS, UNIT_PRICE and UNIT_COST). UNITS has a grain of all the 4 dimensions (Channel, Customer, Product & Time). UNIT_PRICE and UNIT_COST has a grain of only 2 dimensions (Product and Time). So, to start with lets create a sample Application & database called Global->Global using Block Storage. The first step in loading a block storage cube is in creating the dimensions and identifying which dimension is sparse and which is dense. Typically this activity is the most important step while designing a block storage cube since this is instrumental in determining the Cube Aggregation time (this is referred as calculation time in BSO). So, lets create these dimensions first in the Outline.

       

       

Now, the next step is in identifying which dimension is sparse and which is dense. By default, in a Block Storage cube each and every sparse dimension combination is stored in a row-wise format (assume rows in a relational table). Each of these unique combination of sparse members’ memory locations are stored in a easily retrievable index. And the dense dimension member combination is stored as a multidimensional array called as a block. The size of the block determines the data retrieval and calculation performance. Typically, the block size can be anywhere between 8K to 64K(but there are cases wherein block sizes of the order of 200K is desired). In our case, we shall use the below sparse and dense settings.

Channel Dimension – Sparse
Customer Dimension – Sparse
Product Dimension – Sparse
Time Dimension – Dense
Measures Dimension – Dense

In order to modify this we need to go to the outline properties and set the sparse/dense settings.

       

Once this is done, the next step is to determine an approximate number of dimension members for each dimension that we would expect to store in our cube. This is something that can be done after loading each dimension or before. We shall do that now. All we need to do is to arrange the sparse dimensions in the increasing order of number of stored members. In our case, Customers would have more stored members than Product and Channel. And Products dimension would have more stored members than Channel dimension. We have done this activity to determine the order of the dimensions in the outline. As a general rule (there can be exceptions), it is recommended to arrange the dimensions in an hour glass format. This is depicted as shown below

       

So, in our case, arrange the dimensions as shown below. And also make the Measures dimension as the Accounts dimension. The advantage in Essbase is that we have the opportunity to set the aggregation, storage and other properties on a member by member basis. These properties can be automatically loaded through rule files as well. Do not make the 3 measures to roll up. Also, make the Accounts as a label only member.

       

I wanted to have this as a seperate blog entry since this is very crucial in loading an Essbase Cube. In the next blog entry we shall see how to go about loading the dimensions using rule files. We shall see 2 approaches. One is level based hierarchy rule files and the other is parent based hierarchy rule files.

Advertisements

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

Swiss Chocolates and Bliss!!!

Posted by Venkatakrishnan J on December 11, 2008

I just had to write this blog entry as a means of expressing my sincere thanks to Christian Berg who just happened to send me a bunch of swiss chocolates. The story started with this blog entry here wherein i had blogged about an issue that Christian was facing on the BI EE and Essbase Connectivity. Well, as a means of expressing his appreciation, he just forwarded a wonderful assortment of swiss chocolates. And Christian, i must say i was blown away by this gesture and i truly appreciate it. But of course, now my wife knows whom to blame when i put on 5 kgs of extra weight this week!!!

       

       

       

Posted in All Posts, General | Leave a Comment »

Calling BI Experts – An excellent initiative

Posted by Venkatakrishnan J on December 10, 2008

A very good initiative has been started by Mark on his blog here. If you are an Oracle BI expert and if you want to share/present something on which you are passionate about then just go to his blog and put your comments there. Not everything is available currently in the public domain and this is a chance for you to share/collaborate with other experts. For more details, just visit Mark’s blog.

Posted in All Posts, General | Leave a Comment »

Hyperion Essbase 11.1.1 – Handling Textual Facts – Text List Manager and Text Measures

Posted by Venkatakrishnan J on December 10, 2008

One of the new features which was introduced in the 11.1(or 9.5) version of Essbase is the ability to handle textual measures. This is a very intriguing feature since this ties back directly to BI EE and Essbase Connectivity. We shall look how these 2 are connected later. But for now, lets take a look at how Essbase handles these Text Measures. We shall start with a new Application and Database called TestApp->TestDb. By default Text measures are not enabled. In order to Enable them, one would have to change it in the outline property.

               

Once this is done, lets copy the same outline members from Demo->Basic to TestApp->TestDb.

               

Now our requirement is to have a Text Measure called TrueOrFalse. Basically, this measure tracks whether a specific product is available at a specific Market at a specific point in Time. Since we also have a Scenario Dimension (which does not make sense for this metric) we shall use Actual for the metric tracking. So, a value of “True” states that a product is available in a specific Market at a specified point in Time. And a False value states otherwise. In order to load this textual measure, we need to go through a specific set of steps. Currently, an arbitrary entry of Text inside an Essbase Cell is not allowed. The text measure values that can be stored would have to be part of a list called as Text Lists. Only those values (consider them as Text Lookups) that are stored in the Text Lists can be stored inside Essbase. SoThe first step is to create a Text List. Since an outline can have more than one Textual measure, we can have more than one Text List. This list is created from the Textual Manager Tab in the outline. Since we have only one textual measure, lets create one Text List called TrueOrFalse. By Default every text list will have #Missing and #OutofRange. This is to enable NULL cells and Out of Range cells. These values cannot be removed.

               

In our case, we would need 2 Text values called as True and False. Each of them would need an unique ID (which would have to be numeric). So, the way this works is, whenever the Text Value (say True) is encountered in the data source, Essbase would lookup in the Text List and get the corresponding ID stored. Then this ID would only be stored inside the Essbase cell. So, effectively only numeric values get stored inside Essbase. But during data load and retrieval these Text Lists would be used to retrieve them. In order to test this, lets create a simple data source table as shown below

create table TextFactLoad(Year Varchar2(100),Market Varchar2(100), Product Varchar2(100), Scenario Varchar2(100),Accounts Varchar2(100), FACTTEXT Varchar2(100);
insert into TextFactLoad values ('Jan','Boston','Stereo','Actual','TrueOrFalse','True');
insert into TextFactLoad values ('Jan','Boston','Stereo','Actual','TrueOrFalse','False');
Commit;

Now, the next step is to create a rule file which will load the above data into the Essbase Cube.

               

Once the rule file is created lets try to load the data into Essbase.

               

Now, if we take a look in Excel-Addin, we would see the Textual Measure value

               

But if we take a look from Visual Explorer (VE uses MDX to fire queries back to Essbase), we would see that the ID’s would actually be displayed instead of the Measure Values (True and False).

               

Now, lets pick the actual MDX query fired by VE and fire that directly within Essbase.

WITH
SET [Tableau Set 0] AS
'Except(
Descendants([Market].[East], [Gen3,Market], SELF),
{[East].[New_York]})'
SELECT
{[Accounts].[TrueOrFalse]} DIMENSION PROPERTIES [MEMNOR],[ANCESTOR_NAMES] ON COLUMNS,
NON EMPTY CROSSJOIN(
[Tableau Set 0],
CROSSJOIN(
{([Audio].[Stereo], [Qtr1].[Jan])},
[Scenario].Generations(2).Members)) DIMENSION PROPERTIES [MEMNOR],[ANCESTOR_NAMES] ON ROWS
FROM [TextApp].[TextDb]

               

This is quite strange. We are getting the ID for the first one but for the second one we are getting the measure value. I believe this is expected since we are supposed to handle all the Textual measures in MDX using CellValue function. If that is not used, then the results can be quite different. I would blog about the significance of these new MDX functions in a future blog entry.

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

Oracle BI EE 10.1.3.4 & Essbase Connectivity – Report Use Case 3 & 4 – CASE Statements and Variable Measures – Adding and Subtracting Measures

Posted by Venkatakrishnan J on December 9, 2008

We are in the last article(probably one more) of our article series on BI EE and Essbase Connectivity. The one piece that is pending is the Essbase Security and BI EE which i shall cover later. In this article we shall see 2 report use cases which are typically common requirements especially on multidimensional data sources. Lets start with the first use case

A report containing Gen2, Product, Gen2, Market,Actuals and Budget. All the members that are less than 20000(for Actuals) should be displayed as 0. And for Budget all the values should be displayed as is

Typically, the usual way to approach this problem is to build a report like the one shown below. And then on top of it a filter on Actuals > 20000 would be applied.

       

But the problem with the above approach is that the filter would be applied for the entire report instead of the Actuals metric alone. So we would be losing out on members with proper data in the intersections.

       

Now, in order to overcome that, lets remove the filter and in the Actuals column enter the below EVALUATE function

EVALUATE('case when (%1.dimension.currentmember,%2.dimension.currentmember,[Actual]) > 20000 then (%1.dimension.currentmember,%2.dimension.currentmember,[Actual]) else 0 end' AS INTEGER,Market."Gen2,Market",Product."Gen2,Product")

       

Now, if you look at the report, this would show the data for all the intersections for Budget as well as Actual. Also, all the Actuals which have data < 20000 would have been converted to 0.

       

Lets move on to the next use case. A typical requirement is to subtract [Actual] and [Budget] at the reporting layer rather than having an outline member called Variance. The use case is stated below

A report containing Gen2, Product, Gen2, Market, Actual, Budget and a report level variance (a calculated column from BI EE which is Actual – Budget).

We shall use the same report above. Just pull in all the columns Gen2, Product, Gen2, Market, Actual and Budget into the report. Now, add one more column and in the formula of the column use the below evaluate function

EVALUATE('(%1.dimension.currentmember,%2.dimension.currentmember,[Actual]).value - (%1.dimension.currentmember,%2.dimension.currentmember,[Budget]).value', Product."Gen2,Product",Market."Gen2,Market")

       

Though both the above use cases are pretty simple, i wanted to cover them in this blog entry as i found these occurring quite a few times in our internal forums.

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

Oracle BI EE 10.1.3.4 & Essbase Connectivity – Report Use Case2 – Drills from Prompts – EVALUATE in Prompts

Posted by Venkatakrishnan J on December 8, 2008

In the last blog entry we saw one use case wherein we changed measure metric values depending on different dimension members. In this blog entry, lets look at another common multi dimensional reporting requirement. The typical requirement is to choose any member of a dimension in a prompt and then make the report to display all the child members(including that member) of the chosen member. For example, consider the report below.

       

As you see this is nothing but a very simple report containing All the members of Accounts, Gen2 of Year, Gen2 of Products and the their corresponding Actuals, Budget and Variance. Now, our aim is to have a prompt which will display all the members of the Accounts dimension. And on choosing any one member(at any level) in the prompt, we need to display the above report with the corresponding children of the chosen member for the Accounts dimension alone. So, let us start with building the prompt first. When we build a prompt especially for Essbase data sources, we need to make sure that we include a column which does not have an “is prompted” filter. This is necessary else, the MDX generated would be wrong especially when we use EVALUATE functions. Or the best option is to use a CASE WHEN statement which i have covered before in many blog entries. So, in the formula column of the prompt enter the below formula

CASE WHEN 1=0 THEN "Year"."Gen3,Year" ELSE 'Test' END

       

Then in the Show section of the prompt, make the display of prompt values to come from SQL results. Then enter the below logical SQL.

SELECT
EVALUATE('%1.dimension.members',Accounts."Gen3,Accounts") saw_0
FROM "Basic#1" ORDER BY saw_0

Also, make the prompt to set a presentation variable called Drill.

       

When we preview the prompt, it should show us all the members in the accounts dimension.

       

Now, lets go to the report above. And in the Accounts formula column, enter the below EVALUATE function

EVALUATE('Intersect(Descendants([@{Drill}{Accounts}],1,SELF_AND_BEFORE),%1.dimension.members)',Accounts."Gen3,Accounts")

The above EVALUATE function would basically display the children of the presentation variable Drill. The SELF_AND_BEFORE property would basically just include the member as well. We are also doing an intersect operation in order to ensure that the EVALUATE does not throw any error. Now, lets include both this report as well as the prompt in the dashboard. Let us choose Profit in the Prompt.

       

As you see, we only get the immediate children of Profit (which are Margin and Total_Expenses). Similarly, this would work for all the members without any children as well. For example, when COGS is chosen, it will display only COGS in the report since it does not have any children.

       

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

Oracle BI EE 10.1.3.4 & Essbase Connectivity – A Report Use Case

Posted by Venkatakrishnan J on December 5, 2008

While we are in the midst of our Essbase and BI EE connectivity article series, i got an email from Christian Berg (who used to frequent the BI EE forums when i was active before) asking whether a specific report was possible using MDX without using complex and complicated UNION requests. I thought it was worth publishing here just to emphasize the point that i made in the previous 4 articles. MDX is very powerful. Let us take a look at the report. We shall start with Demo Basic outline. I would not paste the exact outline and report screenshots that he sent. Rather i would rephrase that question so that everyone can try this out on the Demo Basic cube. In this case, we shall be using Scenario as our Measure Dimension.

       

       

The report specification goes like this “A report showing value of Actual, Budget and Variance (the measure dimension members) against all the products in Gen3, Products. Actual should show the value of Sales against each product (Accounts dimension member). Budget and Variance should show the value of Sales and Marketing (2 Accounts Members).” In a nutshell the report should look like this

       

Without Using MDX Functions this report is not possible out of the box. The only way is to use UNION requests and even then it would not produce the data in the correct format. So, lets use MDX to generate this report. As a first step pull in the Gen3, Product in our report. Once that is done, pull in the Actuals measure. In the column formula use the below EVALUATE function.

EVALUATE('(%1.dimension.currentmember,[Actual],[Sales]).value',Product."Gen3,Product")

       

This will ensure that we are getting the Actual for all the products with Sales as the Account.

       

Now, the next step is pretty tricky. All we need is a SUM([Product].currentmember,{[Sales],[Marketing]},[Actual]). But unfortunately, MDX would not accept the currentmember function within SUM. And we need to pass a column into EVALUATE. Else it would throw an error. In order to overcome this we shall be using the below evaluate function.

EVALUATE('(%1.dimension.currentmember,[Budget],[Sales]).value - (%2.dimension.currentmember,[Budget],[Sales]).value + SUM({[Sales],[Marketing]},[Budget])',Product."Gen3,Product",Product."Gen3,Product")

So, basically what this does is, we are passing Gen3, Product just to make the EVALUATE function work. The first minus will always result in a zero and that is our intention. The actual function should have the been the SUM part along. Similarly, use the below function for Variance

EVALUATE('(%1.dimension.currentmember,[Variance],[Sales]).value - (%2.dimension.currentmember,[Variance],[Sales]).value + SUM({[Sales],[Marketing]},[Variance])',Product."Gen3,Product",Product."Gen3,Product")

Now, if we look at the report, we would get the desired scenario values without any complicated UNION requests. But remember to disable the drills.

       

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

Oracle BI EE 10.1.3.4 & Essbase Connectivity – EVALUATE and MDX Functions – Multi Level Reporting – Continued

Posted by Venkatakrishnan J on December 5, 2008

Yesterday we saw the 2 types of MDX functions that are fired by BI EE via the EVALUATE function. The important point to know while using these functions is in understanding when a function is treated as a Row Level MDX function and a Column Level MDX function. In order to understand that lets start with a simple report as shown below

As a generic rule, any EVALUATE when used against the measure dimension would be treated as a ROW Level MDX Function. There are some exceptions to this rule. But for now lets not go into those exceptions. And any EVALUATE against any other dimension (non-measure dimension) would be treated as a Column Level MDX Function. In order to make a column level MDX function to be treated as a Row Level MDX Function, the dimension should also be included in the report. To understand this lets start with the above report. Now in the above report, our aim is to make the 2 column in the report as a EVALUATE Column Level Function. So, basically we want 3 members (in multiple levels. For eg we need Market, West and Dallas in the report) in the Market dimension to be displayed. So, lets change the formula of the second column and include the below EVALUATE function

EVALUATE('Intersect(%1.dimension.members,{[East],[Market],[Dallas]})',Market."Gen2,Market")

As you see, we have got a report that we needed. Lets take a look at the MDX Query

With
set [Year2] as '[Year].Generations(2).members'
set [Evaluate0] as '{Intersect([Market].Generations(2).dimension.members,{[East],[Market],[Dallas]}) }'
select
{
[Scenario].[Actual] } on columns,
NON EMPTY {crossjoin ({[Year2]},{[Evaluate0]})}
properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

As you see, the EVALUATE has gone in as a column level MDX function. Now, since multiple column level MDX functions are not supported now (due to a probable bug), lets remove the evaluate from the above report. Now, lets see a version of the EVALUATE function wherein EVALUATE against the same Markets dimension would be treated as a ROW Level MDX Function. For example Rank. Lets add one more column and in the column formula use the below EVALUATE function.

EVALUATE('Rank(%1.dimension.currentmember,%1.dimension.members)',Market."Gen2,Market")

As you see, we have got the Rank of each member in the outline. Now, lets take a look at the MDX Query

With
set [Market2] as '[Market].Generations(2).members'
set [Year2] as '[Year].Generations(2).members'
member [Scenario].[MS1] as 'Rank([Market].Generations(2).dimension.currentmember,[Market].Generations(2).dimension.members)'
select
{ [Scenario].[Actual], [Scenario].[MS1] } on columns,
NON EMPTY {crossjoin ({[Market2]},{[Year2]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

As you see the MDX Function has got passed as a Row Level MDX function. A Row Level MDX function would be passed only when the source dimension exist in the report as a seperate column. i.e in our above report, we need to have the Gen2, Market Column to generate a rank. Else it would not be generated. In the next blog entry let us look at a sample use case of these MDX functions.

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

Oracle BI EE 10.1.3.4 & Essbase Connectivity – EVALUATE and MDX Functions – Multi Level Reporting

Posted by Venkatakrishnan J on December 4, 2008

In the 2 blog entries before here and here we saw the basics of BI EE and Essbase connectivity. In this blog entry we shall see more complex reporting using the EVALUATE function. Wherever possible try using this atleast in this release. This would be made more easier in the coming releases like 11g. But untill then we need to use these approaches. But the advantage of using this approach is that we would have more control on the report. The MDX functions which can be called through EVALUATE can be classified into 2 types. They are

1. Functions that return data (numeric values or measure values) – An example for this would be SUM, RANK etc (I would refer them as Column level MDX Functions)
2. Functions that returns members, sets or tupules – An example for this would be UDA etc (I would refer them as row level MDX functions as they determine the row level members)

Both of these are supported currently. But before we move further we need to understand what the above 2 mean and how they pan out in terms of the MDX queries. Also there are certain limitation in the extent of the usage of these functions. We shall understand each one of them as move forward.

The above screenshot is nothing but a MDX query containing both the types of MDX functions mentioned above. Currently in BI EE both the above function types are supported only for certain dimension types.

1. Column Level MDX Functions – These are only supported on an Essbase Accounts dimension (even if you switch the Measure Dimension within BI EE, this would work only on the Accounts Dimension within the outline). Multiple Column Level MDX Functions are supported.
2. Row Level MDX Functions – These are supported on all the dimensions. But only one Row-Level MDX function per report is supported

With that brief introduction, lets start with a typical requirement which usually comes up in multidimensional reporting. We shall use the same Demo-> Basic Cube. If you had followed my previous blog entries then revert back the aggregation on the Actuals measure to Aggr_External. The requirement is given below

A report showing all the members of Products dimension, Generation 2 of Markets and their corresponding Sales. Basically multi level reporting.

Without the knowledge of MDX Functions, the only way to achieve this is by using UNION requests from within Answers. One of the main reasons for that is BI EE treats each and every level within a dimension as a seperate column. In our report above, we need all the level members for the Products dimension, only one column(Generation 2 of Markets) and their corresponding intersection values for Sales. But understanding of MDX functions makes the task a lot easier. Lets go to answers and pull up the 3 columns as shown below.

Now in the Products column, enter the below formula.

EVALUATE('%1.DIMENSION.MEMBERS',Product."Gen1,Product")

As you see, this is nothing but a row level formula since we are generating sets of members. Anyone with a knowledge of Essbase would appreciate this fact since from an Essbase standpoint we just need to provide valid combinations of members to get at the intersections. Once this is done lets look at the report and the outline of the products dimension.

As you see, we have got the data for all the products, Gen2 of Markets and their corresponding sales. But one would have to disable drills on this EVALUATE column since the drills would not work. The reason for this is the fact that, BI EE still thinks it is at a certain level and would try to fire MDX queries accordingly when we do the drills. So, as a thumb rule, disable the default drills whenever EVALUATE is used. If drills are required use navigate instead. Also, there are certain drawbacks which we shall see below.

Lets try to use the same evaluate function for more than one dimension (for example products as well as markets). So basically, we want a Sales report for all intersections of the Products and Markets dimensions. The evaluate functions are given below

EVALUATE('%1.DIMENSION.MEMBERS',Product."Gen1,Product")
EVALUATE('%1.DIMENSION.MEMBERS',Market."Gen2,Market")

But this would throw an error shown below.

Lets analyze this further. If you look at the error above, this is nothing but an essbase specific error. Which is strange since our evaluate functions are correct. So, lets take a look at the MDX query that is fired back to Essbase.

With
set [Evaluate0] as '{[Product].Generations(2).DIMENSION.MEMBERS }'
set [Evaluate0] as '{[Product].Generations(2).DIMENSION.MEMBERS }'
set [Evaluate1] as '{[Market].Generations(1).DIMENSION.MEMBERS }'
select
{ [Scenario].[Actual] } on columns,
NON EMPTY {crossjoin ({[Evaluate0]},{[Evaluate1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Demo.Basic]

If you look at the query above, the same set for Evaluate0 is passed twice to the query. This i believe is a product bug when it generates the MDX queries. Now, lets remove one of the Evaluate0 sets in the above MDX query and fire this from Essbase.

With
set [Evaluate0] as '{[Product].Generations(2).DIMENSION.MEMBERS }'
set [Evaluate1] as '{[Market].Generations(1).DIMENSION.MEMBERS }'
select
{ [Scenario].[Actual] } on columns,
NON EMPTY {crossjoin ({[Evaluate0]},{[Evaluate1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Demo.Basic]

As you see, this is clearly a product bug since if we remove one of the Evaluate0 sets (which should actually have been the MDX query for our report above). This is such a shame as the above had such a potential to be used for more complex reporting. But this is something that has to be kept in mind while doing BI EE and Essbase Reporting. Multiple Row-Level MDX Functions are not supported.

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

Oracle BI EE 10.1.3.4 & Essbase Connectivity – Understanding MDX Queries and Reports Tuning – Level Based Aggregations and No Aggregations

Posted by Venkatakrishnan J on December 3, 2008

In the last blog entry we saw how the basic BI EE and Essbase connecitivity works. Now, lets try to understand this in detail further. Lets start with understanding how the aggregation works for Essbase data sources. To begin with we shall change the level of aggregation for the Actuals Measure in the Business Model layer.

       

 

Basically, we have assigned Actuals to Level 1 of both Year as well as Markets. Leave the aggregation to the default “Aggr_External”. So, our intention is that we should get the same value that we get at Level1 of Year for Actuals to all the other levels of the Year dimension. Now, lets go back to answers and create a simple report having Gen1,Year ,Gen2, Year and Actuals.

       

 

This works as expected. But typically this is not possible from Essbase alone. MDX cannot generate values which basically show different values than what is actually present inside Essbase. So, to find out how BI EE achieves this, lets take a look at the Physical query.

       

 

As you see, BI EE basically fires 2 different MDX queries and makes an in memory join to achieve the result. The MDX queries are provided below

With
set [Market1] as '[Market].Generations(1).members'
set [Year2] as '[Year].Generations(2).members'
set [Axis1Set] as 'crossjoin ({[Market1]},{[Year2]})'
select {} on columns,
{[Axis1Set]}
properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

 

With
set [Market1] as '[Market].Generations(1).members'
set [Year1] as '[Year].Generations(1).members'
select
{ [Scenario].[Actual] } on columns,
NON EMPTY {crossjoin ({[Market1]},{[Year1]})}
properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

The first query above basically gets a cartesian product of Gen1, Market (remember we had set a level aggregation of Level1 for Market in Actuals) and Gen2, Year (this is what was chosen in our report). This query does not get any data. Instead it is just used as a temporary join placeholder. The output of the first query is shown below

        

 

The second query actually gets the value of Actuals for Gen1, Market and Gen1, Year (which are the levels for the Actuals measure). BI EE actually does go against the top level node rather than doing the aggregation in-memory. It is OLAP aware atleast in this regard. In the case of relational sources it would fire the group by to get to the top node value.

       

 

The result for the report is then obtained by making a cartesian product of the above 2 queries. This cartesian product is achieved in memory of BI Server. So, as much as possible try to avoid setting aggregation levels at the BMM layer. Leave them as defaults. If you are sure that your output results would be less, then use the level based aggregations.

As a next step let us try to understand what the default aggregation (Aggr_External) does. For that let us change the aggregation for Actual measure to none.

       

 

And lets test the same report as above (With Gen1 Year,Gen2, Year and Actuals measure).

       

 

This is very interesting as well. This basically provides data for all the intersections in your Essbase Cube. Try to avoid this as well whenever you are building your reports. The MDX will basically get every intersection that is possible using a cross join across all the dimensions. There are certain use cases wherein this might be useful. The MDX query is given below

With
set [Accounts4] as '[Accounts].Generations(4).members'
set [Market3] as '[Market].Generations(3).members'
set [Product3] as '[Product].Generations(3).members'
set [Year3] as '[Year].Generations(3).members'
set [Axis1Set] as 'crossjoin ({[Accounts4]},crossjoin ({[Market3]},crossjoin ({[Product3]},{[Year3]})))'
select
{[Scenario].[Actual]} on columns,
NON EMPTY {[Axis1Set]} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

Now that we have understood how the level based aggregations work lets understand how we can use MDX functions using EVALUATE to achieve some complex reports. We shall see what MDX functions are supported and how they can be used in the next blog entry.

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