Multiple-fact, Multiple-grain Query on Conformed Dimensions

A query on multiple facts and conformed dimensions respects the cardinality between each fact table and its dimensions and writes SQL to return all the rows from each fact table.

For example, Sales and Product Forecast are both facts.

multiple facts and cardinality to conformed dimensions

Note that this is a simplified representation and not an example of how this would appear in a model built using IBM® Cognos® modeling recommendations.

The Result

Individual queries on Sales and Product Forecast by Month and Product yield the following results. The data in Sales is actually stored at the day level.

A query on Sales and Product Forecast respects the cardinality between each fact table and its dimensions and writes SQL to return all the rows from each fact table. The fact tables are matched on their common keys, month and product, and, where possible, are aggregated to the lowest common level of granularity. In this case, days are rolled up to months. Nulls are often returned for this type of query because a combination of dimensional elements in one fact table may not exist in the other.

Note that in February 2004, Course Pro Umbrellas were in the forecast but there were no actual sales. The data in Sales and Product Forecast exist at different levels of granularity. The data in Sales is at the day level, and Product Forecast is at the month level.

The SQL

The SQL generated by IBM Cognos software, known as a stitched query, is often misunderstood. A stitched query uses multiple subqueries, one for each star, brought together by a full outer join on the common keys. The goal is to preserve all dimensional members occurring on either side of the query.

The following example was edited for length and is used as an example to capture the main features of stitched queries.

select
	coalesce(D2.MONTH_NAME,D3.MONTH_NAME) as MONTH_NAME, 
	coalesce(D2.PRODUCT_NAME,D3.PRODUCT_NAME) as PRODUCT_NAME, 
	D2.EXPECTED_VOLUME as EXPECTED_VOLUME,
	D3.QUANTITY as QUANTITY
from (select TIME.MONTH_NAME as MONTH_NAME,
	PRODUCT_LOOKUP.PRODUCT_NAME as PRODUCT_NAME,
	XSUM(PRODUCT_FORECAST_FACT.EXPECTED_VOLUME for  
	TIME.CURRENT_YEAR,TIME.QUARTER_KEY,TIME.MONTH_KEY,
	PRODUCT.PRODUCT_LINE_CODE, PRODUCT.PRODUCT_TYPE_CODE,
	PRODUCT.PRODUCT_KEY) as EXPECTED_VOLUME
from 
	(select TIME.CURRENT_YEAR as CURRENT_YEAR,
	TIME.QUARTER_KEY as QUARTER_KEY,
	TIME.MONTH_KEY as MONTH_KEY,
	XMIN(TIME.MONTH_NAME for TIME.CURRENT_YEAR,
	TIME.QUARTER_KEY,TIME.MONTH_KEY) as MONTH_NAME
	from TIME_DIMENSION TIME
	group by TIME.MONTH_KEY) TIME
	join PRODUCT_FORECAST_FACT PRODUCT_FORECAST_FACT
	on (TIME.MONTH_KEY = PRODUCT_FORECAST_FACT.MONTH_KEY)
	join PRODUCT PRODUCT on (PRODUCT.PRODUCT_KEY =
	PRODUCT_FORECAST_FACT.PRODUCT_KEY)
where
	(PRODUCT.PRODUCT_NAME in ('Aloe Relief','Course Pro
Umbrella')) and 
	(TIME.MONTH_NAME in ('April 2004','February 2004','February
2006')) 
group by 
	TIME.MONTH_NAME,
	PRODUCT_LOOKUP.PRODUCT_NAME
) D2
 full outer join 
(select TIME.MONTH_NAME as MONTH_NAME,
	PRODUCT_LOOKUP.PRODUCT_NAME as PRODUCT_NAME,
	XSUM(SALES_FACT.QUANTITY for TIME.CURRENT_YEAR,
	TIME.QUARTER_KEY, TIME.MONTH_KEY, 
	PRODUCT.PRODUCT_LINE_CODE, PRODUCT.PRODUCT_TYPE_CODE, 
	PRODUCT.PRODUCT_KEY ) as QUANTITY
from 
select TIME.DAY_KEY,TIME.MONTH_KEY,TIME.QUARTER_KEY,
	TIME.CURRENT_YEAR,TIME.MONTH_EN as MONTH_NAME 
	from TIME_DIMENSION TIME) TIME
	join SALES_FACT SALES_FACT
	on (TIME.DAY_KEY = SALES_FACT.ORDER_DAY_KEY)
	join PRODUCT PRODUCT on (PRODUCT.PRODUCT_KEY = SALES_FACT.PRODUCT_KEY)
where 
	PRODUCT.PRODUCT_NAME in ('Aloe Relief','Course Pro Umbrella'))
	and (TIME.MONTH_NAME in ('April 2004','February 2004','February
2006'))
 group by 
	TIME.MONTH_NAME, 
	PRODUCT.PRODUCT_NAME 
) D3
 on ((D2.MONTH_NAME = D3.MONTH_NAME) and 
 (D2.PRODUCT_NAME = D3.PRODUCT_NAME))

What Is the Coalesce Statement?

A coalesce statement is simply an efficient means of dealing with query items from conformed dimensions. It is used to accept the first non-null value returned from either query subject. This statement allows a full list of keys with no repetitions when doing a full outer join.

Why Is There a Full Outer Join?

A full outer join is necessary to ensure that all the data from each fact table is retrieved. An inner join gives results only if an item in inventory was sold. A right outer join gives all the sales where the items were in inventory. A left outer join gives all the items in inventory that had sales. A full outer join is the only way to learn what was in inventory and what was sold.