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)
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
“orcl SH”.“”.SH.CHANNEL_ALIAS.CHANNEL_CLASS = 'Direct'
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