Verify relationships

The metadata modeling tools can detect and create relationships between tables during import. However, after import, it's always a good practice to ensure that the relationships are as you intend them to be to meet the query requirements.

Are the fact tables truly fact tables in that they have only n cardinality attached to them? Are the dimension tables truly dimension tables with only 1 cardinality attached?

There might be some exceptions with dimension tables in that they might be snowflake dimensions. A snowflake dimension consists of multiple tables that all represent the overall dimension. The tables are normalized.

The following Product tables are an example of a snowflake dimension:

Product line, Product type, and Product all make up the Product hierarchy for the Product dimension. You might ask, since Product type and Product both have n cardinality attached, couldn’t they be seen as fact tables in the context of a query. In this case, no. There is a 1 to many linear path to the Sales fact table. The Cognos® Analytics query service doesn’t see any of these product tables as fact tables in the context of a query.

Consider the following scenario:

In this scenario, if Product, Product name lookup, and Sales fact were all included in the query, both Product name lookup and Sales fact would be treated as fact tables and create an unnecessary stitch query. Is the relationship between Product and Product name lookup truly a 1 to many? Upon investigating the data, Product name lookup is a multilingual table that has multiple rows for each product in the Product table. So this is truly a 1 to many relationship. But based on the business requirement, we need to see only one language at a time, and the language chosen is based on the user’s locale setting in Cognos Analytics. Therefore, we can add a filter to the Product name lookup table. The filter would return only one row per product, which changes the nature of the relationship to 1 to 1, as shown in the following scenario:

As you investigate your relationships, always ensure that dimensions are treated as dimensions, and facts as facts. A simple star schema database design makes this job easy. However, in many cases, there might be some scenarios in the database design that need to be addressed in the model. Later in this document, we look at options to consolidate snowflake dimensions.