|
Monday, 27 April 2009 |
Steps for creating a KPI report in SQL Server BI tool.
To create a KPI report (see FIGURE below) with sample AdventureWork's
data came with SQL Server, follow the following steps:
1. Create a time dimension enabling users to select specific quarter
and year, i.e. 2003 Q1. This is necessary as the data come with the
datawarehouse does not distinguish the same quarter of different years
(a query for all quarters will show only four values: Q1, Q2, Q3, and
Q4.) The newly created dimension will bind year and quarter to show
2003 Q1, 2003 Q2, etc.
2. Create a new column in the 'data source view' (called 'Named
Calculation') to concatenate year and quarter. This is what users will
see for the composite fields (year and quarter.)
3. Create a cube that reflects facts/measures with this newly created
composite time dimension.
4. Create new calculations (called Calculated Members on the cube)
using MDX language to express desired calculations: variance of
actual numbers versus goals.
5. Create a KPI with status and trend definitions using the calculaled
members in step 4.
For the "Trend" field in the screen shot, the expression is as follows:
Case
When [MEASURES].[Reseller Sales Variance Percentage]
> ( [Dim
Time].[Calendar
Hierarchy].PrevMember, [MEASURES].[Reseller Sales Variance
Percentage]) Then 1
When [MEASURES].[Reseller Sales Variance Percentage]
< ([Dim Time].[Calendar Hierarchy].PrevMember,
[MEASURES].[Reseller Sales Variance Percentage]) Then
-1
Else 0
END
6. Create the shown report with user’s selection box that
uses the Unified Dimension Model (the cube and derived calculations.)
For the color field above in the report, the expression is as follows:
=Switch (Fields!Sales_Target_Trend.Value = -1,
"Red",Fields!Sales_Target_Trend.Value = 0, "Yellow",
Fields!Sales_Target_Trend.Value = 1, "Green")
|
