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

MS plug-in for OBIEE

The new office plug-in from Oracle for MS Office integration with OBIEE looks pretty cool on the surface. But the Power point plug-in is very rudimentary and almost not usable. Here are some major draw backs of the product from a first pass.

1) Pivot tables are not supported.

2) Formatting specified in the OBIEE reports/charts would not get migrated to PPT when imported using the BI Plug-in. The charts can be formatted within Power Point and refreshes would preserve the format (specified in PPT).

3) Sorting based on hidden columns in a chart would get messed up when the chart is displayed in PPT using the BI Plug-in. The values (x-axis) are always sorted alphabetically. There is a work around for this though. Push the sorting back to the RPD. Set the hidden column used for sorting in the OBIEE report as the sort key for the column displayed, in the RPD.

4) Object security in the web catalog is not reflected in the Plug-in. For example if a user doesn’t have access to a certain report in the webcat, the user would still see the report in the BI Plug-in pane. But if the user clicks on the report an error pops up and user would not be able to see the data in the report. So there is not data security breach as such but still this is very annoying. The users would be able to see all the reports irrespective of is they have access or not. The users would not be able run the reports that they don’t have access to, the report names would be visible on the BI plug-in pane.

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

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.

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

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

users into this security group.

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.

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

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.

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