Transcript BDBI Radio - Professional Association for SQL Server

BDBI Webcast Radio
The Baker’s Dozen
Business Intelligence
Webcast Radio
13 SQL Server -Business Intelligence
Productivity Tips
Kevin S. Goff
Microsoft SQL Server MVP
February 20, 2014
13 Tips for building Key Performance Indicators
BDBI Radio
• Produced and Hosted by Kevin S. Goff, Microsoft SQL
Server MVP and author of CoDe Magazine
“Baker’s Dozen” Productivity Series from 2004-2013
– http://www.codemag.com/People/Bio/Kevin.Goff
• Pre-recorded webcast – Available twice a month
• Site: http://www.BDBIRadio.com
• My overall blog/site: http://www.KevinSGoff.net
• Program format:
– 13 quick tips on different SQL/BI/SharePoint topics
– In any week, tips might cover one topic (13 features in PowerPivot,
13 SQL Interview topics), or 13 random tips on anything from data
warehousing to MDX/DAX programming, to maybe even a few
.NET tips for scenarios when the .NET and BI worlds collide
– (Email me at [email protected] if you have questions)
01/15/2014
13 tips for KPIs
2
BDBI Radio
• Today’s topic: 13 tips for building Key Performance Indicators
with the Microsoft SQL/BI Stack
• KPIs are critical analytic piece for many data warehouses and
analytic databases
• Some initial background on the concept of KPIs, what they are,
what they aren’t
• Examples of creating/consuming KPIs with the Microsoft BI Tools
01/15/2014
13 tips for KPIs
3
Topics for today
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
What are KPIs? (and what isn’t a KPI)
Balanced KPI Scorecards
Components of a KPI
Simple example of an SSRS report with KPI-like functionality
Creating a KPI using SQL Server Analysis Services (OLAP)
Creating a KPI using SQL Server Analysis Services (Tabular)
Showing an SSAS KPI in Excel
Showing an SSAS KPI in SSRS
Showing an SSAS KPI in PerformancePoint Services
Building a PerformancePoint Services scorecard with KPI hotlinks
Creating KPIs manually in PerformancePoint Services
KPI recommendations (DOs and DON’Ts)
Additional Reading/References
01/15/2014
13 tips for KPIs
4
1-What are KPIs?
Example (could have been hand-drawn)
Employee Sales with respect to a sales quota
01/15/2014
13 tips for KPIs
5
1-What are KPIs?
1. Key Performance Indicator – a measurement of an activity with
respect to a goal/baseline/standard
2. Intent is to evaluate performance associated with the activity, across
different business entities (business dimensions)
3. KPIs often have a visual component to render the measurement
graphically
4. Gross Profit Dollars (in itself) is not a KPI
5. Gross Profit Dollars as a % of a Sales Goal might be a KPI
6. KPIs need to be evaluated over time and then maybe re-assessed
7. The goals/thresholds of a KPI can be data-driven (i.e. goals for 2006
for one product category might differ in 2007)
01/15/2014
13 tips for KPIs
6
2-Balanced Scorecoard
•
•
Balanced Scorecard (Kaplan and Norton)represents “holistic” approach
to KPIs
Four perspectives:
–
Financial
•
•
•
–
Customer Perspective
•
–
•
Customer Evaluations/surveys (90% compliance on satisfaction)
Internal Business Process
•
•
–
Sales with respect to goals
Sales Trend over Prior Period
Return % against a threshold
In Manufacturing, Damages/Shortages
In software shops, Bug Rate, Churn Rate, etc.
Learning and Growth
• % of hours spent in training programs
Progressively difficult to validate data
01/15/2014
13 tips for KPIs
7
3-Components of a KPI
Component
Description
Overall Objective
Evaluate product return dollars as a % of revenue, against a threshold %
that varies by year and product category
The Metric/measure
itself
Return Dollars / Revenue Dollars, as a %
The goal/threshold
A table that stores threshold rates by year/category
Scoring method
-
Good score = Return % is below the threshold %
“OK” or “warning” score = Return % is slightly above threshold %
(maybe by half a % point)
“Bad score means returns % is above threshold, and by more than half
a % point
Graphical display
(there can be many)
Traffic lights (green=good, yellow=warning, red=bad)
Trend rule (optional)
The change in returns % over the same time period a year ago (i.e. rate was
5.5% this quarter, 4.4% one year ago, so % is up, which is not good)
Good trend - returns % is lower than LY
No trend – returns % is within two/tenths of a point of LY
Bad trend – returns % exceeds LY, and by more than two/tenths of a point
01/15/2014
13 tips for KPIs
8
4 – SSRS report with KPI function
Additional
rules
KPI rules annotated
01/15/2014
13 tips for KPIs
9
4 – SSRS report with KPI function
Additional
rules
KPI rules annotated
Link to video to recreate
this report at end of
slide deck
Rules for the KPI are “embedded” in the report.
Sort of like embedding logic in the UI/presentation layer, as opposed to the
business layer
Let’s store a KPI in a central location
01/15/2014
13 tips for KPIs
10
5-Creating a KPI using SSAS OLAP
First, create necessary supporting calculations
- Employee Sales %
- Employee Sales % LY (using
ParallelPeriod)
- Point Change between the two
01/15/2014
13 tips for KPIs
11
5-Creating a KPI using SSAS OLAP
KPI Name
Measure to be
evaluated
Goal (could be
data-driven)
Default
rendering
MDX to
evaluate:
return 1, 0, -1
Optional trend
and logic (1, 0,
-1)
01/15/2014
13 tips for KPIs
12
6-Creating a KPI using SSAS Tabular
Can create KPIs using SSAS Tabular (example)
01/15/2014
13 tips for KPIs
13
7-Using Excel to test KPIs
01/15/2014
13 tips for KPIs
14
8-Showing report in SSRS
• SSRS report
using the
OLAP KPI
• SSRS does not
show KPIs
graphically
• We need to
render images
manually
01/15/2014
13 tips for KPIs
15
8-Showing report in SSRS
• KPI Goal and Trend in OLAP Query designer
• Values are returned as 1, 0,-1 – does not render images graphically
01/15/2014
13 tips for KPIs
16
8-Showing report in SSRS
1) Add images to project
2) Add image control into detail row
Same
approach for
KPI Trend
3) Set Image source to External
and then supply expression to use
specific JPG based on KPI internal
numeric value
01/15/2014
13 tips for KPIs
17
9 - PPS: First KPI Scorecard
Filter Link Connection
Dashboard zone
web parts
We can also
filter on empty
rows
•
•
•
There’s quite a bit going on “under the hood”
PPS provides “filter link connections” to drive the data content based on user filter
selections
PPS also provides a dashboard zone interface on top of SharePoint web parts, to simply
web page authoring
18
10 – Creating KPI Manually
Manual KPI created in PPS, Actual
sales as a % of Target Goal
•
•
•
•
Page 5: Manual KPI
We can create dashboard scorecards with KPIs from SSAS, or we can create KPIs manually in PPS
Also note that scorecard shows the people who report to Amy (children in the Employee Hierarchy) in
the columns axis, and dates underneath CY 2007 in the rows axis
The Manual KPI will define a ratio between the reseller sales (the “actual value”) and the reseller sales
quota (the “Target value”)
19
11 - PPS KPI Hotlink scorecard
Dashboard zone web parts
•
•
•
•
•
•
Page 7: KPI scorecard hotlink – user can click on a KPI and get a supporting analytic chart on the right, based on KPI and date selected
Filter on Date Hierarchy
Screenshot shows Top 10 cities by Reseller sales for Q4 2007 (user clicked the Channel Revenue KPI and the date of Q4 FY 2007.
Screenshot shows Top 10 cities by Internet sales for Q4 2007 (user clicked the Internet Revenue KPI and the date of Q4 FY 2007.
Notice how the 2 charts have a background color that matches the KPI
We will create the charts first, and then the KPI scorecard, and then “link them together”)
20
12-KPI recommendations
• Try not to hardwire goals and thresholds
• Store in tables instead (they can change over time)
• KPIs are evaluated over time – logic can change
01/15/2014
13 tips for KPIs
21
13-Recommended reading
•
•
http://www.amazon.com/The-Balanced-Scorecard-TranslatingStrategy/dp/0875846513/ref=sr_1_1?ie=UTF8&qid=1394227561&sr=81&keywords=kaplan+norton+balanced+scorecard
http://www.amazon.com/Alignment-Balanced-Scorecard-CorporateSynergies/dp/1591396905/ref=sr_1_3?ie=UTF8&qid=1394227596&sr=83&keywords=kaplan+norton+balanced+scorecard
01/15/2014
13 tips for KPIs
22