Oracle BI EE 11g – Handling Double Columns – ID/Description interoperability

August 18th, 2010 by

The other big change in BI EE 11g as mentioned before here, is the ability to assign ID columns for descriptive columns more commonly known as Double Columns. This feature has 2 fold advantages

1. In BI EE 10g, there was no automated way of filtering on ID’s when end users chose the description values in the prompts. The Double column feature provides this ability in 11g.

2. In many implementations where data is captured in multiple languages, the descriptions might be stored in different languages. But the filtering of data will be on ID’s (which will be the same across languages). Double column feature provides that ability now.

Lets try to put this feature to use by using a simple example. We shall be using the Sales Warehouse schema (SH) that comes by default with an Oracle database installation. The screenshot below shows 3 columns from the CHANNELS dimension. One column is CHANNEL_DESC which contains the channel details in English. The second column in CHANNEL_DESC_FR which contains the channel details in French. And the third column is the CHANNEL_ID that acts as an id to both the french and english descriptions.

Our idea is to create 2 prompts, one in french and the other in English, and then using these 2 to filter on 2 separate reports. To do this we start with assigning the CHANNEL_ID column as the descriptor ID column in the Business Model and Mapping layer for both CHANNEL_DESC and CHANNEL_DESC_FR columns.

Once this is done, lets go to BI EE UI and create 2 dashboard prompts. Ideally its not necessary to use 2 prompts as we can use INDEXCOL function in the repository to switch between columns based on the user preference language, but for demonstration, i will create 2 prompts.

The first dashboard prompt will point to the French description field. When we include this column in the prompt, you will notice that the prompt will automatically show the Included ID column as well.

Now, if you look at the options section, we now have the ability to display the Descriptor ID as well.

Lets enable that option as well so that users who are more familiar with the ID’s will have the ability to toggle between the description and the ID.

Lets save this prompt and create another prompt that is similar to the above prompt but with CHANNEL_DESC as the source column.

Lets now create a simple report in Answers containing Year, Product Category and Sales columns. And lets apply the channel filter (on English) as well. When you create the prompt, you will notice that for static filters you can now enforce the filtering on IDs directly. But for this blog entry, lets use the is prompted filter.

If you now, bring the report and the prompt in a dashboard, end users will now have the ability to filter on the description as well as the ID.

If you enable the Select by ID check box, you will notice that the drop down will now have the ID and the description concatenated for easy selection.

Same will be the case for French descriptions as well.

Lets first choose the French Descriptions and then see, in terms of SQL how the query filter is generated

As you notice, though we have chosen the descriptions in the UI, the filters are automatically pushed to the IDs. Same will be the case for filtering on IDs as well.


Comments

  1. Vinod Says:

    Thx Venkat for step by step explanation . Double columns going to be booster for sql performance .

  2. Tomar Says:

    Excellent feature. Descriptor ID feature is also very useful when you want data to be grouped by code along with name in report without selecting the code in report. This can be used to avoid summing up of data under single name value where same name belongs to different codes.

  3. user1202 Says:

    Thank you for this usefull blog.

    I have some trouble in my head:
    We have aliases instead member names in Answers results in OBIEE10.
    How can we set default alias table for dimension to see aliases instead member names in Answers OBIEE11 results?
    Thank you.

  4. user1202 Says:

    I’ve found it!
    We need to create columns for alias tables on Physical Layer and set this columns to display columns on each level of my dims.

  5. David Andersen Says:

    Is there any reason why the SID (surrogate key) could not or should not be used instead of individual id’s for each description (or set of descriptions)? I can’t think of one but might be missing something.

  6. Yogen Says:

    Can this be applied for multiple columns?
    e.g. Geography–>country–>region—>area–>zip code, this five columns combination makes primary key…and i want to create a single prompt having these 5 columns and 6th column just 1 number say Geo_ID, once user select just GEO_ID all these 5 columns must populate automatically or at least the report must run for that particualar zip code only?

Website Design & Build: tymedia.co.uk