Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Creating an Analytic Grid Report in Performance Point Server 2007


By:   |   Last Updated: 2008-03-19   |   Comments (15)   |   Related Tips: > 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.
  • Download a copy of the sample Dashboard Designer workspace here and experiment with the Dashboard Designer.  You can get the Adventure Works BI database used in the sample here (click on AdventureWorksBICI.msi).


Last Updated: 2008-03-19


next webcast button


next tip button



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, April 08, 2014 - 6:19:59 AM - venkatreddy Back To Top

hi,

   this is so helpful to me.

 

Regards,

 venkat reddy


Thursday, August 16, 2012 - 6:35:56 PM - caroline Back To Top

@Ray Barley - hm... that does not really help... i think microsoft must have a workaround solution here... i saw in forums this request floating around but from 2008 on - so i think MS is well aware of this problem just was curious if there is some solution...


Tuesday, August 14, 2012 - 2:38:47 PM - Ray Barley Back To Top

Not that I know of.  A simple alternative would be to have multiple grids instead of one big one.


Tuesday, August 14, 2012 - 1:59:57 PM - caroline Back To Top

 

Is there a possibility to freeze the panes of a grid - e.g. in the headers where you show product categories, sales amount etc.? We are working with those grids but they are pretty big and user has to scroll back and forth to see to which category the numbers apply for.

Many thanks for a short answer.

Caroline


Friday, June 26, 2009 - 5:45:07 AM - hellsbells Back To Top

 I will try the blog. Thanks for your help, much appreciated.


Friday, June 26, 2009 - 5:35:44 AM - raybarley Back To Top

Some of the best information I've gotten has been from the PerformancePointTeam blog at http://blogs.msdn.com/performancepoint/default.aspx

Some of the contributors are actually on the development team. I would try there.

I have seen some strange rendering issues so you might try creating a brand new analytic grid report and putting it on a brand new dashboard.  I know it's a long shot but I would give it a try.


Friday, June 26, 2009 - 5:13:05 AM - hellsbells Back To Top

[quote user="raybarley"]

I haven't seen this problem myself.

Are you sure your SharePoint application pool is running under the same account as the application pool for the preview? 

[/quote] 

 

 Yes. I have 2 reports running on one dashboard. One report displays all the dimensions, and the other report only display the first dimension - it seems like the list is just not rendering for some reason. I am very new to this so I'm doing a lot of trial and error at the moment. Could it possibly my data source is not configured properly? (Bare in mind that the drill downs work fine in design mode)

 

Thanks for your help!


Thursday, June 25, 2009 - 6:26:55 PM - raybarley Back To Top

I haven't seen this problem myself.

Are you sure your SharePoint application pool is running under the same account as the application pool for the preview? 


Thursday, June 25, 2009 - 10:37:12 AM - hellsbells Back To Top

[quote user="kishore_jeethuri"]

I have created an analytical report. With browse option i am able to drill down but when i deploy the same in Sharepoint I am not able to drill down as the dimension attributes are not appering. Please suggest

[/quote]

 

Did you ever figure this issue out? I'm having the exact same issue.

Thanks.


Wednesday, April 09, 2008 - 3:23:18 AM - raybarley Back To Top

Here are a couple of additional suggestions / questions:

First what is your environment? Is everything on one server or virtual PC or multiple?  My test environment is a single virtual PC image with PPS Monitoring and WSS 3.0.  To the extent that your environment differs we might go down different paths to resolve this.

The best resource that I know of is the Deployment Guide for Performance Point Server; you can download it here: http://go.microsoft.com/fwlink/?LinkId=106633  There are additional technical white papers available at http://www.microsoft.com/business/performancepoint/resources/whitepapers.aspx as well but I think the Deployment Guide is the best one for your issue.

Some options to consider (refer to the Deployment Guide for Performance Point Server for the details):

Use the same identity for the SharePoint - 80 application pool as the PPS Monitoring server's application pools (there are 3). 

Enable per-user connections which will pass the user's credentials to be authenticated against the data source instead of the application pool's identity.  If the data source(s) are on separate machines from the SharePoint install then you will also need to enable Kerberos. 

Another resource worth checking is the Microsoft Performance Point Team Blog: http://blogs.msdn.com/performancepoint/default.aspx

 

 

 


Tuesday, April 08, 2008 - 10:29:43 PM - kishore_jeethuri Back To Top

Thanks for your reply boss, I tried but that didn't work out. Dimension attributes are not appearing on deploying the report on Sharepoint. I doubt anything to do with IE settings. Any idea?


Tuesday, April 08, 2008 - 4:01:05 AM - raybarley Back To Top

In my test environment I encountered a similar situation to yours but not exactly.  I think the solution is as follows:

Check the Identity of your SharePoint Application Pool (you do this in IIS Manager).  The default is Network Service but yours may be different.

Open SQL Server Management Studio and connect to your Analysis Server.  Add a Role to your Analysis Service database.  Give it a name like ReadOnly.  Edit the Role properties and add the identity of your SharePoint application pool to the Membership page.  Go through the Data Sources, Cubes, and Dimensions pages and make sure that the Role has Read access to everything.

Finally go back to the Dashboard Designer and edit your data source.  Enter the Role that you created in the Roles text box on the Editor page then publish your data source.

Try to access your dashboard in SharePoint and see if it works. 

 


Tuesday, April 08, 2008 - 2:04:45 AM - kishore_jeethuri Back To Top

I have created an analytical report. With browse option i am able to drill down but when i deploy the same in Sharepoint I am not able to drill down as the dimension attributes are not appering. Please suggest


Friday, April 04, 2008 - 7:20:20 AM - raybarley Back To Top

Here are some things to check. By default Performance Point connects to the SSAS cube using the credentials of the application pool. Make sure that this account isn't denied the ability to do drill through in the cube. A quick way to check this would be to run the application pool as your credentials temporarily and see if the problem goes away. Another thing to check is to check the calculation setting for the cell in the scorecard. It must have a setting of Source data. As an aside views created on the Query tab using MDX do not support ad hoc navigation. I know this isn't your specific issue but I'm just pointing that out since you're already using MDX for other things. Users will not be able to drill down or expand on these views.


Thursday, April 03, 2008 - 10:20:52 PM - hameer.saleem Back To Top

Well whenever i right click and try to drill down either on Analytic Chart or Scorecard KPI.

It gives me a message that you do not have permissions to view the contents of this cell.

Note that some of my KPIs are not directly related to a dimension, they care being calculated through MDX.

But this is happening with all KPIs on scorecard and Bar on Analytic Chart.

 

 


Learn more about SQL Server tools