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):