OBIEE with Key / Name / Value table, the Union solution

A customer asked me to create an analysis on top of a Name – Value table. A table where instead of having one row for each key, there are multiple rows with the structure of Key_Value / Attribute_Name/ Attribute_Value. This structure is very nice for developers. They can add any attribute they want, but it’s a nightmare for reporting.

For example the following simple table:

They are few methods to deal with it both on database level (Pivot / Unpivot functions) and in the RPD. Actually, it should be done in the RPD, but just for this time…

If you prefer, you might use Logical SQL with Joins as described in the post “OBIEE Power to the people – joining the unjoinable”.

But I decided to use the method of Unions at Analysis level.

For example, with the above table I need to create an analysis with the columns:

Key  |  Name  |  Surname  |  Blog

How do I do it? Something similar to the described at “OBIEE – Calculation between results of a union”.

Create am Analysis that returns only the values of a specific Attribute, “Name”, in this case. Add 2 empty columns with formula ” and press Union (top right).

Now Copy / Paste or recreate manually the same with Filters of Attribute=’Surname’ and Attribute=’Blog’.

In surname line change the value column (third) formula to ” and place the actual Value column instead the first ”

In the Blog line place it third.

If you can’t see the filter, press the filter sign on top left.

Now we have the following result:

In the Criteria go to “Result Columns”,

And Press “Add result Column”

Lets name the new column “Name” and it’s formula should be Max(“value” by “key”), but OBIEE will write MAX(saw_2 by saw_0), where saw_0 stands for the first column and saw_2 is the third.

In similar way create a Surname column – MAX(saw_3 by saw_0) and the Blog column – MAX(saw_4 by saw_0).

This is the result:

Exclude the 3 original Value columns and the Attribute column, and it’s working:

You might have noticed that only key ‘yyy’ has a row of data for ‘Security’.
What happens if we replace the filter of the third part of the union and set it to  Attribute=’Security’ (and change the column name accordingly)?
All is fine:

It can be dynamic as well. Just replace the filter with presentation variables, set the column heading to accept presentation variable and place the Analysis with Dashboard Prompt that updates those variables in a dashboard.

Just remember to use default values in the prompt.