BI Dashboards with SQL Server 2008 R2

Category: Education

Presentation Description

No description available.


By: ecastrom (102 month(s) ago)

Hola, si me envian su correo electronico, les enviare la presentacion

By: gustavogerminario (102 month(s) ago)

Excelente presentacion, me gustaria bajarla para dar una explicacion a la gente de infraestrucutra. Gracias

By: ecastrom (102 month(s) ago)

Hola, si me envian su correo electronico, les enviare la presentacion


By: ibdiez (103 month(s) ago)

Hola, Eduardo. Felicidades por la presentación. Me ha gustado mucho. Podria bajarmela, por favor? Estoy haciendo una recopilación de powerpoints que versan sobre BI y me gustaria tener la tuya. Muchas gracias, Eduardo. Un saludo

By: ecastrom (102 month(s) ago)

Hola, si me envian su correo electronico, les enviare la presentacion


Presentation Transcript

BI Dashboards with SQL Server 2008 R2:

BI Dashboards with SQL Server 2008 R2 Eduardo Castro Microsoft MVP

Session Agenda:

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

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

SQL Server Integration Services:

SQL Server Integration Services

BI with Microsoft SQL Server 2008 R2:

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

Delivering BI with SQL Server and Microsoft Excel 2010:

Delivering BI with SQL Server and 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

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

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

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

BI with SQL Server 2008 R2 and Microsoft Excel 2010:

BI with SQL Server 2008 R2 and 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

SQL Server and BI Tools in SharePoint 2010:

SQL Server and 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

SQL Server and PerformancePoint Services:

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

SQL Server and Reporting Services:

SQL Server and 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

BI Dashboards with SQL Server 2008 R2:

BI Dashboards with SQL Server 2008 R2

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

SQL Server and BI with Performance Point Services 2010:

SQL Server and BI with Performance Point Services 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 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 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

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 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 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 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

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 Business Intelligence (BI) Primer BI Tools in SharePoint 2010 Excel Services Performance Point Services

This presentation is based on the following content:

This presentation is based on the following content 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 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 Symmetry Corp –

BI Dashboards with SQL Server 2008 R2:

BI Dashboards with SQL Server 2008 R2 Eduardo Castro Microsoft MVP

Slide 69:

Required Slide

authorStream Live Help