 | |
Scorecard accelerator. How the score value is calculated?
microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Scorecard accelerator. How the score value is calculated? -
06-21-2004
, 03:11 PM
#2
| |||
| |||
|
| Normalized Values, Actual Values, and Cube Measurements |
There are three options for expressing cube data values in KPIs:
Normalized Values
Normalized values, the default method for data display, are expressed as
a percentage of the target value. Business Scorecards uses the following
formulas for converting cube data into a normalized value:
(Current - Actual Worst) / (Target - Actual Worst) for KPI Trend Type
Increasing is Better
1 - (Current - Actual Worst) / (Target - Actual Worst) for KPI Trend
Type Decreasing is Better
Current is the current value in the cube, Target is your target value
for the KPI, and Actual Worst is the value entered in the Edit KPI
Banding dialog to represent the worst performance possible in that
indicator. When the KPI Trend Type is Increasing is Better, such as
"Number of Customers," Actual Worst could be 0. When KPI Trend Type is
Decreasing is Better, such as "Average Customer Wait Time in Minutes,"
Actual Worst could be 60 or 100. In either case, the formula creates a
normalized value based on the cube value's distance from both the target
value and Actual Worst value.
The default three-band indicator has five boundaries, defined by the
following default normalized values:
Worst Case = 0
boundary 1 = 0.5
boundary 2 = 0.75
Best Case = 1
Note that Worst Case is always a normalized value but Actual Worst is
based on actual cube measurements.
Actual Values
You can also use actual values to express cube data and define indicator
boundaries in real numbers. If an organization has a KPI called "Net
Sales" and uses actual values, their default three-level indicator could
be defined as follows (assuming a Best Case of 30,000 sales):
Worst Case = 0
boundary 1 = 15,000
boundary 2 = 22,500
Best Case = 30,000
Boundaries have default settings that you can change. Boundary 1 does
not have to be 50% of the Best Case, for example. For the purposes of
this discussion, we'll continue using default boundary values and the
default three-band KPI indicator.
Cube Measurements (MDX formula)
The third option to express a data value is to use an MDX formula or
some other calculation performed within the OLAP cube. The actual data
in the cube is normalized through pre-determined formulas, and Business
Scorecards reads the normalized value and uses it to give the KPI a
value. This type of measurement often applies when KPIs need to measure
indicators in which a smaller number is preferable to a larger number,
such as product defects. In these cases, you can use a customized cube
measurement. For example, a cube measurement of product defects could
create an arbitrary scale of 0 to 10, and assign higher numbers to low
defect reporting and low numbers when more defects are found.
Regardless of the method you use to express cube data in the KPI
(normalized, actual, or cube measurement), Business Scorecards uses the
data to determine which band range includes the data value.
Actual Scales
Using the example "Net Sales" KPI from above, a cube measurement of
20,000 sales would be expressed as 20,000 using actual values, or .6667
using normalized values. In either case, using the default boundary
values from above, the data value of 20,0000 falls in Band 2 of the KPI
indicator, between boundary 1 (15,000) and boundary 2 (22,500). In the
scorecard's Status column, this KPI displays a yellow light.
The value of 0.6667 would fall into an actual scale here:
To convert this value into a score that can be used at the objective
level, Business Scorecards first determines a band percentage for the
data value 0.6667. In order to find the band percentage, Business
Scorecards uses a real actual value, which is the value that is used in
the formulas described below. The real actual value depends on which
method you use to represent your data. Using actual values, the real
actual value in our example is 20,000. Using normalized values, the real
actual value is 0.6667. Using cube measurements, the real actual value
is whatever the MDX formula or cube calculation produces as a value. The
formulas for determining a KPI's score are the same no matter which
method you use to report cube data. The only difference is the type of
number that you enter into the formulas (actual vs. normalized vs. cube
measurement).
Using the actual scale picture above, Business Scorecards finds the band
percentage in the example KPI by determining the following:
1. Total band distance. Total distance of the band in which the real
actual value falls. We already know the data value falls within the
middle band (Band 2) of our three-level indicator. To calculate total
band distance, subtract the value of the boundary beneath 0.6667 (0.5)
from the value of the boundary immediately above 0.6667 (0.75).
0.75 - 0.5 = 0.25. The total band distance is 0.25.
2. In-band distance.
How far into the band the real actual data falls. Subtract the value of
the boundary below the real actual value (0.5) from the real actual
value (0.6667).
0.6667 - 0.5 = 0.1667. The in-band distance is 0.1667.
3. Band percentage.
The distance of the data value from the lower boundary of the band,
expressed as a percentage of the entire distance of the band. Divide the
In-band distance (0.1667) by the total band distance (0.25).
0.1667 / 0.25 = .6668. The band percentage of the real
actual value is .6668.
Evenly Distributed Scales and Evenly Distributed Values
When using KPI values to calculate the score for an objective, Business
Scorecards translates where a real actual value falls on an actual scale
into where it falls on an evenly distributed scale. This is the KPI's
score. In these calculations, evenly distributed scales have the same
number of bands as actual scales, but the boundaries are equidistant
from each other, and the scale always runs from 0 to 100. Below is the
evenly distributed scale that corresponds to the actual scale in our
example.
Business Scorecards uses formulas similar to those above to determine
the evenly distributed score value, which is the value that is used at
the objective level:
1. Total band distance. Total distance of the evenly distributed band in
which the real actual value falls. We know the real actual value of
.6667 falls into the middle band of the actual scale, so that is the
evenly distributed band used in this formula. Subtract the value of the
lower boundary (33) from the value of the upper boundary (66).
66 - 33 = 33. The total band distance of the evenly
distributed band is 33.
2. In-band distance.
How far into the evenly distributed band the real actual data falls.
Multiply the total band distance of the evenly distributed band (33) by
the band percentage (.6668).
33 * 0.6668 = 22.0044. The in-band distance is 22.0044.
3. Evenly distributed score value.
The real actual value translated to an evenly distributed scale. The
evenly distributed in-band distance (22.0044) + the value of the lower
boundary of the evenly distributed band (33).
22.0044 + 33 = 55.0044. 55.0044 is the evenly distributed
score value. This is the value displayed in the Score column of
scorecards.
When this KPI is rolled up to the objective level, the score of 55.0044
is the value that will be used to calculate the objective score.
...
Quote:
- Deepak
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
#3
| |||
| |||
|
|
This is documented in detail in the Help, under "Rollups". I can only guess that your "Worst" Value for KPI is 58%? Normalized Values, Actual Values, and Cube Measurements There are three options for expressing cube data values in KPIs: Normalized Values Normalized values, the default method for data display, are expressed as a percentage of the target value. Business Scorecards uses the following formulas for converting cube data into a normalized value: (Current - Actual Worst) / (Target - Actual Worst) for KPI Trend Type Increasing is Better 1 - (Current - Actual Worst) / (Target - Actual Worst) for KPI Trend Type Decreasing is Better Current is the current value in the cube, Target is your target value for the KPI, and Actual Worst is the value entered in the Edit KPI Banding dialog to represent the worst performance possible in that indicator. When the KPI Trend Type is Increasing is Better, such as "Number of Customers," Actual Worst could be 0. When KPI Trend Type is Decreasing is Better, such as "Average Customer Wait Time in Minutes," Actual Worst could be 60 or 100. In either case, the formula creates a normalized value based on the cube value's distance from both the target value and Actual Worst value. The default three-band indicator has five boundaries, defined by the following default normalized values: Worst Case = 0 boundary 1 = 0.5 boundary 2 = 0.75 Best Case = 1 Note that Worst Case is always a normalized value but Actual Worst is based on actual cube measurements. Actual Values You can also use actual values to express cube data and define indicator boundaries in real numbers. If an organization has a KPI called "Net Sales" and uses actual values, their default three-level indicator could be defined as follows (assuming a Best Case of 30,000 sales): Worst Case = 0 boundary 1 = 15,000 boundary 2 = 22,500 Best Case = 30,000 Boundaries have default settings that you can change. Boundary 1 does not have to be 50% of the Best Case, for example. For the purposes of this discussion, we'll continue using default boundary values and the default three-band KPI indicator. Cube Measurements (MDX formula) The third option to express a data value is to use an MDX formula or some other calculation performed within the OLAP cube. The actual data in the cube is normalized through pre-determined formulas, and Business Scorecards reads the normalized value and uses it to give the KPI a value. This type of measurement often applies when KPIs need to measure indicators in which a smaller number is preferable to a larger number, such as product defects. In these cases, you can use a customized cube measurement. For example, a cube measurement of product defects could create an arbitrary scale of 0 to 10, and assign higher numbers to low defect reporting and low numbers when more defects are found. Regardless of the method you use to express cube data in the KPI (normalized, actual, or cube measurement), Business Scorecards uses the data to determine which band range includes the data value. Actual Scales Using the example "Net Sales" KPI from above, a cube measurement of 20,000 sales would be expressed as 20,000 using actual values, or .6667 using normalized values. In either case, using the default boundary values from above, the data value of 20,0000 falls in Band 2 of the KPI indicator, between boundary 1 (15,000) and boundary 2 (22,500). In the scorecard's Status column, this KPI displays a yellow light. The value of 0.6667 would fall into an actual scale here: To convert this value into a score that can be used at the objective level, Business Scorecards first determines a band percentage for the data value 0.6667. In order to find the band percentage, Business Scorecards uses a real actual value, which is the value that is used in the formulas described below. The real actual value depends on which method you use to represent your data. Using actual values, the real actual value in our example is 20,000. Using normalized values, the real actual value is 0.6667. Using cube measurements, the real actual value is whatever the MDX formula or cube calculation produces as a value. The formulas for determining a KPI's score are the same no matter which method you use to report cube data. The only difference is the type of number that you enter into the formulas (actual vs. normalized vs. cube measurement). Using the actual scale picture above, Business Scorecards finds the band percentage in the example KPI by determining the following: 1. Total band distance. Total distance of the band in which the real actual value falls. We already know the data value falls within the middle band (Band 2) of our three-level indicator. To calculate total band distance, subtract the value of the boundary beneath 0.6667 (0.5) from the value of the boundary immediately above 0.6667 (0.75). 0.75 - 0.5 = 0.25. The total band distance is 0.25. 2. In-band distance. How far into the band the real actual data falls. Subtract the value of the boundary below the real actual value (0.5) from the real actual value (0.6667). 0.6667 - 0.5 = 0.1667. The in-band distance is 0.1667. 3. Band percentage. The distance of the data value from the lower boundary of the band, expressed as a percentage of the entire distance of the band. Divide the In-band distance (0.1667) by the total band distance (0.25). 0.1667 / 0.25 = .6668. The band percentage of the real actual value is .6668. Evenly Distributed Scales and Evenly Distributed Values When using KPI values to calculate the score for an objective, Business Scorecards translates where a real actual value falls on an actual scale into where it falls on an evenly distributed scale. This is the KPI's score. In these calculations, evenly distributed scales have the same number of bands as actual scales, but the boundaries are equidistant from each other, and the scale always runs from 0 to 100. Below is the evenly distributed scale that corresponds to the actual scale in our example. Business Scorecards uses formulas similar to those above to determine the evenly distributed score value, which is the value that is used at the objective level: 1. Total band distance. Total distance of the evenly distributed band in which the real actual value falls. We know the real actual value of 6667 falls into the middle band of the actual scale, so that is the evenly distributed band used in this formula. Subtract the value of the lower boundary (33) from the value of the upper boundary (66). 66 - 33 = 33. The total band distance of the evenly distributed band is 33. 2. In-band distance. How far into the evenly distributed band the real actual data falls. Multiply the total band distance of the evenly distributed band (33) by the band percentage (.6668). 33 * 0.6668 = 22.0044. The in-band distance is 22.0044. 3. Evenly distributed score value. The real actual value translated to an evenly distributed scale. The evenly distributed in-band distance (22.0044) + the value of the lower boundary of the evenly distributed band (33). 22.0044 + 33 = 55.0044. 55.0044 is the evenly distributed score value. This is the value displayed in the Score column of scorecards. When this KPI is rolled up to the objective level, the score of 55.0044 is the value that will be used to calculate the objective score. .. - Deepak *** Sent via Devdex http://www.devdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
| |||
| |||
|
|
Well, there is just a lack in the mapping page: renaming normalized value to score value! Because after, everywhere we see "score" , not "normalized value". Well.. I'll do some tests. Because I think there is some calculation problems. "Deepak Puri" <deepak_puri (AT) progressive (DOT) com> a écrit dans le message de news:e0t6uQ$VEHA.1380 (AT) TK2MSFTNGP12 (DOT) phx.gbl... This is documented in detail in the Help, under "Rollups". I can only guess that your "Worst" Value for KPI is 58%? Normalized Values, Actual Values, and Cube Measurements There are three options for expressing cube data values in KPIs: Normalized Values Normalized values, the default method for data display, are expressed as a percentage of the target value. Business Scorecards uses the following formulas for converting cube data into a normalized value: (Current - Actual Worst) / (Target - Actual Worst) for KPI Trend Type Increasing is Better 1 - (Current - Actual Worst) / (Target - Actual Worst) for KPI Trend Type Decreasing is Better Current is the current value in the cube, Target is your target value for the KPI, and Actual Worst is the value entered in the Edit KPI Banding dialog to represent the worst performance possible in that indicator. When the KPI Trend Type is Increasing is Better, such as "Number of Customers," Actual Worst could be 0. When KPI Trend Type is Decreasing is Better, such as "Average Customer Wait Time in Minutes," Actual Worst could be 60 or 100. In either case, the formula creates a normalized value based on the cube value's distance from both the target value and Actual Worst value. The default three-band indicator has five boundaries, defined by the following default normalized values: Worst Case = 0 boundary 1 = 0.5 boundary 2 = 0.75 Best Case = 1 Note that Worst Case is always a normalized value but Actual Worst is based on actual cube measurements. Actual Values You can also use actual values to express cube data and define indicator boundaries in real numbers. If an organization has a KPI called "Net Sales" and uses actual values, their default three-level indicator could be defined as follows (assuming a Best Case of 30,000 sales): Worst Case = 0 boundary 1 = 15,000 boundary 2 = 22,500 Best Case = 30,000 Boundaries have default settings that you can change. Boundary 1 does not have to be 50% of the Best Case, for example. For the purposes of this discussion, we'll continue using default boundary values and the default three-band KPI indicator. Cube Measurements (MDX formula) The third option to express a data value is to use an MDX formula or some other calculation performed within the OLAP cube. The actual data in the cube is normalized through pre-determined formulas, and Business Scorecards reads the normalized value and uses it to give the KPI a value. This type of measurement often applies when KPIs need to measure indicators in which a smaller number is preferable to a larger number, such as product defects. In these cases, you can use a customized cube measurement. For example, a cube measurement of product defects could create an arbitrary scale of 0 to 10, and assign higher numbers to low defect reporting and low numbers when more defects are found. Regardless of the method you use to express cube data in the KPI (normalized, actual, or cube measurement), Business Scorecards uses the data to determine which band range includes the data value. Actual Scales Using the example "Net Sales" KPI from above, a cube measurement of 20,000 sales would be expressed as 20,000 using actual values, or .6667 using normalized values. In either case, using the default boundary values from above, the data value of 20,0000 falls in Band 2 of the KPI indicator, between boundary 1 (15,000) and boundary 2 (22,500). In the scorecard's Status column, this KPI displays a yellow light. The value of 0.6667 would fall into an actual scale here: To convert this value into a score that can be used at the objective level, Business Scorecards first determines a band percentage for the data value 0.6667. In order to find the band percentage, Business Scorecards uses a real actual value, which is the value that is used in the formulas described below. The real actual value depends on which method you use to represent your data. Using actual values, the real actual value in our example is 20,000. Using normalized values, the real actual value is 0.6667. Using cube measurements, the real actual value is whatever the MDX formula or cube calculation produces as a value. The formulas for determining a KPI's score are the same no matter which method you use to report cube data. The only difference is the type of number that you enter into the formulas (actual vs. normalized vs. cube measurement). Using the actual scale picture above, Business Scorecards finds the band percentage in the example KPI by determining the following: 1. Total band distance. Total distance of the band in which the real actual value falls. We already know the data value falls within the middle band (Band 2) of our three-level indicator. To calculate total band distance, subtract the value of the boundary beneath 0.6667 (0.5) from the value of the boundary immediately above 0.6667 (0.75). 0.75 - 0.5 = 0.25. The total band distance is 0.25. 2. In-band distance. How far into the band the real actual data falls. Subtract the value of the boundary below the real actual value (0.5) from the real actual value (0.6667). 0.6667 - 0.5 = 0.1667. The in-band distance is 0.1667. 3. Band percentage. The distance of the data value from the lower boundary of the band, expressed as a percentage of the entire distance of the band. Divide the In-band distance (0.1667) by the total band distance (0.25). 0.1667 / 0.25 = .6668. The band percentage of the real actual value is .6668. Evenly Distributed Scales and Evenly Distributed Values When using KPI values to calculate the score for an objective, Business Scorecards translates where a real actual value falls on an actual scale into where it falls on an evenly distributed scale. This is the KPI's score. In these calculations, evenly distributed scales have the same number of bands as actual scales, but the boundaries are equidistant from each other, and the scale always runs from 0 to 100. Below is the evenly distributed scale that corresponds to the actual scale in our example. Business Scorecards uses formulas similar to those above to determine the evenly distributed score value, which is the value that is used at the objective level: 1. Total band distance. Total distance of the evenly distributed band in which the real actual value falls. We know the real actual value of 6667 falls into the middle band of the actual scale, so that is the evenly distributed band used in this formula. Subtract the value of the lower boundary (33) from the value of the upper boundary (66). 66 - 33 = 33. The total band distance of the evenly distributed band is 33. 2. In-band distance. How far into the evenly distributed band the real actual data falls. Multiply the total band distance of the evenly distributed band (33) by the band percentage (.6668). 33 * 0.6668 = 22.0044. The in-band distance is 22.0044. 3. Evenly distributed score value. The real actual value translated to an evenly distributed scale. The evenly distributed in-band distance (22.0044) + the value of the lower boundary of the evenly distributed band (33). 22.0044 + 33 = 55.0044. 55.0044 is the evenly distributed score value. This is the value displayed in the Score column of scorecards. When this KPI is rolled up to the objective level, the score of 55.0044 is the value that will be used to calculate the objective score. .. - Deepak *** Sent via Devdex http://www.devdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
| |||
| |||
|
#6
| |||
| |||
|
| Evenly Distributed Scales and Evenly Distributed Values |
When using KPI values to calculate the score for an objective, Business
Scorecards translates where a real actual value falls on an actual scale
into where it falls on an evenly distributed scale. This is the KPI's
score. In these calculations, evenly distributed scales have the same
number of bands as actual scales, but the boundaries are equidistant
from each other, and the scale always runs from 0 to 100. Below is the
evenly distributed scale that corresponds to the actual scale in our
example.
...
{2 Figures showing Actual and Evenly Distributed Scales}
Quote:
(42 - 0)/(85 - 0) ~= 0.5 or 50%
But this value is then translated to an "evenly distributed scale", as
shown in the 2 figures. The default 3 bands are:
0 to 50%, 50 to 75% and 75 to 100%, so the lower boundary is 50%. But
then the boundaries are made equidistant from each other, so lower
boundary translates to a score of 33.
- Deepak
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
#7
| |||
| |||
|
|
After trying the sample data that you cited, the score seems to be correct, seeing the following help section: Evenly Distributed Scales and Evenly Distributed Values When using KPI values to calculate the score for an objective, Business Scorecards translates where a real actual value falls on an actual scale into where it falls on an evenly distributed scale. This is the KPI's score. In these calculations, evenly distributed scales have the same number of bands as actual scales, but the boundaries are equidistant from each other, and the scale always runs from 0 to 100. Below is the evenly distributed scale that corresponds to the actual scale in our example. .. {2 Figures showing Actual and Evenly Distributed Scales} Your formula for the normalized value appears correct: (42 - 0)/(85 - 0) ~= 0.5 or 50% But this value is then translated to an "evenly distributed scale", as shown in the 2 figures. The default 3 bands are: 0 to 50%, 50 to 75% and 75 to 100%, so the lower boundary is 50%. But then the boundaries are made equidistant from each other, so lower boundary translates to a score of 33. - Deepak *** Sent via Devdex http://www.devdex.com *** Don't just participate in USENET...get rewarded for it! |
#8
| |||
| |||
|
| 1. Total band distance. Total distance of the evenly distributed band in |
..6667 falls into the middle band of the actual scale, so that is the
evenly distributed band used in this formula. Subtract the value of the
lower boundary (33) from the value of the upper boundary (66).
66 - 33 = 33. The total band distance of the evenly
distributed band is 33.
2. In-band distance.
How far into the evenly distributed band the real actual data falls.
Multiply the total band distance of the evenly distributed band (33) by
the band percentage (.6668).
33 * 0.6668 = 22.0044. The in-band distance is 22.0044.
3. Evenly distributed score value.
The real actual value translated to an evenly distributed scale. The
evenly distributed in-band distance (22.0044) + the value of the lower
boundary of the evenly distributed band (33).
22.0044 + 33 = 55.0044. 55.0044 is the evenly distributed
score value. This is the value displayed in the Score column of
scorecards.
...
Quote:
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
#9
| |||
| |||
|
|
Hi, I want to know what is the rule behind the score calculated value in the scorecard accelerator application. I've a satisfaction value (which is a %) of 80%; my target is 85% Score = 75%???? any idea? thanks, Jerome. |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.