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

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

WITH SAWITH0 AS
(select sum(T69590.AMOUNT_SOLD) as c1,
T69588.PROD_CATEGORY as c2,
T69591.CALENDAR_YEAR as c3,
T69591.CALENDAR_YEAR_ID as c4
from SH.TIMES T69591,
SH.PRODUCTS T69588,
SH.CHANNELS T69584,
SH.SALES T69590
where ( T69584.CHANNEL_ID = T69590.CHANNEL_ID
and T69588.PROD_ID = T69590.PROD_ID
and T69590.TIME_ID = T69591.TIME_ID
and (T69584.CHANNEL_ID in (2, 4)) and
(T69590.CHANNEL_ID in (2, 4)) )
group by T69588.PROD_CATEGORY,
T69591.CALENDAR_YEAR, T69591.CALENDAR_YEAR_ID),
SAWITH1 AS
(select distinct 0 as c1,
D1.c2 as c2,
D1.c3 as c3, D1.c1 as c4, D1.c4 as c5
from
SAWITH0 D1)
select D1.c1 as c1,
 D1.c2 as c2,
 D1.c3 as c3,
 D1.c4 as c4
from SAWITH1 D1
order by c1, c3, c2

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.