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

> Nico Web Log (Blog) > Blog - 2010

1 - 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

2 - 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

Advertising

3 - The result

3.1 - 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

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

3.2 - 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

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
Advertising

3.3 - 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