Microsoft Business Intelligence Tools for Excel Analysts (2014)

PART III: Delivering Business Intelligence with SharePoint and Excel Services

Chapter 16: Leveraging PerformancePoint Services

In This Chapter

· Accessing the strengths and limitations of PerformancePoint

· Designing a dashboard through the Authoring Dashboard

· Viewing a dashboard with the PerformancePoint dashboard

In this chapter, we discuss the Authoring Dashboard — the tools and concepts that are necessary to create and edit dashboards with Dashboard Designer — and using PerformancePoint — which allows you to interact with your report.

In this chapter, we use the AdventureWorks Analysis Services Tutorial sample database Installed with SQL Server Analysis Services.

The capabilities within PerformancePoint are geared toward someone with knowledge of databases and other relevant technologies. But if you have the appropriate permissions to a SharePoint site with PerformancePoint enabled, you can also leverage many of its capabilities.

Why PerformancePoint?

Over the years, Microsoft consistently releases impressive database software with its SQL Server family of products, while retaining its leadership position in the office productivity category. Somewhere in between these two product families is a specialized data tools vacuum that Microsoft has addressed with PerformancePoint. From the perspective of end users, this specialized area could be described as a Web-based application that facilitates both general reporting of business intelligence data as well as interactive, dashboard-style analytics.

PerformancePoint strengths

PerformancePoint is one of Microsoft’s long-standing offerings if you need Web-based, interactive, dashboard-style analytics and reporting. Besides the fact that it’s available as part of the SharePoint Enterprise family of applications (check with your IT department to see if it is enabled on a SharePoint site within your organization), here are a few advantages of using PerformancePoint:

· Web-based interface: PerformancePoint is Web-based, requiring no user installation. This makes it perfect if you need author dashboards that are shared with large groups of users. In fact, one of the biggest strengths of PerformancePoint is how well it can scale to massive user bases.

· Rich interactions: You've become accustomed to working with data with ultimate flexibility — working with data in the context of drill-downs, ad hoc queries, or a broad range of interactive features. PerformancePoint provides all of the classic business intelligence tool functionality along with a few extra features.

· Great performance: You don't have to wait for more than a few seconds after each click. PerformancePoint provides good performance in most scenarios.

PerformancePoint limitations

Now that we have discussed the benefits of PerformancePoint, we need to address the cautions that come along with it:

· Limited options for data visualizations: Dashboards are all about visualizing data, which creates a need for significant flexibility to visualize the same data in many different ways. PerformancePoint focuses on a few well-proven visualizations that work well for all kinds of data (bar charts, area charts, grid, and so on). However, there are one or two surprises, such as the decomposition tree.

· Complicated installation: Although it probably won’t matter much to you, installing and configuring PerformancePoint is not a trivial matter. Furthermore, though PerformancePoint is bundled with SharePoint Enterprise, it’s not installed by default and requires some additional setup. Additional server resources may also need to be allocated, depending on the number of projected users and data.

· Learning curve for dashboard authoring: Though not rocket science, there is enough complexity in PerformancePoint to make it a challenge for beginners, especially when considering some of the advanced features of PerformancePoint such as integrating external Web reports and customizing filter behavior with MDX.

Authoring Dashboards

Building and editing dashboards is referred to as authoring. In this section, we focus on authoring interactive PerformancePoint dashboards, which are connected to Analysis Services data sources.

Although it’s beyond the scope of this book, PerformancePoint also provides the functionality to connect to other data sources and build reports and filters based on those custom data connections.

Getting started

PerformancePoint Services is a feature of SharePoint Enterprise Edition. The primary document type in PerformancePoint is a dashboard, of which there are two modes of use: authoring (creating and editing dashboards) and viewing (interacting with dashboards). To author dashboards, you must launch the Dashboard Designer.

A PerformancePoint dashboard is made up of several elements: data connections, reports, and (optionally) filters. In the following sections, we walk through each of these in detail.

Launching the Dashboard Designer

SharePoint maintains interface simplicity by showing only those features and options that are applicable based on where you are and what you are doing in that moment.

To open Dashboard Designer, follow these steps:

1. Open SharePoint and click the View All Site Contents link (typically on the left side bar).

2. In the Site Contents page, you will see several libraries. Find and click the Dashboard library.

If you can't find a Dashboard library in your SharePoint environment, ask your SharePoint Administrator or IT department for help setting up a PerformancePoint library for you.

You’re taken to a PerformancePoint site where the SharePoint Ribbon includes a PerformancePoint tab. (See Figure 16-1.)

Figure 16-1: Open your PerformancePoint Site and click Dashboard Designer.

3. On the PerformancePoint tab, click the Dashboard Designer icon. If prompted to run initial setup scripts, click Yes.

If it’s your first time launching the PerformancePoint Dashboard Designer, it will be installed on your machine. After the installation, you can launch Dashboard Designer from either the PerformancePoint site or your PC (Start → All Programs → SharePoint → PerformancePoint Dashboard Designer).

Whether installing for the first time or simply opening an already installed instance, the Dashboard Designer automatically opens and starts you off with an empty workspace. (See Figure 16-2.)

Figure 16-2: The PerformancePoint Dashboard Designer.

Adding a data connection

You can think of PerformancePoint as a user interface layer between the dashboard users and the business intelligence data infrastructure, which is a data source.

PerformancePoint is designed to use Analysis Services cubes as data sources in an efficient way, and although this is common, PerformancePoint can also use other types of data sources, including relational databases and Excel documents.

In PerformancePoint, you configure data sources manually in the Dashboard Designer as data connections. A PerformancePoint data connection is a small configuration file with information about an underlying data source, making it possible for reports, filters, and other PerformancePoint components to leverage that data source. After they're set up, data connections remain in the PerformancePoint library and you can reuse them across many dashboards.

Follow these steps to configure a new data connection:

1. Right-click Data Connections in the Workspace Browser (the pane on the left side of the window) and select New Data Source.

The Select a Data Source Template dialog box shown in Figure 16-3 opens.

Figure 16-3: The Select a Data Source Template dialog box.

2. Double-click the appropriate template for your data.

In this example, we select the Analysis Services template.

The Data Connections Properties window shown in Figure 16-4 appears.

3. Enter the server name, select the database name, and then click the Cube drop-down menu to select the desired cube.

All other properties are optional (and beyond the scope of this book).

4. Click the Test Data Source button to test the connection.

5. Click the Home tab, and then click Refresh to save your changes to the server.

Figure 16-4: Specify the Data Connection properties.

If you can't select a cube from the Connection Settings dialog box, then either your IT department must resolve a SharePoint configuration issue or you haven’t provided the correct values for a server name and database name where a cube actually exists. One way to make sure you’re pointing to a valid Analysis Services cube is to connect to the cube in an Excel PivotTable first. If you can connect and browse the cube in an Excel PivotTable but can’t connect to that same database and cube in PerformancePoint, then you have a SharePoint configuration issue.

Adding content

After you’ve created and saved one or more data connections, you can create PerformancePoint content. PerformancePoint content types include KPI, Filter, Report, Dashboard, Indicator, and Scorecard. The following sections focus on Filters, Reports, and Dashboards.

You can add content by right-clicking the PerformancePoint Content folder in the Workspace Browser (the pane on the left side), selecting New, and then selecting one of the available content types. (See Figure 16-5.)

Figure 16-5: Adding content.

These contents types are also available as icons on the Create tab of the PerformancePoint Ribbon.

Adding a filter

Interaction between dashboard objects is one of the primary features that makes dashboards such powerful tools. One of the most important dashboard objects is the filter, which you can use in a variety of formats based on the type of data. For example, you can use dates in a calendar or use a drill-down tree for an org chart.

PerformancePoint offers a number of Filter templates. If you choose to add a Filter template, you’re presented with the Select a Filter Template dialog box shown in Figure 16-6.

Figure 16-6: Choosing a Filter template.

The Member Selection template is by far the easiest filter template to work with because you're provided with a visual interface that allows you to define the behavior of the filter by clicking and dragging dimension and members. The other filter templates shown in Figure 16-6 require familiarity with MDX or additional SharePoint configurations.

Follow these steps to add a Member Selection filter:

1. In the Select a Filter Template dialog box (refer to Figure 16-6), click Member Selection and then click OK.

The Create a Filter Wizard appears.

2. Enter a name for your filter in the Name text box and then click Next.

3. Select the data connection you want to use for the filter and then click Next.

4. Click the Select Members option on the left side of the Select Members dialog box, as shown in Figure 16-7.

The Select Members dialog box shows the list of dimensions based on the underlying data connection. Some dimensions contain multiple hierarchies to choose from. In Figure 16-7, the Calendar Date hierarchy is selected from the Date dimension.

Figure 16-7: Choose the dimension you want to us to populate the filter.

5. Choose the dimension you want to use to populate the filter and then click OK to close the Select Members dialog box.

While the Select Members dialog box is open, you can right-click a member in your chosen dimension, choose Set as Default Selection, and then click OK. This action sets the member you selected as the default value for the filter.

6. Choose the dimension you want to use to populate the filter and then click OK to close the Select Members dialog box.

At this point, your Create a Filter Wizard looks similar to the one in Figure 16-8.

7. Click the Next button.

8. Select how you want your filter to be displayed. You have three options:

· List: This option displays your filter as a simple drop-down menu that can be used to select a desired filter item.

· Tree: This option displays your filter as an expandable tree control. Your audience will click to expand the tree control and then select individual items to apply as a filter.

· Multi-Select Tree: This option displays your filter as an expandable tree control with check boxes. Your audience can click to expand the tree control and then select individual items with check boxes.

Figure 16-8: The Create a Filter Wizard with selected dimensions and members.

9. Click the Finish button to review the settings.

10. Click the Close button to close the Create a Filter Wizard.

11. Click the Home tab and then click the Publish Item button to save your changes to the server.

Adding a report

Reports are the primary data visualizers in a PerformancePoint dashboard, providing your audience with a friendly platform to view and explore their data. Several types of reports templates are available in PerformancePoint. One of the easiest and most effective is the Analytic grid. The Analytic grid offers your audience a table that you can expand or collapse to see the multiple levels of detail.

Follow these steps to walk through the mechanics of creating an Analytic Grid report:

1. Click the Create tab and then click Analytic Grid.

The Analytic Grid Report Wizard opens.

2. Enter a name for your report in the Name text box.

3. Click the Select Display Folder to specify where you want your report to be saved on the server. Select an existing folder or create a new one. Click the Next button when you’re done.

4. On the Select a Data Source page, select the data connection you want to use as the source data for your report.

5. Click the Finish button to review the settings.

6. Click the Close button to close the Create a Filter Wizard.

7. Click the Home tab and then click the Publish Item button to save your changes to the server.

8. Click the Design tab to get to the window shown in Figure 16-9.

Figure 16-9: Use the Design tab to build out your report.

9. Click and drag the needed dimensions in the Details window to the Rows, Columns, and Background areas (similar to building a Pivot Table in Excel).

10. Click the Home tab, and then click the Publish Item button to save your changes to the server.

The Design tab has five distinct sections:

· The main window: Here you can see the current layout and results of your report as it will appear in your dashboard. This view updates as you drag and drop cube dimensions or measures onto the cube drop area.

· The Details pane: This area on the right lets you browse the dimensions and measures of the cube, which are defined based on your data connection. As you identify dimensions and members that you want to use in your report, you can drag and drop them into the Rows, Columns, or Background areas.

· The Rows, Columns, and Background areas: By dragging dimensions and measures from the Details pane onto these areas, you effectively build a simple MDX query that is used to populate your report. The Rows area corresponds to the horizontal rows of your grid. The Columns area corresponds to the vertical columns of your grid. The Background area contains any hidden filters you want applied to your grid.

Preparing your final dashboard

Dashboards generally have two or more reports, and at least one filter that operates on one or all of the reports in the dashboard simultaneously (called a global filter). In the PerformancePoint Content window, to save time you can copy and paste when you want to generate multiple reports, filters, or data connections that might have only slight variations. Figure 16-10 shows an example where the first report (Internet Sales Amt) was copied and pasted into the PerformancePoint Content section, and modified so that it would show Reseller Sales instead of Internet Sales.

The steps to do this are as follows:

1. In the Background zone of the new report, right-click the measure you want to duplicate and select Delete (or click the x on the measure).

In this case, you right-click the Internet Sales - Sales Amt measure.

2. Drag the measure to the Background zone of the new report.

In this example, the measure is Reseller Sales - Sales Amt.

3. Rename the new report.

In this case, the new report is Reseller Sales Amt.

Figure 16-10: Content ready for a dashboard.

Publishing dashboards

After you have created filters and reports, you're ready to combine these widgets into a meaningful dashboard that can be deployed to SharePoint for personal use or shared with others.

To create a dashboard, right-click the PerformancePoint Content folder in the Workspace Browser and select New → Dashboard. Then you can choose a template. The most common template is the Header, 2 Columns template, which allows you to place one or more filters in the header and multiple reports in the columns.

You can modify the dashboard later to include more rows and columns.

Dashboard authoring takes place in the Dashboard Editor, which works like other authoring interfaces in PerformancePoint. A list of usable content appears in the Details pane (on the right), which you can drag and drop into the Dashboard Content pane in the center. Unlike Reports, this is not a WYSIWYG editor, so you won’t actually see your data until you publish your dashboard to SharePoint.

Figure 16-11 shows a dashboard consisting of the Internet Sales and Reseller Sales reports in the left and right columns of the Dashboard Content area and the Date filter in the header.

Figure 16-11: A completed dashboard design.

Creating dashboard links

When you add one or more filters to the dashboard, they need to “talk” to the reports. PerformancePoint uses links for interaction between objects. Here's how to create a link:

1. Hover your mouse over the filter that you want to use as a source for the link and select Member Unique Name.

The mouse pointer becomes a cross-hair, indicating drag-and-drop functionality.

2. Drag the filter to the “drop fields” area of a report.

A dialog box opens.

3. Specify a field for the Connect To value. Click OK when you're done.

Assuming you used the same dimension in your filter and your report, you see that dimension in the Connect To list; select that one.

You can apply other settings, but the Connect To value is the only one necessary for a functional link between the filter and the report. Do the same for any other reports that you need to update based on selections made in the filter.

Deploying dashboards to SharePoint

Follow these steps when you’re ready to deploy your dashboard to SharePoint:

1. In Dashboard Designer, go to the Workspace Browser pane and click the PerformancePoint Content option.

2. Click the Home tab and then click the Refresh button.

3. In the Workspace Browser pane, right-click the target dashboard and then choose Deploy to SharePoint.

If you’re publishing the dashboard for the first time to a SharePoint site, the Deploy To dialog box opens.

4. Select the SharePoint Dashboards library that you want to use and specify a page template for the dashboard. When completed, click OK.

After your dashboard is deployed, a browser window opens to display your dashboard.

Using PerformancePoint Dashboards

The complexity PerformancePoint dashboards varies greatly depending on who builds them and what they’re designed to show. Some PerformancePoint dashboards are simple grids, but others contain highly interactive components that require some training to use.

In this section, you get a few tips for navigating, filtering, and using some of the trickier interactive components found in PerformancePoint Dashboards.

Interacting with filters

A filter exposes a list, sometimes hierarchical, of attributes by which the data can be shown. Attributes displayed in a filter are called members. For example, CY 2007 is a member of the Date filter. The currently selected members are displayed at the top of the filter, as shown in Figure 16-12.

Figure 16-12: A PerformancePoint filter.

A carat in front of a member indicates another level of children members below the member with the plus sign; for example, clicking the carat for CY 2007 drills down to show the semi-annual members for that year.

Filters are used to modify the views on a dashboard page, similar to running a report for a specific set of criteria by selecting one or more report parameters. Although the ways in which a filter interacts with a particular report can vary, most often the effect is to limit the data to whatever selections are made in the filter.

But there are many other ways to use filters. For example, a selection made in a date filter might translate into “retrieve all transactions that occurred on this date or any date since” for purposes of populating the report that is connected to the filter. Because the filter link properties are only visible in Dashboard Designer during the authoring process, the user doesn’t always know what is happening and you need to document accordingly.

Though you can place a filter anywhere in a dashboard and configure it to interact with one or more views, which may or may not be located in close proximity to the filter, in a good dashboard design, filters generally appear at the top of the page and interact globally with all views that show data that is relevant to the filter. For example, any chart or grid view that shows data across a date dimension links directly to any date filter in the dashboard. A dashboard user determine whether a filter is linked to a specific view by either changing the selections of the filter while observing the dashboard page to see which views refresh based on the filtering action, or right-clicking the white space of a view (not a cell or data point) and selecting Show Information Bar. If the view is linked, the information bar displays the selections that were applied in the filter.

Searching filters

Integrated into the latest version of PerformancePoint, the filter search feature makes it significantly easier to work with large datasets. Using the filter search, you can perform actions such as the following:

· Search through a list of thousands of customers for a specific customer without scrolling. You could find all customers whose names begin with “SMI” or end with “TH”.

· Select all “Monday” members in a hierarchical Date filter without traversing any hierarchies to check individual boxes (assuming the lowest level has a long date format that includes day of week; for example, “Monday, September 18”). To perform this action, you would search for “Monday” and then select the Select All check box at the bottom of the filter. This causes the report data to be filtered to all Mondays. In the past, doing something like this would have required a separate Day of Week filter.

Ignoring the filter

You cannot remove a filter from the dashboard, but there are other ways to ignore it. The top-most member in a filter is usually a catch-all bucket, appropriately named [All]. If you select the All member of the Date filter, you're viewing data for all available time periods. However, sometimes a filter won't show an All member, and in this case you can click the Select Visible button to select all top-level members. This causes all available data to be retrieved at and below the level of the top-most members in the filter. Similarly, you can click this button twice to clear any selections at the top-most level.

Default and remembered selection, security-limited filters

Filters apply changes to dashboard views after members are selected and Apply is clicked. However, the first time a dashboard page is opened, any filters appearing on that page have one or more members selected by default, causing the views to initialize based on certain predefined conditions. For example, the dashboard design may require that the most recently added data be displayed by default, in which case the date filter would have a default member of the current date, or the current month, and so on. Likewise, a Currency filter may default to USD.

It is also possible that two different users of the same dashboard would see different lists of available members in the same filter, and therefore have different default members. For example, if the business data is segmented by organizational responsibilities, with some users falling into a corporate category and others falling into specific business units, corporate users may see all business units while the rest may only see their business units.

Select and multi-select filter behaviors

Some filters allow more than one member to be selected, making user-defined ranges possible, as when the first six members of the level corresponding to months are selected in the date filter, creating a range of the first-half of the year. Filters that allow multiple selections are called multi-select filters and have check boxes to the left of each member (see Figure 16-13).

Figure 16-13: Higher-level selections override lower-level selections.

Multi-select filters differ from single-select in that multiple “buckets” can be selected, which provides greater flexibility for end users to query needed data. However, multi-select filters can be more difficult to navigate:

· A user can't clear a selection. There is no Clear All button, which can cause difficulties when trying to clear selections that have been made several levels deep in a multilevel hierarchy.

· A top-most ancestor overrides all other choices. If a member is selected, but its parent or ancestor is also selected, the top-most ancestor overrides all, as shown in Figure 16-13, where CY 2007 overrides the selections that were made at lower levels of the hierarchy.

The override also applies to the All member, which should be cleared if specific members below it are selected.

Ad hoc filters

PerformancePoint dashboards are enabled for multidimensional analysis. In a given view, dimensions are preconfigured to be located in either the rows, columns, or filter area. However, dimensions can be moved around, and new dimensions can be added based on user interactions.

For example, right-click a member of the Date dimension and select Drill Down By Dimension Location. The Date dimension moves to the filter area (with the member that you right-clicked already selected), and the Location dimension moves to the view where the Date dimension was before.

Dashboard navigation

A dashboard is comprised of links, filters, views, and cells or chart points (collectively called cells). User interactions occur either at the page level, as when clicking a link or changing a filter, or within a view, as when drilling down on a cell. Links make it possible to navigate from one dashboard page to another, each with different data and page layouts. In PerformancePoint, all navigational links appear at the upper-left corner of the dashboard page. When navigating from one page to another, you see the same filter selections if the same filter appears in both pages.

Dashboard interactive capabilities

Many dashboard reports give the user additional capabilities. These features are designed to make the dashboard conform to more of a “self-service” reporting paradigm and allow for data-driven business decisions.

Right-click a chart or grid report — not a data point in a chart — and you find these options to manipulate your data:

· Pivot your data. Choose Pivot and the X and Y dimensions are swapped in the view. You can often get additional insights in the data.

· Change your report type. Choose Report Type and then select a different type. Changing the report type allows you to view the same data in different ways, sometimes deriving additional insights into the data as you change the view. For example, you can change a line chart to a bar chart or a grid. Sometimes this spotlights interrelationships in the data more effectively.

· Format your report. Choose Format Report and then select a new format. In a grid report, you can switch between compact and tabular layouts. In a chart, you can move the legend or remove it completely. Sometimes moving the legend makes a chart more readable.

· Show all available filters. Choose Show Information Bar and all currently applied filters are displayed at the top of the report, making it easy to see which filters are currently selected in the dashboard. This is especially important if you have a report open in its own window to make it easier to see the data points, and you can no longer view the dashboard filters at a glance without tabbing back to the dashboard Web page.

· Drill through your data. You can choose Drill Down, Drill Down To, and Drill Up. These navigation commands allow you to explore the current data point by either lower-level detail, or by breaking out the current data point across another dimension. Figure16-14 shows how one data cell could be broken out by a completely different dimension.

Figure 16-14: Ad hoc drilling.