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.
No comments:
Post a Comment