Error while Passing Session Variables to Essbase in Evaluate Expression

We can make use of native Essbase Functions using the Evaluate and Evaluate_Aggr Expressions in OBIEE.

Let’s first see how we can call an Essbase Function TOPCOUNT from within OBIEE.

The TOPCOUNT function returns a set of n elements ordered from largest to smallest, optionally based on an evaluation. For example:

To Find the top 3 Products ranked  by Sales I would use the following statement:

EVALUATE(‘TopCount(%1.members, 3, %2)’ as VARCHAR(20), Products,
Sales)

Where 3 is the INDEX  of the Topcount function.

In the above statement the index n which is equal to 3 is passed to the Essbase Cube in order to retrieve the TOP 3 products ranked by sales.

In order to Call this function from within the OBIEE RPD, I have created a new logical column in the BMM layer called Top3 and in  the Expression Builder  I use the following statement. 

EVALUATE(’TopCount(%1.members, 3, %2)’ AS  VARCHAR ( 20 ), “universi-vlz1vh.oracle.com”.”Sales”.”".”Sales_db”.”Gen4,PRODUCT_D”, “universi-vlz1vh.oracle.com”.”Sales”.”".”Sales_db”.”SALE_AMOUNT”)

 

When entering the expression I would recommend inserting the required columns from the Expression Builder window rather than typing it. You will be less prone to make syntax errors this way.

Now when you drag this new column into the Presentation Layer and run a report in Answers you will see that the Top3 column does in fact give you the Top3 products ranked by Sales.

Now let us see if we can make the index in the topcount (3 in the above case) function dynamic. Basically what I would like to do is to populate the index using a variable instead of hard-coding it in the Expression like we did earlier.

Let us see if the Evaluate Expression will work if we use a sessionVariable as the index.

I have created a session variable “N”  which will return a numeric value. Then I created a new logical column and in the expression builder entered the following:

EVALUATE(’TopCount(%1.members, VALUEOF(NQ_SESSION.N)  , %2)’ AS  VARCHAR ( 20 ), “universi-vlz1vh.oracle.com”.”Sales”.”".”Sales_db”.”Gen4,PRODUCT_D”, “universi-vlz1vh.oracle.com”.”Sales”.”".”Sales_db”.”SALE_AMOUNT”)

You can see that I have replaced the index with the session Variable Value.

Now let us run the report and see what we get.

  You can see that an error is generated. If you look at the MDX being generated by OBIEE then you find that the the session Variable value is not being passed into Essbase and so we get the Unknown Member used in Query Error. So to my knowledge it is not possible to dynamically pass the value of a OBIEE session variable to Essbase as an index value.

With
  set [Evaluate0]  as ‘{TopCount([PRODUCT_D].Generations(4).members, VALUEOF(NQ_SESSION.N)  , [Accounts].[SALE_AMOUNT]) }’
  set [Axis1Set] as ‘{[Evaluate0]}’
select
  {} on columns,
  {[Axis1Set]} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Sales.Sales_db]

This is a significant handicap that puts limitations on the use of Evaluate Expressions to leverage the native Essbase Functions.

Choose and Indexcol functions in OBIEE

You can use the choose statement to dynamically choose different columns in a request  depending on the group a user belongs to:

Let us look at  the following request:

Based on the USER logging in we need to choose different columns from the EMPLOYEE_HIER table.

We can do this using the CHOOSE function.

The first step is to create groups in the RPD  and assign USERS to their respective groups.

Next in the Presentation layer of the RPD select  on of the columns on which you need to apply the CHOOSE function.

Click on the Permissions button and give rRead permission to the group whose memebers have Read access to that column.

In the aboive example I have chosen the REGION_MANAGER_NAME column and assigned tread permission for this column to REGIONAL MGR group.

In answers, edit the formula of the column on which you want to apply the CHOOSE function.

Now when a USER logs into the application, the appropriate column is chosen from the EMPLOYEE_HIER table.

 

IndexCol Function

We can get a similar functionality using  the Indexcol function. The syntax for this function is:

     IndexCol( integer value , column1, column2, … )

   Where
         integervalue can take integral values like 0,1,2 etc and
         column1,column2 etc can be any column in the Business Layer

The column is chosen depending on the integer value that gets assigned when a user logs in.  If integer value is 0, then column1 is chose, if it is1 then column2 is chosen and so on.

So in order to implement this function let I create a database table that will contain all the user login ID and a LEVEL column that will will have the integer value associated with that user. Now using  an initialization block and an associated session variable, I can get the integer value associated with the user.

username              level

EMP1                        0

EMP2                        0

MGR1                       1

AM1                           2

RM1                          3

I will use the LEVEL session variable to assign the integer value to the indexcol function.

Now I will choose the column in the BM layer on which I want set the indexcol function, and use the following expression in the expression builder:

Indexcol(VALUEOF(NQ_SESSION.LEVEL),
"DATA_DUP"."EMPLOYEE_HIER"."LOGIN", "DATA_DUP"."EMPLOYEE_HIER"."SALES_MGR_NAME", "DATA_DUP"."EMPLOYEE_HIER"."AREA_MANAGER_NAME", "DATA_DUP"."EMPLOYEE_HIER"."REGION_MANAGER_NAME"

 

Basically what this  does is,

when EMP1 logs in the value of session variable LEVEL is zero and the column LOGIN is chosen

When MGR1 logs in the value of LEVEL is 1 and the column SALES_MGR_NAME is chosen and so on.

Displaying Charts as Images instead of Flash in OBIEE

Recently, one of my clients wanted to spin up Oracle Business indicators, an application to provide Apple Iphone users with real time, secure business information generated by OBIEE and other Oracle Applications.

However, viewing the reports generated by OBIEE on the Iphone requires further configuration of the OBIEE presentation server as  the Iphone presently does not support Flash displayed on web pages.  So reports that contain Flash components like charts  are not displayed correctly.

We  can modify the instanceconfig.xml file to display charts in a custom graphic format  like PNG or JPEG instead of flash.

Include the following in <Server Instance> section of instanceconfig.xml

<Charts>
<DefaultImageType>PNG</DefaultImageType>
</Charts>

It is recommended that you configure a separate presentation server to cater to the mobile device users as displaying charts in PNG or JPEG disables some of the interactive features offered by Flash.

Passing Time Series Functions in OBIEE to ESSBASE

In OBIEE we have the AGO and TODATE Time Series Functions in order to compare measures across time periods. Let us see whether we can make use of these functions when using an Essbase Cube as our Data Source.

Let us first test out the AGO function. The AGO  function calculates the aggregated value as of some time period shifted from the current time.

In order to use these time series functions in OBIEE, the first step is to identify a dimension as the Time Dimension. Double click the Time Dimensional Hierarchy and mark it as a Time Dimension.

Next, expand the Time Dimension hierarchy to its lowest detail level. Double Click the lowest detail level and check the Chronological key.

To create a Month Ago measure, right click on the Fact Table in BMM layer and create a new Logical column. I have named this column Month Ago Sales.

Now check ” Use Existing Logical Column as the source and then click on the Ellipsis to open up the Expression Builder box.

In the Expression Builder, Select  Functions > Time Series Functions > Ago and click insert.

The AGO function requires 3 arguments:

Ago(<<Measure>>, <<Level>>, <<Number of Periods>>)

1. Measure—-Select the appropriate measure from the Fact Table( Sale_Amount)

2. Level——-  Select the required level in the Time Dimension (Quarter)

3. Number of Periods——Is an integer number (# of Periods you want to go back) The AGO function will calculate the Amount sold n Quarters prior to current Quarter if this value is n.

I have shown the parameters chosen for the Month Ago Sales Measure.

 

Close the expression builder, drag the newly created measure column into the Presentation Layer and check in changes.

Now create a test report in Answers and verify your results

Incorporating Attribute Dimensions in Essbase into OBIEE

Attribute dimensions that you define in Essbase are imported into the OBIEE RPD when you import the cube. However, they are not automatically assigned to the correct dimensional level during the import process. Lets us see how we can model attribute dimensions and associate them with the correct level. 

In this blog we will see how we can create attribute dimensions in Essbase and then how we go about incorporating it in OBIEE.

Let us first start with the OLAP model. I want to create an attribute dimension called UNIT PRICE at the PROD_NAME Level in the PRODUCT Dimension.

  In the table properties for PRODUCT table select the UNIT PRICE column and click on Attribute to define UNIT PRICE as an Attribute Dimension. Save the OLAP model and open the Metaoutline for this model.

In the Metaoutline, drag the UNIT PRICE Attribute Dimension to right pane.

Double click the UNIT PRICE attribute dimension and set the association to the correct level. In this case it is the PROD_NAME LEVEL.

  Save the Metaoutline and then load the cube.

After you have built the cube import this into the Physical Layer of the OBIEE RPD and then drag it into the BMM Layer. You can see that the UNIT PRICE Attribute Dimension is not associated with the PRODUCT Dimension. 

We will have to manually add the attribute Dimension column to the PRODUT_D logical table and also associate it to the correct hierarchical level in the PRODUCT hierarchy.

You can now log into Answers and verify that the Attribute Dimensions give the correct results.

del.icio.us Tags: OBIEE,Essbase,Attribute Dimension