Database Reference
In-Depth Information
sales amount is 20% higher than expected, this is great news and should be
highlighted. Similarly, if the sales amount is 20% lower than expected, then
we have to deal immediately with the situation.
The MDX query that computes the goal of the KPI is given next:
WITH MEMBER Measures.SalesPerformanceGoal AS
CASE
WHEN ISEMPTY(PARALLELPERIOD(
[Order Date].Calendar.Month, 12,
[Order Date].Calendar.CurrentMember))
THEN Measures.[Sales Amount]
ELSE 1.15 *
( Measures.[Sales Amount],
PARALLELPERIOD (
[Order Date].Calendar.Month, 12,
[Order Date].Calendar.CurrentMember))
END,
FORMAT STRING =
'
$###,##0.00
'
SELECT
ON COLUMNS,
[Order Date].Calendar.Month.MEMBERS ON ROWS
FROM Sales
{
[Sales Amount], SalesPerformanceGoal
}
In the above query, the
CASE
statement sets the goal to the sales of the
current month if the corresponding month of the previous year is not included
in the time frame of the cube. This query gives the following result:
Sales Amount SalesPerformanceGoal
···
···
···
June 1997
$33,843.80
$33,843.80
July 1997
$51,020.86
$32,041.18
August 1997
$45,841.67
$29,308.07
September 1997
$50,105.74
$25,270.56
October 1997
$62,651.25
$40,801.50
November 1997
$42,536.81
$52,440.05
···
···
···
As can be seen above, since the sales in the Northwind data warehouse started
in July 1996, the goal until June 1997 is set to the current sales.
We can use SQL Server Data Tools for defining the above KPI, which we
name
Sales Performance
. For this, we need to provide MDX expressions for
each of the above properties as follows:
Value
: The measure used for defining the KPI is
[Measures].[Sales Amount]
.
Goal
: The goal to increase 15% over last year sales amount is given by the
CASE
expression in the query above.
Status
: We need to choose a graphical indicator for displaying the status
of the KPI. The available indicators are shown in Fig.
9.13
. We select the
trac light indicator. Then, the MDX expression defined for the status
Search WWH ::
Custom Search