Pivot Table Prompts Duplicating Values

Question

I'm trying to replicate a report from old Oracle Discoverer to OBIEE.

The issue is that when I had the "pivot table prompts" to the OBIEE analysis. I'm left with duplicates instead of unique values in the drop down field. Can anyone explain how I can keep this from occurring? This only happens when I add three or more attributes/columns to the "pivot table prompts" section. This worked in Oracle Discoverer….

Answer 1

The dropdown list is built from the dimension you have defined, so if your key is incorrect you'll get duplicates. Check the physical SQL in nqquery.log if in doubt to see what SQL OBIEE is generating that returns the duplicates, and make sure you have defined your logical dimension correctly.

Answer 2

Be sure, for the physical connection you have defined, that you have checked the below option:

Also, I know there was a bug around this sort of functionality occurring if the column properties were set to repeat, but that is not a common thing people do, and is probably not your scenario, but it is worth a look: Bug 13147411 : 11G PROMPT IS DISPLAYING REPEATING VALUES WHEN ITEM PROPERTIES SET TO REPEAT

As mentioned by rmoff, incorrect keys can often lead to this issue. As he said, you can check the nqquery.log to see the physical SQL, or enter SET VARIABLE LOGLEVEL=4; in the SQL prefix on the advanced tab, then run your report again. This will generate a log in the admin pane of the tool that you can check easily.

Answer 3

The issue was resolved by moving "Repeat" to "Suppress" for the individual columns that I was prompting on in the pivot!

We did this a lot throughout my department because we use the exports for further manipulation rather than using OBIEE only for high level overview and summation. Each row must hold the value in order to filter, so the "Repeat" functionality is often selected.

Thanks for the help/answer!!!