Introduction
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.
An other simple built-in solution exist to achieve this goal: the use of 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)
Articles Related
The design
- Create a new logical table source “CHANNELS_FILTER” in the logical table Channels
- Map the logical column Channel Desc to the physical Column Channels.“Channel Desc” in the tab “Column Mapping”
- Add the filter in the content tab
- Drag and drop the new logical column in the presentation layer
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
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
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
The Old Solution
The design
- Create an table alias of the channel table
- Create all the joins that go to the fact and don't forget the join between the original table and its alias
- Suppress the original column Channel desc in the logical layer
- Drag and drop from the table channel_alias the column “Channel desc” in the logical table dimension “Channel”
- A logical table source channel_alias appear. Open it and add a filter in the content tab.
“orcl SH”.“”.SH.CHANNEL_ALIAS.CHANNEL_CLASS = 'Direct'
- Drag and Drop the logical column “Channel desc” in the presentation layer. Suppress the original one if necessary.
The problem: Cannot find logical table source coverage
If you get this error when you choose the columns Channels.“Channel Class” and Channels.“Channel Desc”, it's because OBIEE can not find any relation between the first logical table source and the alias logical table source.
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 14070]
Cannot find logical table source coverage for logical columns: [Channel Class]. Please check more detailed level keys
are mapped correctly. (HY000)
SQL Issued: SELECT Channels."Channel Class" saw_0, Channels."Channel Desc" saw_1 FROM SH ORDER BY saw_0, saw_1
To resolve this issue, you must create a join between the physical table channels and its alias channel_alias.
One join more, it's not the optimal solution