Error Capturing in OBIEE Writeback

OBIEE Writeback

The OBIEE write back process does a bad job in capturing the errors that happen when writing back data. And often times we need to capture the error. Here is a quick way to capture the error message.

1) Set up writeback as directed in the Oracle documentation.

2) Create an error logging table using the dbms_errlog.create_error_log package. “ERR_LOG_TABLE”

3) Modify the SQL in your inset or update statement from the writeback template to include LOG ERRORS INTO WB_POC_ERR_ORA (‘Write Back’)

For e.g my insert statement that looked like

<insert>insert into WB_INTER_INS_TAB (ACCOUNT_VAL,CAL_ONE_VAL,) values (‘@2′,’@3’) </insert>

Will change to.

<insert>insert into WB_INTER_INS_TAB (ACCOUNT_VAL,CAL_ONE_VAL,) values (‘@2′,’@3’) LOG ERRORS INTO ERR_LOG_TABLE (‘Write Back’)</insert>

Now that you have these errors in a table in the database, you could have triggers, procedures, reports,javascripts built to further give the users meaningful error messages in the front end.

Advertisements

Dynamic Display Name for Presentation columns

There was a question asked on this post here and I thought of having another post to answers Vik’s question.

Hi carpediemconsulting

Good Artical, Thanks

We have a Similar req at my client, but little bit different.

we have a Dimension, in that we have a hierarchy H1 with Levels, Level1, Level 2 , Level 3 we are implimenting this product for different companies, Lets say comp A, & Comp B,
In Comp ‘A’ we call level 1 as ‘Market’ and in comp ‘B’ we call it as “Location”
can we change these Level1 label dynamically when the user login. FYI . these labels are stored in TABLE

Thanks
Vik

As you can see from the properties of a presentation column you could see that there is a provision to specify a custom display name.

I have two users created in the RPD. USER1 and USER2. My objective is to give a custom display name for a Column (Brand column in the Product Table). User1 would like the display to be USER1 BRAND and user2 USER2 BRAND.

I have created a session variable (BRAND_DISP_NAME) which would be initialized to USER1 BRAND for user1 and USER2 BRAND.

In the properties window for the presentation column brand do the following.

Uncheck use logical table Name check box

Check the Custom Display Name check box

Type in the name of the session variable in the text box. In my case VALUEOF(NQ_SESSION.BRAND_DISP_NAME)

Here is a screenshot.

clip_image002

Now lets see how the column shows up for these 2 users in Answers.

USER 1

clip_image004clip_image006

As shown in the screenshots above the display name for the column “brand” is different for these two users.

Hope this answers Vik’s question.

Dynamic Data security in OBIEE

Here is an interesting scenario that I came across recently. The client has 2 hierarchies based on cost centers, Management and P&L. The business push behind this was that lot of users had dual roles in terms of how they look at expenses. Both the hierarchies had cost center at the lowest level and the same number of levels in the hierarchy. The differences were 1) users would have access different set of cost centers in both hierarchies (not necessarily mutually exclusive) 2) roll ups for the cost centers would be different in both hierarchies. The business had a couple of requirements around this.

1) The users should be able to pick the hierarchy that they would like to view the report by.

2) When the user flips between the hierarchies the security applied should change accordingly, ie if they are viewing the report by the P&L hierarchy then the P&L security should be applied and if they pick management then Management hierarchy security should be applied.

Here is a very simplified version of how we implemented the solution.

The security profile table

Hierarchy User_id Cost_center
P&L USER1 1
P&L USER1 2
P&L USER1 3
P&L USER1 4
MGMT USER1 3
MGMT USER1 4
MGMT USER1 5
MGMT USER1 6
MGMT USER1 7

User1 has access to cost centers 1-4 in the P&L hierarchy and 3-7 in the Management Hierarchy.

Here are the 2 hierarchy tables for this example.

Hierarchy Cost_center Level2 Level1
P&L 1 P&L LVL2 1 P&L LVL1 1
P&L 2 P&L LVL2 1 P&L LVL1 1
P&L 3 P&L LVL2 1 P&L LVL1 1
P&L 4 P&L LVL2 1 P&L LVL1 1
P&L 5 P&L LVL2 2 P&L LVL1 1
P&L 6 P&L LVL2 2 P&L LVL1 1
P&L 7 P&L LVL2 2 P&L LVL1 1
Hierarchy Cost_center Level2 Level1
MGMT 1 MGMT LVL2 1 MGMT LVL1 1
MGMT 2 MGMT LVL2 1 MGMT LVL1 1
MGMT 3 MGMT LVL2 1 MGMT LVL1 1
MGMT 4 MGMT LVL2 2 MGMT LVL1 1
MGMT 5 MGMT LVL2 2 MGMT LVL1 1
MGMT 6 MGMT LVL2 2 MGMT LVL1 1
MGMT 7 MGMT LVL2 2 MGMT LVL1 1

I have an expenses fact table where the cost center is a key.

clip_image002

Now lets see how this is modeled in the RPD.

Physical Layer Joins

I have aliased the security profile table as Sec Profile PL and Sec Profile Mgmt

clip_image004

There is a similar join between the Sec Profile Mgmt table with the Management Hierarchy table.

Here is how the BMM layer is done.

The cost center table will have 2 logical sources and each of these logical sources would have an inner join to the appropriate sec profile alias table.

clip_image006clip_image008

All the columns are mapped to both the logical sources.

Now we need to define appropriate fragmentation content for these 2 logical sources. Here is the Mgmt source

clip_image010

Add the user_id column to the logical table and map it to the appropriate logical sources.

Now all that’s left is to go to the relevant group in the RPD and add the security filter.

clip_image012

Create reports with the hierarchy as one of the columns and set filter to a presentation variable

clip_image014

Also create a prompt and set a presentation variable in the prompt.

clip_image016

Throw the 2 in a dashboard and lets see how the reports work for USER1

clip_image018clip_image020

Its very important to have the filter on the hierarchy column in every query otherwise you would end up overstating the numbers.

Letting the users pick joins in OBIEE

The users want to define the joins between tables in answers, when doing adhoc queries. These views are used for real-time reporting and were sitting on top of EBS tables. This requirement was driven by the fact that most of these views could be joined in more than 2 ways depending of the business scenario. In a typical design we would create alias tables to correspond to each of the joins. But in our case the users were not able to define the joins in advance and there is a possibility of the view definitions changing over a period of time. To sum it up the users wanted to simulate a SQL tool with answers, sort of.

clip_image002

In our case View 1 and View 2 could be joined up to 4 different ways and there were quite a few views like that, making the whole aliasing approach cumbersome. One of the developers in the team came up with an idea, though it’s not the perfect solution, but definitely simple and worth mentioning.

First up we extended all the views in the database by adding a dummy column and the values were always 1.

View 1

Col1 Col2 Col3 Col4 Dummy
1
1

View2

Col1 Col2 Col3 Col4 Dummy
1
1

The physical layer in the RPD would have the join between the views as View1.dummy = View2.Dummy. There is nothing special about the BMM layer, the usual and the same with the presentations layer.

Now comes the answers part. If I were to run a query between the 2 views without any filters in the query the results would not make any since the join is View1.dummy = View2.Dummy.

Let’s say for query 1 I want to join on View1.col1 = View2.col1. Here are the steps.

Put a filter on View1.Col1. (View1.Col1 = value1)

Convert that filter to SQL

Equate View1.Col1 to View2.Col2 in the filter SQL.

clip_image004

This will have your physical queries appended by 2 join conditions 1) which is defined in the RPD(View1.dummy = View2.Dummy) and 2) View1.col1 = View2.col1 .

You could define any joins in a similar fashion. One serious limitation is that the joins are always inner joins.

Period Comparison without time series or ago functions.

Recently at a client site we had to connect to some tables in owned by another application and generate some reports for our OBIEE user base. We had no control over the table structures in the external application and we had to generate some time series measures. We couldn’t use the time series wizard or the ago functions provided by OBIEE.

The report had to display all the quarters of the current year and the growth for the same period for each of the quarters.

This is how the repots looks like in a regular table view in answers.

clip_image002

And the picture below shows what the client wanted.

clip_image004

We could have done the same with a complex case expression but we found an alternative which is more elegant and better performing, using pivot table calculations.

First up we need to change the formula for the quarter to the one shown below.

clip_image006

Current year is a repository variable, this could also have been a presentation variable.

And the table view would look like

clip_image008

Now create a pivot table for this with the year on the left and the quarter on the top.

Create 2 calculated items on the year column

The first one (Actuals) as $2 and the second one as $2-$1 (growth) and then hide the details and this would give the sales amt for all the quarters for the current year and the growth.

clip_image010

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:

Image-0001

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.

Image-0003

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

Image-0005 Image-0004

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.

Image-0006

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

Image-0007Image-0008

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”

 

Image-0010

Image-0011

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.

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.