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

Card Puncher Data Processing

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

About

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

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

Repository Design Filter On Column New Logical Table

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

Repository Design Filter On Column Result1

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

Repository Design Filter On Column Result2

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







Share this page:
Follow us:
Task Runner