Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Archive for the ‘PerformancePoint’ Category

Using Reporting Services Report (SSRS) with PerformancePoint Services (PPS)

Posted by denglishbi on December 31, 2010

[tweetmeme source=”denglishbi” only_single=”false”]

UPDATE (6/21/2011): Modified table layout of step-by-step walkthrough to be two columns instead of three. Added a downloaded PDF file as well.

Back in January I did a posting in regards to Using Excel Services Reports with PerformancePoint Server (PPS).  This has been a very popular posting and I thought I would add another one in regards to using Reporting Services (SSRS) reports with PerformancePoint (in PPS 2007 this type of report was called SQL Server Report).

Some of the reason that you might want to include a SSRS report in your PPS dashboard would be because:

  • leverage an existing report created by an end-user
  • incorporate existing operational reports
  • use additional charting options – map, area, range, scatter, polar, bar (not column), funnel, 3D, sparklines, data bars
  • need more flexibility and control over reports, styles, colors, scales, etc.
  • join multiple data sources into a single report
  • combine relational and OLAP data into a single report

The example that I will be showing is using SSRS in SharePoint Integrated Mode, but you can also do this in Native Mode as well, you would just see a different setup screen when you are configuring the report in Dashboard Designer (a tad bit easier in my opinion configuring these in Native Mode – which is labeled as ‘Report Center’ mode in Dashboard Designer, confusing I know…).  I will also be using Report Builder 3.0 to create and deploy the report to the SharePoint site.

image image
Go to Report Library in SharePoint site, select Documents from Ribbon, select New Document, and pick Report Builder Report This will either launch Report Builder or ask you if you want to run and install the application if you haven’t done so yet
image image
Report Builder is a ClickOnce application and by clicking Run you will install the application Once installed the Report Builder application will start up
image image
In this example we will build a Map Reposition the map up a bit so it appears above the legends
image image
A Bubble Map will be used to be able to analyze two metrics A new data set will need to be added that contains the spatial data
image image
A new data source will be added connecting to the Contoso Retail DW SSAS database Use the Sale cube, filter for the United States, setup the Fiscal YQM as a Parameter, pick State Province Name, Sales Amount, and Sales Total Cost
image image
Use STATENAME and map this to the State Province Name field from the data set Pick a theme for the style, setup the bubble size to visualize Sales Amount, and polygon color for the Sales Total Cost
image image
Setup Chart and Legend titles, polygon tooltip, remove color legend, resize/reposition map, and remove default marker size Save report to SharePoint library
image image
Now we are going to add a new Report to our existing PerformancePoint Content library This will launch Dashboard Designer and like the Report Builder you may be prompted to install it (this is also a ClickOnce application)
image image
If nothing launches then you need to make a small adjustment in your IE security settings to Enable ‘Automatic prompting for file downloads’ Now we will create the new PerformancePoint Report
image image
Use the SharePoint Integrated mode, specify the URLs for the Report Server and the RDL file, uncheck the Show toolbar, and specify a name for the PPS report Next we will create a filter that we can use with the report once it is displayed in the dashboard page
image image
The filter we will create will be for the Fiscal YQM and we will remove periods that don’t have any Sales Amount We will use a Tree style display and only allow a single selection
image image
Name the filter and get ready to create the dashboard Add a new Dashboard item
image image
Name the dashboard item, page, add the filter, add the report, and remove the extra column (zone) on the page Create a Connection (formerly link in PPS 2007) between the filter and the report
image image
The filter will connect to the DateFiscalYQM parameter on the report and will pass the Member Unique Name (an SSAS member value to the report) Save the PPS content items and deploy the dashboard to the Dashboards library
image image
Select the Master Page and whether or not you want to include the page navigation or not Test out the filter and view the results with the deployed PPS dashboard

My example here used the Contoso Retail DW sample data which is available from the Microsoft downloads here – Microsoft Contoso BI Demo Dataset for Retail Industry.  This is also using Reporting Services 2008 R2 which includes the new Map report item, Report Builder 3.0, PerformancePoint Services, and SharePoint 2010 Enterprise.

I have two other postings that I did earlier in the year in regards to the new Map report item here that you can check out if you have questions in regards to that:

Download:

Feel free to download the PPS Workspace file (ddwx) and the SSRS report (RDL) file from my SkyDrive which I have included in a zip file.

image image

You might find this posting useful if you want to reuse the workspace file – Migrating PerformancePoint 2010 Content to New Server.

Enjoy and Happy New Year!

Posted in PerformancePoint, Reporting Services | Tagged: , , | 17 Comments »

PerformancePoint Services 2010 Analytical Grid Filter Fix, Sort of

Posted by denglishbi on December 23, 2010

Back in September there was a forum posting that was looking for a solution to resolving the filtering issue with Analytical Grids in PerformancePoint – filter value function not working on analytic grid.  The filtering functionality was working fine with Analytical Charts, but for Grids there seemed to be a slight bug.  When the Top filter was applied to an Analytical Grid report you would received messages like the following:

There is no data to display.

Drag measures, dimensions, or named sets to Rows and Columns to lay out the report.

there are no data rows to display

image

So what was going on, because if you switched the Report Type from Grid to say Bar Chart it worked just fine…

image

If you set this up in a Chart and then flipped back to Grid then it would retain the setting.  If you modified the filter at this point then you would get what you see in the first screenshot above.

Well just this month a new Cumulative Update was released for SQL Server 2008 R2 – Cumulative Update 5.  It just so happens that one of the fixes in this was related to PerformancePoint Services! 

FIX: An analytic grid that is connected to SSAS 2008 R2 returns incorrect data when you apply a filter to the analytic grid in PerformancePoint Dashboard Designer

I typically wait until a Service Pack is released, but I always review the fixes that are included in the Cumulative Updates, because you just never know when you might uncover an item that could resolve an issue that you have been having or might not have been aware of.

In order to get this fix you need to request the hotfix from the main page – Cumulative Update package 5 for SQL Server 2008 R2.  Once you do this you will get an email with the download link and also a password to unzip the actual hotfix package that you can install.

Here are some screenshots of the installation process which took approximately 10 minutes to install on my machine:

image image
image image
image image
image image
image image

 

Once you get this installed you will be on SQL Server 2008 R2 version number 10.50.1753.0.  After I performed the install it did require a reboot in my situation.

Now we go back and test out our filter functionality on the Analytical Grid and the results are…

image

Well we didn’t get an error like the one in the first screenshot, but it doesn’t apply the filter properly like it does if we switch over to an Analytical Chart:

image

It appears that they fixed part of the issue, but it is still not handling the user defined hierarchy filtering.  If we use just a attribute hierarchy then it works just fine.

image

So at this point it will take a little more investigation and a follow-up reply to the original forum posting.

Posted in PerformancePoint, SQL Server | Tagged: , | 2 Comments »

Migrating PerformancePoint 2010 Content to New Server

Posted by denglishbi on November 20, 2010

So you created some cool dashboards with PerformancePoint Services 2010 with SharePoint 2010 and now you want to deploy them to a new environment (server).  How do you do this?  Well with PerformancePoint 2007 there were a couple of options, you could save your work to a workspace file, connect to new environment, and then publish everything.  The other option was to use the BI Report Automation Publisher tool available on CodePlex.  With the tool you were able to reference a workspace file as well, reference certain items, and make appropriate adjustments for server references.  Both were easy options and straight forward for the most part.

Now with the 2010 version this has changed.  I will have to admit that at first I got thrown for a loop and was confused myself since I wasn’t able to simply use the new workspace file, connect to new server, and save the content.  I even tried modifying the file and making environment URL reference changes to try and get the 2007 deployment option to work.  Needless to say I initially started to recreate items in the new environments from scratch and I guess I was not the only one.  I ended up posting an item in the forums – Deploy PPS 2010 dashboard content to new server – to seek some advice and Umair Khan pointed me in the right direction and refreshed my memory.

In PerformancePoint Services 2010 we need to start out by opening up Dashboard Designer and connecting to the new environment.

image

Click on the Import Items button in the Home Ribbon of Dashboard Designer and select your workspace file (this will be the file from your other environment that contains the content that you want to migrate and move to the new server).

image

Now you will walk through a wizard to select the content that you want to import to the new server (environment).

image

In this case I am going to do both items and I only have one location for each on my new server that I am moving the content to.

image

image

image

The items will now be imported and saved to the specified locations and you will receive a results page.

image

You can then add the items to the workspace and start to make the necessary changes. 

image

This is similar to the 2007 way where you have to make all of the necessary changes after the fact when you simply connect and publish the items from an existing workspace file.  It would be nice if there were some options to make some changes to the connections or report file references, but it does move the content.

For some more information on this check out the online content here – Import PerformancePoint Dashboard 2010 content from a SharePoint Server 2010 server, site, or list.

Posted in PerformancePoint | Tagged: , | 10 Comments »

SharePoint 2010 Business Intelligence Virtual Lab

Posted by denglishbi on November 18, 2010

Today I was browsing through the SharePoint 2010 general forums to see if there were any unanswered PerformancePoint questions and found one about PerformancePoint 2010 tutorial.  I replied linking back to Free Online PerformancePoint Services 2010 Training Videos and then someone later posted a reference to a new Microsoft Virtual Lab that I had not seen before MSDN Virtual Lab: Developing Business Intelligence Applications (filed under Microsoft Office SharePoint Server).  The description of this new lab (and it must have been posted this month based on the lab manual screen shots because the MonthToDate TI forumla returns November 2003 in the PerformancePoint lab) is the following:

MSDN Virtual Lab: Developing Business Intelligence Applications

After completing this lab, you will be better able to use the Chart Web Part to create graphical representations of data within SharePoint lists, use Microsoft Excel 2010 to examine and data from SQL Server Analysis Services, and publish an Excel workbook with Excel Services to make it accessible to users using a browser. You will also be able to work with a PerformancePoint Services site and the new Dashboard Designer.

I went ahead and printed out the manual and went through the lab with about 15 minutes to spare on the end (server was a bit unresponsive at times – and you get 90 minutes to complete the lab).  I have not used the Chart Web Part before, so that was something new (I stick with PerformancePoint, Reporting Services, Excel Services, etc.) and the Excel Services portion wasn’t too bad (got to add a sparkline).  The Chart Web Part does provide 3D and Tooltips, something that Excel Services doesn’t provide though…interesting.  The PerformancePoint KPI setup was a good exercise using the Time Intelligence portion of PerformancePoint as well.  All-in-all I would give the lab maybe a 7 out of 10.  There were a couple of minor typo issues in the manual on pages 28 and 41 from what I saw (one stating to map to 2009 and then tells you to type 2010 and the other saying to ‘mote’ to a URL), but overall it was very straight forward.

Definitely nice to have these available and provide you the ability to test drive the technology and get exposure to these items.  And of course it is FREE training opportunity that you can do on your own time.  So what are you waiting for, get over there and check it out.

Posted in Business Intelligence, PerformancePoint, Training | Tagged: , , | 1 Comment »

PerformancePoint Unexpected Error Occurred 11861

Posted by denglishbi on October 12, 2010

From time-to-time when I am building out a new dashboard with PerformancePoint Services 2010 I will all of a sudden get an unexpected error when I try to create an Analytical Report.  When I create the report and add a measure to the report I simply get the error message “An unexpected error occurred. Error 11861.”

image

An unexpected error occurred. Error 11861. Additional details have been logged for your administrator.
Contact the administrator for more details.

When I try to add items to the report I keep getting a message stating “This data source cannot be used because PerformancePoint Services is not configured correctly. Additional details have been logged for your administrator.”

image

That is a funny message.  Kind of scary as well.  Is my PerformancePoint broken?  Oh no!  Help!  What did someone do to the service?  Wait a second, I just got done setting up KPIs with no problems.  What is going on?

Well the issue is that you have created a data connection that is currently not published (saved) to the server.  KPIs don’t have any issues with this, but the Analytical Reports do.  You will notice other odd things during your PerformancePoint development like this where these two items behave a bit differently.

So if we take a look in the Workspace Browser we will see the pencil on the data source icon in the data connections.  This means that it has been modified and is basically in edit mode.  This could also be the case if it was already saved (published) to the server, but in this case I have not performed an initial save.

image

So if we select the data connection and click the Save icon in the Quick Access Toolbar or do a right-click and select Save then the data connection will end up looking like this:

image

Now if we go back to our Analytical Report we were creating we will see the following message:

image

Drag measures, dimensions, or named sets to Rows and Columns to lay out the report.

Now the designer is happy and we can build out the report and add items to the rows, columns, background, etc.

image

So if you get this message don’t be alarmed and go running off to your SharePoint Farm Administrator, simply take a look at your Workspace Browser and see if you have a connection that needs to be published.

Posted in PerformancePoint | Tagged: , | 20 Comments »

PerformancePoint Services PowerPivot Data Sources Error

Posted by denglishbi on July 21, 2010

So I was working on a PerformancePoint 2010 dashboard and making really good progress.  I then ran had to add a new source into a PowerPivot (Excel 2010) file and the source was a SharePoint 2010 list.  I went out to the SharePoint list and clicked on the Export as Data Feed option in the SharePoint Ribbon List options.  When I pulled this atom feed into PowerPivot I ended up receiving an error message (even though the connection tested out okay) stating

“For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method”

So I checked the advanced settings of the connection setup, but didn’t see anything related to this.  I then went ahead and did a quick search and came across this posting from Dave Wickert Using a SharePoint list as a data source and realized that when the SharePoint 2010 server was setup that the ADO.NET Data Services 3.5 SP1 hotfix was not installed on the server.  I went ahead and installed this which then required a reboot for the fix to take effect.

When the server was back up I could then proceed with the SharePoint list import into PowerPivot, but then I realized that none of the PowerPivot data sources in PerformancePoint Dashboard Designer were working.

image 

“This action cannot complete because PerformancePoint Services is not configured correctly.  Additional details have been logged for your administrator”

And when I went into a report referencing the PowerPivot source in the designer I got this message:

image

“This action cannot complete because PerformancePoint Services is not configured correctly.  Additional details have been logged for your administrator.”

Interesting since everything was working fine prior to the server reboot.  So I went and took a look at one of the deployed dashboards and noticed that nothing was working.

image

image

Well this isn’t good.  So I checked that the PowerPivot files were accessible in SharePoint and were working properly which there were.  I then check the authentication setup on the PerformancePoint PowerPivot data sources to verify everything was still setup properly.  The data sources were configured to utilize the Per User Identity option.  Typically this might require Kerberos to be configured, but in our case we were using PowerPivot which is claims based aware and this should work…assuming that the Claims to Windows Token Service is actually started (Information on SharePoint Services that need to be started).  So I go ahead and take a look at the services running on the server.

image

Oh no, the service has not started yet.  I go ahead and start the service and retest my PowerPivot data source connections in Dashboard Designer

image

Success!  We are off to the races.

For some additional information on troubleshooting PerformancePoint Services check out this posting here – PerformancePoint Services troubleshooting.

UPDATE (7/21/2010): Kasper de Jonge just notified me about an issue with this service and once again Dave Wickert has a blog posting about it – Help: c2wts has fallen and it cannot get up.  Yesterday I waited for over 10 minutes and it still had not started.  Looking forward to implementing this fix.

Posted in PerformancePoint | Tagged: , | 6 Comments »

PerformancePoint Server 2007 Build List and Hotfix Update

Posted by denglishbi on May 22, 2010

Occasionally as I run into something odd with PerformancePoint Server or Dashboard Designer I will take a peek out on the Microsoft Support site to see if there are any new hotfixes available.  I had to admit that this is not a fun task by any means because of how the KB article titles are named and the results that are displayed in the search, plus there is no sorting capabilities (at least none that I am aware of).  What I end up doing is running a PerformancePoint Server 2007 Hotfix search on the support site.  Then once I have the results I review the KB article numbers to look for any results that are higher than the previous hotfix or SP that I am aware of.  Not a fun task, but at least it is something.  Once I find these I then review the fixes to determine if there is anything important that might be worth implementing or if it possibly resolves an issue that I am currently experiencing.PerformancePoint 2007

What I have found out is that since SP3 there have been four hotfixes released, the first one was for Planning and the last three were for Monitoring & Analytics.  The only reason that I found out about the last one (which is currently not publically available yet, but should be very soon) was because I contacted some people on the PPS team directly.  I am trying to get this content to be more publically facing, easier to access and get updates on, and also to possibly look into creating a setup similar to what SQL Server has with their Connect site (so that we can submit bugs, suggestions, and review the status) like this example – OLAP Browser.

Here is the complete list of build numbers for PPS 2007 since RTM along with the links to the KB articles.  Just a reminder, the hotfixes are cumulative, so if you install the latest one it will include all prior fixes and service packs as well.

Build Number KB Article Title
3.0.4512.00 KB 2401959 PerformancePoint Server 2007 hotfix package: September 14, 2010
3.0.4510.00 KB 983228 PerformancePoint Server 2007 hotfix package: June 14, 2010
3.0.4506.00 KB 982016 PerformancePoint Server 2007 hotfix package: April 21, 2010
3.0.4504.00 KB 979684 PerformancePoint Server 2007 hotfix package: February 11, 2010
3.0.4502.00 KB 978617 PerformancePoint Server 2007 hotfix package: January 8, 2010
3.0.4501.00 KB 977619 PerformancePoint Server 2007 hotfix package: November 30, 2009
3.0.4417.00 KB 975250 PerformancePoint Server 2007 Service Pack 3
3.0.4318.00 KB 971928 PerformancePoint Server 2007 hotfix package: June 17, 2009
3.0.4317.00 kb 970524 PerformancePoint Server 2007 hotfix package: May 26, 2009
3.0.4315.00 KB 969842 PerformancePoint Server 2007 hotfix package: April 28, 2009
3.0.4314.00 KB 968471 PerformancePoint Server 2007 cumulative update package: March 31, 2009
3.0.4313.00 KB 968131 PerformancePoint Server 2007 hotfix package: February 26, 2009
3.0.4312.00 KB 967900 PerformancePoint Server 2007 hotfix package: February 24, 2009
3.0.4311.00 KB 967343 PerformancePoint Server 2007 hotfix package: February 9, 2009
3.0.4305.00 KB 961878 PerformancePoint Server 2007 hotfix package: February 2, 2009
3.0.4300.00 KB 961041 PerformancePoint Server 2007 hotfix package: December 22, 2008
3.0.4209.00 KB 958291 PerformancePoint Server 2007 Service Pack 2
3.0.4108.00 KB 957166 PerformancePoint Server 2007 hotfix package: August 29, 2008
3.0.4107.00 KB 956553 PerformancePoint Server 2007 hotfix package: August 21, 2008
3.0.4105.00 KB 955751 PerformancePoint Server 2007 hotfix package: July 28, 2008
3.0.4104.00 KB 955432 PerformancePoint Server 2007 hotfix package: July 14, 2008
3.0.4103.00 KB 954710 PerformancePoint Server 2007 hotfix package: July 1, 2008
3.0.4102.00 KB 953766 PerformancePoint Server 2007 hotfix package: June 13, 2008
3.0.3917.00 (Planning) KB 948060 PerformancePoint Server 2007 Service Pack 1
3.0.3916.00 (Monitoring) KB 948060 PerformancePoint Server 2007 Service Pack 1
3.0.3715.00 KB 951326 PerformancePoint Server 2007 hotfix package: April 11, 2008
3.0.3714.00 KB 949690 PerformancePoint Server 2007 hotfix package: March 3, 2008
3.0.3712.00 KB 948718 PerformancePoint Server 2007 hotfix package: February 7, 2008
3.0.3711.00 KB 947262 Cumulative Update 1 for PerformancePoint Server 2007
3.0.3709.00 KB 946222 PerformancePoint Server 2007 hotfix package: December 19, 2007
3.0.3705.00 KB 945663 PerformancePoint Server 2007 hotfix package: November 29, 2007
3.0.3703.00 KB 944842 PerformancePoint Server 2007 hotfix package: November 15, 2007
3.0.3702.00 KB 944452 PerformancePoint Server 2007 hotfix package: November 8, 2007
3.0.3701.00 KB 944387 PerformancePoint Server 2007 hotfix package: November 1, 2007
3.0.3523.00 – 3.0.3524.00   RTM – International
3.0.3520.00   RTM – English

 

You can also check out the build information on the TechNet site, http://technet.microsoft.com/en-us/library/cc298347(office.12).aspx, but this is not a complete list.  If you are using PerformancePoint I would highly recommend installing the current latest public available hotfix to get to build 3.0.4504 because that resolves an issue of unnecessary MDX being issued, so it will reduce overhead and improve load performance.  The latest hotfix (for 3.0.4506) that will be coming out shortly (and I guess another one will be coming out next month in June – not sure what that one will fix) resolves the following issues:

  • Character limit for MDX queries is too low
  • SP3 regression causes multiple font sizes to display in scorecard

I am hoping that this list will possibly be made available on the PPS Team Blog and hopefully at some point some sort of mechanism will be made available so that we can post bugs and suggestions so that we can have a more direct link to the product team to work on not only improving the product but also getting more direct input as to the direction that it is heading.

A couple of items that I have on my list, and these are just a couple, would be

  • cascading parameters (filters) like Reporting Services – I believe we will actually see this for PPS 2010 later this year
  • improvement in Tree filter support that have more than 500 members – way to slow and the list view is not pretty when dealing with hierarchies (apparently improvements have been made in PPS 2010, but that does not help for PPS 2007 users)
  • please get the PerformanceMap incorporated in PPS 2007 (and 2010).  This is an extremely powerful visualization that is in ProClarity and in my opinion more important than the eye candy Decomposition Tree.  The ability to analyze two metrics simultaneously and interact with them provides a ton of insight and analytical power to the end-users.  I don’t think this will happen for 2007 just like Decomposition Tree was not and displaying the ProClarity views is just not the same.

Well that is all I have for now.  Apparently there should be a posting in the near future on the PPS Team Blog to provide the steps needed to display a Decomposition Tree report as a default report view in a PPS 2010 dashboard, so keep monitoring their blog.  If you have any other comments or suggestions make sure you leave comments on their blog postings.  The more we make our voices heard the sooner we should see results.  We need to do our part to help shape the product as well.

Updated (7/7/2010): Link for the most recent hotfix has been officially published now for KB 982016.  Included the link in the list.  A new searching mechanism is now available, at least I believe this is new – TechNet Support.  Just in, new June 2010 hotfix has been released that includes a couple of new fixes – KB 983228.

Updated (12/24/2010): A new hotfix was released for Planning when opening a form in Business Modeler – KB 2401959.

Posted in PerformancePoint | Tagged: | 1 Comment »

Free Online PerformancePoint Services 2010 Training Videos

Posted by denglishbi on April 17, 2010

Are you looking for some training on the PerformancePoint Services? Do you have 50 minutes to spare?  Well you are in luck, you can head over to Up to Speed with PerformancePoint Dashboard Designer and checkout the six videos that are available to give you an overview of the product on how to use it and deploy dashboard solutions in SharePoint 2010.

Here is what is available for you to checkout and I have included the duration of each video:Microsoft SharePoint

The examples that are done are utilizing the new Microsoft Contoso BI Demo Dataset for Retail Industry that appears to be replacing the Adventure Works data going forward.

If you are unable to use audio or are hearing impaired you can utilize the caption option in the videos.  By clicking on this option in the toolbar –> image you will get the closed captions added to the video.

image

By no means are the items that are created in these tutorial videos using any best practices or production ready  dashboards.  The scorecard that gets created in Video 3 does get cleaned up a bit in Video 5.  The metrics referenced still don’t get any formatting applied to them, but at least the target isn’t referencing the same metric as the actual.

The report that gets added in Video 4 is interesting and confused me initially until I realized that the first metric being displayed was Sales Quantity, not Sales Amount…

image

I was wondering how the Sales Gross Margin could be greater than the Sales Amount until I realized that I was actually looking at Sales Quantity that was formatted with Currency format.  NOTE: in general, make sure that the metrics in the cube have the proper formatting associated with the metric.

With the changes that have been done now by integrating the PerformancePoint product into SharePoint you will see that the Deployment has been greatly streamlined and has removed all of the pop-up windows that you previously had to deal with.  You will also notice that in the Properties tab of the Dashboard items you have a new section added for the Deployment Properties.

image

Overall the videos are a good start for anyone that wants to familiarize themselves with the Dashboard Designer tool in PerformancePoint Services that is part of SharePoint 2010 (Enterprise Edition).

If you are looking for more material in regards to SharePoint 2010 you can head over to this site for more content – http://office2010.microsoft.com/sharepoint-help.

If you are still using PerformancePoint 2007 or want to get up-to-speed with that product then head over to here – PerformancePoint Server 2007 training courses.

Posted in PerformancePoint | Tagged: , | 7 Comments »

Using Excel Services Reports with PerformancePoint Server (PPS)

Posted by denglishbi on January 24, 2010

[tweetmeme source=”denglishbi” only_single=”false”]

UPDATE (10/1/2010): I corrected the image of the screenshot for configuring the Products parameter in the Excel file.  I originally had one displayed with DateFiscal for the setup instead of the one referencing Products.  I apologize for that.  If you wanted to do one for DateFiscal you would need to setup the named range for Sales!$B$4.

UPDATE (6/21/2011): Modified table layout of step-by-step walkthrough to be two columns instead of three. Added a downloaded PDF file as well.

One of the nice features about creating dashboards with PerformancePoint Server (PPS) is the ability to incorporate different types of reporting types like Analytical Grids/Charts, SQL Reporting Services Reports (SSRS), ProClarity Analytical Views, and Strategy Maps.  Another one is being able to leverage Excel Services.  As we all know Excel is one of the most widely used tools, so it only makes sense that at some point we will probably want to be able to integrate an existing Excel file within the organization.

Some of the reasons that you might want to use Excel as a report in a PPS dashboard could be:

  • use an Excel report that has already been developed by end-user
  • leverage some of the nice Excel 2007 conditional formatting options – data bars, color scales, and icon sets
  • want more control over the style and format of the chart or table
  • need to incorporate a pie, bar (not column), area, scatter, radar, or bubble chart into your dashboard

The first thing that you will need to do ahead of time before you are able to do this is enable Excel Services on SharePoint 2007 and configure it.  If you are unfamiliar with this process you can check out this reference – Demo: Enable Excel Services and data connections for a SharePoint team site and check the video and download it for reference.  NOTE: Excel Services is an Enterprise feature within SharePoint Server.

Once you have Excel Services configured you will then need to create the Excel file with the items that you would like to include in your dashboard.  In this example I will be using Analysis Services as the data source (the ever famous Adventure Works DW) and will create an Excel PivotTable and PivotChart.  These two Excel items will then be made available in SharePoint (Excel Services) for reference by the PPS dashboard that will then be connected to dashboard filters.  NOTE: this demonstration is using a virtual environment that is setup in a standalone configuration (meaning everything is installed on the server – SQL Server, PPS, SharePoint, etc.).

(You can click on the images below to view a larger image – the flow goes from left to right)

image image
Open up a new Excel workbook and create the data connection to the Analysis Services database Connecting to a local instance of Analysis Services
image  image
Select the AdventureWorksDW database and the Adventure Works cube Enter a name for the Office Data Connection (odc) file
 image  image
Going to create a PivotChart and Pivot table Select ‘Reseller Sales Amount’, ‘Date.Fiscal’, and ‘Geography.Geography’
image image
Only select FY 2004 in the column labels and change the chart type from Column to Pie Remove Chart Title and add Data Bars conditional formatting to the PivotTable
image image
Click in the PivotTable and in the Options enter a meaningful name and deselect the grand total options since we are only going to show one time period in this example Now select the pie and go into the Format Data Labels options and choose the options you would like to include
 image   image
Select the PivotChart and in the Properties in the Layout portion of the Ribbon give the Chart a meaningful name Changed the worksheet name and add an additional hierarchy from the field list in the Report Filter section – Product.Product Categories
 image  image
Go into the data connection properties and export the data connection to the data connection library in SharePoint (just like in the demo link provided above) Publish the Excel workbook to Excel Services
image image
Specify the path to the document library, provide a meaningful file name, and specify the Excel Services options (I just selected items in the workbook and then added the parameter in to be used with PPS) Setup the reference in Dashboard Designer to the Excel Services workbook and the PivotTable
image image
You can click on the View button to see a preview of the item you are referencing Setup the reference in Dashboard Designer to the Excel Services workbook and the PivotChart (you can copy and paste the previous report, change the name in properties, and change the item name being referenced)
image image
Create the dashboard page and link the filters to the reports Deploy the dashboard page
image  
Test the dashboard page filter and drill into the data within the table for further analysis  

NOTE: If you don’t see the Excel Services parameter in Dashboard Designer it is most likely because in the Excel filter you have multiple values selected.  Go back into the file and make sure only one item is selected so that it will be recognized and pulled into the parameters section in Dashboard Designer.

For some additional references in regards to Excel Services checkout some of the links below:

Download:

image

Posted in PerformancePoint | Tagged: , , | 7 Comments »

Installing Dashboard Designer for PerformancePoint Server (PPS)

Posted by denglishbi on January 23, 2010

On a few occasions I have been asked where the install is for the Dashboard Designer application that is used to create and deploy the PerformancePoint Server (PPS) 2007 elements.  This is a fair question because it would be a little confusing for someone that is new to the product.  This isn’t a typical installation that is found on the PPS installation disk like you would install ProClarity Desktop Professional, Excel, SQL Server Management Studio, Business Intelligence Development Studio, etc.

So where is the installation located and how do you install Dashboard Designer?

Dashboard Designer is a ClickOnce application and it is available to install after you have installed PPS Monitoring Server.  ClickOnce provides a way to install the application from a web page (in this case Monitoring Central) which then gets downloaded to the user’s workstation.  When you launch the application it is actually setup to check for updates on the server and provide you an option to install the latest version.

Once the server product has been installed for PPS you will be able to access the Dashboard Designer installation by going to the Monitoring Central web page which by default is http://servername:40000/Central

image

Once you have accessed the Monitoring Central web page you can either click on the Run button or the Download Dashboard Designer hyperlink.  This will launch the Dashboard Designer installation.

image

This is the point where a check will be performed to see if the application is already installed and if you have the latest version that is available on the server.  If the application is installed and is the latest version the application will be launched.  If not you will receive the message box above to run the installation.

image

Once the product has completed the installation it will then be launched.

image

At this point you will be able to verify which PPS web service you are connected to in the lower left-hand corner and also the version (build) number that you are working with in the lower right-hand corner.  In the picture above you will see that I am currently running SP3 of the product (PerformancePoint Server (PPS) 2007 SP3 now available).

Once this is installed you will also get a shortcut placed in your start menu located in the following location: Start—>All Programs—>Microsoft Office PerformancePoint Server 2007—>Dashboard Designer

image

And that is how you install Dashboard Designer for PPS which is not your typical installation process.

For reference, ClickOnce applications get installed in the following location by default — for Windows Vista and Windows Server 2008 (or new version of the Windows OS) C:Users\<USERNAME>\AppData\Local\Apps\2.0 and in Windows XP and Windows Server 2003 it would be C:Documents and Settings\<USERNAME>\Local Settings\Apps\2.0.

Posted in PerformancePoint | Tagged: | 4 Comments »