OBIEE – Dimension fragmentation design to add an automatic filter with the choice of a column

In response to an original Idea of Venkat with this blog entry: [[http://www.rittmanmead.com/2010/02/13/oracle-bi-ee-10-1-3-4-1-puzzlers-puzzle-1/|Puzzlers – Puzzle 1]]

“How do we make BI EE to generate different filters for every column(within a dimension) chosen from Answers?”

The idea is when you add the column Channel desc to an answer the query must be automatically filtered.

You can also found a copy of this entry in the wiki : [[http://gerardnico.com/wiki/dat/obiee/dimension_fragmentation_filter|OBIEE - Dimension fragmentation design to add an automatic filter with the choice of a column]]

An other simple solution to achieve this goal is to use the security filter:
[[dat:obiee:bi_server:security:security_automatic_filter_on_column]]

After a good night of rust, I deleted the alias of the physical table Channels because this solution creates a self join on the table channels. You can see the old solution [[dat:obiee:dimension_fragmentation_filter#the_old_solution|here]]

===== The design =====
*- Suppress the old logical column “Channel Desc”, create a new one with the same name
* 1 – Create a new logical table source “CHANNELS_FILTER” in the logical table Channels
* 2- Map the new logical column Channel Desc to the physical Column Channels.”Channel Desc” in the tab “Column Mapping”
* 3- Add the filter in the content tab
* – Drag and drop the new logical column in the presentation layer

{{:dat:obiee:repository_design_filter_on_column_new_logical_table.jpg|}}

===== The result =====

==== With the column “Channel class” and “Amount Sold” ====
**Sql Request**

SELECT Channels.”Channel Class” saw_0, “Sales Facts”.”Amount Sold” saw_1 FROM SH ORDER BY saw_0

{{:dat:obiee:repository_design_filter_on_column_result1.jpg|}}

As you can see no filter is added to the database query.

**Database Query**

select T161.CHANNEL_CLASS as c1,
sum(T245.AMOUNT_SOLD) as c2
from
SH.CHANNELS T161,
SH.SALES T245
where ( T161.CHANNEL_ID = T245.CHANNEL_ID )
group by T161.CHANNEL_CLASS
order by c1

==== With the column “Channel desc” and “Amount Sold” ====
**Sql Request**

SELECT Channels.”Channel Class” saw_0, Channels.”Channel Desc” saw_1, “Sales Facts”.”Amount Sold” saw_2 FROM SH ORDER BY saw_0, saw_1

By adding the column Channels.”Channel Desc”, OBIEE add automatically a filter and the join between the original table channel and its alias

{{:dat:obiee:repository_design_filter_on_column_result2.jpg|}}

**Database Query**

select T161.CHANNEL_CLASS as c1,
T161.CHANNEL_DESC as c2,
sum(T245.AMOUNT_SOLD) as c3
from
SH.CHANNELS T161,
SH.SALES T245
where ( T161.CHANNEL_ID = T245.CHANNEL_ID and T161.CHANNEL_CLASS = ‘Direct’ )
group by T161.CHANNEL_DESC, T161.CHANNEL_CLASS
order by c1, c2

==== With the column “Channel desc” and “Channel class” ====
**Sql Request**

SELECT Channels.”Channel Class” saw_0, Channels.”Channel Desc” saw_1 FROM SH ORDER BY saw_0, saw_1

**Database Query**

select distinct T161.CHANNEL_CLASS as c1,
T161.CHANNEL_DESC as c2
from
SH.CHANNELS T161
where ( T161.CHANNEL_CLASS = ‘Direct’ )
order by c1, c2

 

View the original article here