When you filter on the month and product, the result is as follows.

Note that this is a simplified representation and not an example of how this would appear in a model built using 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 2007, 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 Cognos 8, 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 to illustrate the main features of stitched queries.

select
	coalesce(D2.MONTH_NAME,D3.MONTH_NAME) as MONTH,coalesce(D2.PRODUCT_NAME,D3.PRODUCT_NAME) as PRODUCT, 
	D2.EXPECTED_VOLUME as SALES_TARGET,
	D3.QUANTITY as QUANTITY
from (select TIME.MONTH_NAME as MONTH,
	PRODUCT_LOOKUP.PRODUCT_NAME as PRODUCT,
	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 SALES_TARGET
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
	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 2007','February 2007','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 byTIME.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 subquery that is built for each detected fact table. 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.

The SQL

If you look at the SQL, you can see that, because Cognos 8 detected that a circular join path exists in the model, it did not include one of the relationships that was not necessary to complete the join path. In this example, the relationship between Time and Product forecast was dropped.

A circular join path rarely results in a query that produces useful results.

select 
	TIME_.MONTH_NAME as MONTH,
	PRODUCT_LOOKUP.PRODUCT_NAME as PRODUCT,
	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,
	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 SALES_TARGET
from 
	(select TIME.DAY_KEY,TIME.MONTH_KEY, TIME.QUARTER_KEY,
	TIME.CURRENT_YEAR,TIME.MONTH_EN as MONTH 
	from TIME_DIMENSION TIME) TIME 
	joinSALES_FACT on (TIME_.DAY_KEY = SALES_FACT.ORDER_DAY_KEY)joinPRODUCT_FORECAST_FACT on (TIME_.MONTH_KEY =PRODUCT_FORECAST_FACT.MONTH_KEY)joinPRODUCT (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.PRODUCT_NAME

You can deal with these issues in Report Studio by creating an inner or outer join between queries or creating calculations that compensate for differences in granularity. This workaround requires the report author to have detailed knowledge of the data. There is no workaround available in Query Studio or Analysis Studio.

Please note that this approach can result in a query that joins across multiple fact tables in a single subquery, which can have a negative impact on performance.

The Result

The results of individual queries on the respective star schemas look like this.

Querying the same items from both star schemas yields the following result.

In this result, the lower level of granularity for records from Sales results in more records being returned for each month and product combination. There is now a 1-n relationship between the rows returned from Product forecast and those returned from Sales.

When you compare this to the result returned in the example of the multiple-fact, multiple grain query on conformed dimensions, you can see that more records are returned and that Expected volume results are repeated across multiple Order Methods. Adding Order Method to the query effectively changes the relationship between Quantity data and Expected volume data to a 1-n relationship. It is no longer possible to relate a single value from Expected volume to one value from Quantity.

Grouping on the Month key demonstrates that the result in this example is based on the same data set as the result in the multiple-fact, multiple-grain query but with a greater degree of granularity.

The SQL