Developing Business Intelligence Apps for SharePoint

Developing Business Intelligence Apps for SharePoint

by David Feldman, Jason Himmelstein
Released July 2013
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781449324674

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

Create dynamic business intelligence (BI) solutions for SharePoint faster and with more capabilities than previously possible. With this book, you’ll learn the entire process—from high-level concepts to development and deployment—for building data-rich BI applications with Visual Studio LightSwitch, SQL Server 2012, and a host of related Microsoft technologies.

You’ll learn practical techniques and patterns necessary to use all of these technologies together as you build an example application through the course of the book, step by step. Discover how to solve real problems, using BI solutions that will evolve to meet future needs.

  • Learn the fundamentals of SharePoint, LightSwitch, and SQL Server 2012
  • Get a solid grounding in BI application basics and database design principles
  • Use LightSwitch to build a help desk app, including data model design and SharePoint data integration
  • Build a tabular cube with Microsoft’s Business Intelligence Semantic Model (BISM)
  • Dive into the data visualization stack, including Excel and SQL Server Reporting Services
  • Create reports with Excel Services, Report Builder, and PowerView
  • Use tips and tricks for setting up your BI application development environment

Publisher resources

View/Submit Errata

Table of contents

  1. Developing Business Intelligence Apps for SharePoint
  2. Preface
    1. Why You Need to Read This Book
    2. Business Application Components
    3. The Example Application
    4. Conventions Used in This Book
    5. Using Code Examples
    6. Safari® Books Online
    7. How to Contact Us
    8. Acknowledgments
  3. I.
    1. 1. SharePoint, Apps, and Business Intelligence
      1. So What Does All This Have to Do with Business Intelligence?
    2. 2. Choosing the Right Tools for the Job
      1. Technology Selection Goals
      2. Solution Components
      3. Visual Studio LightSwitch
      4. SharePoint Server 2010-2013
      5. SharePoint Development: Past, Present, and Future
        1. Farm Solutions: SharePoint 2007
        2. Sandbox Solutions and CSOM: SharePoint 2010
        3. The Cloud App Model with Apps for SharePoint: SharePoint 2013
        4. Our Strategy
      6. SQL Server 2012 for Business Intelligence
      7. Summary
    3. 3. Basic Concepts of Relational Database Design
      1. Normalization
        1. First Normal Form: Stop repeating yourself
        2. Second Normal Form: The Whole Key
        3. Third Normal Form: Nothing but the Key
        4. Bringing It All Together
      2. Many-to-Many Relationships
      3. Summary
  4. II.
    1. 4. Why You Need LightSwitch
      1. Traditional SharePoint Development Is Difficult
      2. Custom Development Is Tedious
      3. Build Custom Apps, Coding Optional
    2. 5. Start with Data
      1. Defining Basic Fields and Data Types
      2. Using the Properties Window
      3. Enhancing a String with a Choice List
      4. Setting Default Values
      5. Adding Relationships
      6. Business Types
      7. Defining Uniqueness
      8. Practicing What We Just Learned
      9. Calculated Computed Properties
      10. Advanced Relationships
      11. Summary
    3. 6. Screens: The LightSwitch User Interface
      1. Creating Screens
        1. Using Read-Only Controls
        2. Displaying Related Fields
        3. Launching the Application for the First Time
      2. Creating a Modal Window
      3. Summary
    4. 7. Adding Business Logic
      1. Change Tracking in LightSwitch
      2. Factoring Out Repeated Logic
      3. Customizing the Add and Edit Buttons
      4. Designing Running Screens
      5. Creating a Custom Details Page
      6. Custom Validation
      7. Summary
    5. 8. Application Security, Access Control, and Personalizing Your Application
      1. Enabling Authentication: Windows or Forms
      2. Adding a Welcome Message Using Our ViewModel
        1. Adding a Query to the Model
        2. Add the Query to the Screen (ViewModel)
        3. Binding to Query Parameters
      3. Summary
    6. 9. Running and Debugging Our Application
      1. The LightSwitch Runtime Experience
      2. The LightSwitch Grid Control
        1. Sorting
        2. Export to Excel
      3. Search
        1. What’s Really Happening Between Our Client and the Server?
      4. Running as a Web Application
      5. Summary
    7. 10. LightSwitch with SharePoint Data
      1. Logical SharePoint Architecture
      2. Adding a SharePoint Data Source
        1. Relating to SharePoint Data
      3. Populating the Knowledge Base
      4. Summary
    8. 11. Deploying Your LightSwitch Application
      1. The Application Designer
      2. The Publishing Wizard
        1. What Kind of Application Do You Want to Deploy?
        2. Where Will the Application’s Services Be Hosted?
        3. Remotely Publish the App or Just Package It for Now?
        4. Do You Need an SSL Certificate?
        5. How Will You Deploy Your Database?
        6. Should LightSwitch Create an Application Administrator Account?
        7. Specify Other Connection Information
        8. Signing Your Code
        9. Ready to Deploy
      3. Deploying Your Packages to the Server
        1. A First Look at the Database
      4. Summary
  5. III.
    1. 12. Introduction to Business Intelligence
      1. What Is Business Intelligence?
      2. Applications of Business Intelligence
      3. Microsoft’s Tools for Business Intelligence
        1. SQL Server Database Engine
        2. SQL Server Analysis Services (Prior to 2012)
        3. PowerPivot for Excel and SharePoint
      4. Summary
    2. 13. Business Intelligence Semantic Model (BISM)
      1. Why Business Intelligence Semantic Model?
      2. BISM Design Goals
      3. Business Intelligence Semantic Model Architecture
      4. Consuming Data from OData Sources
      5. How Do Existing Analysis Services Applications Translate to the New Semantic Model?
      6. Pros and Cons of the New BI Tabular Data Model
      7. How Do the Data Access Methodologies Stack Up?
        1. xVelocity (Tabular)
        2. MOLAP (UDM)
        3. ROLAP (UDM)
        4. DirectQuery (Tabular)
      8. Business Logic
      9. DAX Syntax
      10. Getting Started with DAX
    3. 14. Populating Sample Data into Our Database
      1. Downloading Adventure Works Data from Microsoft
      2. Attaching the Database
      3. Importing People from Adventure Works
      4. Synthesizing Help Desk Queues from Adventure Works
      5. Importing Tickets from Adventure Works
      6. Review the Results
    4. 15. Building the Help Desk Tabular Cube
      1. Importing SQL Server Data into PowerPivot
      2. Connecting Excel to the PowerPivot Model
      3. Importing Data from the Windows Azure Marketplace DataMarket
      4. Summary
    5. 16. Enriching the Cube: Relationships and DAX
      1. Relationships in PowerPivot
      2. Manually Adding Relationships
      3. Traversing Relationships with DAX
      4. Hiding Columns and Tables from Client Tools
      5. Using DAX to Aggregate Rows in a Related Table
      6. Calculating Earliest and Latest Related Dates with DAX
      7. Parsing Strings with DAX
      8. Counting and Aggregating Related Rows with DAX
      9. Count of Distinct Values with DAX
      10. Calculating the Difference Between Dates with DAX
      11. Adding a Measure from the Excel Side
      12. Counting Rows Across an Inactive Relationship
      13. Creating a Hierarchy for Dates
      14. Looking Up Related Data Without an Active Relationship
      15. Summary
    6. 17. Deploying to SharePoint
      1. Sharing with Your Team
      2. Summary
    7. 18. SQL Server Analysis Services (SSAS)
      1. Scalability
      2. Manageability
      3. Security
      4. Development Tools
      5. Direct Feature Comparison
      6. Upgrading a PowerPivot Workbook to a Tabular Model
      7. Validating the Deployment
      8. Automating Processing Your Cube
      9. Summary
  6. IV.
    1. 19. PivotTable Basics
      1. Meaning from Data
      2. The Universal Business Intelligence ToolComment [RR1]: Should the first ...pages be a chapter on Excel 2012 or maybe even an Appendix...it seems like a bunch of great but individual things you can do with you data...no flow...Comment [DMF2]: This is the chapter you broke from one huge chapter into many. I think the chapter just needs to be renamed as its really just pivot table basics. Excel continues for several chapters and excel services isn’t in this chapter anymoreComment [GM3]: I renamed it Pivot Table Basics.
      3. PivotTables
      4. Ranking Largest to Smallest
      5. Percentage of Parent Row
      6. Filtering and Sorting PivotTable Dimensions
      7. Visual Totals
      8. Values on Rows
      9. PivotCharts
      10. Summary
    2. 20. Slicers
      1. Inserting an Additional PivotTable
      2. Connecting Additional PivotTables to Slicers
      3. Summary
    3. 21. Formatting
      1. Custom Slicer Formatting
      2. Disabling Gridlines and Headings
      3. Formatting PivotTables and PivotCharts
      4. Summary
    4. 22. PivotTable Named Sets
      1. Scenario: Last Four Years of Ticket Counts and Total Average Time to Closure
      2. Reusing a Named Set for Another Chart
      3. Summary
    5. 23. Sparklines and Data Bars
      1. Sparklines: Intense, Simple, Word-Sized Graphics
      2. Adding a Data Bar
      3. Summary
    6. 24. Configuring a Gallery for Reporting Services, Power View, and Excel Services
      1. Enabling Required Features
      2. Creating the PowerPivot Gallery
      3. Enabling Business Intelligence Content Types
      4. Setting Up Your Default View
      5. Summary
    7. 25. Reporting Services Basics
      1. What Is Reporting Services?
      2. Report Architecture
      3. Creating a Reporting Services Data Source
      4. Launching Report Builder 3.0
      5. Creating Datasets
      6. Creating a Reporting Services Report
      7. Adding a Chart
      8. Consuming an OData Feed from Reporting Services
      9. Summary
    8. 26. Advanced Reporting Services Charting
      1. Create a Drill-Down Report
        1. Creating a Basic Chart
        2. Applying Predefined Styles
        3. Size the Chart and Preview
        4. Adding a Parameter to the Report
        5. Applying Dataset Filters
        6. Construct a Filter Expression
        7. Validating the Parameterized Report
        8. Creating a Dynamic Chart Title
        9. Headers and Footers
      2. Two Approaches to Drill-Down Reporting
      3. Basic Drill-Down Reports
      4. Advanced Pop-Up Window Drill-Down Report
      5. Summary
    9. 27. Subscriptions and Data Alerts
      1. Report Subscriptions and Delivery
      2. How Does It Work?
      3. Setting Up a Report
      4. Common Scenarios for Subscriptions
      5. Data Alerts
        1. How Data Alerts Work
        2. Managing Data Alerts
        3. Creating a New Data Alert
      6. Summary
    10. 28. Excel Services and Power View
      1. What Is Excel Services?
      2. What Is Power View?
      3. Why Are We Talking About Excel Services and Power View Together?
      4. Publishing a PowerPivot Model to Excel Services
      5. Using Excel Services 2013 as an OData Feed
      6. Using Power View on a PowerPivot Model
      7. Saving Your Power View
      8. Exporting to PowerPoint
      9. Connecting to Tabular CubesComment [GM4]: AU: This section needs some fleshing out. Can you review it?Comment [GM5]: PROD: The author fleshed out this section. I made some grammatical corrections; can you reapply styles (body text and num list)?
      10. Summary
    11. 29. What’s Next for Excel and Power View 2013
      1. Quick Explore
        1. Trend Chart
        2. Drill To
          1. Timeline control
      2. PowerPivot and Power View Are Built into Excel 2013
        1. Enabling the PowerPivot and Power View Add-ins
        2. Adding a Power View Report
      3. Power View Maps
        1. Maps with Pie Charts
      4. Power View Hierarchies and Drill DownComment [GM3]: AU: Please add content between the two headings, or combine the headings into one.Comment [DMF4]: Removed the 2nd
      5. Power View Pie Charts, Slices, and Drill Down
      6. Enabling Tabular Drill Down with the Matrix
      7. Key Performance Indicators
        1. Building an Average Days to Closure KPI
        2. Surfacing KPIs in Power View
        3. Filtering a KPI Table with a Pie Chart
        4. Adding a Trend Chart
        5. Bringing It All Together
      8. SummaryComment [GM10]:
  7. V.
    1. 30. Architecture to Support SharePoint BI
      1. SharePoint Architecture with SQL 2012 BI
        1. Content, Service Application, and Configuration Databases
        2. Reporting Services (SSRS)
        3. Excel Services
        4. PowerPivot and Power View
      2. SharePoint 2013 Changes
        1. SQL Server Analysis Services (SSAS)
        2. The BI Light-Up Story
      3. Summary
    2. 31. The Infrastructure
      1. The Environment
        1. Active Directory
        2. SharePoint Web Server
        3. SharePoint App Server
        4. SQL Database Server
        5. Development Machine
      2. Microsoft SharePoint 2010
      3. Visual Studio LightSwitch 2011
        1. The LightSwitch Development Environment
        2. LightSwitch Server Runtime Components
      4. SQL Server
        1. SQL Server Analysis Services
        2. SQL Reporting Services
        3. SQL Server Database Engine
      5. SQL Server Licensing
      6. Summary
    3. 32. Your Environment
      1. Server Requirements
        1. Physical Versus Virtual
        2. Single Server Versus Multi-Server
      2. Logical Requirements
      3. Recommended Specs
      4. Summary
    4. 33. Active Directory
      1. Before Setting Up Active Directory
      2. Creating the Active Directory
      3. Managing Service Accounts
        1. Service Accounts Management
        2. Managed Service Accounts: Windows Server Version
          1. Configuring SQL using Managed Service Accounts
          2. The Windows Server Team’s Virtual Accounts
        3. Managed Service Accounts: SharePoint Team Edition
      4. Security in Active Directory
        1. Setting Group Policy
      5. Summary
    5. 34. Visual Studio LightSwitch
      1. Visual Studio LightSwitch Client-Side Installation
        1. Microsoft Minimum Requirements for Running Visual Studio LightSwitch
        2. Experiential Recommendation for Visual Studio LightSwitch Requirements
        3. Client-Side Installation of Visual Studio LightSwitch
      2. Visual Studio LightSwitch Server-Side Implementation
        1. Installing Visual Studio LightSwitch Server-Side Extensions
      3. Summary
    6. 35. Installing the BI Components for SharePoint
      1. SQL 2012 for PowerPivot on the App Tier
        1. Upgrading from PowerPivot 2008 R2 to 2012
        2. Clean Install of PowerPivot 2012
      2. Summary
    7. 36. PowerPivot Instance Configuration
      1. Initial PowerPivot Instance Configuration
      2. Summary
    8. 37. PowerPivot Service Application Configuration
      1. PowerPivot Management Dashboard Setup
      2. Request Allocation
        1. Load Balancing with xVelocity
        2. Caching: xVelocity Versus Disk Cache
      3. Using the PowerPivot Management Dashboard
      4. Summary
    9. 38. Excel Services ConfigurationComment [GM1]: PROD: Global - move figures to appear after their references in text.
      1. Excel Services and the Secure Store
        1. What Is the Secure Store Service?
        2. Configuring the Unattended Service Account for Excel Services
      2. Allow Cross-Domain Access
      3. Summary
    10. 39. Office Client Configuration of PowerPivot and Power View
      1. Getting Started
        1. PowerPivot Version 2
      2. The Light-Up Story for PowerPivot and Power View in Office 2013
      3. Power View Light Up on SharePoint
      4. Summary
    11. 40. SQL Server Reporting Services Configuration
      1. Provisioning Subscriptions and Alerts
      2. Email Configuration
      3. Key Management
      4. Leveraging EffectiveUserName
      5. Summary
  8. Index
  9. About the Authors
  10. Colophon
  11. Copyright

Product information

  • Title: Developing Business Intelligence Apps for SharePoint
  • Author(s): David Feldman, Jason Himmelstein
  • Release date: July 2013
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781449324674