Defining column dependencies

Define column dependencies to ensure that the fact data is aggregated correctly based on the keys or attributes of those keys that are used in the query.

The column dependency groups are related to each other in a hierarchy group in an order from coarse to fine granularity.

Tip: An attribute is a column that has the Usage property set to Attribute or Identifier. A fact is a column that has the Usage property set to Measure.

About this task

You do not need to specify column dependencies for all tables. Do it only when double-counting would take place. Your decision to specify column dependencies affects other Cognos® Analytics components, such as reports or dashboards.

Procedure

  1. Open an existing data module or create a new module that is based on a source that contains tables with repeated data at different levels of granularity.
  2. Identify the attribute and fact columns that might cause double-counting. For example, the table can contain data at the month, quarter, and year level.

    Verify whether column properties and data formats are properly specified. For example, change the Usage property to Attribute, or assign the data format of Currency to fact (measure) columns. Save the data module.

  3. Optional: To see how the data is aggregated before column dependency is specified, you can create a report that is based on your saved data module. Later, you can use this report to verify the effect of applying column dependency on data aggregation.
  4. From the table context (right-click) menu Context menu, select Specify column dependencies.

    The Column dependencies Icon representing column dependency view view is opened.

  5. Drag the attribute columns that you identified in step 2, such as Year, Quarter, Month, and Day from the Data module panel to the Column dependencies view.
  6. Click the group icon Icon for a grouped column, and draw a line from the highest level attribute to the left of the next level attribute. Group the columns in a logical order to create a hierarchy group.

    Repeat this action for each level until the hierarchy is complete from coarse to fine granularity.

  7. Drag any related attributes or measures, such as Quarter (caption), Month (caption), Sales target, and Date and Revenue, inside the related attribute area.
    Note: Each column from the table must be in one group. Otherwise, validation warnings are shown.

    You can view the groups in the Horizontal view or Vertical view.

  8. Verify, and if needed, modify the column dependency settings. For more information, see Configuring column dependencies.
  9. Save the data module.

Results

The following scenario 1 example shows how to group hierarchy columns and add their attributes in a denormalized table that includes facts at different levels of granularity.

Figure 1. Example of column dependencies for scenario 1
Column dependency example

The following scenario 2 example shows column dependencies in a Time dimension table that can relate to multiple fact tables at different levels of granularity. You can see how columns in the Time dimension can be grouped, and their attributes added. In this case, the Time dimension is joined to the Sales Target fact table on the Month Key, and to the Sales fact table on the Day Key. The hierarchy for the Time dimension table is specified and configured to prevent double counting when a query includes the Day Key level and the Sales Target fact, which would repeat for every day in the month.

Figure 2. Example of column dependencies for scenario 2
Column dependency example

The following scenario 3 example shows how column dependencies can be defined when a dimension table contains measures. The column dependency is built around the unique Training Key column. All other columns are nested underneath as attributes of the Training Key column. In this case, there is no hierarchy in the dimension data so only one dependency group is defined.

Figure 3. Example of column dependencies for scenario 3
Column dependency example