Creating an Analytic Grid Report in Performance Point Server 2007
By: Ray Barley | Comments (15) | Related: > Performance Point
Problem
We are just getting started with Performance Point Server 2007 and trying to help our business users to develop their first analytic report. Can you provide some detail on how we go about doing this at a detailed level? I know we need to setup a number of preliminary items before we actually build the grid report, so insight on those items would be very beneficial. Thank you in advance!
Solution
The PPS product is a recent addition to the Microsoft Business Intelligence offering. PPS enables users to develop a variety of reports and scorecards, then compose them into a dashboard which is deployed to SharePoint. Business users can then simply navigate to a URL in a SharePoint site to access the dashboard.
The focal point for developing business intelligence applications using PPS is the Dashboard Designer, a rich client application with the now familiar Microsoft Office 2007 user interface. We will walk through the following steps to create our first Analytic Report:
- Deploy the Dashboard Designer to our local machine
- Create a Data Source to specify where to pull data for our report
- Create an Analytic Grid Report
- Add the report to a new dashboard
- Deploy the dashboard to SharePoint
Deploying the Dashboard Designer
To deploy the Dashboard Designer, open a browser and navigate to the PPS Monitoring Central site. The URL for the site is http://<server>:port/central; server is the machine where PPS Monitoring was installed, port is the port number for the PPS Monitoring Central website, and central is the site name. These parameters are all set when the PPS Monitoring server is installed; 40000 is the default port. Click the Run button to download and install the Dashboard Designer. When this step is completed, the Dashboard Designer will be added to the Start menu under Microsoft Office Performance Point Server 2007.
Creating a Data Source
To create a Data Source in the Dashboard Designer click the Create tab then click Data Source.
Click the Analysis Services template; the Analytic Grid Report requires a Data Source type of SQL Server Analysis Services (SSAS).
After selecting Analysis Services as the Data Source type, provide a name for the Data Source. If you enter a display folder location, a folder will be added under Data Sources in the Workspace Browser and the new Data Source will be placed into that folder. Checking Grant read permission to all authenticated users is usually a good idea; otherwise you can fine tune the security in a later step.
After clicking Finish on the Create a Data Source dialog, a tabbed dialog is displayed to define the Data Source. The Editor tab is used to specify the connection settings for the SSAS database and cube. We are using the Adventure Works sample that comes with SQL Server 2005.
The Properties tab allows you to specify some general properties, your own custom properties, and permissions. There are two roles in the permissions - Editor and Reader. An Editor can change the Data Source; a Reader cannot.
The Time tab allows you to map the time dimension in your cube to the Time Member Associations (bottom of the dialog). Select the appropriate hierarchy in your time dimension, click the Browse button to select a reference member, then select the Hierarchy level from the drop down that corresponds to the member. In the example below, I selected the earliest day in the time dimension for the Reference Member, specified that this member represents Day in the hierarchy level, then mapped it to the Reference Date of 7/1/2001. The Time Member Associations simply map the standard Time Aggregations to their corresponding levels in the time dimension hierarchy. One important note here is that you can only specify a single hierarchy in your time dimension for the Data Source.
When clicking the Browse button on the Time tab, navigate the hierarchy levels and select a date member as shown below. By selecting a member at the lowest level of the hierarchy, we can work with the entire hierarchy in our reports and scorecards.
For additional details on the time dimension in an SSAS cube, take a look at this tip.
At this point the Data Source configuration is complete. Click the Save button at the top of the Dashboard Designer window to save your work in a file. The Dashboard Designer works just like any other Microsoft Office application; you save your work in a file with a particular extension that identifies its type. The Dashboard Designer file type is called a workspace and the extension is .bswx. The content of the workspace file is actually an XML document.
There is one final step to be completed for any element that we create with the Dashboard Designer and that is to publish it to the PPS Monitoring server. Publishing the element takes its definition in the Dashboard Designer workspace file and saves it to the PPS Monitoring server database where it becomes available for use. There are several options for publishing:
- Right click on the element in the Workspace Browser and select Publish from the popup menu
- Select the element in the Workspace Browser and click the Publish Item icon on the Home tab
- Click the Publish All icon on the Home tab
In the screen shot below, note the pencil icon on the AdventureWorks Data Source. This icon is an indicator that the element needs to be published either because it is new or it has been changed.
Creating an Analytic Grid Report
To create an Analytic Grid report, click the Create tab in the Dashboard Designer then Analytic Grid.
Provide a name for the report. Click the Grant read permission to all authenticated users checkbox to allow others to view the report. You can fine tune the security in a later step if necessary.
Select the Data Source for the report. Note that the Data Source must be published as described above. A Data Source that you created but haven't yet published will not appear in the list below.
After completing the Create an Analytic Grid Report step, the report designer surface is displayed. The user interface is very similar to creating a pivot table in Excel. The Details pane (on the far right) shows the measures, dimensions and named sets from the cube. Expand the Measures and Dimensions nodes to view the available options. The bottom portion of the designer surface has containers for rows, columns and background. Select measures and dimensions from the Details pane and drag/drop them onto the appropriate container (rows, columns or background). We'll discuss Background in a future tip, showing how it works with dashboard filters.
To create our first report, follow these steps:
- Drag Sales Amount and Gross Profit from Measures in the Details pane and drop onto the Columns area
- Drag Product Categories from Dimensions in the Details pane and drop onto the Rows area
You should see the following report:
Click on the triangle (next to the X) on Product Categories in the Rows container to drill down into the Product Category members. Click the selections as shown below:
The report should refresh and now look like this:
Now that we have created a sample report, let's explore some of the built-in features. Click the Browse button in the report designer surface. The report will be displayed in a new window. Note the icons across the top of the window. From left to right, clicking on the icon will perform the following action:
- Go back
- Go forward
- Show grid
- Show bar chart
- Show stacked bar chart
- Show 100% stacked bar chart
- Show line chart
- Show line chart with markers
- Filter empty series
- Filter empty bottom axis
For example click the Show bar chart icon and you will see the following:
From the bar chart there are many more options available to explore the data. Right clicking on one of bars of the chart will popup a menu allowing you to drill down, drill up, sort, filter and pivot. As an example, right click the Bikes bar and select Drill Down from the popup menu. This will redisplay the report with the breakdown of the Bikes category as shown below:
Remember that you can click the Back icon to go back.
Now that we have created a sample Analytic Grid report, we need to publish the report. Our final step is to create a dashboard and add our report to it.
Creating and Deploying a Dashboard
Dashboards are the main element that we create using the Dashboard Designer. The dashboard is a container for reports and scorecards. We layout the dashboard in the Dashboard Designer then publish it to SharePoint where users can access it. PPS comes with a set of SharePoint web parts that are used to render a dashboard.
To create a dashboard click the Create tab in Dashboard Designer then click Dashboard.
The first step in creating a dashboard is to specify the layout. Although you have to pick an initial layout, you can make changes to it later.
The second step in creating a dashboard is to specify a name.
After specifying the dashboard name, the Dashboard Designer displays the design surface for the dashboard.
The design surface initially reflects the layout that we chose. You can now drag and drop scorecards, reports and filters from the Details pane onto the Header, Left Column or Right Column containers. To complete our example drag the Sample Analytic Grid Report from the Details pane and drop it onto the Left Column container. The designer will now look like this:
The design experience is not WYSIWYG as you might have expected. However, you can preview the dashboard before deploying it to SharePoint. Before you can preview the dashboard you have to publish it. After publishing the dashboard click the Edit tab then click the Preview icon. You will see the following:
The Preview function creates a web page on the fly and renders the dashboard using the PPS web parts. When you are satisfied with the dashboard you can publish it to SharePoint. Click the Edit tab in the Dashboard Designer then click the SharePoint Site icon. You will step through a couple of dialogs then the dashboard will be available on a SharePoint web page.
Next Steps
- Take a look at Performance Point Server. It is a new tool in the Microsoft BI stack that will help you to provide the kind of Business Intelligence applications that users want. For a great starting point and to download an evaluation copy visit this site.
About the author
Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.
View all my tips