Figure 10-9: KPI Scorecard

rmb99 · January 19th, 2010, 03:10 PM

The issues discussed beginning on page 362 are illustrated in the sample report âChapter 10 Reports / KPI Scorecardâ contained in the downloadable samples. Examination of the rendered report in Figure 10-9 (page 366) indicates some problems. The most obvious is that the magnitude of many of the quota and actual sales amounts is in the billions and trillions of dollars, which is not intuitively reasonable.

Closer inspection in Report Designer shows further problems with the underlying data set. In particular, the query relates sales quotas to territory of the sale when, based upon the FactSalesQuota table, quotas are related only to employee and calendar period (year/quarter).

Also, the dataset query does not return a row for the first year of sales for each employee. From a business requirement perspective, there are multiple problems with this. First, the sales quota variance for this year is not included. Furthermore, totals by sales territory region are incomplete. Lastly, when looking at the Year Variance column, there is no displayed value to compare with the second employee sales year (thefirst year actually shown).

I have modified the report as follows:

Â· Tablix data set query:

o Change join of derived tables ThisYearSales and LastYearSales from INNER to LEFT JOIN to include first year of employee sales. (For the first year of sales for an employee from ThisYearSales, there is no row in LastYearSales based on the join criteria.)

o Add new derived table, SalesQuota, to generate quota amounts rather than generating them within the ThisYearSales derived table. OUTER JOIN with ThisYearSales allows for employee having a sales quota in a year with no actual sales or sales where a quota was not established. (Sales quota and amount values have a different granularity in the underlying data warehouse. Generating these in the same derived table causes incorrect aggregation.)

o Aggregate employee/year sales quota and sales amounts within each derived table, rather than after the join of these tables.

o Generate sales territory from DimEmployee (vs FastResellerSales) because there are some employee sales in multiple territories in same year. Also, the granularity of sales quota in FactSalesQuota includes employee and calendar/year but not territory. Based on this new join criterion, there are several employees assigned to a non-specified territory (NA).

Code:

/***
 
 KPI Scorecard - Revised data set query - rmb99 - 01/18/10
 
***/
 
SELECT 
 ThisYearSales.SalesTerritoryRegion
 ,ThisYearSales.SalesTerritoryKey
 ,ThisYearSales.CalendarYear AS CalendarYear
 ,ThisYearSales.EmployeeName
 ,ThisYearSales.SalesAmt AS ExtendedAmountSum
 ,SalesQuota.SalesAmountQuota AS SalesAmountQuotaSum
 ,LastYearSales.SalesAmt AS ExtendedAmountSumLastYear
 
FROM
 
/*** This Year Sales ***/
 
 (SELECT 
       DimSalesTerritory.SalesTerritoryKey
       , DimSalesTerritory.SalesTerritoryRegion
       , DimEmployee.EmployeeKey
       , DimEmployee.FirstName + ' ' + DimEmployee.LastName AS EmployeeName
       , DimDate.CalendarYear
       , SUM(FactResellerSales.ExtendedAmount) AS SalesAmt
 
 FROM 
       FactResellerSales 
       INNER JOIN DimEmployee 
             ON FactResellerSales.EmployeeKey = DimEmployee.EmployeeKey 
       INNER JOIN DimSalesTerritory 
             ON DimEmployee.SalesTerritoryKey = DimSalesTerritory.SalesTerritoryKey
       INNER JOIN DimDate 
             ON FactResellerSales.OrderDateKey = DimDate.DateKey
 
 GROUP BY 
       DimSalesTerritory.SalesTerritoryKey
       , DimSalesTerritory.SalesTerritoryRegion
       , DimEmployee.EmployeeKey
       , DimEmployee.FirstName + ' ' + DimEmployee.LastName
       , DimDate.CalendarYear
 
 ) AS ThisYearSales 
 
LEFT JOIN
 
/*** Last Year Sales ***/
 
 (SELECT  
       DimSalesTerritory_1.SalesTerritoryKey
       , DimEmployee_1.EmployeeKey
       , DimDate_1.CalendarYear
       , SUM(FactResellerSales_1.ExtendedAmount) AS SalesAmt
 
 FROM 
       FactResellerSales AS FactResellerSales_1 
       INNER JOIN DimEmployee AS DimEmployee_1 
             ON FactResellerSales_1.EmployeeKey = DimEmployee_1.EmployeeKey
       INNER JOIN DimSalesTerritory AS DimSalesTerritory_1 
             ON DimSalesTerritory_1.SalesTerritoryKey = DimEmployee_1.SalesTerritoryKey
       INNER JOIN DimDate AS DimDate_1 
             ON FactResellerSales_1.OrderDateKey = DimDate_1.DateKey
 
GROUP BY 
DimSalesTerritory_1.SalesTerritoryKey
       , DimEmployee_1.EmployeeKey
 , DimDate_1.CalendarYear
 
 ) AS LastYearSales
 
ON ThisYearSales.SalesTerritoryKey = LastYearSales.SalesTerritoryKey
 AND ThisYearSales.EmployeeKey = LastYearSales.EmployeeKey
 AND LastYearSales.CalendarYear = ThisYearSales.CalendarYear - 1 
 
FULL OUTER JOIN
 
/*** Sales Quota ***/
 
 (SELECT 
       DimEmployee_2.EmployeeKey
       , FactSalesQuota.CalendarYear
       , SUM(FactSalesQuota.SalesAmountQuota) AS SalesAmountQuota
 
 FROM 
       FactSalesQuota 
       INNER JOIN DimEmployee AS DimEmployee_2 
             ON FactSalesQuota.EmployeeKey = DimEmployee_2.EmployeeKey
 
GROUP BY 
       DimEmployee_2.EmployeeKey
       , FactSalesQuota.CalendarYear
 ) AS SalesQuota
 
ON ThisYearSales.EmployeeKey = SalesQuota.EmployeeKey
 AND ThisYearSales.CalendarYear = SalesQuota.CalendarYear
 
ORDER BY 
 ThisYearSales.SalesTerritoryRegion
 , ThisYearSales.CalendarYear
 , ThisYearSales.EmployeeName

Â· When each employee first sales year is included in the data set, the logic for the yearly sales variance indicator (to right of the Year Variance column) must display a blank when last year sales are zero. The expression has been modified as follows, and includes nested IIF logic:

Code:

=iif(isnothing(Fields!ExtendedAmountSumLastYear.Value)
,nothing
,iif(Fields!ExtendedAmountSum.Value /iif(isnothing(Fields!ExtendedAmountSumLastYear.Value),.000001,Fields!ExtendedAmountSumLastYear.Value) < .8
,"indicator_yellow_16"
,nothing))

Note that all values within the conditions tested by IIF statements are evaluated before a value is assigned to the entire expression. Therefore, it is necessary to conditionally replace a null value of Fields!ExtendedAmountSumLastYear.Value with a non-zero dummy value (0.000001) when it is the denominator to avoid dividing by zero and causing an invalid image to be displayed. (An alternative would be to create a division function using custom code that could be called by the indicator expression.)

Â· The sales quotas and amounts in the data mart do not cause the sales quota variance indicator to the right of the Total Sales column to be other than a yellow triangle. To better illustrate different conditions, I modified the custom code in report properties that is called by this indicator as follows:

Code:

Function SalesQuotaVarianceIndicator(ActualSales As Decimal, Quota As Decimal) As String
Select Case ActualSales/Quota
Case Is > 1.00
Return "green"
Case Is < .85
Return "red"
Case Else
Return "yellow"
End Select
End Function

The indicator is green if annual employee sales are greater than the quota (rather than 125%), red if less than 85% (vs. 75%) and otherwise yellow. Only two employees in the data mart exceeded their quotas in any year. Otherwise, employees and region totals have either a red or yellow indicator. In an actual report, this logic would be determined by the business rules but this change better illustrates the utility of the indicator.