Multi-fact, multi-grain queries

Multiple-fact, multiple-grain queries occur when a table containing dimensional data is joined to multiple fact tables on different key columns.

For example, the Time dimension is joined to the Sales Fact on the Day Key (day grain), and to Sales Target Fact on the Month Key (month grain). The following tables, using some simple data, illustrate the concepts and query outputs.

A dimensional table typically contains distinct groups, or levels, of attribute data with keys that repeat. In the previous example, the Time dimension illustrates this rule with repeating Year, Quarter, and Month values. Cognos® Analytics automatically aggregates values to the lowest common level of granularity that is present in the query. The potential for double-counting arises when creating totals on columns that contain repeated data. For example, Sales Target Fact values, which are at the Month Key level, would repeat for every Day Key for Sales Fact values, which are at the Day Key level.

If you visualize data at a level of granularity below the lowest common level, in this case the Day level, the data of higher granularity, the Month level, is repeated, as shown in the Sales Target in the following table.

Please note that the Total value for Sales Target is 75, which is not correct since the Sales Target value for the month of January is only 25.

When the level of granularity of the data is modeled correctly, double-counting of the Sales Target Fact values is avoided, as shown in the query result in the following table.

For information, see Prevent double counting.

Non-shared dimension scenario

In the following query example, the Order Method table is introduced into the query. Order Method applies only to Revenue from the Sales Fact table, and not to Sales Target from the Sales Target Fact table. In this scenario, Sales Target is repeated for every row introduced by the Order Method, but the Sales Target values are not double-counted.

Table 6. Sales Target values are not double-counted

Year	Month	Day	Order Method	Revenue	Sales Target
2020	20200101	Jan 1 2020	Mail	4	25
2020	20200101	Jan 1 2020	Web	4	25
2020	20200101	Jan 1 2020	Visit	2	25
2020	20200101	Jan 2 2020	Mail	5	25
2020	20200101	Jan 2 2020	Visit	5	25
2020	20200101	Jan 3 2020	Mail	5	25
2020	20200101	Jan 3 2020	Web	5	25
Total				30	25