Developing Business Intelligence Apps for SharePoint

591 Pages·2013·38.31 MB·English

by  David Feldman

Developing Business Intelligence
Apps for SharePoint
David Feldman and Jason Himmelstein
Developing Business Intelligence Apps for SharePoint
by David Feldman and Jason Himmelstein
Copyright © 2013 Jason Himmelstein, David Feldman. All rights reserved.
Printed in the United States of America.
Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472.
O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are
also available for most titles (http://my.safaribooksonline.com). For more information, contact our corporate/
institutional sales department: 800-998-9938 or [email protected].
Editor: Rachel Roumeliotis Indexer: Ellen Troutman Zaig
Production Editor: Christopher Hearse Cover Designer: Randy Comer
Copyeditor: Gillian McGarvey Interior Designer: David Futato
Proofreader: Charles Roumeliotis Illustrator: Rebecca Demarest
June 2013: First Edition
Revision History for the First Edition:
2013-06-28: First release
See http://oreilly.com/catalog/errata.csp?isbn=9781449320836 for release details.
Nutshell Handbook, the Nutshell Handbook logo, and the O’Reilly logo are registered trademarks of O’Reilly
Media, Inc. Developing Business Intelligence Apps for SharePoint, the image of an Eastern Kingbird, and
related trade dress are trademarks of O’Reilly Media, Inc.
Many of the designations used by manufacturers and sellers to distinguish their products are claimed as
trademarks. Where those designations appear in this book, and O’Reilly Media, Inc., was aware of a trade‐
mark claim, the designations have been printed in caps or initial caps.
While every precaution has been taken in the preparation of this book, the publisher and authors assume
no responsibility for errors or omissions, or for damages resulting from the use of the information contained
herein.
ISBN: 978-1-449-32083-6
[LSI]
Table of Contents
Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
Part I.
1. SharePoint, Apps, and Business Intelligence. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
So What Does All This Have to Do with Business Intelligence? 4
2. Choosing the Right Tools for the Job. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Technology Selection Goals 5
Solution Components 6
Visual Studio LightSwitch 6
SharePoint Server 2010-2013 10
SharePoint Development: Past, Present, and Future 11
Farm Solutions: SharePoint 2007 12
Sandbox Solutions and CSOM: SharePoint 2010 13
The Cloud App Model with Apps for SharePoint: SharePoint 2013 14
Our Strategy 16
SQL Server 2012 for Business Intelligence 16
Summary 17
3. Basic Concepts of Relational Database Design. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Normalization 19
First Normal Form: Stop repeating yourself 20
Second Normal Form: The Whole Key 22
Third Normal Form: Nothing but the Key 24
Bringing It All Together 25
Many-to-Many Relationships 25
iii
Summary 26
Part II.
4. Why You Need LightSwitch. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Traditional SharePoint Development Is Difficult 29
Custom Development Is Tedious 30
Build Custom Apps, Coding Optional 32
5. Start with Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Defining Basic Fields and Data Types 34
Using the Properties Window 35
Enhancing a String with a Choice List 36
Setting Default Values 37
Adding Relationships 39
Business Types 42
Defining Uniqueness 43
Practicing What We Just Learned 43
Calculated Computed Properties 44
Advanced Relationships 45
Summary 45
6. Screens: The LightSwitch User Interface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Creating Screens 48
Using Read-Only Controls 49
Displaying Related Fields 51
Launching the Application for the First Time 52
Creating a Modal Window 55
Summary 58
7. Adding Business Logic. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Change Tracking in LightSwitch 61
Factoring Out Repeated Logic 62
Customizing the Add and Edit Buttons 63
Designing Running Screens 68
Creating a Custom Details Page 70
Custom Validation 73
Summary 75
8. Application Security, Access Control, and Personalizing Your Application. . . . . . . . . . . . 77
Enabling Authentication: Windows or Forms 77
iv | Table of Contents
Adding a Welcome Message Using Our ViewModel 79
Adding a Query to the Model 84
Add the Query to the Screen (ViewModel) 85
Binding to Query Parameters 86
Summary 89
9. Running and Debugging Our Application. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
The LightSwitch Runtime Experience 91
The LightSwitch Grid Control 91
Sorting 91
Export to Excel 91
Search 92
What’s Really Happening Between Our Client and the Server? 92
Running as a Web Application 93
Summary 96
10. LightSwitch with SharePoint Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
Logical SharePoint Architecture 97
Adding a SharePoint Data Source 103
Relating to SharePoint Data 106
Populating the Knowledge Base 108
Summary 112
11. Deploying Your LightSwitch Application. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
The Application Designer 113
The Publishing Wizard 114
What Kind of Application Do You Want to Deploy? 114
Where Will the Application’s Services Be Hosted? 115
Remotely Publish the App or Just Package It for Now? 116
Do You Need an SSL Certificate? 117
How Will You Deploy Your Database? 118
Should LightSwitch Create an Application Administrator Account? 119
Specify Other Connection Information 120
Signing Your Code 121
Ready to Deploy 122
Deploying Your Packages to the Server 123
A First Look at the Database 134
Summary 135
Part III.
Table of Contents | v
12. Introduction to Business Intelligence. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
What Is Business Intelligence? 139
Applications of Business Intelligence 140
Microsoft’s Tools for Business Intelligence 141
SQL Server Database Engine 143
SQL Server Analysis Services (Prior to 2012) 144
PowerPivot for Excel and SharePoint 145
Summary 147
13. Business Intelligence Semantic Model (BISM). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Why Business Intelligence Semantic Model? 149
BISM Design Goals 149
Business Intelligence Semantic Model Architecture 150
Consuming Data from OData Sources 151
How Do Existing Analysis Services Applications Translate to the New
Semantic Model? 153
Pros and Cons of the New BI Tabular Data Model 156
How Do the Data Access Methodologies Stack Up? 157
xVelocity (Tabular) 157
MOLAP (UDM) 157
ROLAP (UDM) 158
DirectQuery (Tabular) 158
Business Logic 158
DAX Syntax 159
Getting Started with DAX 160
14. Populating Sample Data into Our Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
Downloading Adventure Works Data from Microsoft 161
Attaching the Database 162
Importing People from Adventure Works 166
Synthesizing Help Desk Queues from Adventure Works 168
Importing Tickets from Adventure Works 172
Review the Results 174
15. Building the Help Desk Tabular Cube. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
Importing SQL Server Data into PowerPivot 178
Connecting Excel to the PowerPivot Model 184
Importing Data from the Windows Azure Marketplace DataMarket 187
Summary 195
16. Enriching the Cube: Relationships and DAX. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
Relationships in PowerPivot 197
vi | Table of Contents
Manually Adding Relationships 200
Traversing Relationships with DAX 201
Hiding Columns and Tables from Client Tools 203
Using DAX to Aggregate Rows in a Related Table 205
Calculating Earliest and Latest Related Dates with DAX 207
Parsing Strings with DAX 208
Counting and Aggregating Related Rows with DAX 210
Count of Distinct Values with DAX 213
Calculating the Difference Between Dates with DAX 214
Adding a Measure from the Excel Side 217
Counting Rows Across an Inactive Relationship 218
Creating a Hierarchy for Dates 224
Looking Up Related Data Without an Active Relationship 228
Summary 231
17. Deploying to SharePoint. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Sharing with Your Team 233
Summary 239
18. SQL Server Analysis Services (SSAS). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
Scalability 241
Manageability 242
Security 242
Development Tools 243
Direct Feature Comparison 244
Upgrading a PowerPivot Workbook to a Tabular Model 245
Validating the Deployment 253
Automating Processing Your Cube 253
Summary 262
Part IV.
19. PivotTable Basics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265
Meaning from Data 265
The Universal Business Intelligence Tool 265
PivotTables 266
Ranking Largest to Smallest 267
Percentage of Parent Row 268
Filtering and Sorting PivotTable Dimensions 269
Visual Totals 270
Values on Rows 274
Table of Contents | vii
PivotCharts 275
Summary 279
20. Slicers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281
Inserting an Additional PivotTable 283
Connecting Additional PivotTables to Slicers 287
Summary 289
21. Formatting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291
Custom Slicer Formatting 291
Disabling Gridlines and Headings 296
Formatting PivotTables and PivotCharts 297
Summary 300
22. PivotTable Named Sets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
Scenario: Last Four Years of Ticket Counts and Total Average Time to Closure 302
Reusing a Named Set for Another Chart 305
Summary 308
23. Sparklines and Data Bars. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309
Sparklines: Intense, Simple, Word-Sized Graphics 309
Adding a Data Bar 317
Summary 318
24. Configuring a Gallery for Reporting Services, Power View, and Excel Services. . . . . . . 319
Enabling Required Features 319
Creating the PowerPivot Gallery 320
Enabling Business Intelligence Content Types 322
Setting Up Your Default View 324
Summary 326
25. Reporting Services Basics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327
What Is Reporting Services? 327
Report Architecture 327
Creating a Reporting Services Data Source 328
Launching Report Builder 3.0 330
Creating Datasets 332
Creating a Reporting Services Report 339
Adding a Chart 341
Consuming an OData Feed from Reporting Services 348
viii | Table of Contents