Result set of a multi-fact query contains blanks

The result set of a multi-fact query contains blanks in some columns, even though there is a conformed dimension that should join the records from both fact tables.

When IBM® Cognos Business Intelligence processes a multi-fact query, it breaks it up into two single fact queries (each with items from a single star in the star schema), then stitches the results of both of those queries together to create a single result set. You can see the two queries and stitching in the native SQL generated for the multi-fact query in Report Studio.

Stitching these queries together is not the same as joining tables in the model. Stitching the two sides together requires a stitch key that exists in each single fact query as a unique value for the row. The rows from the two single fact queries are then matched 1:0, based on the stitch key values. The stitching must be 1:0. Otherwise, you can get double counting of measure data, or exclude rows that should be included. The stitching also means that it is possible for a row from one of the single fact queries to not have a corresponding row from the other single fact query, leading to blanks in the result set.

When the fact tables contain non-conformed dimensions between them, there is not a suitable stitch key among the query items selected. As a result, IBM Cognos BI creates one using RSUM, and joins the results of the two queries. For more information, see the topic Multiple-fact, Multiple-grain Query on Non-Conformed Dimensions in Guidelines for Modeling Metadata Guide.

When there is a conformed dimension, it is still possible to not have a suitable stitch key, depending on what items are used in the query and other model design factors. In such cases, IBM Cognos BI uses the conformed dimensions and the row numbers from the results of each query to create a stitch key. If one single fact query returns more rows than the other, there are blanks in the result.

To resolve the problem, it might be possible to avoid splitting the query by changing the model. For more information, see Resolving Queries That Should Not Have Been Split in the Framework Manager User Guide. If you cannot avoid splitting the query and the report needs these two result sets to be joined differently, create two separate, single-fact queries in Report Studio, create a join between them, and define the join links and cardinality as needed.