New book: Business Intelligence in Microsoft SharePoint 2010

Article
06/10/2011

We're pleased to announce the availability of the Microsoft Press book Business Intelligence in Microsoft SharePoint 2010 (416 pages; ISBN: 978-0-7356-4340-6). This book gives business intelligence (BI) developers, SharePoint administrators, and business users a view into SharePoint Server 2010 as a platform for providing BI for their organizations, teams, and individuals as a self-service option. Technologies discussed in this book include samples for SQL Server 2008 R2 Analysis Services (as a backdrop for "trusted data"), Excel Services, PerformancePoint Services, Visio Services, and PowerPivot.

One unique chapter in the book provides alternative approaches for choosing a Microsoft BI tool, which is a challenge, because there are a handful of report, dashboard, and scorecard authoring and viewing tools that you or your organization might use in different situations. This challenge is also most apparent when companies are at a different stages of maturity for getting the right data to the right people at the right time. The authors explain how to analyze where you are in the BI achievement cycle, and how to get to where you want to be with BI.

Appendix A gives you two options for getting what you need in place to start using Microsoft business intelligence. Chapter 5, PowerPivot for Excel and SharePoint and the appendix B: DAX Function Reference, gives you a clear examples of how to incorporate self-service business intelligence. Appendix C: SharePoint As a Service -"Office 365" is a primer for what is to come for giving users an option to have SharePoint hosted in the cloud.

We've posted an excerpt from the last chapter, Chapter 8, "Bringing It All Together," which ties several of the technologies together to display Web Parts in a SharePoint dashboard. Preview this book now!

CHAPTER 8 Bringing It All Together

Objectives

After completing this chapter you will be able to:

· Understand the basic concepts of BI dashboards in SharePoint

· Understand how SharePoint supports the concurrent use of multiple BI products

· Create a SharePoint dashboard that uses several different Microsoft BI products and features

Introduction

Users in organizations often need to gain insights from data across many different sources. They may need to look at sales data alongside orders data or forecast data. While the requirement itself seems straightforward, the data often resides in many different places—or the people who analyze the data perform that analysis in different ways, using different products. You don’t always have a clear “one size fits all” answer to the question of which product to use to best visualize a particular data source. You might also need to determine which product to use based on the maturity of an organization, its capabilities, or simply the user’s comfort level with the technology.

For example, one user might use Reporting Services to show insights about customer trends, and another might use Excel Services to show how a particular customer segment lines up with cost projections. Management in the organization might actually want to see both analyses side by side, to help answer a business-critical question. The simplest way to do this would be to allow these products to work in a side-by-side fashion, providing integrated views of the data, rather than forcing yet another user to copy each BI report and regenerate it using a single tool.

One of the strengths of SharePoint is that it gives users the ability to bring data and insights from different products together in a holistic way. Whether the data comes from a SQL Server data source, from an Analysis Services cube, from within a SharePoint list, from an Excel file, or from any one of a number of other places, the Microsoft BI stack with SharePoint gives you the tools to easily view insights from the various data sources in a single integrated view. BI developers can choose to use any of the products described in this book, because through SharePoint, all the products can deliver side-by-side analyses to help business users gain deeper insights while still allowing individual users to use the products that make the most sense to them, based on the specific data being used or on their comfort level with a particular product or technology.

Dashboards

The concept of a dashboard is probably very familiar to most readers. At the simplest level, a dashboard brings visualizations of data and status together into a single place, so users can easily—usually at a glance—view how a particular business effort is doing. Dashboards are suitable for many different purposes, including measuring status against goals, monitoring progress, and managing business process. The best dashboards provide a way to take action on the information they show, such as quickly sending an alert or email to the right individual if something needs to be done.

Dashboards can be constructed from many different types of content: charts, icons showing status (usually referred to as key performance indicators, or KPIs), key numbers and statistics, fully interactive reports, tables, or just about any other visualization that shows how well an organization is tracking toward its goals.

You can use all the products discussed in this book to create meaningful views. The previous chapters have provided a good overview of when to use the individual products and how to get started with them. This chapter focuses on what the end user sees, by first walking through some straightforward examples that show how to gather insights created using each product, and then combining those insights onto a single dashboard page so that end users can consume the information easily.

Tools in SharePoint for Authoring Dashboards

While you can use each product discussed in this book to create a single full-page report that functions much like a dashboard, you can also combine views from each product into a single dashboard page.

Here are the three primary tools you can use to do this:

· PerformancePoint Dashboard Designer PerformancePoint is a different dashboard experience altogether. You should distinguish the dashboard experience described in Chapter 7, “PerformancePoint Services,” from the Web Part experience explained in this chapter. You can use a PerformancePoint dashboard to display PerformancePoint objects in a browser. The authoring tool, PerformancePoint Dashboard Designer, is a OneClick application available when PerformancePoint Services is configured. Dashboard Designer allows you to build integrated BI solutions that bring the published results of the other authoring tools together into interactive dashboards. To learn more about Dashboard Designer, see Chapter 7.

· SharePoint page/dashboard user interface You can use the SharePoint interface for all the other dashboard-building products. For example, using the native SharePoint user interface, you can customize Web Parts, SharePoint KPIs, and SharePoint pages to combine insights from such products as Excel Services, Visio Services, and more. This chapter discusses the basic elements, such as Web Parts, Web Part pages, filters, and SharePoint KPIs.

· SharePoint Designer Using SharePoint Designer, you can fully customize pages in SharePoint, making it easy to add a custom look-and-feel while taking advantage of advanced functionality such as configuring custom behaviors for alerts or workflows. SharePoint Designer is the premier tool for creating great no-code customized solutions. It’s mentioned here for completeness, but this chapter doesn’t cover it in any detail. See the Microsoft product page at https://sharepoint.microsoft.com/en-us/product/Related-Technologies/Pages/SharePoint-Designer.aspx for more information about SharePoint Designer.

Report Builder is another available tool for BI developers; it is the report authoring environment for creating reports with SQL Reporting Services. Discussion of Report Builder features is out of scope for this book, but if you’d like more information about Report Builder, see “Getting Started with Report Builder 3.0,” at https://technet.microsoft.com/en-us/library/dd220460.aspx.

Which Dashboard Tool Should I Use?

It is not Microsoft’s intention to confuse customers with several different dashboards. Often, the BI tools that you should use depend on the specific problems that you are trying to solve, the BI maturity level of your organization (see Chapter 2, “Choosing the Right BI Tool”), the expertise of people who build or use the dashboard, and other considerations, such as the KPI functionality offered by a SharePoint Web Part dashboard versus a KPI authored in PerformancePoint Dashboard Designer.

Basically, you don’t want to use a jackhammer when all you need is a small ping hammer to help users make decisions. The functionality of many of these tools overlaps. You might decide which tool to use based on your familiarity or proficiency with the tool. In any case, the following guidelines can be helpful when choosing which dashboard-creation technology to use.

Note The following are high-level suggestions that stem from a generalized dashboard-usage perspective only. See the individual chapters for more in-depth explanations of the strengths of each product and when to use it.

Use Performance Point to create comprehensive KPIs, scorecards, reports, filters, and dashboards when:

· You want to include any of the following multidimensional data sources:

o SQL Server Analysis Services

Note An Analysis Services cube is a multidimensional data source that is ideal for a rich KPI. Analysis Services KPIs, as discussed in chapter 3, “Getting to Trusted Data”, can be imported into a scorecard by using the PerformancePoint Scorecard Wizard.

o PowerPivot model

Note A PowerPivot model must first be created by using the PowerPivot add-in for Excel and then published to a SharePoint site that has PowerPivot services enabled.

· You want to include tabular data sources such as the following:

o SharePoint list

o Excel Services

o SQL Server table

o Excel workbook

o Custom data source

· You need visualizations that allow you to drill down, such as decomposition trees to see the underlying data for a particular value.

· Need more advanced KPIs that support the following:

o Multiple data sources with which KPIs can perform calculations

o More complex visualizations (such as gauges)

o A large number of states—important when you want to display and communicate the current state of your business as well as its desired future state (or multiple forecasts)

· You want dynamic hierarchies that refresh when the data source is updated.

· You want Time Intelligence features that allow you both to filter and to create variations on the filter that allow the user to select a single “current date.”

· You want to create or include any of the following reports or report features in your dashboard.

Note Some reports or report features are created Dashboard Designer, while others are already created in another BI tool, such as SQL Server Reporting Services. The distinction is discussed in chapter 7, “PerformancePoint Services.”

o Analytic chart

o Analytic grid

o KPI details

o Show details

o Decomposition tree

o Reporting Services report

o ProClarity Analytics Server Page report

Use “native” dashboard tools, such as Excel Services, SharePoint dashboard pages, KPIs, and filters, when:

· You want to include any of the following data sources:

· Analysis Services

· PowerPivot

· SQL Server

· Excel workbook

· Visio diagram

· The BI reports or logic are already based in Excel (often the case, given Excel’s widespread usage).

· The needs around your KPIs are fairly simple and don’t have more than a few states (up to 5 states).

· You need KPIs on a page or series of pages, have very simple KPI needs, and don’t want to spend the time creating and managing more complex solutions such as a Performance Point scorecard or workbook file.

· You need to prototype a solution quickly. (For example, experienced Excel users can build a full report faster in Excel, using conditional formatting, and so on.)

· You need a solution that can be manipulated on-the-fly. (It’s easy to edit Excel reports or use the SharePoint user interface to tweak dashboard pages with little or no training.)

Dashboard (Web Part) Pages in SharePoint

The lightest-weight dashboard authoring tool is a simple web browser that takes advantage of the user interface that SharePoint has provided to build dashboard pages that use Web Parts.

Web Parts are logical containers in SharePoint pages that can display content. The Web Part framework in SharePoint provides easy drag-and-drop interactivity, includes a Settings page, and includes other user-interface features to make configuring pages fairly simple. Web Part pages are generally essential for creating a dashboard-like experience in SharePoint. You would typically use Web Parts when you need to display content from different files or products (such as Excel Services, Visio Services, Reporting Services, and so on) in a page, when you want to display that content side by side with other SharePoint content, or when that content needs to interact with other SharePoint entities in the same page (such as lists or other Web Parts).

PerformancePoint dashboard pages are ordinary Web Part pages that contain various components as connected Web Parts. The Filter, Scorecard, Report, and Stack Web Part are discussed in more detail in the section “Create a Dashboard” in Chapter 7.

To create a dashboard page in SharePoint

1. Go to the SharePoint site where you want to add your dashboard page, expand the Site Actions drop-down list, and choose More Options, as shown in the following illustration.

2. When the Create page opens, on the right-hand side of the page, under Pages And Sites, choose Web Part Page.

Alternatively, depending on whether Silverlight is enabled, you might see a slightly different user interface. On the Create page, in the Browse From list, choose Filter By Page and then choose the Web Part Page option as shown in the following illustration. Then click Create (on the right-hand side of the page).

Now you must make some choices. As shown in the following illustration, you need to select your preferred page layout, enter a name for the page, and specify where to store the page.

3. Accept the defaults, and name the page WebPartPage. Feel free to experiment with the different layout options available—whatever you find pleasing. The Save Location is the document library where SharePoint stores your new page.

4. Click Create to display a new blank Web Part page, as shown in the following illustration.

Use Excel Services in the Dashboard

To make the dashboard more interesting, you can use the next exercise to get some data from an Excel workbook and show it on the page by using the Excel Web Access Web Part. Before doing that though, you need an Excel workbook.

You can use almost any workbook to do this. The following example walks you through the steps to create a simple workbook that works with some of the filters you can add to the page in later sections of this chapter.

Create the Excel Workbook

The workbook creation process has two parts. First you need to add a pivot table connected to OLAP data in Analysis Services, and then you can generate a chart from that data.

To add a pivot table to a workbook

1. Start the data connection wizard in Excel, click the Data tab, click From Other Sources, and select From Analysis Services, as shown in the following illustration.

2. Complete the Data Connection Wizard to connect to the Contoso Retail DW database, select the Sales cube, and click Finish.

3. In the Import Data dialog box, choose PivotTable Report to create a new pivot table report in your sheet.

4. In the PivotTable Field List dialog box, choose Sales from the topmost filter to see only those fields relevant for the Sales data.

5. Scroll through the field list, selecting the check box next to the following fields: Sales Amount, Product , andCalendar YWD. This adds the primary data to the spreadsheet that we are working with.

6. Drag the Calendar YWD field from the Column Labels area to the Report Filter area, as shown in the following before-and-after illustrations.

(before) (after)

You should end up with a pivot table in your workbook, as shown in the following illustration.

To add a simple chart to the workbook

1. Continuing with the same file you created in the preceding procedure, make sure your cell selection is located in the pivot table, and on the Insert tab, click Pie to choose a Pie chart.

2. To format the chart so that it looks a little better in the report, move the chart and then grab its corner to resize it so that it fits next to your pivot table.

3. Optionally, choose a chart style that you like from the chart ribbon.

4. On the Chart Analyze tab, in the Field group, click Hide All.

You should now have a finished report that resembles the following illustration.

5. Save the workbook to SharePoint, and view it in a browser by using Excel Services.

When you view the workbook on the server, make sure the pivot table refreshes and that all your data connectivity is working. If it isn’t, see the section “External Data Configuration” in Chapter 4, “Excel Services,” for some steps that should help.

Prepare the Workbook for the Dashboard: Add Parameters

Because the ultimate goal is to end up with multiple Web Parts on a dashboard page, you need a way to filter the data on the page at the same time. You can use a SharePoint filter to do this. A SharePoint filter is yet another Web Part on the page that takes a given value and sends it to other Web Parts on the page. Then, based on the value provided by the SharePoint filter Web Part, the other Web Parts can change or filter the data they display. This simple mechanism enables users to choose a given value and then see all the different Web Parts on the page get filtered by their choice. SharePoint filters are created either in a SharePoint Web Part page user interface or in SharePoint Designer.

Before configuring the Excel Services Web Parts so that they can be filtered, you need to make some simple modifications to the workbook file so that it can be filtered in the dashboard. You must modify the workbook so that it can accept a filter value and recalculate based on that value. You do this by specifying workbook parameters.

A workbook parameter is a single cell in Excel that accepts input values when the file is loaded on the server. This provides a way to modify a cell’s contents even in read-only or view-only permission situations, without allowing the rest of the workbook to be edited. Workbook parameters are single-cell named ranges that don’t contain any formulas.

To specify a workbook parameter

In the following exercise, the goal is to allow users to change the date filter for the pivot table. First you need to give it a name.

Note This exercise uses the same workbook you created earlier in the chapter. 1. In column B of row 1, select the date filter cell (showing “All” in the following illustration) on the pivot table, type DateFilterCell in the box to the left of the formula bar, and press Enter. Now you can refer to that cell by name, which makes it easy to specify it as a parameter later.

2. Click File, click Save & Send, and then click Save To SharePoint.

3. Click Publish Options (as shown in the right pane of the preceding illustration) to open the Publish Options dialog box, and then, to define the workbook parameters, click the Parameters tab, shown in the following illustration.

4. In the Publish Options dialog box, click Add to display the Add Parameters dialog box, shown in the following illustration, where you can choose which single-cell named ranges to add as parameters.

5. In the Add Parameters dialog box, select the DateFilterCell cell that you defined earlier and click OK.

6. Click OK to close the Publish Options dialog box.

7. Save the file in SharePoint. (If you opened the file directly from SharePoint, you can simply click Save, or you can click Save As to overwrite the earlier version of the file.)

The workbook you just saved in SharePoint now allows users to set values in the DateFilterCell cell even if the workbook is in read-only or view-only mode. Changing the cell value triggers a refresh of both the pivot table and pivot chart. You’ll use this parameter later when we associate it with a SharePoint filter.

Show the Workbook in Web Parts

Now it’s time to show the pivot table and chart in separate Web Parts on the dashboard page. The first step is to add the Excel Web Access Web Parts to the page and configure them.

To add an Excel Web Access Web Part

1. From the Page tab of your browser, navigate to the Web Part page you created before. (Remember that it might be in the Site Assets library of your site, depending on where you saved it.)

2. Pick a zone, and then click Add A Web Part to expand the top of the page so that you can choose which Web Part to add.

3. As shown in the preceding illustration, select Business Data in the Categories area, choose Excel Web Access in the Web Parts area, and then click Add to add the Web Part to the page in the zone you selected previously.

4. You should now have an empty Excel Web Access Web Part on the page. You can use this Web Part to load and display Excel workbooks using Excel Services.

To configure the Web Part

Now you can configure the Web Part to display the workbook you created earlier.

1. In the Select A Workbook page, shown in the preceding illustration, click the Click Here To Open The Tool Pane link to expand the tool pane for the Web Part, displaying all the configuration options.

2. Under the Workbook Display area, click the blue button to browse for a workbook to display. A webpage dialog box opens, enabling you to navigate within SharePoint to pick a workbook. Use that dialog box to select the workbook you saved in SharePoint from the previous steps, or just type the URL to the workbook directly.

You should end up with a URL for the workbook you want to display, as shown in the following illustration.

3. Scroll down to the bottom of the Web Part properties tool pane, and click OK. You should now see your workbook displayed in the Web Part, as shown in the following illustration.

Notice that the workbook looks just like an Excel file at this point. You can click the sheet tabs, see the chart, interact with the pivot table, and so forth.

You can control what the Web Part actually shows—for example, you can have it show only the chart.

4. Click the drop-down arrow and select Edit Web Part, as shown in the following illustration, to open the Web Part Properties tool pane.

5. In the tool pane, under Named Item, type in Chart 1. (This works only if you have created a workbook with at least one chart in it and have not changed the default name of the chart. If you did rename your chart, type the name you gave to the chart.)

6. Click either OK or Apply at the bottom of the Web Part Properties tool pane, and the Web Part displays the chart named Chart 1, as shown in the following illustration.

Notice that the Web Part no longer displays this page as a spreadsheet. Instead, it displays each object, one at a time, in the Web Part. This viewing mode is referred to as the Named Item View. When users expand the View drop-down list, they can choose to display any of the other objects in the workbook.

The Named Item View is the view used in most Web Parts, because most people just want to see the parts of a workbook that are interesting and related in a dashboard. This is also the mode that is used when the workbook author publishes only a selection of items from Excel (as described in the “To publish and Excel file” procedure in Chapter 4). That is, because the workbook author chose to display only certain items rather than full sheets, each item appears in Named Item View.

Therefore, in the case where the author chose to show only a set of items from the workbook, the Web Part shows whichever is the first item in the workbook (sorted alphabetically)—even if you don’t specify the name of an item in the Named Item Web Part Text box. However, you can also specify which item should appear first in the workbook by using the Named Item text box in the Web Part properties task pane.

Set Other Web Part Properties

Open the Web Part properties tool pane again. Notice that many properties that can affect how the workbook is displayed are listed. You won’t explore all the properties here, but generally, you can find properties for controlling whether the toolbar is visible, what commands are on the toolbar (if it is visible), whether or not the Named Item drop-down list is displayed, and what types of interactivity you want to allow for the Web Part (such as sorting, filtering, recalculation, and so on).

For now, turn the toolbar off. Under Type Of Toolbar, choose None from the drop-down list, as shown in the following illustration.

Then scroll down and expand the appearance section. Notice the width and height controls. These controls are used frequently for adjusting dashboards that have many objects on the page, to get the right look and feel. You need to adjust these to make the Web Part fit the displayed Excel content in a way that doesn’t show unnecessary scrollbars. Finally, click OK to close the Web Part Properties tool pane.

Add More Web Parts, and Finish

Repeat the steps in the preceding section, but this time set the Named Item to PivotTable1 to display the pivot table you created earlier. Notice that in the Excel client you can see the name of each item in the ribbon for that item. Feel free to add any other Web Parts to the page as well.

On the ribbon at the top of the SharePoint page, click Stop Editing. Until now, the Web Part page has displayed in Edit mode. This mode shows all the various zones, drop-downs for displaying edit menus, editing ribbons, and so forth. When you click Stop Editing, the Web Part page displays in the way that visitors to the site will see it. This page has many other settings that you can use to make the page cleaner (such as turning off Web Part titles). You can freely experiment with these options to learn how they modify the look and feel of the page.

As shown in the preceding illustration, our example has two Web Parts on the page—one showing a chart and the other showing a pivot table. Both come from the same workbook. However, at this point, when you drill down on the pivot table, the chart does not update. This is because each Web Part loads its own copy of the workbook—that is, it gets its own session on the server. Each session is completely separate, so changes from one session don’t affect other sessions. This is the reason why you can have a single Web Part page viewable by many users at the same time, yet one user’s operations (filter, sort, drill, and so on) affect only that user.

Note The example in this section is for illustrational purposes only. It’s intended to show how you can have multiple Web Parts interacting on the same page. In a real-world scenario, the power of multiple Web Parts becomes apparent when you have items from different workbook files that can be viewed side by side, rather than items from the same file.

The next section shows you how to put a single filter on the page that enables both the chart and the pivot table to update.