How to resolve report issue based on metrics package from version 10.1.0 to version 10.1.1

Check list of all posts

Problem
When upgrading metrics package version from 10.1.0 to 10.1.1, report based on this package doesn’t work anymore. metrics


Analysis
When importing FM metrics package from version 10.1.0, report works. As soon as FM metrics package opened by FM 10.1.1 is published to content store, report stops working. By comparing generated SQL statements, you can see there are huge differences. The main difference is that MOD_SCORECARD_GROUPS comes to play.  Changing report to force report use MOD_STRATEGY_METRICS , report is generated with same result, but with extremely slow performance.  Looking into metrics, you’ll find the SQL is much longer and very slow when running in Query analyzer.


Solution
The solution is to remove the join between Metrics and MOD_SCORECARD_GROUPS
And the join between Strategies and MOD_SCORECARD_GROUPS

How to create data for all periods in scorecard including most recent period, YTD, Quarter and Annual

Check list of all posts

Context
Please see attached report below
(Period /actual /target /tolerance)

Monthly data is denoted as YTD, all actual data is same for Month, Quarter and annual, however, the target data is different between YTD and annual. While the target for month YTD and Quarter data is goal accumulated to the month, the target for annual is whole year goal.  Annual metric gives business user an idea about how far is the actual from target.

Solution

The idea is to create two metrics: YTD one and Annual one. YTD metric is only calculated until current month, while annual metric is calculated until current year, or month December.  Both metrics share the same YTD source table.

Implementation

1. Create source table that contains YTD data as below, (Please see solution in detail from last post How to generate Year to Date (YTD) data for scorecard using SQL statement – an practical approach)

The current month is August 2012.  Actual data is not changed for month 9, 10, 11 and 12, while the goal is changed for month 9, 10,11 and 12.

2. Create regular metrics ( month, YTD)

Regular metrics design

3. Create annual metrics

Annual metrics design 

4. Check data  in metric studio

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

How to generate Year to Date (YTD) data for scorecard using SQL statement – an practical approach

Check list of all posts

Context

In scorecard development, Year to Month data metrics is frequently used. The best way to archive such a metrics is to create a metrics source table to contain Year to Month data. It seems to be an easy task from business point of view; however, it can be a pretty challenge task. The main issue is that month data can be missed.

Given a sample below

Month data

Month	Sales
Jan	$10
Mar	$10

Year to Month data

Month	Sales
Jan	$10
Feb	$10
Mar	$20

The key is that you need to provide data for the month, even when there is no sale. In case when data is not populated for metric table, user won’t see metric for that month, it is February in this case.

This document is intended to provide a practical solution using SQL statements. gosalesdw database in SQL server is used. Sure that you can use ETL to handle it as well, but it could be complicated process, especially when data volume is very big.   You can download script for the review in detail, the goal is to generate metric source data as below

Implementation

Step 1: Create a master temp table to hold records. In order words, even there is not data for a specific month, year to month data should be available.  

SELECT F.organization_key AS [Dim1],
      F.employee_key     AS [Dim2],
      DT.[year]          AS [Year],
      DT.[month]         AS [Month]
INTO   #t_master_records
FROM   (SELECT F.organization_key,
              F.employee_key
       FROM   gosalesdw.sls_sales_fact F
              INNER JOIN (SELECT DISTINCT [current_year]  AS [year],
                                          [current_month] AS [Month]
                          FROM   gosalesdw.go_time_dim
                          WHERE  [current_year] = 2007
                                 AND [current_month] <> 0) DT
                      ON ( F.order_day_key / 10000 = DT.[year]
                           AND order_day_key / 100 - ( order_day_key / 10000 )
                                                     * 100 =
                               DT.[month] )
       WHERE  F.organization_key = 11101
              AND F.employee_key = 4001
       GROUP  BY F.organization_key,
                 F.employee_key) F,
      (SELECT DISTINCT [current_year]  AS [year],
                       [current_month] AS [Month]
       FROM   gosalesdw.go_time_dim
       WHERE  [current_year] = 2007
              AND [current_month] <> 0) DT

SELECT *
FROM   #t_master_records
ORDER  BY [month]



Step 2: Create an actual temp table to actual data for each month

SELECT F.organization_key                                      AS [Dim1],
      F.employee_key                                          AS [Dim2],
      F.order_day_key / 10000                                 AS [Year],
      F.order_day_key / 100 - ( order_day_key / 10000 ) * 100 AS [Month],
      Sum(F.sale_total)                                       AS [Actual]
INTO   #t_actual
FROM   gosalesdw.sls_sales_fact F
      INNER JOIN (SELECT DISTINCT [current_year]  AS [year],
                                  [current_month] AS [Month]
                  FROM   gosalesdw.go_time_dim
                  WHERE  [current_year] = 2007
                         AND [current_month] <> 0) DT
              ON ( F.order_day_key / 10000 = DT.[year]
                   AND order_day_key / 100 - ( order_day_key / 10000 ) * 100 =
                       DT.[month] )
WHERE  F.organization_key = 11101
      AND F.employee_key = 4001
GROUP  BY F.organization_key,
         F.employee_key,
         F.order_day_key / 10000,
         F.order_day_key / 100 - ( order_day_key / 10000 ) * 100

SELECT *
FROM   #t_actual
ORDER  BY [month]


Step 3: Create a target temp table to target data for each month

SELECT F.organization_key                      AS [Dim1],
      F.employee_key                          AS [Dim2],
      F.month_key / 100                       AS [Year],
      F.month_key - ( month_key / 100 ) * 100 AS [Month],
      Sum(F.sales_target)                     AS [Target]
INTO   #t_target
FROM   gosalesdw.sls_sales_targ_fact F
      INNER JOIN (SELECT DISTINCT [current_year]  AS [year],
                                  [current_month] AS [Month]
                  FROM   gosalesdw.go_time_dim
                  WHERE  [current_year] = 2007
                         AND [current_month] <> 0) DT
              ON ( F.month_key / 100 = DT.[year]
                   AND month_key - ( month_key / 100 ) * 100 = DT.[month] )
WHERE  F.organization_key = 11101
      AND F.employee_key = 4001
GROUP  BY F.organization_key,
         F.employee_key,
         F.month_key / 100,
         F.month_key - ( month_key / 100 ) * 100

SELECT *
FROM   #t_target
ORDER  BY [month]



Step 4: Get metrics data using mater table to join with both actual and target data, where Year to month actual and target data will be generated. Please note that date information has been change to date format so that metric calendar can be applied.

SELECT #t_master_records.[dim1],
      #t_master_records.[dim2],
      CONVERT(DATETIME, CONVERT(VARCHAR(4), #t_master_records.[year])
                        + '-'
                        + CONVERT(VARCHAR(2), #t_master_records.[month])
                        + '-01') AS [Date],
      Sum(#t_actual.actual)      AS Actual,
      Sum(#t_target.[target])    AS [Target]
FROM   #t_master_records
      LEFT JOIN #t_actual
             ON ( #t_actual.[dim1] = #t_master_records.[dim1]
                  AND #t_actual.[dim2] = #t_master_records.[dim2]
                  AND #t_actual.[year] = #t_master_records.[year]
                  AND #t_actual.[month] <= #t_master_records.[month] )
      LEFT JOIN #t_target
             ON ( #t_target.[dim1] = #t_master_records.[dim1]
                  AND #t_target.[dim2] = #t_master_records.[dim2]
                  AND #t_target.[year] = #t_master_records.[year]
                  AND #t_target.[month] <= #t_master_records.[month] )
GROUP  BY #t_master_records.[dim1],
         #t_master_records.[dim2],
         CONVERT(DATETIME, CONVERT(VARCHAR(4), #t_master_records.[year])
                           + '-'
                           + CONVERT(VARCHAR(2), #t_master_records.[month])
                           + '-01'),
         #t_master_records.[year],
         #t_master_records.[month]
ORDER  BY #t_master_records.[month]

How to remove data of invalid qualifiers in metric studio

Check list of all posts

Metric type Daily Call Avg. – All  is used to demonstrate the quickest way to get rid of invalid qualifiers. The qualifier was implemented based on activity type. It is requested to change it based on contract type.

First of all, change qualifiers from activity to contract type in metric designer,
Then execute, publish, clean up historical data, and transfer data from stage to metric store, we can see the old structure still there
It looks like there is no easy solution, the option 1 is to somehow clean up Metric store, the option 2 is to drop metric type and then redo it. Both options are pretty expensive.  There is a way to handle it using metric studio tool to remove these qualifiers, then all metrics will removed accordingly. Once republish data, and then old qualifiers will be GONE.

How to handle different metric types with same name

Check list of all posts

When you want to replace part of your scorecards with newly contents, it is probably requested to use the same name for the sake of consistency. How to achieve this goal?

There are three options:

• Option 1: Create new metric types, but with the same name
• Option 2: Don’t create any new metric type, but overwrite metric data
• Option 3: Create new metric types, but with different name

Option 1:

1. It is possible in scorecard design to use same name  called Call Frequency Attainment

2. However, two identifiers are used; the identifier of old one is WORKLOAD_MT, while the identifier of new one is V_WorkLoadAttain

3. In Metric designer, you will only see one name Call Frequency Attainment  when doing metric mapping

4. To figure out which one is the real one mapped; you can go to xml to determine.

- <metricsMapping>

- <metric>

<name>WorkloadActual</name>

<metricTypeID>V_WorkLoadAttain</metricTypeID>

- <valueSourceMapping attribute="PREVIOUS_YEAR_MT">

<valueSource type="expression">total([Metric Types].[AG_MS_VEE_WorkloadAttainment].[WorkLoadActual])/total([Metric Types].[AG_MS_VEE_WorkloadAttainment].[WorkLoadGoal])</valueSource>

</valueSourceMapping>

- <mapping attribute="aggregate_function">

<valueSource type="text">Automatic</valueSource>

</mapping>

- <mapping attribute="rollup_aggregate_function">

<valueSource type="text">Automatic</valueSource>

</mapping>

</metric>

5. In strategy design, you will see two metric type with the same name, it displayed with the sequence from metric type, the old one is listed first

6. In report, we can share the same strategy where each strategy element contains both old and new one.  We can define filter to control what metric type to display based on scorecard.

#/* control what metric type to show */#

(  (?Scorecard? starts with '2%')

and ([Metrics].[Metrics].[Metric Type External ID] not in ('WORKLOAD_MT','WORKLOAD_YEAR_MT','CUSTOMER_CALL_ENTRY_MT','PRE_CALL_PLANNING_MT','SALES_REP_ACTIVITY_MT','DAILY_CALL_AVERAGE_FOR_AB_CUSTOMER_MT','DAILY_CALL_AVERAGE_FOR_AB_Physician_MT')))

or

(  (not ?Scorecard? starts with '2%')

and ([Metrics].[Metrics].[Metric Type External ID] not in

('V_WorkLoadAttain','V_WorkLoadAttainAnn','V_AvgCallEntry','V_PreCallPlan','V_UnReportDays','V_DailyCallAvgSub','V_DailyCallAvgSub')))

7. Re# 6, another more flexible solution is to use data security to filter metric types to be applied.

Option 2: This is probably the better solution, as you don’t need to make any special handling in metric studio, metric strategy design and report adjustment. The only big issue is to decide how to handle the top level (All business unit).  The only thing you need to touch is metric designer

1. You can have multiple inputs to feed to metric type in metric designer by slice and dice manner

• Feed data at specific level, such as different business unit
• Feed data to different kinds of value, such as actual and objective

2. The running sequence becomes extremely important, when extracting the same metric with multiple steps. The basic idea is overwritten, subsequent step always overwrite data from last step, which is exactly opposite approach in TM1 rule.

Option 3: This option is to make option 1 much more clearly in term of maintenance; the only change is to metric name in report, which is very easy to proceed.