You are previewing Pro SharePoint 2010 Business Intelligence Solutions.
O'Reilly logo
Pro SharePoint 2010 Business Intelligence Solutions

Book Description

Business Intelligence (BI) software is everywhere. It is the code and tools that allow you to view different components of a business using a single visual platform, making comprehending mountains of data easier. With SharePoint 2010, Microsoft has married one of the most-used team collaboration tools with its business intelligence products, including PerformancePoint Services and Excel Services. Now, users of SharePoint can have a single place to get a view of their business, its organization and processes, and its financial performance.

Constructing this type of portal is a process unique to each business. With Pro SharePoint 2010 Business Intelligence Solutions, authors Sahil Malik and Srini Sistla take you through the different tools, services, and frameworks available for building a BI portal atop SharePoint 2010. With comprehensive coverage of Visio Services, PerformancePoint, Excel Services, and a practical look at building scorecards, dashboards, and other reporting solutions, this book answers the fundamental questions about why and how BI can work for your business.

Table of Contents

  1. Copyright
  2. About the Authors
  3. Acknowlegments
  4. Introduction
  5. 1. Business Intelligence Basics
    1. 1.1. Introduction
    2. 1.2. What Will You Learn?
    3. 1.3. Why Intelligence?
    4. 1.4. Understanding BI
    5. 1.5. OLTP vs. OLAP
      1. 1.5.1. Online Transaction Processing System
      2. 1.5.2. Online Analytical Processing System
        1. 1.5.2.1. The Unified Dimensional Model and Data Cubes
        2. 1.5.2.2. Facts and Dimensions
        3. 1.5.2.3. Extract, Transform, and Load
        4. 1.5.2.4. Need for Staging
        5. 1.5.2.5. Transformation
        6. 1.5.2.6. MDX Scripting
          1. 1.5.2.6.1. Tuples and Sets
        7. 1.5.2.7. Putting It All Together
    6. 1.6. The BI Foundation
      1. 1.6.1. Data Warehouses
      2. 1.6.2. Data Marts
      3. 1.6.3. Decision Support Systems and Data Mining
    7. 1.7. Tools
      1. 1.7.1. SQL Server Management Studio
      2. 1.7.2. SQL Server BIDS
        1. 1.7.2.1. Transforming OLTP Data Using SSIS
        2. 1.7.2.2. Creating Cubes Using SSAS
        3. 1.7.2.3. Final Pointers for Migrating OLTP Data to OLAP
    8. 1.8. SQL Server 2008 R2 Parallel Data Warehouse
    9. 1.9. SharePoint and Business Intelligence
      1. 1.9.1. What's New in SharePoint 2010?
        1. 1.9.1.1. Secure Store Service
        2. 1.9.1.2. Visio Services
        3. 1.9.1.3. Reporting Services and Report Builder
        4. 1.9.1.4. PerformancePoint Services
        5. 1.9.1.5. Excel and Power Pivot
        6. 1.9.1.6. Business Connectivity Services
    10. 1.10. Summary
    11. 1.11. What's Next?
  6. 2. Visio Services
    1. 2.1. Introduction
    2. 2.2. What Will You Learn?
      1. 2.2.1. Why Visio?
    3. 2.3. Presenting Visio Services
      1. 2.3.1. Setting up Visio Services
      2. 2.3.2. Visio Services Features
      3. 2.3.3. Architecture
      4. 2.3.4. The Visio 2010 Client's Data Tab
        1. 2.3.4.1.
          1. 2.3.4.1.1. Link Data to Shapes
          2. 2.3.4.1.2. Data Graphics
          3. 2.3.4.1.3. Automatically Link
          4. 2.3.4.1.4. Refresh All
          5. 2.3.4.1.5. Insert Legend
          6. 2.3.4.1.6. Shape Data Window
          7. 2.3.4.1.7. External Data Window
      5. 2.3.5. Integrating Visio with SharePoint
        1. 2.3.5.1. Using the Visio Web Access Web Part
        2. 2.3.5.2. Visio Web Access Web Part Connections
        3. 2.3.5.3. Using a SQL Server Data Source
          1. 2.3.5.3.1. The Double-Hop Issue
        4. 2.3.5.4. Secure Store Services
          1. 2.3.5.4.1. Configuring Secure Store Services
        5. 2.3.5.5. Using Visio with SQL Server and SSS
        6. 2.3.5.6. Visio Services JavaScript Mashup API
          1. 2.3.5.6.1. Understanding the assets.js Code
        7. 2.3.5.7. Working with SharePoint Designer 2010
        8. 2.3.5.8. Creating Your Own Custom Data Providers
    4. 2.4. Administration of Visio Services
      1. 2.4.1.
        1. 2.4.1.1. Creating a New Visio Graphics Service Application
        2. 2.4.1.2. Deleting an Existing Visio Graphics Service Application
        3. 2.4.1.3. List all Existing Visio Graphics Service Applications
        4. 2.4.1.4. Creating a New Visio Graphics Service Application Proxy
        5. 2.4.1.5. Delete an Existing Visio Graphics Service Application Proxy
        6. 2.4.1.6. List All Existing Visio Graphics Service Application Proxies
        7. 2.4.1.7. Setting Up Visio Graphics Service Global Settings
        8. 2.4.1.8. Setting Up a Graphics Service Trusted Data Provider
      2. 2.4.2. Factors Affecting the Performance of Visio Services
    5. 2.5. Summary
    6. 2.6. What's Next?
  7. 3. Reporting Services
    1. 3.1. What Will You Learn in This Chapter?
    2. 3.2. Software Prerequisites
    3. 3.3. Introduction
    4. 3.4. Architecture
      1. 3.4.1. Choosing the Right SSRS Mode
    5. 3.5. Installation
      1. 3.5.1. Configure Report Server
        1. 3.5.1.1. Reporting Services Add-In Features
        2. 3.5.1.2. Reporting Services Add-in New Features in SQL Server 2008 R2
      2. 3.5.2. Configure SharePoint Server for Reporting Services
        1. 3.5.2.1. Reporting Services Integration Settings
        2. 3.5.2.2. Add a Report Server to the Integration
        3. 3.5.2.3. Set Server Defaults
        4. 3.5.2.4. Report Life Cycle
        5. 3.5.2.5. Data Source Management
      3. 3.5.3. Authoring Tools
    6. 3.6. Building and Publishing a Basic Report
      1. 3.6.1. Using Report Builder to Edit a Published Report
      2. 3.6.2. Loading Reports using the Report Viewer Web Part
      3. 3.6.3. Reporting Services Content Types
      4. 3.6.4. Subscription to Reports
      5. 3.6.5. Integration with SharePoint list
    7. 3.7. Access Services
      1. 3.7.1. Configuring Reporting Services for Access Services
      2. 3.7.2. Summary
      3. 3.7.3. What's Next?
  8. 4. Business Connectivity Services
    1. 4.1. What Will You Learn in This Chapter?
    2. 4.2. Software Prerequisites
    3. 4.3. BCS Terminology
    4. 4.4. Introduction
    5. 4.5. Setting Up Business Data Connectivity Services
      1. 4.5.1.
        1. 4.5.1.1. Set Up a New BDC Service Application
        2. 4.5.1.2. Views
        3. 4.5.1.3. BDC Models
          1. 4.5.1.3.1. Import, Export and Delete
        4. 4.5.1.4. Permissions
          1. 4.5.1.4.1. Set Object Permissions
          2. 4.5.1.4.2. Set Metadata Store Permissions
        5. 4.5.1.5. Manage
          1. 4.5.1.5.1. Settings
          2. 4.5.1.5.2. Delete
        6. 4.5.1.6. Profile Pages
          1. 4.5.1.6.1. Configure
          2. 4.5.1.6.2. Create or Upgrade
          3. 4.5.1.6.3. Business Connectivity Services Layer
          4. 4.5.1.6.4. External System Layer
          5. 4.5.1.6.5. Office Client Layer
        7. 4.5.1.7. Tools Decision Tree
        8. 4.5.1.8. External Content Types
      2. 4.5.2. Creating Your First ECT Using SharePoint Designer
        1. 4.5.2.1. Configure External Content Type Permissions
        2. 4.5.2.2. Configure ECT Data Connections with Secure Store Service
      3. 4.5.3. Authoring an ECT Using Visual Studio with a .NET Connector
        1. 4.5.3.1. Creating an ECT Using SharePoint Designer and .NET Type
      4. 4.5.4. BDC Runtime
      5. 4.5.5. Integration with Office Applications
        1. 4.5.5.1. Integration with Office Word
        2. 4.5.5.2. Integration with SharePoint Workspace
      6. 4.5.6. PowerShell Commands
      7. 4.5.7. Summary
      8. 4.5.8. What's Next?
  9. 5. Excel Services
    1. 5.1. What Will You Learn in This Chapter?
    2. 5.2. Software Prerequisites
    3. 5.3. Introduction
      1. 5.3.1. Architecture
      2. 5.3.2. Setting up Excel Services
        1. 5.3.2.1. Global Settings
          1. 5.3.2.1.1. Security
          2. 5.3.2.1.2. Load Balancing
          3. 5.3.2.1.3. Session Management
          4. 5.3.2.1.4. Memory Utilization
          5. 5.3.2.1.5. Workbook Cache
          6. 5.3.2.1.6. External Data
        2. 5.3.2.2. Trusted File Locations
          1. 5.3.2.2.1. Location
          2. 5.3.2.2.2. Session Management
          3. 5.3.2.2.3. Workbook Properties
          4. 5.3.2.2.4. Calculation Behavior
          5. 5.3.2.2.5. External Data
          6. 5.3.2.2.6. User-Defined Functions
        3. 5.3.2.3. Trusted Data Providers
        4. 5.3.2.4. Trusted Data Connection Libraries
        5. 5.3.2.5. User-Defined Function Assemblies
      3. 5.3.3. Authoring Your First Excel Sheet and Publishing it to Excel Services
      4. 5.3.4. What's New in Excel Services 2010
      5. 5.3.5. PowerShell Commands
        1. 5.3.5.1. Backup and Recovery Operation
      6. 5.3.6. Authoring and Publishing
      7. 5.3.7. Excel Services REST API
      8. 5.3.8. Excel Services SOAP API
      9. 5.3.9. Excel Services and SSAS
        1. 5.3.9.1. Configuring Secure Store Services
      10. 5.3.10. PowerPivot for Excel
        1. 5.3.10.1. Setting Up PowerPivot
      11. 5.3.11. Excel Services and ECMAScript
      12. 5.3.12. Plan and Know Excel Services
      13. 5.3.13. Summary
      14. 5.3.14. What's Next?
  10. 6. PerformancePoint Services
    1. 6.1. What Will You Learn in This Chapter?
    2. 6.2. Software Prerequisites
    3. 6.3. Introduction
    4. 6.4. PerformancePoint Services Architecture
      1. 6.4.1. Service Components and Interfaces
        1. 6.4.1.1. SharePoint Content Lists and Libraries
        2. 6.4.1.2. PerformancePoint Service Databases
        3. 6.4.1.3. SQL Server Analysis Services (SSAS) Cubes
        4. 6.4.1.4. Other Reporting Data Sources
      2. 6.4.2. Securing PerformancePoint Solutions
      3. 6.4.3. Business Intelligence Solution Components
        1. 6.4.3.1. BI Component Types
          1. 6.4.3.1.1. Dashboards
          2. 6.4.3.1.2. Indicators and Key Performance Indicators
          3. 6.4.3.1.3. Data Sources
          4. 6.4.3.1.4. Filters
          5. 6.4.3.1.5. Scorecards
          6. 6.4.3.1.6. Reports
        2. 6.4.3.2. PerformancePoint SharePoint Components
          1. 6.4.3.2.1. PPS Content Types
          2. 6.4.3.2.2. List and Library Templates
          3. 6.4.3.2.3. Web Parts
          4. 6.4.3.2.4. Business Intelligence Center Site Template
          5. 6.4.3.2.5. Features
    5. 6.5. Setting Up PerformancePoint Services
      1. 6.5.1. Application Settings
        1. 6.5.1.1. Secure Store and Unattended Service Account
        2. 6.5.1.2. Comments
        3. 6.5.1.3. Cache
        4. 6.5.1.4. Data Sources
        5. 6.5.1.5. Filters
        6. 6.5.1.6. Select Measure Control
        7. 6.5.1.7. Show Details
        8. 6.5.1.8. Decomposition Tree
      2. 6.5.2. Trusted Data Source Locations
      3. 6.5.3. Trusted Content Locations
      4. 6.5.4. Import PerformancePoint Server 2007 Content
    6. 6.6. Managing PerformancePoint with PowerShell
    7. 6.7. Authoring and Publishing PerformancePoint Solutions
      1. 6.7.1. Deploying the Business Intelligence Center
      2. 6.7.2. Creating a Dashboard Designer Workspace
      3. 6.7.3. Creating a Data Source
      4. 6.7.4. Creating Key Performance Indicators
      5. 6.7.5. Creating a Scorecard
      6. 6.7.6. Creating Filters
      7. 6.7.7. Creating Reports
      8. 6.7.8. Creating a Dashboard
      9. 6.7.9. Deploying the Solution to SharePoint, and Exploring
    8. 6.8. Advanced Report Types
      1. 6.8.1. Create an Excel Services Report
      2. 6.8.2. Create a Strategy Map Report
    9. 6.9. Summary