In response to an original Idea of Venkat with this blog entry: 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 : 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: OBIEE - How to define the BI server security to add automatically a filter when a column is added to an answer (row security level)
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 here
Sql Request
SELECT Channels."Channel Class" saw_0, "Sales Facts"."Amount Sold" saw_1 FROM SH ORDER BY saw_0
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
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
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
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