Business Intelligence in Microsoft® SharePoint® 2010

Business Intelligence in Microsoft® SharePoint® 2010

by Mariano Teixeira Neto Norman P. Warren John Campbell, and Stacia Misner
Released April 2011
Publisher(s): Microsoft Press
ISBN: 9780735661714

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Dive into the business intelligence (BI) features in SharePoint 2010—and use the right combination of tools to deliver compelling solutions. This practical guide helps you explore several BI application services available in SharePoint 2010 and Microsoft SQL Server 2008 R2. You’ll learn each technology with step-by-step instructions, and determine which ones work best in specific BI scenarios—whether you’re a SharePoint administrator, SQL Server developer, or business analyst.

  • Choose the BI tools that meet your needs—and learn how they work together

  • Examine the BI lifecycle, from determining key performance indicators to building dashboards

  • Take Microsoft Excel further—gain more control and functionality with web-based Excel Services

  • Mash up data from multiple sources using PowerPivot for Excel 2010

  • Create data visualizations with objects, context, and metrics using Microsoft Visio Services

  • Build dashboards, scorecards, and other monitoring and analysis tools with PerformancePoint Services

  • Use SharePoint to view BI reports side by side, no matter which tools were used to produced them

    • Your companion web content includes:

  • Interactive exercises that help you try out concepts or techniques

  • Code samples that enable you to work with the exercises

    •

    Table of contents

    1. Introduction
      1. Which Tool Do I Use?
        1. BI Maturity Model
        2. How Do the Tools Work Together to Help Me Solve My Needs for BI?
      2. Who Should Read This Book
        1. SharePoint Administrator
        2. Business User
        3. BI Developer
      3. Scope of This Book
      4. Organization of This Book
        1. Chapter 1, Business Intelligence in SharePoint
        2. Chapter 2, Choosing the Right BI Tool
        3. Chapter 3, Getting to Trusted Data
        4. Chapter 4, Excel Services
        5. Chapter 5, PowerPivot for Excel and SharePoint
        6. Chapter 6, Visio and Visio Services
        7. Chapter 7, PerformancePoint Services
        8. Chapter 8, Bringing It All Together
        9. Appendix A: Virtual Machine Setup and SharePoint Configuration
        10. Appendix B: DAX Function Reference
        11. Appendix C: SharePoint As a Service—“Office 365”
      5. Finding Your Best Starting Point in This Book
      6. What’s Not in This Book
        1. Access Services
        2. SQL Server 2008 R2 Reporting Services in SharePoint
        3. Business Connectivity Services
          1. How Is BCS Different from BDC in SharePoint 2007?
          2. Duet Enterprise
        4. Web Analytics
    2. Conventions Used in This Book
      1. System Requirements
        1. Code Samples
        2. Installing the Code Samples
        3. Using the Code Samples
    3. Acknowledgments
    4. Errata and Book Support
      1. We Want to Hear from You
      2. Stay in Touch
    5. 1. Business Intelligence in SharePoint
      1. Introduction
      2. Leading Up to Business Intelligence
      3. Beware of Losing Sight of What Matters Most
      4. What Is BI?
      5. The Need for Business Intelligence Today
      6. Microsoft’s Vision for BI
      7. What SharePoint Does for BI
      8. The BI Stack: SQL Server + SharePoint + Office
      9. Authoring in Microsoft BI Tools
      10. Some Examples of BI in SharePoint 2010
        1. PerformancePoint and the BI Stack
        2. Collaborative Decision Making: BI in Social Computing
          1. To add a rating feature to a list
      11. Summary
    6. 2. Choosing the Right BI Tool
      1. Introduction
      2. Business User Communities
        1. Casual Users vs. Power Users
        2. Organizational Hierarchy
        3. BI Communities
          1. Organizational BI
            1. Reporting Services
            2. Excel Services
            3. PerformancePoint Services
          2. Team BI
            1. SharePoint BI
            2. Visio Services
            3. PowerPivot for SharePoint
            4. Excel Services
            5. Reporting Services
            6. PerformancePoint Services
          3. Self-Service and Personal BI
            1. Excel
            2. PowerPivot for Excel
            3. Report Builder
            4. Visio
      3. The Progression of BI
        1. BI Maturity Model
          1. Stage 0: Prenatal
          2. Stage 1: Infant
          3. The Gulf
          4. Stage 2: Child
          5. Stage 3: Teenager
          6. The Chasm
          7. Stage 4: Adult
          8. Stage 5: Sage
        2. Road Map to Analytical Competition
          1. Stage 1: Analytically Impaired
          2. Stage 2: Localized Analytics
          3. Stage 3: Analytical Aspirations
          4. Stage 4: Analytical Companies
          5. Stage 5: Analytical Competitors
      4. Tool Selection
        1. Excel
        2. PowerPivot for Excel
        3. Excel Services
        4. PowerPivot for SharePoint
        5. Reporting Services
        6. SharePoint BI
        7. PerformancePoint Services
        8. Visio Services
      5. Summary
    7. 3. Getting to Trusted Data
      1. Introduction to Trusted Data
      2. SQL Server 2008 R2 + SharePoint 2010 + Office 2010
      3. BI in SQL Server2008 R2
        1. Core BI Components
          1. SQL Server Database Engine
          2. SQL Server Integration Services (SSIS)
          3. SQL Server Analysis Services (SSAS)
          4. Data Mining
        2. SQL Server Reporting Services
        3. Business Intelligence Development Studio
        4. Other SQL Server 2008 R2 BI Features
          1. PowerPivot for Excel and PowerPivot for SharePoint
          2. Master Data Services
          3. StreamInsight and Complex Event Processing
      4. Life Cycle of a BI Implementation
        1. Step 1: Decide What to Analyze, Measure, or Forecast
        2. Step 2: Inventory the Data You Have
        3. Step 3: Create and Populate the Data Warehouse
          1. What Is a Data Warehouse?
          2. Data Warehouse vs. Data Mart
          3. Facts and Dimensions
          4. Moving Data by Using SSIS
          5. From Data Warehouse to Report, Using SSIS
        4. Step 4: Create an SSAS Cube from Warehouse Data
          1. What Is OLAP?
          2. Why Use SSAS?
          3. What Is a Cube?
          4. What Is MDX?
          5. Alternatives for Creating Cubes
        5. Step 5: Surfacing OLAP Data to Front-End Tools
      5. Create a Cube from Data in SalesContosoDM
        1. Northwind Database
        2. Data Warehouse Scenario
        3. Getting Started with the Data Source
          1. To download and install the Northwind data source
        4. Design and Create the Data Warehouse
          1. To create the fact and dimension tables
          2. To populate NorthwindOrdersDW dimensions tables
          3. To populate the NorthwindOrdersDW FactOrders table
        5. Create an Analysis Services Cube, Based on NorthwindOrdersDW Data
        6. Creating a Cube Summarized
          1. To create relationships between FactOrders, DimProducts, and DimCategories
      6. Summary
    8. 4. Excel Services
      1. Excel Services Overview
        1. Brief History—the 2007 Release
          1. BI Functionality
          2. Sharing and Managing Workbooks
          3. Extensibility
          4. Excel Web Services
          5. User Defined Functions
        2. The 2010 Release
          1. Continued BI Support
          2. Editing and Excel Web Application
          3. Improved Extensibility
          4. Excel Services as a Cloud-Based Service
        3. When to Use Excel Services
          1. It’s Already Excel
          2. Excel: Fast to Create and Easy to Adopt
          3. It’s a Great Ad-Hoc Tool
          4. It Scales Excel Files to Many Users
      2. Configuration
        1. Installation
        2. Administration
        3. File Security
        4. Server Security
        5. External Data Configuration
          1. To configure authentication in the workbook
        6. Locking Down Excel Files
        7. View Only Permissions
          1. To apply View Only permissions
          2. To publish an Excel file
      3. Create the Workbook
        1. To get the data in the workbook
        2. To add another PivotTable
        3. To add conditional formatting
        4. Adding Slicers
          1. To insert slicers
          2. To format the slicers
          3. To connect the slicers to another PivotTable
          4. To add a chart
        5. Clean Up the Report
        6. Sparkline Overview
          1. To add some data and insert a PivotTable
          2. To insert the sparklines
        7. Connect the Slicer to the Sparklines
          1. To enhance the look of the sparklines
          2. To hide the PivotTable that the sparklines are summarizing
          3. To finish and save to SharePoint
      4. Viewing and Editing Workbooks in Excel Services
        1. Viewing Workbooks
        2. Editing Workbooks
        3. Excel Services and Dashboards
      5. Extending Excel Services
        1. User Defined Functions
        2. Excel Web Services
        3. ECMAScript (JavaScript, JScript) Object Model
        4. Excel Services REST
      6. Summary
    9. 5. PowerPivot for Excel and SharePoint
      1. Introduction
      2. A Brief History of PowerPivot
      3. When Do I Use PowerPivot for Excel?
      4. When Do I Use PowerPivot for SharePoint?
      5. Getting Started
        1. Installing PowerPivot for Excel
          1. To get started with PowerPivot
        2. Installing PowerPivot for SharePoint
        3. Creating a PowerPivot Workbook
          1. Importing data
          2. Importing from a Relational Database
            1. To import database data
          3. Importing from Windows Azure Marketplace DataMarket
            1. To import data from Azure DataMarket
          4. Pasting from the Clipboard
      6. Enhancing and Analyzing the Data
        1. Relationships
        2. Calculations with DAX
        3. PivotTables and PivotCharts with PowerPivot
      7. Publishing to SharePoint
        1. To publish your workbook
      8. PowerPivot Gallery
      9. Scheduling Data Refreshes
        1. Data Refresh
        2. Schedule Details
        3. Earliest Start Time
        4. E-mail Notifications
        5. Credentials
        6. Data Sources
      10. Monitoring with PowerPivot for SharePoint
        1. Infrastructure—Server Health
          1. Query Response Times
          2. Average Instance CPU
          3. Average Instance Memory
          4. Activity and Performance
        2. Workbook Activity
          1. Chart
          2. List
        3. Data Refresh
          1. Recent Activity
          2. Recent Failures
        4. Reports
      11. Summary
    10. 6. Visio and Visio Services
      1. Introduction
      2. A Brief History of Visio
        1. What Does Visio Give You?
        2. BI in Visio 2007 and Visio 2010
          1. PivotDiagram
            1. To insert a PivotDiagram into your drawing
            2. To filter data display for different levels
          2. Visio Data Selector Wizard
          3. Data Graphic
          4. Visio Viewer
      3. What’s New in Visio 2010 and Visio Services
        1. Diagram Validation in Visio 2010
        2. SharePoint 2010 Visio Services
          1. Visio Diagram Repository
          2. Process Diagrams Document Library
        3. Downloadable Add-Ins for Visio and Visio Services
      4. When Do I Use Visio and Visio Services for BI?
        1. Case Study: Global Crossing
        2. Case Study: Virgin Mobile India
      5. Configuration (Visio Services)
        1. Security (Visio Services)
          1. File Security
          2. Server Security
        2. Connecting to SharePoint Lists and Visio Services
        3. When to Use an .odc File
      6. Planning and Architecture (Visio Services)
      7. Creating the Visio Diagram
      8. Connecting to External Data and Display Data on the Shapes
        1. To create an ODC file in Excel
        2. To connect to data by using the Data Selector Wizard
        3. To create a SharePoint Data Connection Library
      9. Publishing a Visio Diagram
        1. To publish a diagram as a Web Drawing
      10. Visio Drawing Web Parts
        1. To embed a Visio Web Drawing
        2. Extending Visio Services
      11. Summary
    11. 7. PerformancePoint Services
      1. Introduction
      2. History of PerformancePoint Services
      3. Overview of PerformancePoint Services Components
        1. Data Sources
        2. Indicators
        3. KPIs
        4. Scorecards
        5. Reports
          1. Context Menu Features
        6. Dashboards
        7. Filters
        8. Parts of Dashboard Designer
        9. Other Dashboard Designer Features
          1. Dashboard Content in SharePoint Folders
          2. Dashboard Item History
      4. What’s New in PerformancePoint Services
        1. Improvements for Dashboard Authors and Users
        2. Improvements for IT Professionals
          1. Platform Integration with SharePoint Products and Technologies
          2. Security
        3. Improvements for Developers
        4. Retired Features
      5. When to Use PerformancePoint Services
        1. Available Case Studies
        2. Scenario: Tenaska
      6. PerformancePoint Services Architecture
      7. PerformancePoint Services Configuration
        1. PerformancePoint Service Application Configured
          1. To manage PerformancePoint service applications in Central Administration
          2. To list running service applications with Windows PowerShell
        2. Manage and Maintain PerformancePoint Services
        3. Import PerformancePoint 2007 Content
        4. Configure Security for PerformancePoint
          1. To configure SSS for PerformancePoint
          2. To configure the Unattended Service Account
        5. Configure Data and Content Locations
          1. To configure a trusted data source location
          2. To configure a trusted content source location
        6. Start PerformancePoint Dashboard Designer
          1. To deploy the Business Intelligence Center using the template
          2. To launch Dashboard Designer
        7. Troubleshoot SQL Server Data Source Configuration
      8. Providing a Performance Solution
        1. Design the KPIs, Scorecards, Reports, and Dashboard
        2. Create a Simple Dashboard
          1. Create a Data Source
            1. To create an Analysis Services data source
            2. To save to the workspace and refresh your data sources
          2. Create Key Performance Indicators
            1. To create a KPI
          3. Organize the Workspace Browser
            1. To create a folder for KPIs
          4. Create a Scorecard
            1. To set the scoring pattern and indicator for the KPI
            2. To create the scorecard
            3. To add dimensions to the scorecard
          5. Notes About the Scorecard
          6. Create a Filter
            1. To create a filter
          7. Add a Report
            1. To add a bar chart
          8. Create a Dashboard
            1. To create a dashboard
          9. Modify Your Zones
            1. To modify a zone
          10. Other Options in Dashboard Designer
      9. Summary
      10. Quick Reference
    12. 8. Bringing It All Together
      1. Introduction
      2. Dashboards
        1. Tools in SharePoint for Authoring Dashboards
        2. Which Dashboard Tool Should I Use?
      3. Dashboard (Web Part) Pages in SharePoint
        1. To create a dashboard page in SharePoint
      4. Use Excel Services in the Dashboard
        1. Create the Excel Workbook
          1. To add a pivot table to a workbook
          2. To add a simple chart to the workbook
        2. Prepare the Workbook for the Dashboard: Add Parameters
          1. To specify a workbook parameter
        3. Show the Workbook in Web Parts
          1. To add an Excel Web Access Web Part
          2. To configure the Web Part
        4. Set Other Web Part Properties
        5. Add More Web Parts and Finish
      5. Add a SharePoint Filter to the Page
        1. Create a Reusable Data Connection
          1. To save the connection information to SharePoint
          2. Add the Filter to the Dashboard
            1. To add an Analysis Services filter to a dashboard page
          3. Configure the Filter
            1. To configure the Analysis Services filter
        2. Connect the Filter to Other Web Parts
          1. To connect a filter to other Web Parts
        3. Add SharePoint KPIs
          1. To create a new status list
          2. To add the KPI to the dashboard
        4. Connect the Filter to the KPI
          1. To connect a filter to a KPI
        5. Add a Visio Web Drawing
          1. To create a Visio diagram
          2. To link data to your diagram
          3. To publish to SharePoint
          4. To embed the Web Drawing as a Web Part
        6. Add a PerformancePoint Web Part
          1. To start PerformancePoint Dashboard Designer
          2. To create a dashboard
          3. To add Web Parts to the Web Parts page
        7. The Web Part Page
      6. Summary
      7. Quick Reference
    13. A. Virtual Machine Setup and SharePoint Configuration
      1. Options for Software Installation and Configuration
      2. Overview of Hyper-V, for Both Options
      3. Option 1: Set Up a Pre-configured VM
        1. Download
        2. What Comes with the Download and Other Considerations
          1. Virtual Machine “a”
          2. (Optional) Virtual Machine “b”
          3. System Requirements
        3. Pre-configured VM Setup
          1. Performance Considerations
          2. Host Configuration
            1. To prepare the host machine
          3. Configure Hyper-V for the VM
            1. To configure Hyper-V for the VM
            2. To import and configure the VM
            3. To restore VM 2010-7a.vhd
            4. To configure the network adapter
          4. Snapshots and Saved State
            1. To create a snapshot
            2. To apply a snapshot
          5. Start the VM
            1. To start the 2010-7a VM
            2. To stop the VM image
          6. Activation and Expiration
            1. To reset the activation or “rearm” the VM
          7. Post-Setup Performance Tweaks
            1. To restore the Microsoft Contoso BI Demo Dataset for Retail Industry
            2. To defragment all host drives
            3. To set the host video resolution
            4. To disable virus scanning
            5. To convert the VM’s dynamic Virtual Hard Drive (VHD) to a static VHD
      4. Option 2: Set Up Your Own VMs on Windows 2008 R2
        1. Architecture for Server Configuration
        2. Select a Host Computer with Windows 2008 R2
        3. Install and Configure Windows 2008 R2 for Hyper-V
          1. Install Windows 2008 R2 on Your Physical Host Machine
          2. Configure Hyper-V
          3. Re-configure Your Virtual Networks
        4. Install Software on Your VMs
          1. Install Windows 2008 R2 on Your VMs
          2. Install SQL Server 2008 R2
          3. Install SharePoint Server 2010
      5. SharePoint 2010 Installation and Configuration
        1. Install SharePoint Server 2010
        2. Configure SharePoint Server 2010
          1. To view service applications in Central Administration
          2. To list running service applications with Windows PowerShell
      6. Security for the Services Dedicated to BI
        1. Different Names
        2. Excel and Visio Services
        3. Configure the .odc file
          1. To publish an existing Office Data Connection file (.odc) to SharePoint
        4. PerformancePoint Services
        5. Resources for Configuring Security
      7. Conclusion
    14. B. DAX Function Reference
      1. Date and Time Functions
      2. Information Functions
      3. Filter and Value Functions
      4. Logical Functions
      5. Math and Trig Functions
      6. Statistical Functions
      7. Text Functions
      8. Time Intelligence Functions
    15. C. SharePoint As a Service—“Office 365”
      1. A Basic Overview of Software, Services, and the Cloud
        1. A “Service” at the Most Basic Level
        2. A “Service” in the Computing World
        3. The Cloud
          1. Not All Clouds Are Equal
          2. The Microsoft Cloud
        4. “Software”—What It Means in a Services World
        5. Tradeoffs: Service vs. Software
          1. Key Advantages of Software
          2. Key Advantages of Services
          3. Which Is Right for You?
      2. More Traditional Productivity Applications As Services
      3. SharePoint As a Service in Microsoft Office 365
        1. What Is Office 365?
        2. Using Office
        3. Connecting the Office Client
        4. Using SharePoint Online
        5. SharePoint Online vs. SharePoint On-Premises
          1. Hardware
          2. Configuration Settings
          3. Data Connectivity in Office 365
        6. Availability of Service Applications and BI in Office 365
      4. Summary
    16. Index
    17. D. About the Authors
      1. Norm Warren
      2. Mariano Teixeira Neto
      3. John Campbell
      4. Stacia Misner
    18. About the Authors
    19. Copyright

    Product information

    • Title: Business Intelligence in Microsoft® SharePoint® 2010
    • Author(s): Mariano Teixeira Neto Norman P. Warren John Campbell, and Stacia Misner
    • Release date: April 2011
    • Publisher(s): Microsoft Press
    • ISBN: 9780735661714