Balanced Scorecard Spreadsheets Represents Kpi - PDF by xqe20338

VIEWS: 457 PAGES: 74

More Info
									Microsoft Business Intelligence
             M A R C H 1 8 TH, 2 0 0 9

            CHUCK RUSSELL
            SENIOR PARTNER
         Microsoft Business Intelligence

Improving organizations
by providing business
insights to all employees
leading to better, faster,
more relevant decisions

Drive pervasive business performance
Implement a complete and integrated
data delivery solution
Deliver widespread intelligence through
Microsoft Office
Meet enterprise needs with affordable
     Business Intelligence Stakeholders

                                    and End User

                           BI Platform
Content Producers                                   Content Consumers
All Types of Decision Making
                   Organic                                      Intentional

        Personal     Easy discovery of data
                     Simple, intuitive tools
                     Creative and agile

                                           Performance Management
                                           Consistent corporate definitions, KPIs

                                               Corporate policies and processes
                                                    Contextual and accountable
                          BI Platform Architecture

   Analytic                                    Scorecards, Analytics, Planning
 Applications                                     (PerformancePoint 2007)


 Data Delivery
                        Report Builder                   End-user Analysis
                                SSRS                            (Excel)

                             Integrate                         Analyze                    Report
                       (SQL Integration Services)        (SQL Analysis Services)   (SQL Reporting Services)
                                                    Data Warehouse, Data Marts,
                                                         Operational Data
                                                           (SQL Server 2008)

     Office      SQL
The Microsoft BI Data Delivery Components
SQL Server 2008

                  Next Generation DBMS
                  Provides the foundation BI
                  1. RDBMS
                  2. Analysis Services
                  3. Reporting Services
                  4. Integration Services
                  5. Support for Massive Data
                  6. Etc.. Hey it’s SQL Server.
SQL Server 2008 Analysis Services
         Unified                KPI Framework
         Dimensional            Experience the
         Model                  manageability of
         Enjoy the power        a rich centralized
         of a combined          repository for
         metadata model         definition and
         – the UDM –            storage of KPI’s
         bridging the           that are easily
         flexibility of         made available
         relational             for end-user
         reporting with         consumption via
         the performance        e.g. BSM, Excel
         of the classic         SharePoint

         Business               Data Mining
         Intelligence           Create new
         Wizards                a breed of
         Enable even the        predictive
         most novice            analysis
         users to build BI      applications
         models and             through a rich
         projects               set of data
         addressing some        mining tools
         of the more            and predefined
         complex BI             algorithms
SQL Server 2008 Integration Services

           Metadata              Development
           Management            Productivity
           Impact and            Development
           Lineage               Environment
           Analysis from         with visual
           source                debugging
           systems to            and version
           cubes                 control

                                  Plug in
           Scalable data
                                  written in C#
           platform with
                                  or call
                                  logic using
SQL Server 2008 Reporting Services

          Report                Report
          authoring             management
          Visually              Manage report
          design                categorization,
          reports &             data sources,
          layout via            security,
          the                   subscriptions
          Report                via the Report
          Designer              Manager

                                Model design
         End user               Design
         report creation        semantic
         Enable end             models that
         users to               map to real
         dynamically            business
         create their           entities via
         own Ad-hoc             the Model
         reports via the        Designer
         Report Builder
BI Studio & SQL Server Management Studio

                             Development Facility for:
                             1. Report Models
                             2. Reports
                             3. Dimensional Models and
                                other Analysis Services
                             4. SSIS (ETL) Jobs
                  Business Abstraction Layers

 Unified Dimensional Model (UDM) - The role of a Unified
  Dimensional Model (UDM) is to provide a bridge between the user and the data
  sources. A UDM is constructed over one or more physical data sources, and then
  the end user issues queries against the UDM using one of a variety of client tools,
  such as Microsoft Excel. The UDM:
     Is used in for querying Analysis Services
     Is dimensional, meaning structures like dates are define hierarchically to support OLAP
     Hides Complexity.
 Report Model – Create an abstraction of the underlying data sources; hiding
  the navigational aspects of data retrieval from the user. Report models have the
  following features:
     Database fields and views can be given logical business names, so knowledge of the
      database structure is not required to produce reports.
     Items can be grouped logically.
     Relationships between items can be define
     Model elements can be secured so that users can see only the data that they have
      permission to see.
Report Builder

                 Ad Hoc Query and Reporting
                 Tool that supports the
                 creation of reports via the

                 Report Builder uses the
                 Report Model(s) that have
                 been created and published
                 in order to facilitate end-user
                 Query and Analysis
Office Excel 2007

 Conditional          Data Connection
 Formatting           Library
 Spot trends          Quickly
 and exceptions       access the
 in your data to      most common
 better inform        data sources
 your decisions       in your
                      enterprise for
                      data analysis

 Publish to            Pivot Table
 SharePoint            Against SQL
 Server 2007           Server Analysis
 Enhanced Sorting      Services
 and Filtering         Analyze data
                       with new
 Sort by cell color    PivotTable tools
 or filter data by     and enhanced
 quarter to work       support for SQL
 with only the data    Server Analysis
 you need
SharePoint 2007
 Publish from Excel   Excel Services
 The Excel Services   Create fully
 menu enables         interactive
 you to specify       web based
 which items to       data-bound
 display and how      worksheets,
 users can work       including charts,
 with the work-       tables, and
 sheet. Then save     PivotTable views
 directly to          without any
 SharePoint           custom coding
 Server 2007

 Secure Sharing        Dashboards
 Safeguard             Create rich,
 sensitive             interactive
 information by        dashboards that
 controlling access    assemble and
 to worksheets.        display business
 With Excel            information from
 Services Web-         disparate
 enabled access,       sources, using
 you can limit what    built in web parts
 users can view,       such as dynamic
 protecting the        (KPI) and Office
 underlying data       Excel 2007
 and models            spreadsheets
              Performance Point Server 2007

 Full Office experience
 Complete perspective across all functions
 Easy to personalize scorecards, analytics, plans
 Priced for broad deployment
Performance Point Features


                       •   Integrated with
                           SharePoint & MS
                       •   Integrates with SQL
                           Server 2008
                       •   Collaborative
                       •   Analytics - drill
                           down/up with
                       •   KPI and Scorecards
                       •   Iterative Monitoring
                           of Performance
                       •   Provides
                           Infrastructure for
                           Enterprise Planning
                           & Budgeting
What Happened to All those Other Products?





         November 06   Mid CY07                            2008 - 2010
                  Demo Environment

 Windows 2008 Enterprise Server (64bit)
 SQL Server Enterprise 2008 with SQL Server Analysis
    Services 2008 (64bit)
   SharePoint 2007 (64bit)
   MS Reporting Services installed at various places in the
   Authentication performed using Active Directory with
    Kerberos for SSO support.
   Adventure Works DW 2008 – Data warehouse in a box.
   I’m coming in remote for ‘speed’…we’ll see how fast.
                        Demo Plan

 Personal BI
   Excel Spreadsheets!
   Ad Hoc Querying and other Neat Stuff

 Team BI
   Reporting, Report Distribution
   SharePoint Integration with Reporting Services
   Overview of Analysis Services and Evaluating Data with Excel
   Excel Services within SharePoint

 Enterprise BI
   Collaborative BI Portals
   Performance Point Contoso Slide Deck
                                                   Demo Script

   Go to CIDev Portal and Demo extracting a spreadsheet from Documents/Sales Reporting
   Quickly Open up Analysis Services Model in Visual Studio 2008 to show the model used in the spreadsheet.
       Show them the UDM within the cube that’s created.
       Browse the Cube
   Demonstrate the various types of VS Projects: Reports, SSIS, and Analysis Services, Reporting Model
   Open up the Internet Sales Reporting Model
       Describe it
   Open up the Internet Sales Reporting Package
       Show the more complex report
       Demo full life cycle, round trip, build/deploy.
   Go Back to SharePoint and Demo the Large Report executing from within SharePoint.
       Show the reporting Dashboard
       Show the Collaboration within the Portal
   Show Report Manager
       Consuming Reports without SharePoint
       Scheduling Reports
       Etc.
   Go Back to SharePoint and in the File Viewer Open up Report Builder.
       Create a simple Report
       Save the Report
       Go execute the report in SharePoint
   Go to the Report Center Site
       Describe Layout
       Show Dashboard 1
       Show Reporting Services Example
   Discuss Contoso

           Contoso designs and markets bicycles, frames, components
            and accessories for mountain, road and touring cycling.
           Contoso follows a Balanced Scorecard Methodology to track
            its performance combining scorecards and analytics. Contoso
            also uses PerformancePoint for monitoring, analyzing and

           The persona: Jennifer Adams, Contoso’s CFO.
           Business challenge: margin issues across some product
           Issues prior to solution
                   Limited integration: had to jump from applications to applications
                    to just get information.
                   Misaligned information: when information was found there was no
                    way to tie it back to corporate efforts and objectives.
                   Slow and tedious: identifying issues quickly, performing root-cause
                    analysis required costly IT and analyst time.
                     Contoso Demo Flow

                              Driving                                  Q&A
Demo scenario              accountability
                                            More than just numbers


                                                           Affecting the
            Integration                    Analytics
          Delivery in Office            Asking questions
This CFO uses Microsoft®
Office SharePoint™ Server
as a thin and personalized
interface to her corporate
  One type of information
that can be managed inside
       SharePoint is
 This CFO can now consume
      very rich analytics

   And benefit from a full
fledge scorecard (built here
   following the balanced
  scorecard methodology)
This CFO can now interact
 with very rich analytics
And drill into the data
    As well as get quick
  information on KPIs by
simply hovering over them.
 In order to make better
 informed decisions, she
needs to review related but
unstructured information.
  She opens the document
library that is related to the
 scorecard in order to get a
 more complete view of her
She opens the strategy map
   that is related to this
   scorecard in order to
understand how KPIs relate
      to each other.
Now, she wants to know if
there is anything more she
 could review in order to
  complete her analysis.
The application suggests to
    review the finance
    The finance scorecard focuses
    on finance metrics (revenue,
     cost, profit), across multiple
      The scorecard also display
       owners at the KPI level.

She can now assign tasks to
resolve issues based on the
      kpi ownership.
If she needs to, she can
  filter the scorecard.
    When she does so, the
   scorecard and all related
analysis views will be filtered
  accordingly, guaranteeing
 consistency of information.
     She can also look at the
annotations associated to the KPIs.

  Annotations allow her to gain
  more context and understand
  more than ‘just the numbers’.
She wants to drill thru the
 gross profit margin KPI
because it currently shows
       some issues.
   The following analytical view
opens, presenting her with multiple
 applications to analyze the data.
 These applications will help her
 understand complex data much
 She first opens a ‘performance map’.
This application provides a quick view
    of the data after 2 dimensions.
Here the size of the box is determined
by ‘sales amt’ and the color of the box
     represents ‘% Gross Margin’.
She understands that North America is
 selling a lot [their boxes are bigger].
    NA doesn’t have margin issues
  compared to the other geographies
       [NA’s colors are green]
    This CFO drills under Europe
 She can see product
categories split across
Or geographies spilt across
    product categories
She can drill up and down
And decompose the data
further moving from the
performance map to the
  decomposition tree.
  Decomposition tree is a
 great and flexible way to
ask your data very intuitive
  As a first step, she goes
down the product hierarchy

   But then switches to a
    geographical view to
  understand who sells the
most road bikes, which is the
 bigger revenue category in
She then drills across to
   time dimension to
understand when certain
  geos sell road bikes
                                                                         The data can be analyzed
                                                                              across any geo

At this point, we are using all available hierarchies in the data (product category, geography
                                           and time).
We can of course go back to
  a much simpler view
When users perform this type of
analysis, they assume that they
  picked the right metrics to
      analyze the data by.
 Switching their analysis to a
   different metrics is often
Switching their analysis to a
  different metrics is very
       easy using the
    decomposition tree
The analysis is now ran the
gross profit margin, but the
   CFO didn’t have to go
through all the steps of the
 previous analytical path.
The CFO has now being able to
   resolve the margin issue.
Holland is the country creating
the problem in Europe: it was a
 top 3 in revenue, but the last
  performance from a margin

The CFO can save and share
 this view with the rest of
     the organization.
 The analysis can also be
shared in PowerPoint in a
static and dynamic mode.
Going back to the analytical
view, the CFO notices that
  the revenue forecast is
She collaborates with the
  owner of the forecast
   process in real time
   Who suggests that she
checks her email for a sales
forecast update notification
  The assignment opens in
    excel. Notice that the
  functionality is available
      natively in excel.

 The user benefits from the flexible
interface and the PerformancePoint
  functionality. Users get the right
 amount of information at the given
 point in time. Process and security
    transpire thru to the end-user
Notice that the forecast # is
the same as the number in
    the scorecard. The
scorecard and the planning
 experience are integrated.
The yellow cells are the cells
 the user can contribute to.
They represent the business
 drivers that determine the
     forecast numbers.
 The CFO decides that the
company will only sell 120
road bikes instead of 300.
 The application then runs
centrally stored calculation
         and logic.
   A new forecasting #
While this model might work when
you have a few subcategories, it is
  not highly scalable if you have
many lines to update to show a new
     This is when you can use
     ‘spreading’ functionality.
Spreading allows end users to set a
 bottom line # and have rules and
logic defined in PerformancePoint
run the allocation at the sub-level.
 Here, the CFO simply updates the total
 qty of bikes and the calculation engine
 spreads the result of centrally defined
The results could be based on seasonality
 or any other calculations defined on the
The CFO can roll up this analysis
  to the European forecast and
include an annotation at the cell
   level to explain that the new
    allocation has been spread.
Annotations are stored centrally
 to enforce auditability of the
The form can be shared and
Comments at the assignment level
   can be added to share more
contextual information. Like cell
 annotations, they are also stored
centrally to facilitate auditability.
Once refreshed, the new forecast
 # is reflected in the scorecard.
Microsoft Business Intelligence
             M A R C H 1 8 TH, 2 0 0 9

            CHUCK RUSSELL
            SENIOR PARTNER

To top