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

Tags:

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

  1. Surprised there are not more comments! Anyway, I like the post and the blog too. Will subsribe to your RSS feed.

  2. gerardnico says:

    Thanks. Always good to have feedback. Good as bad ;-) . Happy Sunday.
    Nico

  3. Great solution.
    I would love to see more of Oracle Apps OBIEE solutions

  4. Jini says:

    Hi,

    This blog was very helpful to me as I have the exact same requirements mentioned in this blog. I went the same way as you have explained at the top but I still have not got the desired results.
    So here’s what I have done so far -

    1. Joined Participant table and Tax table in physical layer
    2. added Tax table as logical table source of Participant dimension in business layer
    3. added this logic in the content tab of Tax table to take out the year from our as of date session variable-
    Tax Fact table.Tax Year Number = EXTRACT( YEAR FROM EVALUATE(‘to_date(to_char(to_date(%1,”mm-dd-yyyy”),”dd-mon-yyyy”),”dd-mon-yyyy”) ‘, REPLACE( VALUEOF(NQ_SESSION.”AS_OF_DATE”), ’12:00:00 AM’, ”)))

    now when I am running the report the columns from the Tax table are just coming out as blank.
    When I looked at the query seems like the content tab logic is step 3 above is not getting added to the query’s where clause. I am not sure why is that. Any idea???

    Regards,
    Jini

  5. Jini says:

    Just want to mention that the Participant table has 4 logical table sources and out of 4, 2 of them has the logic in content tab. I wanted to point this out because I am not sure if there’s any limitation in OBIEE tool on how many filters it can add from the logical table sources..
    Looking forward for all the help that I can get on this issue. Thanks.

  6. gerardnico says:

    Hy Jini,

    Sorry for the late answer but I don’t have always free time. The best way to get a quick answer is to post your problem to the OBI Forum.

    I hope that you found a solution.

    Cheers
    Nico

  7. Gokhan says:

    I also found a solution
    put CHANNEL_CLASS column on select then put i into case when like this case when CHANNEL_CLASS IS NOT NUL THEN 0 END
    obiee will group that value by 0 also apply filter which you set in physical layer.

Leave a Reply