Random Thoughts

Views on life

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 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: