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.

Advertisements

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.

Modeling an Essbase Cube In OBIEE

In my previous blog entry we created a simple Essbase Cube. Now lets see how we can get that cube into OBIEE. First we have to get to the Essbase cube and prep it a bit so it looks neat in OBIEE. Navigate to the cube in the Administration services console. Right Click on one of the dimension tables and select generations.

croppercapture2

Give appropriate names for the different levels in your hierarchy. If you don’t do this your hierarchy levels would show up as Gen 1, Gen2 etc.

croppercapture3

After you rename all the levels in all your hierarchies, save the outline. It’s ok to skip this step too. When you save the outline go with retain all data.

Now open up your admin tool. File > Import from Multidimensional

Enter the name of you “Essbase” server and the login credentials

croppercapture4

You can see all the cubes in the Essbase server that the user has access to.

croppercapture5

Select the cube that you want to import. Now you have the cube imported in the physical layer. You will see that the physical layer looks a lot different that your usual tables and joins. All the hierarchies are defined in the physical layer and there are no fact tables only columns. You will also see that the levels in the hierarchy reflect the names that you had changed to in the beginning from the Administration service console.

croppercapture6

Just drag the cube into the BMM layer and then to the presentation layer. When you drag it into the BMM layer you will see that the fact tables and hierarchies are defined, saving you some work. Also all your measures will be set to External Aggregation. Save your RPD and go to Answers to play with your cube data.

Creating a Simple Essbase Cube

First up open up the administration services console and create a new application and an associated database. You could use one of the existing application or database. I am going with a new application and a new data base here11

Right Click on Applications and create a new application and pick the aggregate storage option.

1-1

Right click on the application just created and create a database for the application (Sales_db in my case)

1-2

You could create your metaoutline from the administration service console itself but I prefer to do it from the Integration services console. It’s much easier from there.

Open up your integration services console. Your screen should look something similar to this.

1-3

I have created an Oracle database schema (hyp_meta) to hold my metadata and have defined a DSN with the same name to connect to the same.  I hit create after providing my login credentials and that gives me a message saying the catalog has been created successfully. Once you close the OLAP Metadata Catalog Setup you will get another screen which is the integration services login screen.

1-4

Provide all the login credentials for the Integration services and the Essbase Server. The user name and the password in the Integration services section has to match the username and password for the schema that you created to hold your metadata. The Essbase section should be provided with login details for the Essbase server.

The next page gives me the option to create a new OLAP model or a new Metaoutline.

1-5

Every Metaoutline should be associated to an OLAP model and since I don’t have an OLAP Model defined I pick new OLAP Model.

At the next screen pick the data source (the DB where your source data is). In my case the source data is in cube_data (a schema on my local oracle DB). After you provide the login credentials the next screen that shows up would have all the tables under that schema listed on the left pane.

1-7

In my database I have a fact table and 2 dimension tables that I am going to be using for this cube. Here is how my sales fact looks like

1-8

The product dimension

1-9

The employee dimension

1-10

You could create the fact tables and the dimension tables automatically in the integration services console but creating them manually is better.

First up drag and drop the fact table into the workspace. Once you drop the fact into the workspace you would be prompted to create a time dimension and an accounts dimension.  I pick no on the time dimension and yes on the Accounts dimension. The new accounts dimension would have all the columns that you have in the main fact table. Next drag and drop the dimension tables into the work space. (Product and employee in my case)

Now create the joins between your dimension tables and you fact table. (The icon shown below, add joins mode, lets you do that)

1-11

My data model looks like this after I create the joins.

1-12

Next UP I have to create the dimension hierarchies.

Right click on the dimension table and hit hierarchies in the properties window.

1-13

In my product hierarchy I have defined 2 levels Family and Brand and I have omitted the lowest level, which is the product name. Product Name was omitted because I want to load only the aggregated data up to brand in my cube. The same process should be repeated for the rest of the hierarchies. Now you should have a simple OLAP Data model.

Now I am going to add a couple more columns (calculations) in my fact table.

Column # 1 – Count the number of transaction in my fact table (count(row_wid))

Double click on the fact table and select the columns tab. Pick add.

1-14

Select the Physical column to be mapped to the new column. Hit OK. In the column properties window that pops up pick the appropriate aggregation rule (Count in this case).

1-15

Now you will see that a new column has been created in the fact table and in the accounts dimension as well.

Column # 2

This column is a count of the number of transactions in the fact table where the discount flag = Y. Repeat the same process as in column #1. In the column properties window hit the transformation Rule tab. Select the “Pass Through” check box.

1-16

1-17

This should create a new column which would give me the count of all transactions where the Discount flag is Y.

Column # 3

Similar to column # 2 but the discount flag is ‘N’

Now save the OLAP model.

Time to create a new Metaoutline for this OLAP model.

File > New

1-18

Highlight OLAP Metaoutline and then pick the OLAP model you just created in the drop down. You should be able to see all the dimensions in the OLAP Metaoutline (left pane).

1-19

Just drag and drop the individual hierarchies into workspace. Drag and drop all the columns that you want from the accounts dimension as well. This is how my workspace looks like after I do that.

1-20

Save the Metaoutline. Right click on the top most node in the work space and select member and data Load.

1-21

Pick the application name and the corresponding database. The application created in the beginning in the administration services console.

1-22

The cube is ready when you get the successfully loaded message. You can preview the data either from the Administration Services Console or any client (excel etc).

To see the data in the administration console navigate to the application db and right click and preview the data.

1-24