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

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 design

1. Create an table alias of the channel table
2. Create all the joins that go to the fact and don't forget the join between the original table and its alias

1. Suppress the original column Channel desc in the logical layer
2. Drag and drop from the table channel_alias the column “Channel desc” in the logical table dimension “Channel”
3. 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'

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