How to prepare fact data for metrics in Cognos scorecarding (Design approach)

Check list of all posts

As known, metric designer can define pretty much” three dimensions”

• Scorecard, or scorecard hierarchy
• Time hierarchy, or Year, Quarter, and Month
• Qualifier, which can be considered as a dimension

When preparing data for a metric, there are many options to implement besides different kinds of data source. The key is to choose an appropriate option to balance ETL and Metrics Designer.  There are three questions to be answered:

Q1:  which stage to perform calculation?  ETL or Metric Designer (2)
Please be aware of functionalities in metrics mapping, which can dramatically reduce the workload of ETL.  These functions are very powerful as it is in report studio.   However, in some cases you can’t use the default calculation, such as month data is actually denoted as Year to Month Data. ( see old post How to generate Year to Date (YTD) data for scorecard using SQL statement – an practical approach)

Q2:  Is actual value and target separately loaded?  Yes  or No (2)
Please note that actual value and target can be separately loaded. This approach could bring a major advantage of data load in term of ETL. For example, when target is given by separated data stream, or manually process. You can stitch target data with actual table in ETL, but this option could be very complicated.

Q3:  Can monthly, quarterly, and yearly metrics separately loaded? Yes  or No (2)
Please be aware of business calendar function, metric studio can automatically aggregate actual and target data. However, you cannot use default aggregate function at all, when the actual and target cannot be aggregated. In this case, we need to build separated job for month, quarter, and year respectively. For example, Targets are manually given for month, quarter, and year.

It will end up 8 different design approaches as bellow

Approach#	perform calculation	separately load actual and target	separately load time hierarchy	#  of metric jobs	Note
1	ETL	No	No	1
2	ETL	No	Yes	3	Consider month, quarter year as three different jobs in metric
3	ETL	Yes	No	2	One for actual, and one for target
4	ETL	Yes	Yes	4/6	4: when actual can be rolled up, and target can’t be rolled up.  or vice visa 6: both actual and target can’t be rolled up
5	Metric Designer	No	No	1
6	Metric Designer	No	Yes	3	Consider month, quarter year as three different jobs in metric
7	Metric Designer	Yes	No	2	One for actual, and one for target
8	Metric Designer	Yes	Yes	4/6	4: when actual can be rolled up, and target can’t be rolled up.  or vice visa 6: both actual and target can’t be rolled up