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

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.
Now, if you look at the options section,
 we now have the ability to display
 the Descriptor ID as well.
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
x

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.

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.

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.