Whatever stage your company has reached in its ability to provide your business users with optimal decision-making data, this book can help you advance that capacity by providing a selection of “crawl, walk, and run” scenarios.

SharePoint 2010 (enterprise license) now offers several BI tools. We show how they work together in compelling ways.

This book’s approach is unique in the following ways:

Just as a SQL BI developer peeks into SharePoint 2010 products, we want SharePoint administrators to peek into the tasks involved in developing BI solutions and the inherent difficulty in getting to trusted data. A SharePoint administrator must be aware that you typically can’t just “turn on” BI in SharePoint or in SQL Server. Instead, you follow a process. A SharePoint administrator should also be aware of the newest BI features and tools, as well as existing technologies, and have some idea of how to set them up. In this book, we give SharePoint administrators an overview of the latest available BI tools and how they work with SharePoint 2010. This book strives to give SharePoint administrators an understanding of the work and expertise required for an extensive range of possible BI implementations.

In this book, the term “business user” describes people who are eager to understand the technologies that can help them, their teams, and their organizations measure, analyze, forecast, and report on the most important aspects of the company’s business data. A business user may also be a technical decision-maker, deciding which products work best for the individual, team, or organization. By understanding how technology and business needs meet through reporting, measuring, analyzing, and more, we hope that business users will see a return on investment through increased accountability and better alignment with organizational goals.

Using SharePoint 2010 and other stand-alone tools, business users can benefit from learning about the end-to-end process for surfacing and presenting insights to decision-makers. Business users know that trusted insights can change behavior and decisions, which can ultimately help to lead a company in the right direction.

Business users who can benefit from the integrated BI tools offered by Microsoft Office, SharePoint, and SQL Server include:

Each of the preceding roles has its own unique accountabilities. For each role, we provide simple examples showing how to create BI end results such as the following:

End users may also want to know how to do some tricks in SharePoint, such as how to add a rating system in a SharePoint list, view a blog post, implementing collaborative decision-making in SharePoint 2010, or rating BI assets.

Put simply, the BI developer’s task is to establish trusted data sources (tabular data and OLAP cubes) in SQL Server for the various services (Excel, Visio, PerformancePoint) and for PowerPivot and SQL Server Reporting Services. BI developers also help create connections to the trusted data sources and help ensure that the data is the right data.

Organizational BI begins by establishing a single source for trusted data. If users cannot trust the data that’s in front of them to make decisions, they won’t trust the tools that deliver the data. They will abandon those tools to seek some other way to get the right data, which likely means abandoning their considerable investment in those tools, in both time and money, to invest in new ones.

Data can come from a variety of sources, and in many cases, companies have spent lots of money and time to establish a repeatable ETL (Extract, Transform, and Load) process. This requires a BI developer who knows something about data warehouses (SQL Server), integrating data from various sources using SQL Server Integration Services, and developing T-SQL procedures. If a company decides that creating OLAP cubes is worth the effort, it will also hire (or train) SQL Server Analysis Services experts to do the job. Microsoft has provided the tools to tie all this data together, and this book can help you use them to get the best value from your data management tools.

Using the information in this book, BI developers can help decide which tools to use to surface the data. They can also communicate closely with the SharePoint Administrator in cases where trusted data must be shared.

In this book, the authors provide a discussion of SQL Server Analysis Services OLAP cubes because OLAP cubes are the ideal data sources for organizational BI using PerformancePoint Services, for data sources used by the other services (such as Excel Services, Visio Services, and others), and now for “personal BI” using PowerPivot for Excel and PowerPivot for SharePoint.

BI is a difficult concept to pin down precisely, because it covers a wide range of products and technologies and thus means slightly different things to different people. This chapter discusses exactly what the authors mean by the term “business intelligence,” the Microsoft approach to BI, and how SharePoint fits into the picture.

Customers often ask which tools they should use when trying to select among a variety of Microsoft offerings. They’re often confused and need information as to why they might want to prefer SQL Server Reporting Services in SharePoint over PerformancePoint Services, or why they might use the Excel 2010 PowerPivot add-in instead of Excel or Excel Services. After all, each product connects to a database and surfaces data from an OLAP cube.

The difficulties of making such decisions are compounded because different teams and companies are at different stages in their ability to surface data to business users for optimal decision-making. Overall, this chapter attempts to answer questions about which tools to use, clarifying the purposes and capabilities of the various products, and helping you choose which ones are most appropriate for your situation.

This chapter discusses how a company can surface reliable data that business users can work with to author reports and make decisions. Historically, BI started in SQL Server, so we take you on a tour that starts with disparate data sources and then we provide step-by-step exercises showing how to create your own mini-data warehouse—and then show you how to create a multidimensional cube.

Most business intelligence begins in Excel, which can be considered the most pervasive BI tool that exists. But sharing Excel files has always been a huge challenge. Excel Services not only provides the ability to share Excel-based content safely and securely—it also adds powerful management capabilities. Such features as the PivotTable, PivotChart, and Sparklines in Excel improve the look and feel of how data is presented. Among several hands-on examples, you’ll see how to create a PivotTable, sparklines, and slicers to provide slice-and-dice capability on the screen for analysis, and how to add your pivot table to a simple dashboard webpage so that you can share it.

A PowerPivot workbook looks like an Excel workbook, and that’s how it is supposed to look. The PowerPivot experience is designed to feel as seamless as possible to an Excel user. The difference is under the hood, where PowerPivot enhances Excel. Because PowerPivot uses the VertiPaq engine, it extends Excel so that you can work with millions of rows. Moreover, operations—even with huge volumes of data—are fast! Aggregations that might have taken a day to calculate in SQL Server Analysis Services take only seconds in PowerPivot. In this chapter, you’ll see how to mash-up data from different sources, share that data securely via SharePoint, create Data Analysis Expressions (DAX) queries, and more.

This chapter shows you how to create data-driven diagrams that provide interactive processes and context.

One exciting solution that PerformancePoint Services offers is the ability to show a dashboard that reflects KPIs, such as the available disk space of managed servers. This chapter explains how to create a dashboard with scorecard, KPIs, reports, and connections to data sources.

In this chapter, you’ll capitalize on the concepts and products discussed in all the preceding chapters by walking through the steps to create a dashboard that shows data from various sources, such as PerformancePoint Web Part, Visio Services, Excel Services, and PowerPivot.

In this appendix, you’ll find detailed setup instructions, including helpful screen captures, so that you can get up-and-running quickly to work through the book’s exercises. We also provide some instruction for configuring SharePoint Server 2010, along with links to relevant sites.

This appendix provides a reference to DAX, introduced in Chapter 5. DAX is an expression language based on Excel formula syntax and is designed to work with multiple tables of data. DAX includes functions that implement relational database concepts.

This appendix discusses how Microsoft enables disparate businesses—from the smallest one-person home office to the largest enterprises—to experience the benefits of SharePoint without needing to know how to install, manage, deploy, patch, back up, scale out, or generally maintain the machines or software. The authors anticipate more breakthrough cloud features for BI in the future and encourage readers to get a better understanding of the relationship between the cloud and SharePoint.

Microsoft Access is a relational database management system. Software developers and data architects can use Access to develop application software, and “power users” can use it to build individual and workgroup-level applications.

Access Services is a service application that lets you host Access databases within SharePoint Server 2010. Through Access Services, users can edit, update, and create linked Access 2010 databases, which are then both viewed and manipulated using either a web browser or the Access client. In other words, Access services extends “access” to Access, so that even users who don’t have the Access client installed on their desktop can perform operations with the Access application through Access Services.

Access Services can also generate the RDL language used by SQL Server Reporting Services. This is important because it enables you to quickly and easily report on SharePoint data.

There is a self-service element to Access that lets users incorporate rapid application development principles (RAD) to more quickly create data-driven websites without coding in ASP.NET. This is attractive to smaller companies that have a small IT department—sometimes only one or two IT workers. Access and Access Services also become attractive to larger companies when projects are prioritized into already-full IT development schedules, or when users want to provide a very quick proof-of-concept data-driven website.

SQL Server 2008 R2 Reporting Services with SharePoint integration has several new features, including support for multiple SharePoint Zones, support for the SharePoint Universal Logging service, a new data extension, and a query designer for SharePoint Lists as a data source. The SharePoint List data extension supports getting data from the following SharePoint technologies: SharePoint lists for SharePoint Foundation 2010, SharePoint Server 2010, Windows SharePoint Services 3.0, and Office SharePoint Server 2007.

SQL Server Reporting Services Report Builder 3.0 is a report-authoring tool that enables you to create ad-hoc reports quickly. The tool helps report creation, collaboration, and consistency by enabling business users to create and share report components that can be accessed via a shared component library.

We didn’t quite omit this topic entirely; we did include a somewhat longer summary of what SQL Server Reporting Services is in Chapter 3.

Microsoft Business Connectivity Services (BCS), formerly named the Business Data Catalog, provides read/write access to external data from line-of-business (LOB) systems (such as Microsoft Dynamics, Oracle, or Siebel), web services, databases, and other external systems from within Microsoft SharePoint 2010. SharePoint 2010 has product features that can use external data directly, both online and offline. BCS enables tools such as Microsoft Visual Studio 2010 and Microsoft SharePoint Designer 2010 to help make connections to the external data.

Web Analytics helps you collect, report, and analyze usage data so that you can measure the effectiveness of your SharePoint Server 2010 deployment. The following is an overview of the new Web Analytics features: