Business Intelligence in Microsoft SharePoint 2013

Business Intelligence in Microsoft SharePoint 2013

by Mariano Teixeira Neto Norman P. Warren Stacia Misner, Ivan Sanders, and Scott A. Helmers
Released May 2013
Publisher(s): Microsoft Press
ISBN: 9780735675421

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 features in SharePoint 2013—and use the right combination of tools to deliver compelling solutions.

Take control of business intelligence (BI) with the tools offered by SharePoint 2013 and Microsoft SQL Server 2012. Led by a group of BI and SharePoint experts, you’ll get step-by-step instructions for understanding how to use these technologies best in specific BI scenarios—whether you’re a SharePoint administrator, SQL Server developer, or business analyst.

Discover how to:

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

  • Use web-based Microsoft Excel services and publish workbooks on a SharePoint Server

  • Mash up data from multiple sources and create Data Analysis Expressions (DAX) using PowerPivot

  • Create data-driven diagrams that provide interactive processes and context with Microsoft Visio Services

  • Use dashboards, scorecards, reports, and key performance indicators to monitor and analyze your business

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

    •

    Table of contents

    1. Table of Contents
    2. Introduction
      1. Who this book is for
        1. SharePoint administrator/developer
        2. Business user and data scientist
        3. BI developer
      2. How this book is organized
      3. What’s not covered in this book
        1. Access Services
        2. SQL Server 2012 Reporting Services in SharePoint
        3. Business Connectivity Services
        4. Duet Enterprise
        5. Web analytics
      4. Conventions used in this book
      5. Companion content
      6. System Requirements
      7. Acknowledgments
      8. Support and feedback
        1. Errata
        2. We want to hear from you
        3. Stay in touch
    3. 1. Business intelligence in SharePoint
      1. Leading up to BI
      2. Beware of losing sight of what matters most
      3. What is BI?
        1. The need for BI today
        2. What is self-service BI?
      4. Microsoft’s vision for BI and self-service BI
      5. What SharePoint does for BI
      6. The BI stack: SQL Server + SharePoint + Office
        1. Authoring in Microsoft BI tools
      7. Examples of BI in SharePoint 2013
        1. PerformancePoint and the BI stack
        2. Power Pivot and BISM Model: A Fulfillment Report for Tracking Products
          1. The story and report requirements
          2. Choosing a tool, introducing self-service BI, and planning for adoption
            1. Understanding the culture
            2. BI maturity
          3. Discovering a visual concept for a report
        3. The steps to implementation
          1. Determine data sources and importing data
          2. Import data into PowerPivot, explore data, and design
          3. Create the Power View report
          4. Publish to SharePoint
          5. Modify according to user needs
          6. Automate and formalize in SQL Server Data Tools
        4. Sharing with other teams (building user adoption)
        5. A summary of the fulfillment example
      8. Creating a report by using an Odata feed from a SharePoint list
      9. Summary
    4. 2. Planning for business intelligence adoption
      1. Business user communities
        1. Understanding your audience: 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. Power View in SharePoint
            7. PerformancePoint Services
          3. Self-service and personal BI
            1. Excel
            2. Power View in Excel
            3. Power View in SharePoint
            4. PowerPivot in Excel
            5. Report Builder
            6. Visio
      2. The progression of BI
        1. The Business Intelligence 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
      3. Tool selection
        1. Excel
        2. Excel Services
        3. Reporting Services
        4. SharePoint BI
        5. PerformancePoint Services
        6. Visio Services
      4. An action plan for adoption: Build it and they might come
        1. Self-service BI versus traditional BI
        2. Lessons learned: Adoption for self-service BI in SharePoint
      5. Summary
    5. 3. The lifecycle of a business intelligence implementation
      1. Working together: SQL Server 2012 + SharePoint 2013 + Office 2013
      2. SQL Server 2012 features
        1. 1 The SQL Server database engine
        2. 2 SQL Server Integration Services or other tools
        3. 3 The Business Intelligence Semantic Model
          1. Tabular modeling vs. multidimensional modeling
        4. 4 Additional BI tools
          1. SQL Server Reporting Services
          2. Data mining
        5. 5 SQL Server Data Tools
      3. The lifecycle of a BI implementation
        1. Step 1: Decide what to analyze, measure, or forecast
        2. Step 2: Get to trusted data
          1. What is trusted data?
          2. The data warehouse
          3. What is a data warehouse?
          4. The data warehouse vs. the data mart
          5. Facts and dimensions
          6. Moving data by using SSIS
        3. Step 3 or 4: Load data into a SSDT (Visual Studio) project
          1. Import an existing PowerPivot model
          2. Load data into the model
        4. Step 5: Model the data
          1. Excel: Test the modeled data
        5. Step 6: Deploy the model to SSAS
          1. Roles (back-end permissions)
          2. Partitions
          3. Automating data processing (refresh tabular data)
        6. Prepare to automate data processing
        7. Create the XMLA for a new job
        8. Step 7: Create a BISM file in SharePoint 2013
          1. Adding content types to SharePoint 2013 library
          2. Creating a BISM connection file to a specific tabular database
          3. Adding a BISM connection file in SharePoint 2013
      4. Summary
    6. 4. Using PowerPivot in Excel 2013
      1. The Data Model
        1. Creating the Data Model
        2. Adding data to the Data Model
        3. Creating table relationships by using the Data Model
        4. Working with the Data Model
      2. PowerPivot 2013
        1. Data refresh
        2. Compatibility issues
      3. Calculations with DAX
        1. A new DAX function
      4. Importing data from Windows Azure Marketplace
      5. Paving the ground
      6. Summary
    7. 5. Using Power View in Excel 2013
      1. Introducing Power View
        1. A brief history
        2. Comparing editions of Power View
      2. What’s new in Power View
        1. More visualizations
        2. Additional formatting options
        3. Key performance indicators
        4. New drill functionality
      3. Using Power View
        1. When do you use Power View?
        2. When do you avoid using Power View?
        3. Setting up Power View
      4. Creating visualizations
        1. Getting started
          1. Creating a Data Model
          2. Inserting a Power View Sheet
        2. Creating a table
          1. Adding fields to a table
          2. Sorting a table
          3. Resizing and moving a table
        3. Creating a matrix
          1. Converting a table to a matrix
          2. Defining the matrix layout
          3. Adding hierarchies to the matrix
        4. Creating a chart
          1. Adding a clustered bar chart
          2. Configuring multiples
          3. Interacting with a scatter chart
        5. Creating a map
          1. Converting a table to a map
          2. Drilling to details
        6. Creating cards
          1. Converting a table to cards
          2. Restructuring cards
        7. Using KPIs
          1. Defining KPIs
          2. Visualizing KPIs
      5. Filtering data
        1. Highlighting data
        2. Adding a slicer
        3. Filtering by using tiles
          1. Adding tiles to a visualization
          2. Adding a second visualization to a tiles container
        4. Using the Filter pane
          1. Creating a basic filter
          2. Creating an advanced filter
      6. Saving a Power View workbook
      7. Summary
    8. 6. Business intelligence with Excel Services 2013
      1. A brief history of Excel Services
        1. 2007: The introduction of Excel Services
          1. BI functionality
          2. Sharing and managing workbooks
          3. Extensibility
          4. Excel Web Services
          5. User-Defined Functions
        2. 2010: Expanded capabilities
          1. Continued BI support
          2. Improved extensibility
          3. The world of services
        3. 2013: Continued expansion
          1. Continued BI Support
          2. Interactive View
      2. When to use Excel Services
        1. It’s already Excel
        2. It’s fast to create and easy to adopt
        3. It is a great ad hoc tool
        4. It scales Excel files to many users
      3. The Data Model in Excel Services
      4. Configuring the server
        1. Installation
        2. Administration
        3. Excel Services security
          1. File security
          2. Server security
        4. External data configuration
          1. Configure the authentication in the workbook
      5. Opening an Excel workbook in the browser
        1. Viewing workbooks
        2. Editing workbooks
        3. Configure a simple Excel dashboard by using Web Parts
          1. Create a workbook
          2. Create the dashboard page
          3. Add the Excel Web Access Web Part
          4. Configure the Web Part
          5. Set other Web Part properties
          6. Add more Web Parts and finish
      6. Extending Excel Services
        1. UDFs
        2. Excel Web Services
        3. ECMAScript (JavaScript, JScript) object model
        4. Excel Services REST
        5. Excel Interactive View
      7. Summary
    9. 7. Using PowerPivot for SharePoint 2013
      1. A brief history
      2. When do I use PowerPivot for SharePoint?
      3. Getting started
        1. Installing PowerPivot for SharePoint
      4. Publishing to SharePoint
        1. The PowerPivot Gallery
      5. Scheduling data refreshes
        1. Data Refresh
        2. Schedule Details
        3. Earliest Start Time
        4. E-mail Notifications
        5. Credentials
        6. Data Sources
      6. Workbooks as a data source
      7. 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
      8. Summary
    10. 8. Using PerformancePoint Services
      1. A brief history of PerformancePoint Services
      2. An overview of PerformancePoint Services components
        1. Data sources
          1. Indicators
          2. KPIs
        2. Scorecards
        3. Reports
        4. Context menu features
        5. Dashboards
          1. Filters
          2. Parts of the Dashboard Designer
        6. Other features
          1. Dashboard content in SharePoint folders
          2. Permissions
          3. History of dashboard items (versioning)
          4. Workspace file
      3. What’s new in PerformancePoint Services 2013
        1. What’s new for designers
          1. New SharePoint 2013 site themes
          2. New filter enhancements
          3. New filter search
          4. New BI Center
          5. Dashboard Designer on the ribbon
        2. New for IT professionals
          1. The EffectiveUsername property
          2. Custom target applications from secure store
          3. Server-side migration
        3. When do I use PerformancePoint Services for BI?
          1. When to use PerformancePoint Services
          2. Available case studies
      4. The PerformancePoint Services architecture
      5. PerformancePoint Services configuration
        1. PerformancePoint Service Application configured
        2. Manage and maintain PerformancePoint Services
        3. Configure security for PerformancePoint
          1. Troubleshooting the SQL Server data-source configuration
          2. Configure data and content locations
        4. Start PerformancePoint Dashboard Designer
      6. Providing a performance solution
        1. Design the KPIs, scorecards, reports, and dashboard
          1. Create KPIs
          2. Organize the Workspace Browser
          3. Create a scorecard
          4. Notes about the scorecard
          5. Creating a filter
          6. Adding a report
          7. Creating a dashboard
          8. Other options in Dashboard Designer
      7. Summary
    11. 9. Using Visio and Visio Services
      1. Background
      2. What’s new in Visio 2013
      3. Six reasons to include Visio 2013 in your BI suite
        1. Linking to data
        2. Visualizing data
        3. Collaborating to create the best result
          1. Commenting
          2. Coauthoring
        4. Validating diagrams
        5. Saving as a website
        6. Saving to Visio Services
      4. When do I use Visio and Visio Services?
        1. Netaphor Software
        2. Additional case studies
      5. Incorporating Visio into a BI solution
      6. Visio Services: Example 1
        1. Organizing the data
        2. Creating the Visio diagram
        3. Visualizing data
        4. Saving to Visio Services
      7. Visio Services: Example 2
        1. Organizing the data
        2. Creating the Visio diagram
        3. Saving to Visio Services
        4. Linking to data
        5. Visualizing data
        6. Creating a Web Part page
        7. Refreshing the diagram when data changes
      8. Summary
    12. 10. Bringing it all together
      1. Dashboards
        1. Making dashboards useful
      2. Tools in SharePoint for authoring dashboards
      3. Which dashboard tool should I use?
      4. Dashboard (Web Part) pages in SharePoint
      5. Using Excel Services in the dashboard (Web Part page)
        1. Creating the Excel workbook
      6. Preparing the workbook for the dashboard: adding parameters
        1. Showing the workbook in Web Parts
        2. Setting other Web Part properties
        3. Using the filter added in Excel 2013
      7. Adding to the dashboard (Web Part page)
        1. Visio Web Access Web Part
        2. PerformancePoint Web Parts
        3. The Web Part page
      8. Summary
    13. A. Running scripts to set up a demonstration environment
      1. Hardware considerations
      2. Introducing the scripts
      3. Step 1: Install the Active Directory Demo Build 2.1
        1. Prerequisites
          1. Software requirements
          2. Hardware requirements
        2. Installing the content pack
        3. Post installation
      4. Step 2: Install the SQL 2012 SP1 Content Pack Demo Build 2.0.0
        1. Contents of SQL 2012 SP1 Content Pack Demo Build 2.0.0
        2. Prerequisites
        3. Installing the content pack
        4. Post installation
      5. Step 3: Install the SharePoint 2013 Demo Build 2.0
        1. Prerequisites
        2. Installing the content pack
        3. Post installations and known issues
      6. Step 4: Install the UserProfile Provisioning Demo 2.0
        1. Prerequisites
        2. Installing the content pack
      7. Step 5: Install the Self-Service BI Demo 2.0 Content Pack
        1. Prerequisites
        2. Installing the content pack
        3. Post installations/known issues
      8. Step 6: Install the Visio Services Demo Content Pack
        1. Prerequisites
        2. Installing the content pack
    14. B. Microsoft and “Big Data”
      1. What is Big Data?
        1. Volume
        2. Velocity
        3. Variety
        4. Comparing Big Data to electrification
        5. The “hype cycle” for Big Data
      2. The Big Data toolset
        1. Hadoop, MapReduce, and HDFS
          1. MapReduce
        2. Pig and Hive
          1. Pig
          2. Hive
        3. Other tools
        4. What is NoSQL?
        5. Big players (companies)
      3. Using Microsoft’s Big Data tools
        1. HDInsight
        2. Setting up in Windows Azure
      4. Getting value from Big Data
        1. Excel-Hive Add-in
        2. The Data Explorer for Excel Add-in (preview)
        3. Data Quality Services
      5. Summary
    15. C. About the Authors
    16. Index
    17. About the Authors
    18. Copyright

    Product information

    • Title: Business Intelligence in Microsoft SharePoint 2013
    • Author(s): Mariano Teixeira Neto Norman P. Warren Stacia Misner, Ivan Sanders, and Scott A. Helmers
    • Release date: May 2013
    • Publisher(s): Microsoft Press
    • ISBN: 9780735675421