Sharing SSAS cube data in SharePoint with PerformancePoint Server 2007

More SSAS tips and advice Utilize SSAS for data predictions and classification using Excel Using MDX and UDM in an SSAS environment

Microsoft created a server tool with its own developer and designer environment for this purpose called Microsoft Office PerformancePoint Server (PPS) 2007. The most current release is Service Pack 2, which, in its purest essence, is a performance management toolkit aimed primarily at business domain experts or business users. It is similar to Hyperion, Cognos and other business intelligence tools for financial planners and analysts and sales and marketing teams. The reality is that business intelligence professionals often end up as the primary designers of these solutions due to the complexities involved in ensuring accurate dimension management, extract, transform and load (ETL) processes, key performance indicators (KPIs) management and change management.

PPS has three distinct solution areas that are a combination of three products with separate origins, development staffs and lineage. The first is PPS M&A, or monitoring and analytics. M&A comes from the remnants of the former Microsoft Business Scorecard Manager product: It allows you to build, publish and manage scorecards and publish them on dashboards through a design interface geared toward business users. The second, from the Microsoft group that built the financial planning application formerly known as Biz Sharp, is now the set of business modeler, planning and Excel add-ins for finance in PPS. The third part incorporates the analytics capabilities obtained in the ProClarity acquisition, such as drill-down graphs, ad hoc queries and dashboards.

As PPS progresses in its lifecycle, the plan is to make it more native to the Microsoft Office SharePoint Server offering by moving many PPS capabilities directly into SharePoint. This eliminates the separate ProClarity analytic charts while making the SharePoint designer experience the primary tool for building scorecards and dashboards.

When you use your SSAS 2005 cube to build business dashboard applications, it is not necessary to pre-build BI structures in SSAS before utilizing PPS. You could use the PPS business modeler to build a noncomplex cube structure and models, followed by loading the data from data sources into the staging tables that are included in PPS. But most PPS projects do not allow for very complex or scalable cube structures built through the business modeler. PPS uses SSAS 2005 to build a cube from the configurations you build, but it does not support cube optimization techniques on that derived cube, such as partitioning or different aggregation storage strategies. These limitations have led to Microsoft's recent announcements about an uncertain future for PPS and, in particular, for its business planning functionality. For now, let us focus on the core capability used to build scorecards and dashboards with PPS monitor and analytics capabilities, based on existing SSAS 2005 cubes.

You can start by launching the PPS 2007 Dashboard Designer, which is a click-once .NET application that checks the server for new versions of the tool before launching.

Figure 1: The PPS 2007 Dashboard Designer (Click to enlarge)

In the workspace browser on the left-hand side, you should first configure a data source. Select Analysis Services as a "Multidimensional" data source, as shown in Figure 2.

Figure 2: Select Analysis Services as the data source. (Click to enlarge)

When Analysis Services has been selected as your source, the wizard asks a series of questions to identify the source SSAS 2005 server and the cube where your data structures are stored. Use the selectors to find the appropriate database to be used.

With the data source completed, you can now build a scorecard. We are assuming you've already built KPIs in SSAS. From the Dashboard Designer main screen, select "Scorecards" from the left-hand pane. Create a new scorecard and follow the prompts, starting by providing a new name for the scorecard. To measure business metrics from your KPIs through the scorecard, select the option to use KPIs from Analysis Services. Then select the "Add KPI" button, as indicated below in Figure 3.

Figure 3: Add Analysis Services KPIs (Click to enlarge)

At this point, you can select the individual KPIs already defined in SSAS and give a business user-friendly name to each. The "band method" column tells PPS to display the measurement in terms of which is a better value, increasing or decreasing. Additionally, you must set the target value so that the server can determine the current measure's indicator. From the main designer screen, you have many different icons that can be used for indicators. Keep in mind that the primary purpose of a BI scorecard is to provide quick and easy-to-understand feedback to business users about the current performance of specific business metrics as compared with a preset plan.

Before leaving the definition of the scorecard, look at the right-hand pane for details. You will find cube items that can be used to add to the scorecard. In Figure 4, you can see specific products from the Product Family dimension in this sample.

Figure 4: Add members to your scorecard (Click to enlarge)

This enables you to add dimension members to your scorecard and show status indicators next to specific members, such as values for current sales in specific regions or countries. Drag and drop the measures you wish to include to the far left, with the actual and target values displayed next to each measure. Also be aware of known issues in pre-SP2 versions of PPS that do not allow the measures to update automatically as they change in SSAS. In these cases, you need to open the scorecard in Dashboard Designer and re-publish the scorecard and dashboard. In other words, if your ETL process updates dimensions in the SSAS cube, do not expect that to be reflected automatically in the scorecard.

Once the KPIs are defined in the PPS metadata, we can open the KPIs from the Dashboard Designer. The next step is to set the proper thresholds for each KPI, as in Figure 5 below. Setting the thresholds from best to worst using the slider bars will establish them for the scorecard indicators.

Figure 5: Set thresholds from PPS Dashboard Builder (Click to enlarge)

After closing the KPI editor, publish this scorecard as part of a dashboard. I have included a screenshot as Figure 6 that shows the different components to use in the dashboard when designing it through PPS. An advantage of publishing your dashboard through PPS is that these report types can be published through and use the same filtering capabilities as those in SharePoint.

This allows for a more cohesive BI portal where all SharePoint Web parts are interactive and display data from the same context. This is also where you can include some of the best-of-breed ProClarity drill-down charts to your dashboard and connect them with common filters and other Web parts. PPS 2007 includes its own analytic charts that were designed specifically for PPS, but they do not compare with the superb capabilities and rendering of ProClarity. Indeed, it appears that with the future of PPS up in the air, the functionality of ProClarity may speed the migration to SharePoint for monitoring and analytics in the Microsoft BI product suite and become your dominant charts in the future.

Figure 6: Other report types for PPS dashboards (Click to enlarge)

Before we can publish the scorecard, we need to build a dashboard that includes the scorecard as part of the design. Note that as you work through this process, you may need to refresh or update the designer for new or updated components to appear. Find those controls on the "Home" ribbon, shown in Figure 7. When putting together the dashboard, you will be presented with a design surface that includes several distinct content sections to hold your scorecards, filters and other reports, shown in Figure 8.

Figure 7: The Dashboard Designer (Click to enlarge)

Figure 8: Build the dashboard (Click to enlarge)

When you begin creating a dashboard, the wizard presents you with several different configurations for the design and flow of the dashboard. For the sake of trialing the PPS dashboard builder for this tip, select the header with two columns design. You can then drag the components that you have built from the details pane on the right-hand side into the areas on the scorecard, along with dragging the scorecard that we just built into the left-hand column on the dashboard. Once you have named the dashboard and organize the items appropriately, you can save it and deploy it directly to a SharePoint site. The "Edit" ribbon has a button to deploy to SharePoint.

Once you have deployed successfully, you will now have a fully functioning dashboard in a SharePoint BI portal that can be shared throughout your organization, as shown in Figure 9.

Figure 9: Sample dashboard published through SharePoint (Click to enlarge)

As this is an introductory tip on how to quickly deploy a scorecard and dashboard in PerformancePoint Server 2007, I have omitted details about security and analytic charts. If you have sensitive business data that requires role-based access to what is displayed in the dashboard, set the proper privileges before you deploying to within your enterprise.

ABOUT THE AUTHOR

Mark Kromer is an internationally recognized leader in the business intelligence and database communities, having authored articles and blogs for TDWI, Microsoft, MSDN and Technet. Mark has more than 15 years' experience in IT, focusing on database technologies and solutions, including spending the past 2.5 years with Microsoft as the lead product manager for business intelligence customer solutions. Check out his blog at http://blogs.msdn.com/makrom.