What are multi-fact, multi-grain queries

In this section, the term dimension is used in the conceptual sense. A query subject with cardinality of 1:1 or 0:1 behaves as a dimension.

A dimensional query subject typically has distinct groups, or levels, of attribute data with keys that repeat. The IBM® Cognos® studios automatically aggregate to the lowest common level of granularity present in the report. The potential for double-counting arises when creating totals on columns that contain repeated data. When the level of granularity of the data is modeled correctly, double-counting can be avoided.

This example shows two fact query subjects, Sales and Product forecast, that share two dimensional query subjects, Time and Product.

Time is the focal point of the granularity issue in this example. Sales is joined to Time on the Day key, and Product forecast is joined to Time on the Month key. Because of the different join keys, a minimum of two determinants must be clearly identified on Time. For example, the determinants for Month and Day have their keys identified. Day is the unique key for Time, Month keys are repeated for each day in the month.

For example, the determinant for Month is as follows.

The Product query subject could have at least three determinants: Product line, Product type, and Product. It has relationships to both fact tables on the Product key. There are no granularity issues with respect to the Product query subject.

By default, a report is aggregated to retrieve records from each fact table at the lowest common level of granularity. If you create a report that uses Quantity from Sales, Expected volume from Product forecast, Month from Time, and Product name from Product, the report retrieves records from each fact table at the lowest common level of granularity. In this example, it is at the month and product level.

To prevent double-counting when data exists at multiple levels of granularity, create at least two determinants for the Time query subject. For an example, see Determinants.

If you do not specify the determinants properly in the Time query subject, incorrect aggregation may occur. For example, Expected volume values that exist at the Month level in Product forecast is repeated for each day in the Time query subject. If determinants are not set correctly, the values for Expected volume are multiplied by the number of days in the month.

Note the different numbers in the Expected volume column.