O'Reilly logo

Developing Business Intelligence Apps for SharePoint by David Feldman, Jason Himmelstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required


Why You Need to Read This Book

This may seem like a strange way to start a tech book—but you really, really need to read this book. Let us explain. We’ll start with a little bit about ourselves and why we needed this book.

I’m Dave, the developer. I’ve been obsessed with Microsoft Web Application technologies since I was 16 years old. It began with the beta of classic ASP and Access 2.0 to be exact. Since then, I’ve wound my way through every version of SQL Server, Site Server, .NET, Silverlight, SharePoint, and just about anything else I found in my MSDN subscription. I’ve worked for startups and Fortune 100 companies in a myriad of different industries, in roles that include developer, architect, and even manager of a 30-person development team. Even with all of this great experience, I still really needed a book like this. Unfortunately, it didn’t exist yet.

I’m Jason, the IT professional. I’ve been working in technology since my teens as well, however I chose a different path from Dave. While he was busy learning languages and writing code I was off discovering the intricacies of interconnected systems. From my first jobs managing desktops, to my experiences as an Active Director and Exchange architect, to finally winding up as a SharePoint Practice Director and Chief Solutions Architect whose job it is to architect and design SharePoint environments that work and perform at scale, I too have been looking for a book like this.

Over the past 15 years, Dave’s job has gotten a lot easier. Development used to start from scratch with developers tasks to code all aspects of a monolithic application which was deployed to a single server environment. To enable a high degree of developer productivity, we, the IT professionals, have a larger number of platforms to build out and integrate. In the last section of this book, Jason will give you the tools you need in order to be successful with all these technologies.

This book is about application architecture as much as it is about any of the individual technologies described in it. Einstein once advised to simplify as much as humanly possible, but no more. Throughout this book, we’ll attempt to keep it simple while giving you the techniques you need to build effective business solutions using SharePoint, LightSwitch, and SQL 2012.

Our industry is undergoing an incredible amount of change right now and for Microsoft developers, things may even seem a bit unstable. In the past couple of years, as developers, we’ve seen the evolution of ASP.NET AJAX and ASP.NET MVC, WCF, SharePoint, Silverlight, and the Windows Azure public cloud. The SQL Server data platform has been adding new capabilities faster than the developer community can absorb them, including SSRS Native, SSRS SharePoint Integrated, Unified Dimensional Model, PowerPivot, Power View, tabular cubes, Master Data Services, and Data Quality Services to name a few. In addition, we have the Office platform where a strategy around personal self-service business intelligence (BI) has driven data visualization capabilities into Excel and PowerPoint, enabling information workers to commoditize the work of business intelligence professionals. As SQL Server and Office added new capabilities to create content, SharePoint became a way to share and collaborate around our data. To add even more complexity, new tools made the development of SharePoint features possible under SharePoint 2010, giving developers a way to extend the capabilities of Microsoft’s collaboration platform with our own ASP.NET code.

As developers, we are accustomed to a wide variety of technologies in our toolbox. Microsoft has done a great job of making Visual Studio into the most productive environment for writing business applications and has even used developers as an extended sales force for their server products. However, in 2010, Microsoft realized that the strategy of running a limited version of the .NET runtime in a web browser plug-in called Silverlight wouldn’t work with the explosion of Internet-connected devices and the unanticipated success of the iPhone. This is unfortunate because Silverlight development is pretty awesome—robust tools, a high degree of interactivity, great controls, and good security. However, the number of platforms from iOS to Android to Windows Phone, WebOS, even cars, refrigerators, game consoles, TVs, and anything else with a few circuit cards in it is far too vast. Microsoft realized at the peak of Silverlight’s popularity in 2010 that they needed to start over and begin to focus on open standards like HTML and JavaScript if they wanted to reach every connected device. The big downside, of course, is that HTML and JavaScript are far more limited than compiled .NET code. The challenge of creating complex applications that function well on many devices is not unique to Microsoft; industry-wide, this evolution will continue to unfold for the next few years. As a result, product life cycles are being compressed; instead of two to three years, new capabilities appear multiple times per year, making it nearly impossible to keep up.

As a designer of business solutions, we are given the financial and human capital of our customers and are trusted to devise solutions that are maintainable and that will evolve to meet future needs. Imagine a world where you spend less time thinking about plumbing code and more time solving real problems. A world where model-driven applications and reporting evolve and grow over time instead of becoming brittle and filled with bugs from past developers. If that sounds pretty good to you, then you should really read this book.

Business Application Components

If you’re reading this, you’re probably involved in the creation of business applications or business intelligence solutions, or maybe you aren’t yet and would like to be. We build these apps to solve a problem or fulfill a need that our customers have and as such, we want to create a solution quickly. We want to write as little code as possible while still creating a really great solution that is maintainable. Using SharePoint, LightSwitch, and SQL 2012, we can create powerful solutions in record time. Let’s explore the parts.

SharePoint Server

SharePoint Server is Microsoft’s web portal, collaboration, social, and business intelligence platform. Basically, think of it as the center of the world for business collaboration. The scenario in this book works on SharePoint 2010, but we will also discuss SharePoint 2013, including changes to the app model in the next release to make sure that our solution grows and evolves over time.

Visual Studio LightSwitch

Visual Studio LightSwitch is a model-driven development capability that provides the easiest way to build data-driven applications for the Web and desktop that are deployable on-premise or to the cloud. LightSwitch enables us to focus on business logic by freeing us from the plumbing code and many of the technology decisions. We discuss in detail the evolution of the LightSwitch product—from LightSwitch 2011 to LightSwitch within Visual Studio 2012 and the HTML client released in Visual Studio 2012 Update 2—and how we can use this platform to protect our investment and deliver value faster to our customers.

SQL Server 2012

From a reporting perspective, we turn to new capabilities delivered in SQL Server 2012, including PowerPivot for Excel and SharePoint, Reporting Services integrated into SharePoint, and Power View for presentation-ready ad hoc capabilities.

The best developer story in the world is worth nothing if you can’t make it work on your servers so we’ll finish up by sharing our secrets from what we used when authoring this book to the recommended practices and pitfalls you should avoid while building your environments.

Many books exist on the individual topics that we discuss, but this is the only publication that puts all the pieces together. You will find that the combined value is far greater than the sum of the individual components. In just a few days, you can build a sophisticated business intelligence app for SharePoint.

The Example Application

In choosing an example app for this book, we wanted something anyone could relate to and we also wanted to keep it simple enough to not distract from the patterns and skills you’ll be learning. In Part I, we talk about the architecture of our solution and the drivers behind it. We’ll make sure that by the time you head into the development of the application, you have a solid grounding in the fundamentals, including database design principles.

In Part II, we begin the construction of a help desk application using Visual Studio LightSwitch, as shown in Figure 1. We start by designing the data model, leveraging the principles we discussed in Part I, and then we walk through the construction of the application, integration of SharePoint data, and deployment of the application.

The HelpDeskManager
Figure 1. The HelpDeskManager

Part III introduces the concepts behind business intelligence and walks you through the creation of PowerPivot and tabular cubes complete with a dive into the DAX language, which can be used to enrich your reporting model and gain new insights into ticket trends for the various queues in our help desk application. Figure 2 highlights the diagram view of the PowerPivot model that we will create.

Diagram view of the PowerPivot
Figure 2. Diagram view of the PowerPivot

In Part IV, we leverage the data visualization stack to create and share insights from our data as shown in Figure 3. Excel, SQL Server Reporting Services, and Power View each bring new capabilities and we dive deep into a number of the more exciting new features and bring them all together in SharePoint with our LightSwitch application. These technologies are evolving, with new releases of LightSwitch, SharePoint, and SQL Server happening at an unprecedented rate. We will also discuss changes in SharePoint 2013 and the integration of Power View into Excel.

Data visualization stack
Figure 3. Data visualization stack

Finally, in Part V we will walk you through setting up an environment to do your development in and give you recommended practices and pitfalls to avoid as you do so.

Conventions Used in This Book

The following typographical conventions are used in this book:


Indicates new terms, URLs, email addresses, filenames, and file extensions.

Constant width

Used for program listings, as well as within paragraphs to refer to program elements such as variable or function names, databases, data types, environment variables, statements, and keywords.

Constant width bold

Shows commands or other text that should be typed literally by the user.

Constant width italic

Shows text that should be replaced with user-supplied values or by values determined by context.


This icon signifies a tip, suggestion, or general note.


This icon indicates a warning or caution.

Using Code Examples

This book is here to help you get your job done. In general, if this book includes code examples, you may use the code in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing a CD-ROM of examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission.

We appreciate, but do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: “Developing Business Intelligence Apps for SharePoint by David Feldman and Jason Himmelstein (O’Reilly). Copyright 2013 Jason Himmelstein, David Feldman, 978-1-449-32083-6.”

If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at .

Safari® Books Online


Safari Books Online (www.safaribooksonline.com) is an on-demand digital library that delivers expert content in both book and video form from the world’s leading authors in technology and business.

Technology professionals, software developers, web designers, and business and creative professionals use Safari Books Online as their primary resource for research, problem solving, learning, and certification training.

Safari Books Online offers a range of product mixes and pricing programs for organizations, government agencies, and individuals. Subscribers have access to thousands of books, training videos, and prepublication manuscripts in one fully searchable database from publishers like O’Reilly Media, Prentice Hall Professional, Addison-Wesley Professional, Microsoft Press, Sams, Que, Peachpit Press, Focal Press, Cisco Press, John Wiley & Sons, Syngress, Morgan Kaufmann, IBM Redbooks, Packt, Adobe Press, FT Press, Apress, Manning, New Riders, McGraw-Hill, Jones & Bartlett, Course Technology, and dozens more. For more information about Safari Books Online, please visit us online.

How to Contact Us

Please address comments and questions concerning this book to the publisher:

O’Reilly Media, Inc.
1005 Gravenstein Highway North
Sebastopol, CA 95472
800-998-9938 (in the United States or Canada)
707-829-0515 (international or local)
707-829-0104 (fax)

We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at http://oreil.ly/Dev-Business-Apps.

To comment or ask technical questions about this book, send email to .

For more information about our books, courses, conferences, and news, see our website at http://www.oreilly.com.

Find us on Facebook: http://facebook.com/oreilly

Follow us on Twitter: http://twitter.com/oreillymedia

Watch us on YouTube: http://www.youtube.com/oreillymedia


The writing of this book was but a small part of the effort put forth to make this book a reality. We would like to sincerely thank the book team: Our editor, Rachel Roumeliotis; our tech reviewers, Dan Usher, Scott Hoag, Christian Buckley, Kyle Davis, and Jeremiah Hamilton; our copyeditor, Gillian McGarvey; and the entire O’Reilly team who took our words and made them a book.

—Dave and Jason

This book would not have been possible without the contributions and coaching from so many people along the way. I would like to thank my wife, Lauren, daughter, Ellie, and my entire family for their patience and encouragement as I wrote and rewrote each chapter. Thanks to the brilliant Scott Hillier, Andrew Connell, and Jay Schmelzer who were always available to bounce ideas around and help me understand the some of the motivations behind this great set of products. Finally thanks to the brilliant developers and leaders I’m lucky enough to work with every day. Seeing real business solutions created from these technologies tools has helped give me the perspective to accomplish this work. Thanks.

—Dave Feldman

My involvement in the SharePoint Community has largely driven the past three years of my professional life, and I would like to thank Dan Holme, Cornelius J. van Dyk, Chris McNulty, Sean McDonough, Todd Klindt, Mark Rackley, Scott Hillier, Andrew Connell, Geoff Varosky, Jim Wilcox, Marcy Kellar, Mike Gilronan, Sara Clark, and all the others of whom there are too many to name, thank you for your confidence, encouragement, and hard work that made writing this book possible by your passionate contribution and amazing example.

To my sons, Max and Sam, look at what your Dad did! Know that you can accomplish anything that you set your mind to. Nothing is out of your reach because if your Dad can do it, you sure can too. As with everything I do, I did this for you. Sorry it’s not as cool as the Muppets, Clifford the Big Red Dog, or being a Super Hero. I love you.

To my amazing, patient, and supportive wife, Jill. Thank you for pushing me to finish even when I would rather watch football, goof off, or doing whatever the hell else I could think of instead of writing. This book never would have happened without your constant and lovingly nagging me until I completed the work. I love you more than you will ever know.

To my spectacular parents, brother, and grand-parents, amazing friends and co-workers, patient bosses, and extended family who have supported and put up with me over the past year as we wrote this: THANK YOU!

—Jason Himmelstein

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required