Developing Business Intelligence Apps for SharePoint

David Feldman is a software development manager in the defense industry. He is an industry-recognized expert in SharePoint, SSAS, Silverlight and SQL. Built a world-class technical organization of 40+ Microsoft Certified developers and infrastructure architects.

Jason Himmelstein, MCTS, MCITP, is the SharePoint Practice Director for Sentri, Inc. and has been working with Microsoft Server technologies since 1998 when he was supporting Motorola in Austin, TX. He went to work for the University of Texas for 5 years where he developed expertise in Active Directory and Exchange. He left UT to go on the road doing work for Dell in a partnership with Microsoft doing architecture, design and implementation of Active Directory and Exchange. He started working with SharePoint in 2007 as an Enterprise Architect for Raytheon Company. Today he leads a team of dynamic SharePoint Consultants for Sentri, a four-time Microsoft Partner of the Year.

Chapter 1 SharePoint, Apps, and Business Intelligence
So What Does All This Have to Do with Business Intelligence?
Chapter 2 Choosing the Right Tools for the Job
Technology Selection Goals
Solution Components
Visual Studio LightSwitch
SharePoint Server 2010-2013
SharePoint Development: Past, Present, and Future
SQL Server 2012 for Business Intelligence
Summary
Chapter 3 Basic Concepts of Relational Database Design
Normalization
Many-to-Many Relationships
Summary
Chapter 4 Why You Need LightSwitch
Traditional SharePoint Development Is Difficult
Custom Development Is Tedious
Build Custom Apps, Coding Optional
Chapter 5 Start with Data
Defining Basic Fields and Data Types
Using the Properties Window
Enhancing a String with a Choice List
Setting Default Values
Adding Relationships
Business Types
Defining Uniqueness
Practicing What We Just Learned
Calculated Computed Properties
Advanced Relationships
Summary
Chapter 6 Screens: The LightSwitch User Interface
Creating Screens
Creating a Modal Window
Summary
Chapter 7 Adding Business Logic
Change Tracking in LightSwitch
Factoring Out Repeated Logic
Customizing the Add and Edit Buttons
Designing Running Screens
Creating a Custom Details Page
Custom Validation
Summary
Chapter 8 Application Security, Access Control, and Personalizing Your Application
Enabling Authentication: Windows or Forms
Adding a Welcome Message Using Our ViewModel
Summary
Chapter 9 Running and Debugging Our Application
The LightSwitch Runtime Experience
The LightSwitch Grid Control
Search
Running as a Web Application
Summary
Chapter 10 LightSwitch with SharePoint Data
Logical SharePoint Architecture
Adding a SharePoint Data Source
Populating the Knowledge Base
Summary
Chapter 11 Deploying Your LightSwitch Application
The Application Designer
The Publishing Wizard
Deploying Your Packages to the Server
Summary
Chapter 12 Introduction to Business Intelligence
What Is Business Intelligence?
Applications of Business Intelligence
Microsoft’s Tools for Business Intelligence
Summary
Chapter 13 Business Intelligence Semantic Model (BISM)
Why Business Intelligence Semantic Model?
BISM Design Goals
Business Intelligence Semantic Model Architecture
Consuming Data from OData Sources
How Do Existing Analysis Services Applications Translate to the New Semantic Model?
Pros and Cons of the New BI Tabular Data Model
How Do the Data Access Methodologies Stack Up?
Business Logic
DAX Syntax
Getting Started with DAX
Chapter 14 Populating Sample Data into Our Database
Downloading Adventure Works Data from Microsoft
Attaching the Database
Importing People from Adventure Works
Synthesizing Help Desk Queues from Adventure Works
Importing Tickets from Adventure Works
Review the Results
Chapter 15 Building the Help Desk Tabular Cube
Importing SQL Server Data into PowerPivot
Connecting Excel to the PowerPivot Model
Importing Data from the Windows Azure Marketplace DataMarket
Summary
Chapter 16 Enriching the Cube: Relationships and DAX
Relationships in PowerPivot
Manually Adding Relationships
Traversing Relationships with DAX
Hiding Columns and Tables from Client Tools
Using DAX to Aggregate Rows in a Related Table
Calculating Earliest and Latest Related Dates with DAX
Parsing Strings with DAX
Counting and Aggregating Related Rows with DAX
Count of Distinct Values with DAX
Calculating the Difference Between Dates with DAX
Adding a Measure from the Excel Side
Counting Rows Across an Inactive Relationship
Creating a Hierarchy for Dates
Looking Up Related Data Without an Active Relationship
Summary
Chapter 17 Deploying to SharePoint
Sharing with Your Team
Summary
Chapter 18 SQL Server Analysis Services (SSAS)
Scalability
Manageability
Security
Development Tools
Direct Feature Comparison
Upgrading a PowerPivot Workbook to a Tabular Model
Validating the Deployment
Automating Processing Your Cube
Summary
Chapter 19 PivotTable Basics
Meaning from Data
The Universal Business Intelligence Tool
PivotTables
Ranking Largest to Smallest
Percentage of Parent Row
Filtering and Sorting PivotTable Dimensions
Visual Totals
Values on Rows
PivotCharts
Summary
Chapter 20 Slicers
Inserting an Additional PivotTable
Connecting Additional PivotTables to Slicers
Summary
Chapter 21 Formatting
Custom Slicer Formatting
Disabling Gridlines and Headings
Formatting PivotTables and PivotCharts
Summary
Chapter 22 PivotTable Named Sets
Scenario: Last Four Years of Ticket Counts and Total Average Time to Closure
Reusing a Named Set for Another Chart
Summary
Chapter 23 Sparklines and Data Bars
Sparklines: Intense, Simple, Word-Sized Graphics
Adding a Data Bar
Summary
Chapter 24 Configuring a Gallery for Reporting Services, Power View, and Excel Services
Enabling Required Features
Creating the PowerPivot Gallery
Enabling Business Intelligence Content Types
Setting Up Your Default View
Summary
Chapter 25 Reporting Services Basics
What Is Reporting Services?
Report Architecture
Creating a Reporting Services Data Source
Launching Report Builder 3.0
Creating Datasets
Creating a Reporting Services Report
Adding a Chart
Consuming an OData Feed from Reporting Services
Summary
Chapter 26 Advanced Reporting Services Charting
Create a Drill-Down Report
Two Approaches to Drill-Down Reporting
Basic Drill-Down Reports
Advanced Pop-Up Window Drill-Down Report
Summary
Chapter 27 Subscriptions and Data Alerts
Report Subscriptions and Delivery
How Does It Work?
Setting Up a Report
Common Scenarios for Subscriptions
Data Alerts
Summary
Chapter 28 Excel Services and Power View
What Is Excel Services?
What Is Power View?
Why Are We Talking About Excel Services and Power View Together?
Publishing a PowerPivot Model to Excel Services
Using Excel Services 2013 as an OData Feed
Using Power View on a PowerPivot Model
Saving Your Power View
Exporting to PowerPoint
Connecting to Tabular Cubes
Summary
Chapter 29 What’s Next for Excel and Power View 2013
Quick Explore
PowerPivot and Power View Are Built into Excel 2013
Power View Maps
Power View Hierarchies and Drill Down
Power View Pie Charts, Slices, and Drill Down
Enabling Tabular Drill Down with the Matrix
Key Performance Indicators
Chapter 30 Architecture to Support SharePoint BI
SharePoint Architecture with SQL 2012 BI
SharePoint 2013 Changes
Summary
Chapter 31 The Infrastructure
The Environment
Microsoft SharePoint 2010
Visual Studio LightSwitch 2011
SQL Server
SQL Server Licensing
Summary
Chapter 32 Your Environment
Server Requirements
Logical Requirements
Recommended Specs
Summary
Chapter 33 Active Directory
Before Setting Up Active Directory
Creating the Active Directory
Managing Service Accounts
Security in Active Directory
Summary
Chapter 34 Visual Studio LightSwitch
Visual Studio LightSwitch Client-Side Installation
Visual Studio LightSwitch Server-Side Implementation
Summary
Chapter 35 Installing the BI Components for SharePoint
SQL 2012 for PowerPivot on the App Tier
Summary
Chapter 36 PowerPivot Instance Configuration
Initial PowerPivot Instance Configuration
Summary
Chapter 37 PowerPivot Service Application Configuration
PowerPivot Management Dashboard Setup
Request Allocation
Using the PowerPivot Management Dashboard
Summary
Chapter 38 Excel Services Configuration
Excel Services and the Secure Store
Allow Cross-Domain Access
Summary
Chapter 39 Office Client Configuration of PowerPivot and Power View
Getting Started
The Light-Up Story for PowerPivot and Power View in Office 2013
Power View Light Up on SharePoint
Summary
Chapter 40 SQL Server Reporting Services Configuration
Provisioning Subscriptions and Alerts
Email Configuration
Key Management
Leveraging EffectiveUserName
Summary
Colophon