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

Image-0002

Image-0000

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.

Image-0005Image-0006

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.

Image-0004Image-0003

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.

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.

Image-0000

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

Image-0001 Image-0002

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.

Image-0003

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

Image-0004

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.

Image-0007

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

Image-0008

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.

Image-0001

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.Image-0002

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

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

Image-0004

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.

Image-0005

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.

Image-0006

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

Image-0007

Data Visibility in an OBIEE/Essbase Integrated Environment

In my previous blog we seamlessly integrated an Essbase cube with OBIEE.  In this blog  we will see how a simple data-visibility scheme can be implemented for this OBIEE, Essbase integration.

In my SALES_F fact table you can see that every transaction is assigned to one of 5 employees (1 through 5) joined to the EMPLOYEE_D table by the EMPLOYEE_WID foreign key.  Our goal is to set up data visibility such that when an employee logs in to the OBIEE application, he can see only the transaction details that are assigned to him.

Image-0000

First, we create 5 users in the RPD using Security Manager. Image-0001

Next, create a new security group (Employee Group) in the RPD and assign the 5

users into this security group.

Image-0002

Image-0003

Now we need to assign permissions for this security group. Click the permissions button and click on Filters tab.

Click add and and select the fact table (SALES_F) on which you want to apply the filter.Image-0005

Click on the Business Model Filter to open up the expression builder window.

Image-0007

Image-0008

In the expression builder enter the filter condition: CUBE_DATA.EMPLOYEE_D.LOGIN =  VALUEOF(NQ_SESSION.”USER”) .

This filters the contents of the fact table to the employee logging in to the OBIEE application. We are able to achieve seamless data level security implementation between OBIEE and Essbase  as the Essbase Cube AND OBIEE have the same data model and the login column is mapped to the cube as well as to the  EMPLOYEE_D table in the oracle DB.

Image-0011

Now you can log into Answers and verify that your Data visibility scheme gives you the right results.

Image-0010

Seamless Integration of OBIEE with an Essbase Cube

In my previous blog we created a simple Essbase cube and also modeled the cube in OBIEE. In this blog I will talk about achieving seamless integration between OBIEE and the Essbase Cube I had created in my previous blog. The purpose is to use the hyperion cube as aggregated data and the actual Oracle tables for detailed data. My cube has aggregated data up to the Product Brand level and the detailed data (at Product level) Oracle Database.

Open up your OBIEE admin tool.

First up we have to model the detailed data in OBIEE (Set up the Fact tables, measures, hierarchies etc.),

Import the detailed tables in to the Physical layer of the RPD. (In my case, the Sales fact, Employee and Product tables.

Define the necessary joins between the tables in the Physical layer,

CropperCapture[1]

Drag and drop the tables in to the BMM layer.

Define hierarchies for the dimension tables you just dragged and dropped.

CropperCapture[2]

When you define the hierarchy levels make sure that the different levels match to the cube that you have created in Essbase.

Now go to your Fact table and create all the measures and make sure that the definitions are in agreement with the Essbase Cube.

In my case I have created three logical columns count, count_discount and count_no_discount.

Count is the number of transactions in the fact tables

Count_Discount is the number of transactions with Discount_Flg=’Y’ and Count_No_Discount is the number of transactions with Discount_Flg =’N”.

I have the same measures defined in the cube and the data is aggregated at the Brand Level.

CropperCapture[4]CropperCapture[5]

Import the cube in to the Physical layer if you haven’t already done that.

Now we need to map the measures in the sales fact table to the measures in the cube. This can be done easily by dragging and dropping the cube measures from the Physical Layer to the corresponding logical column in the BMM Layer. CropperCapture[8]

This will create a new Logical Table source (Sales_db) for the Fact table.

CropperCapture[7]

Now we need to set the Logical levels for the Sales_db logical table Source. Double-click the Sales_db Logical table source and click on the content tab. Since the cube contains aggregated data for products at the Brand Level set the Logical Level as Brand for the Product Dimension. Likewise,  set the Logical level as Login for the Employee Dimension.

CropperCapture[9]

Now we need to map the columns in the Dimension Tables (Product and Employee) to the columns in the cube. Similar to what we did for the measure columns in the Fact table, drag and drop the Family and Brand Members from the Physical Layer to the corresponding columns in the Product table in the BMM layer.

CropperCapture[10]

This will create a new Logical table Source (Sales_db) for the Product table. Now we have to set the Logical Level for this Source. Double Click Sales_db logical table Source and go to the content tab. Since the cube contains aggregated data at the Brand Level, set the  Logical Level to Brand,

CropperCapture[11]

Similarly, we have to map the columns in the employee table to the columns in the cube and set the Logical Level. In my case the Logical Level for the Employee Dimension is Login.

CropperCapture[12]

This completes the Data Modeling for seamless integration of OBIEE and an Essbase Cube.