IBM Cognos Dynamic Cubes

84 IBM Cognos Dynamic Cubes

4.8 Multi-grain fact scenarios

A common modeling problem is multi-granularity, which occurs when levels of dimensional

detail for facts differ. In your data source, the level of information in a dimension can be more

precise than fact data of some fact tables in which the dimension takes part.

For example, a time dimension can have dimension information for the levels of year, quarter,

month, and day. For a Sales fact table, the facts exist at the day level. For a fact table with

planned sales values, the fact grain or level is probably at a higher level of detail such as

month.

This difference in fact grain can make it difficult to plan queries correctly if a report user

included a level below the fact grain in the report.

The method for handling this scenario is to create role-playing dimensions for each instance

of a dimension that has varying levels of granularity to different fact tables.

In this example, you need to model a time dimension down to the day level for the Sales cube

and model a time dimension down to the month level for the sales target cube. For the former,

the relationship is formed between the appropriate keys in the fact table and the day level. For

the latter, the relationship is formed between the month level and the fact table keys.

If you want to create reports that used data from both cubes, you can create a virtual cube.

The virtual cube can have the two Time dimensions merged. The levels of both source cubes

will be merged. The virtual cube might have levels that are below the grain of measures from

one of the source cubes. Queries that are made in the virtual cube that use dimension levels

below the grain of a measure will return null values, ensuring that the consumers of the cube

do not have double counting. For more information about virtual cubes, see Chapter 7,

“Dimensional security” on page 147.

Perhaps, as in the example in 4.7, “Role-playing dimensions” on page 82, you have the

following cubes:

򐂰 A sales fact cube where the fact grain for the time dimension is at the day level

򐂰 A planned sales cube where the fact grain for the time dimension is at the month level,

A virtual cube that uses both of these cubes as its source enables you to make a query with

day level objects (either the level itself or a member of that level, depending on the studio that

you are using) against the sales facts and get results and get the expected nulls for the

planned sales facts. If you used a Time dimension grain that was common to both fact tables

you get non-null results for measures from both fact tables.

Instructions that illustrate this are in the 4.15, “Modeling for multi-grain scenarios” on

page 112.

Multiple fact tables

If you want to allow the package users to query against multiple cubes, use virtual cubes.

With virtual cubes you can build a cube that shares dimensions between cubes and build

reports that use facts from multiple cubes. One example of this is to have a virtual cube that

includes a cube with actual values and a cube with target or budget values.

To the virtual cube, you can build in calculated measures that reference measures from both

source cubes. One example of those is a measure that calculates the variance from plan by

subtracting the plan values from the actual values. If the remainder is a positive number, then

the variance will exceed the plan. This can be beneficial or not, depending on what was