Random Thoughts

Views on life

Archive for the ‘OBIEE’ Category

Duplicate values in Dashboard Prompts (OBIEE)

Posted by Ananth Sridharan on November 2, 2010

I have been working with OBIEE for several years now, and the one thing about this product that continues to amaze me is the BI Server’s query engine.

The situation:
I need to show a unique list of values for this prompt, and the BI Server will just not listen! Even when I explicitly use “Distinct”, the BI Server would simply ignore my command.

The solution:
There isn’t enough emphasis on how to build the Business Model, and this issue surfaces from that fact. Often times, when creating logical dimension tables, the logical primary key is not defined properly. The logical primary key field(s) is/are assumed, by the BI Server, to uniquely identify a dataset.

You will typically find duplicate values when you use a field that has been defined as the single primary key field in a logical dimension table although in reality, this field is not unique across rows in the underlying table.

The solution, therefore, is to define the primary key properly. If that is not an option, I will suggest that you add another logical column that maps to the same physical column but is not part of the logical primary key, and use this column instead in the prompt. Magically, this will use a “Distinct” automatically and bring a smile back to your face 🙂

Posted in OBIEE | Leave a Comment »

OBIEE: The outer join challenge solved

Posted by Ananth Sridharan on July 16, 2010

The case:
a. I am looking at spend by supplier, and as a user, I would like to have the flexibility of grouping the suppliers
b. I have a thousand over suppliers, but I am interested in grouping only a hundred or so
c. This is a very dynamic grouping that could potential change 2-3 times a week

The approach:
a. Define a lookup table that will have a list of suppliers, and the group to which the supplier belongs to. Lets assume type 1 to keep it simple
b. Either
  – Write ETL logic to bring all the suppliers over into this lookup table with a default value that can be overwritten by the user
  – Use a left outer join (keep all in fact) on the lookup table – this will require the user to maintain the table, and no ETL logic

The problem:
When we use the left outer join, there are 2 options in OBIEE that come to mind. 1, define an LTS to bring the 2 tables together with a left outer join, and 2, define a left outer between the logical dim and fact tables.
I am not a fan of the 1st as that would use the left outer join always (whether I use the grouping or not). And the 2nd approach works except when you use a NOT filter criteria on the grouping. Consider this:
I want to get the spend by supplier group where the supplier group does not equal SG_TECH. When I fetch the results in OBIEE, all the unmapped suppliers will be missed out.

The solution:
Simple as always. When you map the group field into the logical layer, use the ifnull(, ‘UNMAPPED’) function to set a default value. This way all the suppliers that are not mapped will not be treated as null values but as ‘UNMAPPED’, and therefore will not be excluded from the report

What’s next:
The “materialize” (Oracle) database hint

Posted in OBIEE | Tagged: , | Leave a Comment »

Deleting a user in the OBIEE catalog

Posted by Ananth Sridharan on March 29, 2010

I had recently run into this issue with deleting users in OBIEE and my initial reaction was that the solution to this should be trivial. Boy, I was so wrong. I have seen a few posts that help delete users from the catalog, but that would require you to stop the heart before you perform the operation.

It’s frustrating, especially when you have deleted a user in the catalog that you didn’t want to in the first place! If you haven’t got there, or tried that yet, please don’t. But for those who have, this post helps to fix the issue without bringing down any of the servers. Here’s how:

The first few screenshots aims to illustrate the issue, and progressively take you to the solution.

Problem description

Before you delete a user, all is well, and the catalog manager would show the user folder in the correct state:

Delete the user from the web and…

..you will notice that there is a <Deleted Account> tag against the user folder in the catalog. This will not allow you to delete the 1338802 folder – that’s the problem!

And worse, if this user logs back in, the user will have access to all the shared stuff, but no access to his/her personal catalog folder!!!

Most of us will then try to delete the folder from the catalog – this would initially seem like a no brainer till you hit upon the issue wherein you are unable to delete the folder.


Change owner from “System Account” to Administrator for the user folder you wish to delete:

Change permissions on the folder: This one is a bit tricky – Select <Deleted Account> and move to right, select Administrator from the right and move to the left. Select “Apply Recursively” and click on ok (next 3 screenshots)

Now you are nearly ready to delete the folder. As a last step, rename the folder, and then delete it:

Problem solved!

This was about a not so nice issue with OBIEE. What will follow next is a really cool capability of OBIEE: Data federation, or is it fragmentation???

Posted in Administration, OBIEE | Tagged: , | Leave a Comment »

%d bloggers like this: