Database Reference
In-Depth Information
At this point, you have created a new derived measure for your cube that you can use in your reports.
Although the calculated member did not work in this particular case, it will often work just fine and is a
useful tool in many cubes.
in this exercise, you created a testing copy of our cube. You then created both derived and calculated
measures and verified which one of these new measures was correct. Finally, we move the derived member
to the cube we are currently working on, DWPubsSalesVer1. Now we need to continue configuring our cube
by moving to the next tab, which allows us to include KPis.
KPIs
A key performance indicator (KPI) is a way of grouping measures together into five basic categories. The five
basic categories start at -1 and proceed to a +1 using an increment of ( -1, -0.50, 0, +0.50, and +1). The numbering
system may seem odd to some, but it has to do with the science of statistics. Because of this, SSAS uses only these
five categories, and they cannot be redefined.
The idea behind a KPI is for you to reduce the number of individual values in a tabular report to the essence
of those values. This is convenient when you have a large report and what you really want to see is whether
something has achieved a predefined target value, exceeded it, or did not make it to that value.
KPIs can be created in programming code such as SQL, C#, or MDX. In Listing 11-4 , you can see an example of
an MDX statement that defines a range of values and categorizes each value within that range as either -1, 0, or 1. To
keep things simple, we excluded the .05 and -05 categories and will work with just these three categories for now.
Listing 11-4.
MDX Statement That Groups Values into Three KPI Categories
WITH MEMBER [MyKPI]
AS
case
when [SalesQuantity]<25 or null then -1
when [SalesQuantity]>= 25 and [SalesQuantity]<= 50 then 0
when [SalesQuantity]>50 then 1
end
SELECT
{ [Measures].[SalesQuantity], [Measures].[MyKPI] } on 0,
{ [DimTitles].[Title].members } on 1
From[CubeDWPubsSales]
The categorization comes from the MDX Case expression where the values of sales quantity are divided into
the three categories of less than 25, 25 to 50, or more than 50. We chose this range at random for our example,
but the range you choose for your BI solution should be based upon a range that has some significance to the
business you are building it for.
In the cube designer window, you can include an MDX expression using the KPI tab, as shown in Figure
11-46
. On the KPI tab, click the new KPI button (circled in Figure
11-46
), and a new KPI will appear under the
KPI organizer pane. Rename the KPI to something descriptive that has no spaces by typing the new name in the
Name textbox. In our example, we have named it SalesQty25To50KPI.
