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 Servers 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 isnt 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 :)
.svg)
.svg)