Transcript Delivering KPIs With Analysis Services

Delivering KPIs With Analysis Services
Peter Myers
Mentor
SolidQ
Presenter Introduction
•
•
•
•
Peter Myers
SolidQ – Mentor and Trainer
BBus, MCP, MCITP, MCT, MVP
14 years of experience designing, developing and
supporting software solutions based on Microsoft
data and development platforms
• Based in San Francisco
• [email protected]
Session Outline
•
•
•
•
•
Defining KPIs
KPI Data Requirements
Introducing Analysis Services 2008 R2
Defining Analysis Services KPIs
Delivering Analysis Services KPIs
– Excel 2010
– Reporting Services 2008 R2
– PerformancePoint Services 2010
Defining KPIs
•
•
•
•
•
KPI = Key Performance Indicator
Quantifiable measurements comparing actual values to
target values, and are used to monitor performance
Aligned with corporate strategy and objectives
Designed to drive desired behavior
Presents a measure of overall organizational health
when combined into a collection for a business
scorecard
Defining KPIs
Example
KPI Data Requirements
•
•
•
•
•
A KPI at minimum requires an actual value and a
target value
Ideally corporate data systems will deliver both values
Actuals are typically retrieved from operational
databases
Targets can be retrieved from formal planning systems
The absence of planning systems may involve:
–
–
Maintaining target values in supplementary data stores
Defining KPIs with fixed target values
Introducing Analysis Services 2008 R2
• OLAP component
– Delivers high performance query results
– Aggregates and organizes data from business data
sources
– Performs calculations difficult to perform using
relational queries
– Supports advanced Business Intelligence, including
KPIs
• Data mining component
– Discovers patterns in both relational and OLAP data
– Enhances the OLAP component with discovered results
Demonstrations
• Preparing the cube to store target values
• Seeding target values based on historic actual
values using:
– Simple factor
– Data Mining (Time Series)
• Contributing target values using writeback with
Excel 2010
Defining Analysis Services KPIs
• KPIs can be defined as part of the cube
metadata
– Use the KPI tab of the Cube Designer, or
– Use the CREATE KPI statement in the cube’s MDX
Script
• Less common, KPIs can be defined at session
scope
Defining Analysis Services KPIs
• KPI Properties:
–
–
–
–
Value
Goal
Status
Trend
MDX
expressions
−
−
−
−
−
−
StatusGraphic
TrendGraphic
ParentKpiID
Weight
AssociatedMeasureGroupID
CurrentTimeMember
Note: Tabular development approaches in Analysis Services for
SQL Server 2012 can also define KPIs
Demonstrations
• Defining Analysis Services KPIs
Delivering Analysis Services KPIs
Excel
• Add KPIs to PivotTables
• Use the CUBEKPIMEMBER function to retrieve specific
KPI members:
=CUBEKPIMEMBER(connection, kpi_name,
kpi_property, [caption])
kpi_property:
1=Value, 2=Goal, 3=Status, 4=Trend, 5=Weight,
6=CurrentTimeMember
• Share workbooks using SharePoint Excel Services
Delivering Analysis Services KPIs
Reporting Services 2008 R2
•
•
Retrieve KPI data
using the Analysis
Services data
provider
Present KPI values
and supplement
with Gauges and
Indicators
Delivering Analysis Services KPIs
PerformancePoint Services 2010
• Import KPIs
• Add KPIs to
scorecards
• Embed scorecards
into dashboards and
enrich with analytic
reports
Demonstrations
• Delivering the Analysis Services KPIs with:
– Excel 2010
– Reporting Services 2008 R2
– PerformancePoint Services 2010
Summary
• KPIs are quantifiable measurements comparing
actual values to target values, and are used to
monitor performance
• Analysis Services can be used to define KPIs
• Analysis Services can also be used to store and
manipulate data surfaced by KPIs
• Excel, Reporting Services and PerformancePoint
Services can easily represent data queried from
Analysis Services KPIs
Thank you!
For attending this session and
PASS SQLRally Nordic 2011, Stockholm