How to model Cognos FM to flexibly align Forecast with Actual data

Check list of all posts

Obviously, it is not easy to align data between actual and forecast data. The normal issue is that data granularity of forecast is different from granularity of actual. We can resolve this issue with Cognos FM using determinant.  However, sometimes data provided on forecast side is described in text to indicate different slice and dice, such product break down by brand, or by location. This article is intended to resolve this issue with a flexible way.

Problem
In order to explain this issue in detail, we create a sample below.

 

 

The forecast table in database has column Product, but it is actually not product itself. You can see from this sample, forecast data is saved in two different ways:
1. Product itself, i.e. Product1
2. Product with different breakdown, i.e.
• Product1 - Brand1
• Product1 - Brand2
• Product1 - Location1
• Product1 - Location2
The question is how to align data in the way as below in report.

As this project need to model data as DMR, we cannot simple use query to join and union to derive data on report level.  Somehow, we need to leverage Cognos stitched query concept, make it work by introducing conformed dimensions,  and link conformed dimension to derived columns from forecast side. In addition, to avoid duplicated data, we need to logically separate forecast data into total level and derived break down level.

Solution
Below is the FM design screenshot, where we get 3 dimensions designed and separate logic forecast tables

1. Create TOTAL_FORECAST on business layer
[Business Layer].[TOTAL_FORECAST].[PRODUCT]  not in (
'Product1 - Brand1'
,'Product1 - Brand2'
,'Product1 - Location1'
,'Product1 - Location2')

2. Create DERIVED_FORECAST on business layer
[Business Layer].[DERIVED_FORECAST].[PRODUCT]   in (
'Product1 - Brand1'
,'Product1 - Brand2'
,'Product1 - Location1'
,'Product1 - Location2')

3. DERIVED_PRODUCT: DECODE ([Business Layer].[DERIVED_FORECAST].[PRODUCT] ,'Product1 - Brand1','Product1','Product1 - Brand2','Product1','Product1 - Location1','Product1','Product1 - Location2','Product1')

4. DERIVED_BRAND: DECODE ([Business Layer].[DERIVED_FORECAST].[PRODUCT] ,'Product1 - Brand1','Brand1','Product1 - Brand2','Brand2')

5. DERIVED_LOCATION: DECODE ([Business Layer].[DERIVED_FORECAST].[PRODUCT] ,'Product1 - Location1','Location1','Product1 - Location2','Location2')

6. Make FORECAST derived from TOTAL_FORECAST and DERIVED_FORECAST for presentation layer.

7. Create report based on DMR