Column dependencies - multi-fact queries

When we are modeling against a data warehouse (or using a star schema design), we often have dimensions and facts that are joined on varying grains of detail.

Let’s take a look at a sample model before we add column dependencies. We have the following joins defined.

Products joins to Sales Fact on Product Key. This is the lowest grain of detail in the database.

When we look at the relationship between Sales Target and Products, we can see that it joins on a higher grain (Product type key).

This is not the lowest grain in the Products table so we will see repeating values if we include information from both fact tables in our report.

Here is an example of the data in a report:

In this example, our Sales Target is way higher than our Sales Total. If we add more information to the report, we can see where these numbers are coming from.

Since we only have a Sales Target for every Product type - not for every product - we are seeing the Sales Target repeat for every product. Because Cognos Analytics will sum these values by default, we see the incorrect information when we only include Product line, Sales total, and Sales target. Even with more detail, we will see incorrect subtotal values.

So, how can we fix this? We can add column dependencies in the data module.

To do this, we go to each conformed dimension (each dimension that sits between two fact tables) and go to the Column dependencies screen.

We start here with a blank canvas.

From the Products table, we drag over the columns that represent the different groupings of data we have on this query – so on the product table we have three major levels or groupings of data: product line, product type, and product.

You should choose the key or code columns here whenever possible (or the fields that are used for the joins).

We need to link these dependencies in order of coarsest grain to most fine. To do this, we start with product line and drag the connection icon on top of product type. Do the same from product type to product.

Next you add all of the columns that are at the same grain as your initial columns:

We repeat this process with the other conformed dimensions.

If we test our report again, here is what we will see:

If we add more details:

You can see the data is still being repeated (the only option since Cognos does not know how that product type sales target could be divided by product) but when we look at the summary column, it is showing the correct value.

(Return to overview of column dependencies)