BI DashBoard Creation

BI Dashboards with SQL Server and Performance Point Services 2010:

BI Dashboards with SQL Server and Performance Point Services 2010 Eduardo Castro ecastro@mswindowscr.org http://comunidadwindows.org http://ecastrom.blogspot.com

This presentation is a summary of the following:

This presentation is a summary of the following BIO303 – Creating Real Time Dashboards with Microsoft SharePoint Server 2010 BIO301 – Advanced Dashboard Creation using SharePoint 2010 BIO06-INT- How to apply user Driven Detailed Context to your Dashboard BIO01 – HOL Deploying KPI and Scorecards with PerformancePoint Services BIO02 – HOL Developing Reports and Scorecards with PerformancePoint Services

Session Agenda:

Session Agenda Using Business Intelligence (BI) Introducing Microsoft BI Delivering BI with Microsoft SQL Server 2008 R2 Delivering BI with Microsoft Excel 2010 Delivering BI with SharePoint Server 2010

What is Business Intelligence?:

What is Business Intelligence? A category of methodologies and technologies for gathering, storing, analyzing and providing access to data to help enterprise users make business decisions.

Business Intelligence Applications:

Business Intelligence Applications Summarize business critical operations Purchasing, finance, investments, etc… Used at multiple organizational level Executive, Accounting, Personal Often embedded in larger processes

Using Business Intelligence (BI):

Using Business Intelligence (BI) Get Real-Time Information Find Inefficiencies Save Money See what’s happening right now Understand variances and drill to detail to find trends Know your cost structure and take action to ensure you hit the bottom line Find Profitable Customers Focus resources where it really matters

Microsoft’s BI Vision and Strategy:

Microsoft’s BI Vision and Strategy To improve organizations by providing business insights to all employees, leading to better, faster, more relevant decisions Microsoft has a long-term commitment to delivering a complete and integrated BI offering SQL Server has led innovation in the BI space for more than a decade There is widespread delivery of intelligence through Microsoft Office The platforms are enterprise-grade and affordable

Integrated End-To-End Offering:

Integrated End-To-End Offering END USER TOOLS Excel PowerPivot Events SharePoint Server SEARCH DELIVERY Reports Dashboards Excel Workbooks Analytic Views Scorecards PowerPivot CONTENT MANAGEMENT COLLABORATION DATA PLATFORM SQL Server Reporting Services SQL Server Analysis Services SQL Server DBMS SQL Server Integration Services StreamInsight

Microsoft Business Intelligence Get more out of products you already own!:

Microsoft Business Intelligence Get more out of products you already own! Business User Experience Business Collaboration Platform Information Platform Information Platform Analysis Services Reporting Services Integration Services Master Data Data Mining Services Data Warehousing Integrated Content and Collaboration Thin Clients Dashboards BI Search Content Management Compositions Familiar User Experience Self-service Access and Insight D ata Exploration and Analysis Predictive Analysis Data Visualization Contextual Visualization

BI for Everyone:

BI for Everyone Specialized BI Tools BI for Everyone

Delivering BI with Microsoft SQL Server 2008 R2:

Delivering BI with Microsoft SQL Server 2008 R2

BI with Microsoft SQL Server 2008 R2:

BI with Microsoft SQL Server 2008 R2 DEMO Cubes in SQL Server 2008 R2

Delivering BI with Microsoft Excel 2010:

Delivering BI with Microsoft Excel 2010 Provides an interface familiar to most information workers Supports basic, advanced, and embedded analysis Integrates with SQL Server and Analysis Services as well as other data sources Includes a new add-in for highly-performing data integration and analysis Enables collaboration through server workbooks shared across the Web Supplements portals and dashboards

SQL Server PowerPivot for Excel:

SQL Server PowerPivot for Excel Manages compressed storage of, and extremely fast querying and reporting from, large volumes of related tabular data Data can be loaded from a variety of data sources Leverages familiar Excel features Uses Data Analysis Expressions (DAX) for sophisticated transformation of data Supports publication of PowerPivot workbooks to SharePoint

What is Excel Services?:

What is Excel Services? Server hosting of Excel calculation engine Provides a web thin client for Excel workbooks Excel aggregates data from multiple sources Exposes data using REST and JavaScript Excel 2007 Custom applications Browser

Management of Excel Content:

Management of Excel Content IT can control parts of Excel Workbooks Excel file sharing is all or nothing Excel Services can expose smaller parts Ex. Expose the results without exposing formula Ex. Expose one summary chart, but not the raw data IT can control permissions to each part

Exposing Excel Content:

Exposing Excel Content Content can be exposed in a variety of ways Publish workbook to a SharePoint site Entire workbook, specific sheets, specific items Web Services REST Services Javascript Object Model

Existing Excel applications:

Existing Excel applications Excel data “trapped” in existing workbooks Excel Services lets us expose this information Users can interact and collaborate Excel “applications” can be exposed on web

Excel Workbooks in the browser:

Excel Workbooks in the browser Provides a browser based view of Excel Allows interaction with Excel Workbook Ex. Pivot Table, Sorting, Filtering, Parameters

Excel Services Web Parts:

Excel Services Web Parts Excel Services exposed using Web Parts Web Parts display named items Parameters exposed as Web Part connections Parameter changes will update charts Allows real time feedback to users

Excel Services + PowerPivot:

Excel Services + PowerPivot In-memory Data Cubes in Excel Excel addin allows creation of cube Cube persisted in the Excel Workbook Consumers of workbook have access to cube Excel Services Gemini Browser Excel Workbook

BI with Microsoft Excel 2010:

BI with Microsoft Excel 2010 DEMO Creating a BI Dashboard with Excel 2010

Introducing Microsoft SharePoint Server 2010:

Introducing Microsoft SharePoint Server 2010 Connect and Empower People Cut Costs with a Unified Infrastructure Rapidly Respond to Business Needs Communities Search Sites Composites Content Insights

BI Tools in SharePoint 2010:

BI Tools in SharePoint 2010

SPS 2010 BI Web Parts:

SPS 2010 BI Web Parts Simple web parts that provide visualization Quick summary of data Can access multiple data sources Other Web Parts Lists and External Lists Excel Services Fixed Values Lists and External Lists Excel Services SQL Analysis Services

Excel Services:

Excel Services Provides a browser thin client for Excel Allows trusted distribution of Excel Workbooks Users can expose Excel content in Web Parts Provides workbook data using service APIs

PerformancePoint Services:

PerformancePoint Services Context-driven dashboards across systems Provides transparency and accountability Interactive access using browser Can be created/updated by IT professional

Reporting Services:

Reporting Services Professional report authoring environment Designed for developer or IT professional Does not ship with SharePoint 2010 SP 2010 has Reporting Services Web Parts Used to create professional reports for BI apps

BI Search:

BI Search Extends the reach of search to BI elements Adds Excel and Reporting Services to search Presents results based on dimensions Links directly to specific view of the report

BI Center Site Template:

BI Center Site Template Evolution of the Reporting Center template Predefined lists for PPS, Excel Services Starting point for BI portals

Chart Web Part:

Chart Web Part demo Creating a BI Site in SharePoint

What are Dashboards?:

What are Dashboards? Visual displays of mission critical analysis Answers fundamental business questions Single screen display of information “Real Time” summary of data Interactive links to details

What are Scorecards?:

What are Scorecards? Provides an overall view of status indicators Modeled after the business not the data Manages key performance indicators (KPIs) Often included as part of a dashboard Balanced Scorecard Certified

Designing PPS Applications:

Designing PPS Applications Dashboard designer used to create content ClickOnce application launched from browser Manages content directly in SharePoint Lists for content, data sources, dashboards

Aggregating Data Sources:

Aggregating Data Sources Data access is managed with Data Sources Data is accessible from multiple sources Ex. Analysis Services, Excel, Lists, SQL, etc… Accessed with service account or current user Stored in list as an XML file (*. ppsdc )

PPS Content in the Browser:

PPS Content in the Browser Business users view content in the browser Stored as Web Part pages Web Part connections used to relate content Ex. Item choice in a scorecard updates reports

Visual Data Exploration:

Visual Data Exploration Decomposition trees allow data exploration New in SharePoint 2010 Available by right clicking data in browser Analyze -> Decomposition Tree Allows filtering\expanding across dimensions

BI with Performance Point Services 2010:

BI with Performance Point Services 2010 DEMO

Project 2010 Business Intelligence:

Project 2010 Business Intelligence

Business Intelligence in Project 2010 Driven by customer feedback:

Business Intelligence in Project 2010 Driven by customer feedback Project Server 2003 Limited reporting relying on OLAP Databases Project Server 2007 H ard to build comprehensive dashboards using out-of-box functionality on the Server Relying on “legacy” Office Web Components (OWC) ActiveX components “New” visual reports functionality on the desktop application Project Server 2010 Leveraging the architecture of Project Server 2007 Building on the powerful collaboration and BI platform of SharePoint 2010 w/ Enterprise CAL

BI related architecture changes in Project Server 2010 (1/2) What hasn’t changed:

BI related architecture changes in Project Server 2010 (1/2) What hasn’t changed Same underlying database structure as in Project 2007 Relational OLAP Data flow is the same Leverage queue for reporting publish jobs

BI related architecture changes in Project Server 2010 (2/2) What’s changed for 2010?:

BI related architecture changes in Project Server 2010 (2/2 ) What’s changed for 2010? Infrastructure No Office Web Components Data Analysis views are not upgraded Not integrated with Views Management Built on SharePoint BI Technologies New BI Center site Out of the box integration with Excel Services 3 tier architecture Data New Data and Views in Reporting DB New entities in the Reporting DB Multiple OLAP Database support Portfolio and Project data together

BI with Performance Point and Project Server 2010:

BI with Performance Point and Project Server 2010 DEMO

Dashboard Design Best Practices:

Dashboard Design Best Practices

Dashboard Definition:

Dashboard Definition Visual Display of the most important information needed to achieve one or more objectives which fits entirely on a single computer screen so it can be monitored at a glance Information Dashboard Design By Stephen Few

What Makes a Good Dashboard:

What Makes a Good Dashboard Some are Better Than Others

Smart KPIs:

Smart KPIs Specific – Clear on what is being measured Measurable – Accurate and consistent Actionable – Influence to alter the outcome Relevant – Significance and leverage Timely – Appropriate intervals T R A M S

Dashboard - Best Practices:

Dashboard - Best Practices

General Design Considerations:

General Design Considerations Displays Patterns Abstract Data Minimal Grids “ Graphical ” No Scrolling Nothing Hidden Linked Content “ Single Page ” Domain Aware Leverages Knowledge “ Guided Analytics ” Better Dashboard Design All Relevant Info One Place to Look Not Data Driven “ Disparate Data ”

Key Data/Metrics in Context:

Key Data/Metrics in Context Key Data/Metrics High level performance metrics Generally tied to goals and bonuses Focus on actionable data Disparate sources Context The 3Ts = Target, Trend, Typical Similar/Related Data/Metrics Basis for interpretation

Dense, not Cluttered:

Dense, not Cluttered Dense Information All relevant data/metrics Different perspectives Not Cluttered – “At a Glance” It is not how much, but how well Easy to view, not an eye chart Data-Ink Ratio ( ) Non-essential ink should be removed Supporting ink (axis lines) = low visual impact The Visual Display of Quantitative Information Edward R. Tufte

Zero Learning Curve:

Zero Learning Curve Dashboard viewing is not a person’s job Oriented at casual users, line mgt, & execs Little/No accumulated experience Each viewing is the first time Dashboards are not analytical tools But they provide guided analytics Targeted to a Person/Role/Problem Set

Exception Oriented:

Exception Oriented Outliers should POP out Highlight Both Problems Opportunities What is an Exception? > 1 Std Dev, 2…. Five9s When action needs be taken Visual Cues Color Size Shape Boldness Position Icons

Supporting Detail:

Supporting Detail Dashboards are not an end unto themselves Guided Analytics Hierarchical Drill-Down Contextual Content Updates Associated Layers Root Cause Analysis Detail Reports Ad Hoc Analysis Links to New Pages Update Dashboard Webparts

Dashboard Components:

Dashboard Components Grids Charts Diagrams Maps Scorecards Tree Map Gauges Decomp Tree And So Much More……

First a Word on Color:

A dashboard is not a painting Usefu lness is Paramount First a Word on C o l o r Use Color Sparingly – Generally Soft Colors Highlighting – Bold Colors Different Colors = Different Meaning Try shades of grey Use a Single Hue Increase the intensity for low to high values Remember - 7% of men are color blind

Grids:

Grids When to use: Values are familiar Need to lookup a value Small differences are important Mixed units of measure Design Considerations Try to keep square- ish Comparisons are done by column Limit length with Top/Bottom

Grid Embellishments:

Grid Embellishments Highlights Items of interest Data Bars Helpful to read magnitude Color Scales/Icons Can overwhelm the data Arrows Used for change over time Icons are best used to: Replace data to save space Show variance or score

Scorecards:

Scorecards Are a special type of grid Created for KPIs and Objectives Can be methodology oriented Balanced Scorecard, 6 Sigma, … Used as a navigation aid A list with data Scorecards are less about data and more about process

End-To-End Business Logic:

End-To-End Business Logic Data Scorecards Measures KPIs Score Metrics Tolerances Derivation Navigation Budget Plan Forecast Benchmarks Objectives Perspectives Processes Initiatives Organize Catalog Audited Accurate Approved

Basic Charts:

Basic Charts Lines Charts – Time Series oriented Highlights trend, patterns or variability Highlights relationships between series Trend lines & projections can be added Bar Chart – Comparison Oriented Categorical comparisons within a dimension Nominal comparisons across data ( sales to cost ) Pie Charts – Part to Whole Only works with small sets Hard to visually quantify A lot of space for not much data http://www.symcorp.com

Interlude: Form vs. Function:

Interlude: Form vs. Function Too Dense? 1 Page – 6#s Pretty Yes! Useful? Use Carefully Bright Colors Animation Pie Charts Gauges 3D Charts If the dashboard is not visually appealing, people will not go to it If the dashboard is not effective and practical, people will not use it What is fun the first time Might be boring the 100 th time

Bar Charts :

Bar Charts Horizontal Long labels Pattern comparisons Long- ish lists Vertical Groups with in groups Stacked and 100% Bars Pareto Charts Doesn’t work for large numbers of groups Bars are easily drilled into

Hybrids – Charts in Grids:

Hybrids – Charts in Grids Great Data-Ink Ratio Effective Mixing Of Charts & Values Introduction of Sparklines & Bullet Graphs Small Multiples Trellis Charts

Bullet Graphs:

Bullet Graphs A bullet graph is a variation of a bar graph developed by Stephen Few . Seemingly inspired by the traditional thermometer charts and progress bars found in many dashboards, the bullet graph serves as a replacement for dashboard gauges and meters Black Bar = Value Green / Red Bar = Target Blue Pointer = Last Year Symmetry’s Variation Sales Costs

Maps:

Maps Maps are highly specialized Be careful of charts within a map Maps are great for demographics Useful in showing regional components Impact grids can be effective

Gauges:

Gauges Flashy, Showy, Attention Getting Best for continuous process Poor Data-Ink Ratio Hard to set gauge scale for the general case When using a set of gauges expected value should be straight up

Diagrams:

Diagrams Balanced Scorecard Strategy Map Root Cause/Fishbone Diagram Impact Diagram Process Diagram Diagrams are underutilized Graphical Representations

Graphical Excellence:

Graphical Excellence Show the data Focus on the substance of the numbers Don’t distort the data Present many numbers in a small space Encourage comparisons Show data in several perspectives The Visual Display of Quantitative Information Edward R. Tufte

Screen Layout:

Screen Layout Number of Frames Up to 4 is good, no more than 6 Frame Proportions Size = implied importance Uniform otherwise Location Top left = primary focus Bottom right = supporting detail Off page = irrelevant Printing The dirty little secret

Frame Options:

Frame Options Different frame on same page Must fit on the page Can drill from here to new page New page in same browser Easy to get back Full page available New browser tab/window Allows for comparisons Users must close windows/tabs

Navigation Framework:

Navigation Framework A tiered set of dashboards based on role or function A set of grids, graphs, or visualizations that examine a single metric A set of detailed reports or ad-hoc analytics http://www.symcorp.com

Navigation :

Navigation Identify the key dimensions for the metric Customer, product, department Drill into one or more of these Top 10 rankings Identify the audience Marketing manager – Drill to product Sales Manager – Drill to customer

Navigation - What to Show:

Navigation - What to Show

Summary:

Summary Business Intelligence (BI) Primer BI Tools in SharePoint 2010 Excel Services Performance Point Services

This presentation is a summary of the following:

This presentation is a summary of the following BIO303 – Creating Real Time Dashboards with Microsoft SharePoint Server 2010 BIO301 – Advanced Dashboard Creation using SharePoint 2010 BIO06-INT- How to apply user Driven Detailed Context to your Dashboard BIO01 – HOL Deploying KPI and Scorecards with PerformancePoint Services BIO02 – HOL Developing Reports and Scorecards with PerformancePoint Services

Resources:

Resources Show Me the Numbers: Designing Tables and Graphs to Enlighten Stephen Few Information Dashboard Design: The Effective Visual Communication of Data Stephen Few The Visual Display of Quantitative Information, 2nd edition Edward R. Tufte http://www.perceptualedge.com/ http://dashboardspy.com/ Symmetry Corp – www.symcorp.com

Project 2010 Business Intelligence Related Resources:

Project 2010 Business Intelligence Related Resources Webcasts ( http://www.microsoft.com/events/series/epm.aspx ) Project 2010 Business Intelligence Center http://go.microsoft.com/? linkid=9726143 Accessible from Project Server 2010 TechCenter site http://technet.microsoft.com/projectserver / SharePoint 2010 Business Intelligence Center http://technet.microsoft.com/sharepoint/ee692578.aspx

Slide 79:

Required Slide