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

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

OBIEE - Fragmentation (Partitioning)

The design
1. Create a new logical table source CHANNELS_FILTER in the logical table Channels 2. Map the logical column Channel Desc to the physical Column Channels.Channel Desc in the tab Column Mapping 3. Add the filter in the content tab 4. 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

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. One join more, it's not the optimal solution

Joining two fact tables with different dimensions into single logical table
Often question on OTN forum is that if we have two fact tables and they are sharing some dimensions and some dimensions are not shared how we can show data for all dimensions. For example if we have F1 (D1, D2 and D3), and F2 (D1 and D2 and D4) and user choose

F1 F2 D1 D2 D3 D4 he need to get data for F1 that matchs only for D1-D2-D3 and data for F2 that matchs only D1-D2-D4, all that in one row, so D3 and D4 are not common dimensions.

How we can achieve this in BMM model in one logical fact table?

Fist, we make in our repository table SALES_TIME_CHANNELS that has only TIMES and CHANNELS dimensions and SALES that has only PRODUCTS and TIMES. So, the only common dimension here is TIMES.

Physical diagram:

Source select for SALES_TIME_CHANNELS:

select (amount_sold-100) as amount1, time_id, channel_id from sales

BMM:

We make complex joins for all dimensions to our fact LT:

It is required to create dimensions for all dimension tables. Not that we have two logical table sources for SALES fact logical table. As we have 2 measure (one from SALES table, second from SALES_TIME_CHANNELS table) we need to specify aggregation level for those measures because they are not sharing some dimensions.

Go first to QUANTITY_SOLD and put total logical level for ChannelsDim dimension. With this we exclude channels dimension for SALES table in the GROUP BY part because SALES is only aggregated by TIME and PRODUCTS dimension:

The similar we do for amount1 column:

Test in Answers:

We can see that measure amount1 is aggregated only by CALENDAR_YEAR and CHANNEL_CLASS and measure QUANTITY_SOLD is aggregated only by PROD_CATEGORY and CALENDAR_YEAR:

NQQuery.log (OBIEE server generates 2 separate queries):

Aggregates in OBIEE
Aggregate fact tables contain same measure data like in the lowest granularity fact table but summarized on certain level. Aggregates in obiee can be created using aggregate persistence wizard or manually.

For the first option:

http://www.oracle.com/technology/obe/obe_bi/bi_ee_1013/aggpersist/aggpersist.htm http://www.rittmanmead.com/2007/10/26/using-the-obiee-aggregate-persistence-wizard http://obiee101.blogspot.com/2008/11/obiee-aggregate-persistence-wizard.html

Advanced option is using materialized views, dimensions and query rewrite.

I'll show the second option (manually).

Creating database objects

For this example we'll create database objects, higher level dimension tables, aggregates, indexes, ect. Something about higher dimension tables, it depends how you understand normalized and denormalized structure in business intelligence term. Dimension tables are always denormalized, each level is placed inside it. If you for example query sh.products table you'll see that the lowest level has information about high levels. If you are using dimension operator in OWB to load data into, the result is dimension table with addition that all levels are separately loaded with each with its own ID, primary key. So other aggregation fact tables can reference high level dimension ID from the same dimension. The very similar way is how olap dimension works, see global.channel_dimview. Anyway, we'll create higher dimension level tables for this example purpose.

create table months as select distinct calendar_month_id, calendar_month_desc,

calendar_year_id, calendar_year from times

alter table months add constraint months_pk primary key (calendar_month_id);

create table categories as select distinct prod_category_id, prod_category from products

alter table categories add constraint categories_pk primary key (prod_category_id)

create table years as select distinct calendar_year_id, calendar_year from times

alter table years add constraint years_pk primary key (calendar_year_id)

create table sales_months as select t.calendar_month_id,

sum(s.amount_sold) as amount_sold, sum(s.quantity_sold) as quantity_sold from sales s, times t where s.time_id=t.time_id group by t.calendar_month_id;

alter table sales_months add constraint sm_months_fk foreign key (calendar_month_id) references months (calendar_month_id)

create bitmap index sm_months_idx on sales_months (calendar_month_id);

create table sales_year_cat as select t.calendar_year_id, p.prod_category_id, sum(s.quantity_sold) as quantity_sold, sum(s.amount_sold) as amount_sold from sales s, products p, times t where s.prod_id=p.prod_id and s.time_id=t.time_id group by t.calendar_year_id, p.prod_category_id;

alter table sales_year_cat add constraint syc_years_fk foreign key (calendar_year_id) references years (calendar_year_id)

create bitmap index syc_years_idx on sales_year_cat (calendar_year_id);

alter table sales_year_cat add constraint syc_categories_fk foreign key (prod_category_id) references categories (prod_category_id)

create bitmap index syc_categories_idx on sales_year_cat (prod_category_id);

create table sales_months_cat_ch as select t.calendar_month_id, p.prod_category_id, c.channel_id, sum(s.quantity_sold) as quantity_sold, sum(s.amount_sold) as amount_sold from sales s, products p, times t, channels c where s.prod_id=p.prod_id and s.time_id=t.time_id and s.channel_id=c.channel_id group by t.calendar_month_id, p.prod_category_id, c.channel_id;

alter table sales_months_cat_ch add constraint smcc_months_fk foreign key (calendar_month_id) references months (calendar_month_id)

create bitmap index smcc_months_idx on sales_months_cat_ch (calendar_month_id);

alter table sales_months_cat_ch add constraint smcc_channels_fk foreign key (channel_id) references channels (channel_id)

create bitmap index smcc_channels_idx

on sales_months_cat_ch (channel_id);

alter table sales_months_cat_ch add constraint smcc_categories_fk foreign key (prod_category_id) references categories (prod_category_id)

create bitmap index smcc_categories_idx on sales_months_cat_ch (prod_category_id);

The focus is on how to implement this in obiee, not how these tables are refreshed with data or recreated as a part of the job of ETL process.

Implementation in obiee

Physical layer:

Foreign keys:

SALES.PRODUCT_ID >- PRODUCTS.PRODUCT_ID SALES.TIME_ID >- TIMES.TIME_ID SALES.CHANNEL_ID >- PRODUCTS.CHANNEL_ID

SALES_MONTHS_CAT_CH.CHANNEL_ID >- CHANNELS.CHANNEL_ID

SALES_MONTHS_CAT_CH.PROD_CATEGORY_ID >- CATEGORIES.PROD_CATEGORY_ID SALES_MONTHS_CAT_CH.CALENDAR_MONTH_ID >- MONTHS.CALENDAR_MONTH_ID

SALES_YEAR_CAT.PROD_CATEGORY_ID >- CATEGORIES.PROD_CATEGORY_ID SALES_YEAR_CAT.CALENDAR_YEAR_ID >- YEARS.CALENDAR_YEAR_ID

SALES_MONTHS.CALENDAR_MONTH_ID >- MONTHS.CALENDAR_MONTH_ID

BMM:

Drag and drop attributes from the physical layer to BMM, for example CALENDAR_YEAR_ID and CALENDAR_YEAR from YEARS physical table to TIMES logical table to create additional logical table sources. We repeat this step for other higher level dimension tables on the physical layer as weel as for SALES_MONTHS, SALES_YEAR_CAT and SALES_MONTHS_CAT_CH aggregate fact tables that contains measures AMOUNT_SOLD and QUANTITY_SOLD.

Dimensions:

On each logical fact table source on the logical fact table SALES we need to set aggregation levels and this is mandatory step for obiee to redirect SQL query on aggregate tables.

Aggregate sources are activated on certain levels of dimension.

Test

If we add CALENDAR_MONTH_DESC, instead of going to SALES (TIME_ID lowest level) and summarize it on the month level, the SQL query is redirected to SALES_MONTHS:

NQQuery-log:

In case of CALENDAR_YEAR the SQL query is also redirected to SALES_MONTHS:

Some other cases:

CALENDAR_MONTH_DESC, PROD_CATEGORY and CHANNEL_DESC:

NQQuery-log:

CALENDAR_YEAR and PROD_CATEGORY:

NQQuery.log: