P L A T F O R M O V E R V I E W M A R C H 1 8T H, 2 0 0 9
C H U C K R U S S E L L S E N I O R P A R T N E R
C O L L E C T I V E I N T E L L I G E N C E I N C .
C R U S S E L L @ C O L L E C T I V E I N T E L L I G E N C E . C O M
Microsoft Business Intelligence
Microsoft Business Intelligence
Improving organizations
by providing business
insights to all employees
leading to better, faster,
more relevant decisions
D rive pervasive business performance
Implement a complete and integrated
data delivery solution
Deliver widespread intelligence through
Microsoft Office
Meet enterprise needs with affordable
economics
Analytic Applications and End User
Tools Development
Tools
BI Platform
Business Intelligence Stakeholders
Content Producers Content Consumers
All Types of Decision Making
Sh ar ed Per sonal Sc ope
Organic Intentional
Development
Self-Service
Performance Management
Easy discovery of data
Simple, intuitive tools
Ad-hoc
Creative and agile
Consistent corporate definitions, KPIs
Corporate policies and processes
Contextual and accountable
BI Platform Architecture
Portal
(SharePoint)
Office SQL
Data Warehouse, Data Marts, Operational Data
(SQL Server 2008)
Integrate
(SQL Integration Services)
Analyze
(SQL Analysis Services)
Report
(SQL Reporting Services)
End-user Analysis
(Excel)
Infrastructure
Platform
Analytic
Applications
Data Delivery
Scorecards, Analytics, Planning (PerformancePoint 2007)
Report Builder
SSRS
The Microsoft BI Data Delivery Components
SQL Server 2008
Next Generation DBMS Provides the foundation BI Services:
1. RDBMS
2. Analysis Services
3. Reporting Services
4. Integration Services
5. Support for Massive Data Warehouses
6. Etc.. Hey it’s SQL Server.
SQL Server 2008 Analysis Services
Unified Dimensional Model
Enjoy the power of a combined metadata model – the UDM – bridging the flexibility of relational reporting with the performance of the classic OLAP
KPI Framework Experience the manageability of a rich centralized repository for definition and storage of KPI’s that are easily made available for end-user consumption via e.g. BSM, Excel SharePoint
Business Intelligence Wizards
Enable even the most novice users to build BI models and projects
addressing some of the more complex BI scenarios
Data Mining Create new a breed of predictive analysis applications through a rich set of data mining tools and predefined algorithms
SQL Server 2008 Integration Services
Metadata Management Impact and Lineage
Analysis from source
systems to cubes
Development Productivity Development Environment with visual debugging and version control
Enterprise Data
Integration Scalable data integration platform with parallel
pipeline architecture
Extensible Architecture Plug in
custom components written in C#
or call existing business logic using VB.NET
SQL Server 2008 Reporting Services
Report authoring Visually design reports &
layout via the
Report Designer
Report
management Manage report categorization, data sources, security,
subscriptions via the Report Manager
End user
report creation Enable end users to dynamically create their own Ad-hoc reports via the Report Builder
Model design Design
semantic models that map to real business entities via the Model Designer
BI Studio & SQL Server Management Studio
Development Facility for:
1. Report Models
2. Reports
3. Dimensional Models and other Analysis Services structures
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 operations
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 web.
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 Formatting
Spot trends and exceptions in your data to better inform your decisions
Data Connection Library
Quickly access the most common data sources in your
enterprise for data analysis
Pivot Table Against SQL Server Analysis Services
Analyze data with new
PivotTable tools and enhanced support for SQL Server Analysis Services
Publish to SharePoint Server 2007
Enhanced Sorting and Filtering
Sort by cell color or filter data by quarter to work with only the data you need
SharePoint 2007
Publish from Excel The Excel Services menu enables you to specify which items to display and how users can work with the work- sheet. Then save directly to
SharePoint Server 2007
Secure Sharing Safeguard sensitive information by controlling access to worksheets.
With Excel Services Web- enabled access, you can limit what users can view, protecting the underlying data and models
Excel Services Create fully interactive web based data-bound worksheets, including charts, tables, and PivotTable views without any custom coding
Dashboards Create rich, interactive dashboards that assemble and display business information from disparate
sources, using built in web parts such as dynamic (KPI) and Office Excel 2007 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
Performance Management
• Integrated with SharePoint & MS Office
• Integrates with SQL Server 2008
• Collaborative
• Analytics - drill down/up with visualization
• KPI and Scorecards
• Iterative Monitoring of Performance
• Provides
Infrastructure for Enterprise Planning
& Budgeting
What Happened to All those Other Products?
Scor ecards An al yti cs P la nn ing
Mid CY07
November 06 2008 - 2010
Scor ecards An al yti cs P la nn ing
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
‘farm’.
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
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
planning.
The persona: Jennifer Adams, Contoso’s CFO.
Business challenge: margin issues across some product
categories.
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
Demo scenario
Integration
Delivery in Office
Monitoring
Driving
accountability
Analytics
Asking questions
Collaboration
More than just numbers
Planning
Affecting the
future
Q&A
END
This CFO uses Microsoft®
Office SharePoint™ Server as a thin and personalized
interface to her corporate information
One type of information that can be managed inside
SharePoint is PerformancePoint
scorecards.
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
business.
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
scorecard
The finance scorecard focuses on finance metrics (revenue, cost, profit), across multiple
quarters.
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
faster.
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
geographies
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
questions
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 bikes.
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
challenging.
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 standpoint
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 wrong
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
PerformancePoint™
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 experience.
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 # appears.
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
forecast.
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 logic.
The results could be based on seasonality or any other calculations defined on the
server.
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
forecast.
The form can be shared and submitted.
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.
P L A T F O R M O V E R V I E W M A R C H 1 8T H, 2 0 0 9
C H U C K R U S S E L L S E N I O R P A R T N E R
C O L L E C T I V E I N T E L L I G E N C E I N C .
C R U S S E L L @ C O L L E C T I V E I N T E L L I G E N C E . C O M
