PerformancePoint Services

Specifying Your ADOMD.NET Data Provider Version

Kevin Donovan [MSFT] — Wed, 12 Sep 2012 03:57:00 GMT

A while back, I wrote a post about how to get your PerformancePoint 2010 installation working with SQL 2012.That post talked about where to find the SQL Server 2008 R2 SP1 feature pack that contains, among other things, version 10.5.2500 of the ADOMD.NET data provider, which PerformancePoint needs in order to connect to any 2012 Analysis Services data source -- PowerPivot workbook models included. This works great for PerformancePoint 2010, but if you need the same functionality in your PerformancePoint 2013 instance, using SQL 2012, you will need major version 11.0 of the ADOMD.NET data provider. You can download that, by way of the spPowerPivot.msi, here.

The Situation

At this point, you might be asking yourself why you need to take the extra step to install the MSI when you can simply install the SQL BI components for SharePoint when you install an instance of SQL Server 2012 SP1. Without going in to too much detail, there are two reasons: First, SharePoint no longer bundles the provider in the prerequisite installer (as was the case in 2010). So, any data providers will have to be installed separately. This is why we added a friendly reminder after you provision a new PerformancePoint service application:

Second, if your SharePoint farm contains more than just one machine (not counting your SQL back-end), the recommendation is that you install the MSI on each machine in the farm running PerformancePoint Services, since for any given request, PerformancePoint could call from that box, requiring the ADOMD.NET provider in order to connect. In addition to these two things, the MSI gives you the ability to connect to PowerPivot workbooks as a PerformancePoint data source. Yes, this was available in PerformancePoint 2010, but in 2013, you need spPowerPivot.msi to accomplish the same thing (assuming that you didn't install the add-in from the SQL install). Incidentally, the MSI's installation instructions can be found here.

The Assembly Redirect

As with PerformancePoint 2010, in PerformancePoint 2013, we compile with major version 10 of the ADOMD.NET provider. So, if you need to use version 11 (and you will for your 2012 BI workbooks in SharePoint 2013), you need to tell PerformancePoint to do so. New in PerformancePoint 2013 is an assembly redirect that you can use for just that purpose. We added the redirect so that going forward you can use new provider versions from Analysis Services without having to upgrade SharePoint (e.g. PerformancePoint). This is particularly handy if your organization mixes and matches versions of SharePoint and Analysis Services. To see the redirect block that we added, on each server running PerformancePoint Services, navigate to \Program Files\Microsoft Office Servers\15.0\WebServices\PpsMonitoringServer. Open the web.config file. It should be located in the same directory as PerformancePointService.svc. At the very bottom of the file, you should see an <assemblyBinding> block contained within a <runtime> block, like this:

Change the values in the <bindingRedirect> element from oldVersion="9.0.0.0" to oldVersion=10.0.0.0" and from newVersion="10.0.0.0" to newVersion="11.0.0.0". This will allow PerformancePoint to leverage the GAC'd version 11 ADOMD.NET assembly when making calls to Analysis Services 2012 (and prior versions). It will also allow you to use the latest PowerPivot models as data sources in PerformancePoint -- something our customers have come to really rely on.

Thanks for reading...

Kevin Donovan
Program Manager, Office BI

Changing the Look of your PerformancePoint Dashboards with SharePoint Themes

Kevin Donovan [MSFT] — Thu, 30 Aug 2012 18:19:00 GMT

Hello, I’m Kevin Beto, the Test Manager for the Office BI team and co-author of Microsoft PerformancePoint Services for SharePoint 2010 UNLEASHED, and I’d like to tell you about some of the changes we made to support the SharePoint “Change the Look” functionality that we’re delivering to you in the SharePoint 2013 release.

How to change your theme
Changing the theme is pretty simple as long as you have “Owner” level permissions on the site. First thing you need to do is go to the Site Settings page found under the little gear icon in the upper right corner of your SharePoint site:
Select “Change the look” under the “Look and Feel” section:

From there you are presented with a gallery of some default themes. Sea Monster is my personal favorite, but you can pick your own favorite theme. You can even publish your own templates for more options! Click on a theme you want to go with:

At this point you see a very light preview of what this theme entails, and you have the option to tweak it a little if you are feeling especially creative today. Go ahead and click the “Try it out” button in the upper right.

From there, you get an opportunity to preview what that new theme would look like. Go ahead and accept it by clicking the “Yes keep it” button in the upper right to make the change permanent throughout the site.

Scorecards
Being a relatively new parent, I want to make sure my kids have names that are unique, but not so unique that they are the only person in the world with that name. And since I am a consummate nerd, I made a scorecard that evaluated the uniqueness of names across the United States. Here is that scorecard with the “City” theme:

If hot pink highlights don’t scream uniqueness, I don’t know what does! As you can see, the great state of Washington is pretty middle-of-the-road for name uniqueness in the Western region of the US in 2010. We’ve got about 2200 unique names given out in 2010, way behind California’s 6700, but well ahead of Wyoming’s 300. I can get more details on this, and see the theme continue on my “Show Details” page.

And here’s a list generated by PerformancePoint that I can export to Excel and work further in if I wanted to.

Analytic Grids
If I wanted to see specific stats on the names chosen for my boys, an Analytic Grid is a much better option. This is what Analytic Grids could look like with the Sea Monster Theme:

Love the new background, gives it a much better look than the stark white of 2010. The data is telling me that maybe we went a little overboard with Vincenzo’s name as only 6 others were born with his name in Washington in 2010. Oh well, he can always move to California after he grows up if he wants to start a club.

We have also extended the themes to filters, like so:

Caveats
Of course, as a Test Manager, my team and I spend a lot of time finding problems with the product and there are a few notable places where we don’t handle this feature well that I’d like to call out.

Analytic Charts – Theming changes don’t apply to Analytic Charts. We made this decision for technical reasons. Since we currently render this as an image, there would have to be an extra round trip to the server which would have had performance implications. We tried to make good decisions on the defaults so it looks at least pretty good in most themes, but there are a few out there that don’t look great. If this is a big problem for you, definitely let us know, and in the meantime I’d recommend trying to implement your dashboard using Excel Services reports as we’ve added some new ad hoc exploration features that offer a comparable experience in SharePoint 2013. Or if you want a more customized experience, Reporting Services reports also fit the bill.

Decomposition trees also don’t inherit theming changes. This whole feature is implemented in Silverlight, so the theming styles don’t necessarily map directly to concepts in this feature, and it pops up in a separate window when you are looking at it. You’ll always get the white background on this feature and that classic experience that you know and love so much.

Conclusion
Thanks for reading this far, and let’s keep the conversation going in the comments section. I’d love to hear about your experiences, both good and bad, with both this feature and your general experiences with BI offerings from Microsoft.

Kevin Beto
Test Manager, Office BI

Introducing PerformancePoint Services 2013

Kevin Donovan [MSFT] — Fri, 03 Aug 2012 14:22:00 GMT

Greetings, everyone. It has certainly been a big few weeks for us here on the Office team. The Office Next blog was released, and it's overflowing with great information. Plus, information about the integration throughout Office BI, and how people experience data, is spectacular. Each day that I read customer feedback about the team's accomplishments for the 2013 release preview, the more I learn about how many things our customers love about it. But I also learn about the things that we still need to do (and I'm working diligently on those). In this post, I want to talk about our improvements to PerformancePoint Services in 2013.

First, however, this is a good opportunity to address the speculation, confusion, and misinformation around our PerformancePoint investments the past few years. To clarify this confusion, let me confirm that PerformancePoint will continue to be Microsoft’s solution for creating interactive, context-driven dashboards with balanced scorecards, key performance indicators, analytic reports, and filters. It will continue to be an integral part of Microsoft’s Business Intelligence offering, which I encourage you to review by downloading and trying the SharePoint Server 2013 Preview and SQL Server 2012 SP1, CTP3.

Over the coming months, we will detail the new PerformancePoint features and talk about how they work. But for now, here is an overview of what you can expect to see. You'll notice from this list that PerformancePoint continues to be a pillar in the OfficeBI stack.

1. Themes

PerformancePoint now respects SharePoint's new theming capabilities. This was a lot of work for the team, and it took up the majority of our time. SharePoint cut the number of CSS files on the server by the order of hundreds, and we had to make sure that dashboards continued to look great. You can see that the background images from the theme actually bleed through the elements on your dashboard!

2. Filter Enhancements

We made a number of enhancements to PerformancePoint filters too. Here are some of the things we made better. You can now

show sub selections in tree filters by bolding each parent in the hierarchy.
dynamically size the tree filter height based on the number of visible items in the tree.
select different tree filter actions by

selecting all
clearing all
resetting to default
selecting children
getting all filter items (when 5,000 limit is reached)

create a filter based off a measure
see significant performance improvements

3. Filter Search

Related to all of the general filter enhancements that we made, we also implemented the ability to search amongst filter members. This has been at, or near, the top of the customer request list for a long time, and we wanted to make sure that we implemented it for the 2013 release. With filter search, you can now

search within single- and multi-select tree filters
search Analysis Services/PowerPivot data sources
search Member Selection, MDX Query, and Named Set filters

We will post more in-depth discussion about filter search, but for now, I wanted to mention that this was added to the product for 2013.

4. EffectiveUsername

This is a feature that both PerformancePoint and Excel Services implemented as a way to save the IT admin from having to set up Kerberos constrained delegation in order to use per-user authentication (applause!). When you check this option on the PerformancePoint service application settings page, you tell PerformancePoint to add the EffectiveUsername property to the connection string that it passes to analysis services (AS). The value of that property is the username of the individual user making the call. When AS sees the value, it returns the results of the query, security-trimmed to that user. The following screen shot shows the context of the feature on PerformancePoint's service application settings page.

5. Custom Target Applications from Secure Store

Along the same lines as the EffectiveUsername feature, we also added the ability for you to specify any secure store target application when defining your data source in dashboard designer. If you want to, you can use the one auto-generated when you provision the service, but you don't have to anymore. This was big for customers who created target applications for Excel Services, but could not use those same target apps for PerformancePoint. Now you can provide users with a number of different target applications, each with different credential sets that map correctly to the authorization allowed by the back-end data source.

Oh, and did I mention that you can specify this option for the entire service as well?

6. BI Center

We changed up the BI Center site to make it easier to use and to highlight some of the dashboard components that have become so important with information workers. Those include PowerPivot and Excel Services. We really tried to reduce the complexity of the ppssample.aspx (BI Center from 2010) page in order to make it straightforward and simple.

In case you were wondering, you can still get to the familiar ppssample.aspx page that you may be used to from the 2010 product (Site Contents --> Pages), but that page is no longer the primary way in which users launch dashboard designer (which brings me to the next feature).

7. Dashboard Designer in the Ribbon

PerformancePoint is now more deeply integrated into SharePoint than ever been before.

Clicking on the Dashboard Designer button launches the click-once application (yes, dashboard designer is still a click-once application). The appearance of the PerformancePoint ribbon tab is based on content type. So it appears in document libraries where the "Web Part Page" content type is present, and it appears in lists where any of the PerformancePoint content types have been added. This whole feature is great because now you can launch the designer without having to navigate to the old, BI Center landing page.

8. Server-Side Migration

This is probably the biggest feature in the list. And it really warrants its own discussion because this is a fairly complex feature. Look for a post on it in the coming weeks. But we have created a server-side dashboard migration feature that allows you to move any or all of your PerformancePoint content from one site or server to another site or server. Since PerformancePoint is primarily an enterprise dashboard service, this addresses the common complaint that there was no good way for enterprise customers to satisfy their dev --> test --> prod migration scenarios. All that has now changed. From the SharePoint ribbon, granted you have the right permissions, you can package up your content (data sources too) and migrate it to any other place in the farm.

So there you have it. We have done a lot of work in this release, and I understand that we still have a lot to do in order to address many of the the issues that customers currently face.

Again, download the SharePoint preview, and dig in. I'm excited to hear your feedback.

Kevin Donovan
Program Manager, OfficeBI

Leveraging PerformancePoint in HTTPS Enabled Sites

Kevin Donovan [MSFT] — Wed, 20 Jun 2012 03:25:00 GMT

This blog is long overdue, and it is something that support has encountered from customers for some time. If you have tried to configure PerformancePoint Services to work in an HTTPS enabled site, you will probably have run into several error messages when attempting to create a data source connection to either a SharePoint list or an Excel Services data source.

The following are some of the error messages that you may have seen. One is specific to PerformancePoint, and the other has to do with SharePoint Foundation.

PerformancePoint Dashboard Error Message:
“PerformancePoint Services could not connect to the specified data source. Verify that either the current user or Unattended Service Account has read permissions to the data source, depending on your security configuration. Also verify that all required connection information is provided and correct.”

ULS Error messages:

“SharePoint Foundation | Topology | 8311 | Critical | An operation failed because the following certificate has validation errors:<<certificate path & certificate thumbprint>>\n\nErrors:\n\n The root of the certificate chain is not a trusted root authority..”

“PerformancePoint Service | PerformancePoint Services | ef8z | Critical | PerformancePoint Services could not connect to the specified data source. Verify that either the current user or Unattended Service Account has read permissions to the data source, depending on your security configuration. Also verify that all required connection information is provided and correct. System.Net.WebException: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure…”

The most telling message is the SharePoint Foundation error message, since it indicates that the actual problem is a certificate-related issue. The PerformancePoint messages can be a little misleading since it may lead you down an authentication/authorization related troubleshooting path. While at its heart this is indeed the problem, there is only so many ways you can check to make sure the Unattended Service Account or the Service Account itself has all the appropriate permissions. That said, if it is a certificate issue, always look for the SharePoint Foundation critical ULS message, which is the dead giveaway for PerformancePoint certificate issues. If you have encountered these messages, try the following. Many customers have found success with the following solution.

Solution
Assumption: This assumes that the certificates have already been acquired and that the bindings have been configured in IIS for the SharePoint sites that you are working with. If this is configured successfully you will be able to browse your https sites without receiving any certificate errors. (Here is a blog post that provides a decent overview of the process as well as authoritative links to related documentation.)

Step 1: Extract Certificates to the SharePoint Server
Each certificate in the certificate path (Root, Intermediate and leaf) will need to be installed to the Local Computer’s Trusted Root Certification Authorities. In order to accomplish this, you will need to extract each certificate in the certificate path from the “bundled” certificate.

Navigate to the Web Front End or Application Server.
Open Internet Explorer and navigate to the site you will be using (https://site).
View the Certificate associated with the site by looking at the URL security report.

Select the pad-lock icon to the right of the URL
Select View Certificates.

Select the Certificate Path tab.

Select the root node in your certificate path and select “View Certificate.”

In the Certificate Window that opens, select the Details tab
Select the “Copy to File…” button
The Certificate Export Wizard will open.
Choose the default Certificate format “DER encoded binary X.509 (.CER)” and select Next.

Specify a location to which you can save the file, and assign the file name “RootCertificate” (select Next).

Specify a location first by clicking on the Browse button.
Choose Desktop.
Specify a name (e.g. “Root”).

Select Finish.
Repeat steps 4 – 10 for each certificate in your Certificate Path.

Step 2: Add each certificate to the Local Computer’s Trusted Root Certification Authority
Each certificate that was exported needs to be imported to the local computer’s Trusted Root Certification Authority on each server in the Farm.

On each server in your farm, ensure that the certificates exported are added to the Local Computer, Trusted Root Certification Authorities (it is easiest to just copy the certificates to each server in the farm once you have exported the certificates on your first server).
Launch the Local Certificate Manager.

a. Start | Run | MMC

b. Select the File Menu | Add/Remove Snap-in

c. In the Add or Remove Snap-ins window, select Certificates and click the Add button.

d. In the Certificates snap-in window select Computer account | Next.

e. Choose Local computer | Finish.

f. Back in the Add or Remove Snap-ins window select OK.g. Expand the Certificates (Local Computer) node in the Console window.

g. Expand the Certificates (Local Computer) node in the Console window.

h. Right-click Trusted Root Certification Authorities | All Tasks | Import.

i. Navigate to the root Certificate that you exported previously and select Next.
j. Choose the Defaults the rest of the way through, and select Finish. You should receive a message indicating that the import was successful.

Repeat steps h – i for each certificate in the Certification path

Step 3: Each certificate needs to be added to SharePoint’s Managed Trust

Once the certificate is installed locally, it is necessary for the Certificate to be added to SharePoint’s managed trust. This will only need to be done from one of your servers, as the trust is recognized farm wide. Add each certificate to SharePoint’s Managed Trust.

Open SharePoint Central Administration and navigate to Security | Managed Trust.

b. From the Trust Relationships ribbon, select New, the “Establish Trust Relationship” window will open.
c. For General settings, specify a name (i.e. RootCA, IntermediateLevel1 IntermediateLevel2).
d. For the Root Certificate for the trust relationship, select Browse, and select the certificate you created earlier.

e. Click OK.

Repeat steps a – e for each certificate in your certificate path.

On your SharePoint web front end, issue an IISReset

Start | Run | cmd
IISReset <enter>

Test Dashboard Designer data source creation by creating a new SharePoint List data source as well as an Excel Services data source.

Appendix A: Checking for imported Certificates

At times it may be necessary to check to see if the certificates are imported correctly or conversely to validate that the certificates have been fully removed from the system. Several times, we have had stubborn certificates that will not remove completely from a system until removed from these certificate stores in the registry.

Start Regedit

Start menu
Run
Regedit

Check both HKEY_LOCAL_MACHINE and HKEY_LOCAL_USER followed by SOFTWARE\Microsoft\SystemCertificates\My\Certificates
Compare the Thumbprint value of the certificate with the nodes showing in the Certificates container.

Appendix B PowerShell Commands that may be useful

Checking for certificates with PowerShell

The Following script will export a list of the certificates installed to SharePoint’s Managed Trust with which you can then compare against the certificates added to your Local Certificates.

Checking for certificates added to the Local Certification Authority

That’s all there is to it! This isn’t particularly complicated, but there are a few steps to work through before it all works smoothly.

John Fulton
Microsoft Support Escalation Engineer
SharePoint, Office BI

How to Refresh a Cached Excel Services Report

Kevin Donovan [MSFT] — Fri, 08 Jun 2012 01:43:00 GMT

This post doesn’t so much describe how to do something as why you shouldn’t worry about something you may see. It can be unnerving to make a change in a SharePoint object, then not see the change when you check for it. Here is a case where there is an explanation for that behavior. Here is a case where caching, which helps with rendering speed and performance, may impact how you specify an Excel Services data source. It’s not a big inconvenience, but I wanted to point it out nonetheless.

Assume you have an Excel Services workbook uploaded to a SharePoint document library.

Now, create an Excel Services data source for PerformancePoint Services, using Dashboard Designer. The Item Name pull-down shows the sole, named region.

Here comes the change: we’re going to modify the workbook itself and see how that affects the existing PerformancePoint data source. Open the workbook using Excel, add a new table or named range, set the Publish Options to include the new range, and save the document back to the SharePoint document library (same name).

Back in Dashboard Designer, in the Data Connection dialog, with the Connection Settings unchanged, open the Item Name drop-down.

The list hasn’t changed! What happened to the new item? As I said, this can be somewhat unnerving.

The explanation has to do with the product’s behavior “under the hood.” To improve responsiveness and efficiency, Dashboard Designer caches recently used items, so fewer data-intense transfers (between the client and its server) are needed. In this one situation, the signal to Dashboard Designer that there is a change invalidating its cache does not get through, so it shows you the unchanged version of the Item Name list.

The Fix

One way to make Dashboard Designer get a fresh copy of the data source information is to make a change to the external details of the workbook. For example, if you changed the filename or a property, Dashboard Designer would discard the cache entry for the data source and fetch a fresh copy from the server.

Here, we’ve changed the name of the file on SharePoint, and when accessing the newly named file in Dashboard Designer, the Item Name pull-down shows the added range name.

The other way to get the updated contents reflected in Dashboard Designer is to wait for the cache to time out. This is a site-selectable value, and defaults to 10 minutes.

We hope this helps, should you ever see this issue.
Thanks for checking in!

Paul Thrasher
Test Engineer, Office BI

Time Intelligence Differences between Grids and Scorecards

Kevin Donovan [MSFT] — Wed, 08 Feb 2012 19:11:00 GMT

Time intelligence (TI) is one of the great features of PerformancePoint, but it has always taken users time to understand and implement. And we have received more than a few discussion list posts and customer inquiries when things don't add up -- literally. Today, I wanted to draw your attention to a small case in point. One of our testers brought this to my attention, and I thought that it would be nice to share with the community.

The issue is, in short, that grids and charts don't aggregate data the same way that scorecards do when TI filters have been applied to them. Many of you may have already encountered this, been frustrated by it, and have learned to account for it. In order to show you the discrepancy between the two visualizations, you really just need four things: you need a data source that has TI mapping set up, a KPI (and a scorecard), a grid, and a filter. Like usual, I'll use the AdventureWorks cube to illustrate my point.

The Data Source

For the purposes of this, I'm just going to use the standard AdventureWorks OLAP cube. Our data source will be based on the Date.Date.Calendar dimension, and, in this case, we will need to select a member for which there's actually data -- 1/1/2002. Our hierarchy level will be based on day. We are going to map to the same date that our member is set to. Here is a screen shot that will clear things up.

The TI Filter

Simply create a regular TI connection formula filter. This will create a day-based calendar control that we can use to make our filtering granular to the day.

The Analytic Grid

The grid that we are using isn't special in itself. I created a grid with product categories on rows, sales amount on columns, and ship date calendar on the background. The ship date calendar is what we will use to filter against our TI calendar control.

Now, while the grid itself isn't all that special, the connection to the TI filter is. And that's because it uses a connection formula.

You can see that we connect to the Ship Date Calendar in the "Connect to:" field. And by specifying quarter,day in the connection formula, I'm telling the formula to aggregate by quarter and to include the selected day in that aggregation. That's what's giving me the value of $109,809,274.20. Let's move to the KPI now.

The KPI

Our KPI is set like the following: Create a blank KPI. For the purposes of this blog, we will just create an "actual" (as opposed to an actual and a target). We don't need to change the number format (I, however, did change mine to currency). Under Data Mappings, change the data source to use our AdventureWorks source, and use the Sales Amount measure, since that's what we're also using in our grid. Incidentally, go ahead and change the Calculation type to "Data value" so that you can decomp the value later if you want.

As part of the Dimensional Data Source Mapping, add a new Time Intelligence Filter, and specify quarter,day as part of the Time Formula Editor.

Now, both our grid and our scorecard KPI are both configured with the same time dimension formula: quarter,day.

The Dashboard

On the dashboard, I connected my TI filter to my scorecard using Current date-time for both the source and for the destination. After that connection has been made, I will have one calendar control that filters both my scorecard and my analytic grid. And both of those objects will aggregate to quarter + day against the exact same data source. Here is the resulting dashboard:

You'll notice that I highlighted the values. I did that because they are, in fact, different. And, even then, by only a few dollars. Theoretically, they should be the same. Like I mentioned above, they are running against the same data source and are aggregated using the same TI formulas. This is the point that I wanted to highlight. This is not a case of bad data, but rather a case where scorecards and grids roll up data differently. In this example, Grids do not include the day hierarchy, but scorecards do. The lesson here is to be careful with your TI formulas because the different reports do not necessarily treat them equally.

Let's see what happens when we alter the formulas a bit. Actually, we're only going to alter one formula: the scorecard's. When I remove the child hierarchy, day, our values fall back in line with one another. So altering the time formula on the KPI from quarter,day to just quarter, like this

yields a dashboard that looks like this:

Now the grid and the scorecard values match, as you would expect. Again, many of you may have already encountered this issue. And if you have, you like already account for it. But if you have not, it's good to be aware of it and to work around it using whatever methods make sense for you.

That's it for now. Thanks for reading.

Kevin Donovan
Program Manager
Office BI

What’s So Special About SharePoint’s December Update?

Kevin Donovan [MSFT] — Wed, 28 Dec 2011 23:18:00 GMT

Just in case you didn’t already know, a cumulative update for SharePoint Server 2010 was released in mid-December, and it’s a big deal for business intelligence.

Cumulative Update 2011-12 (CU 2011-12) for Microsoft SharePoint Server 2010 is exciting because it enables people to view PerformancePoint reports, scorecards, Excel Services reports, and other items on Apple iPad devices. Imagine how convenient it will be for users to open and use dashboards on an iPad.

We just published an article on TechNet that provides more details about which kinds of reports and scorecards will and won’t work on iPad devices, how to configure business intelligence content to display with best results, and how to navigate content on iPad devices. See Viewing reports and scorecards on Apple iPad devices (http://technet.microsoft.com/library/hh697482.aspx), and tell us what you think.

Want to learn more about CU 2011-12? Start with the following resources:

Updates for SharePoint 2010 Products (http://technet.microsoft.com/sharepoint/ff800847.aspx) provides links and information about the latest software updates, including CU 2011-12. Start here for the latest information and guidance about how to apply software updates for your SharePoint Server environment.
KB 2597014 - Description of the SharePoint Server 2010 cumulative update package (SharePoint server-package): December 13, 2011 (http://support.microsoft.com/default.aspx?scid=kb;EN-US;2597014) provides information about CU 2011-12, including prerequisites, known issues, hotfixes, and other details.
December 2011 CU for SharePoint 2010 has been released (http://blogs.technet.com/b/stefan_gossner/archive/2011/12/14/december-2011-cu-for-sharepoint-2010-has-been-released.aspx) provides an overview of KB articles that are relevant to CU 2011-12 and links to additional information.

We’re always interested in your feedback, so please let us know what you think. You can add a comment to this blog post, or use the feedback mechanism in the upper right corner of the TechNet article.

Thank you, and Happy Dashboarding!
Denise Stendera
Writer, Business Intelligence in SharePoint Server

You Can Use SQL Server "Denali" PowerPivot Models as PerformancePoint 2010 Data Sources

Kevin Donovan [MSFT] — Wed, 21 Dec 2011 17:14:00 GMT

Since SQL Server Denali hit the public download space, several people have tried using it to create PowerPivot models as data sources for PerformancePoint 2010. And why shouldn't they? PerformancePoint works just fine in the scenario where you specify the reference to a PowerPivot model in the data source connection string for an Analysis Services data source.

The Problem

But just about every one of those cutting-edged users has wisely pointed out that something fundamentally breaks when trying this same scenario with a Denali RC0-based PowerPivot workbook. There are a couple of reasons for this, which I address below. The error that manifests in the event log indicates that the existing, installed provider is too old. And that's true if you're running a standard SharePoint 2010 SP1 installation. The error most people have been seeing is this:

Exception details: Microsoft.AnalysisServices.AdomdClient.AdomdUnknownResponseException: Unsupported data format : application/vnd.ms-excel.12

The good news is that a workaround exists.

The Solution

The easiest way to make this scenario work is to install the ADOMD.Net (version 10.5) data provider on your SharePoint farm. You can get this by downloading it from the SQL Server 2008 R2 SP1 Feature Pack (make sure that you download the version appropriate for your server architecture). Version 10.5 of this provider is backward compatible with the new version 11.0. And because it's a minor version, it will overwrite the version 10.0 provider, which is the major version that PerformancePoint looks for at runtime. This latter notion explains the alert that the installer throws when you begin the installation. Go ahead and click, "Yes".

The Detail

I will use the example of a 1M farm in this case. SharePoint's pre-requisite installer will install both the 2005 and 2008 versions of ADOMD.Net. This ensures that Analysis Services data connections work smoothly out of the box. If you look in add/remove programs on your SharePoint server, you can see the provider versions. Version 10.1 is the one that PerformancePoint will use when connecting to the PowerPivot workbook when you use it as a data source.

When you install SQL Server Denali, it will install the version 11.0 ADOMD data provider. You can see this as well by looking at the list of assemblies on the server.

The problem is that there is no policy telling the request to use the latest version of the provider, and PerformancePoint is looking specifically for major version 10. So, in effect, version 11.0 is sitting there all alone and unused. However, when you install the ADOMD client version 10.5, things work because that version is compatible with the latest PowerPivot models, and PerformancePoint is still happy because it sees version 10, just like it expects to. Unlike major versions of the provider, which exist side-by-side, minor versions overwrite each other, which is why PerformancePoint grabs the right one even after you overwrite version 10.1 with version 10.5. So, after installing the SQL Server 2008 R2 SP1 update, when you look at the list in add/remove programs, you will see this:

Once you have updated your provider, PerformancePoint should be able to connect to the PowerPivot workbook as you would expect.

There are a couple of parting notes here. First, notice that the name of the cube, as interpreted by PerformancePoint, is now called "Model". You may recall that it was called "Sandbox" for PowerPivot 2010 models. Second, most of the published documentation indicates that you need to add the PROVIDER property to the PerformancePoint connection string, like this: PROVIDER=MSOLAP;DATA SOURCE=http://contoso/Documents/PowerPivot_Sample.xlsx. This string is a little redundant; there is no need to set the provider property because the provider is adomd.net. And adomd.net will create the right data access object without the user having to specify MSOLAP to connect to the data source.

Kevin Donovan
Program Manager, Office BI

Finding the Latest PerformancePoint 2010 Services Updates

Kevin Donovan [MSFT] — Thu, 01 Dec 2011 01:02:00 GMT

Sometimes it's difficult to find the latest updates for SharePoint 2010 and for PerformancePoint Services, specifically. The easiest way to see these latest updates is to visit the Microsoft Office Update Center or subscribe to the Updates for Microsoft...(read more)

How to Highlight Table Rows Based on Slicer Selection

Kevin Donovan [MSFT] — Wed, 23 Nov 2011 22:30:00 GMT

When you connect Slicers to Pivot Tables or Pivot Charts, their effect on the data is obvious: changing your Slicer selection causes data values to change and in some cases, rows to disappear. However, rather than excluding rows with a Slicer, sometimes...(read more)

Choose the Right Business Intelligence Style for your Project

Kevin Donovan [MSFT] — Thu, 17 Nov 2011 16:18:00 GMT

The Office Business Intelligence group gets the occasional question about why we offer several different BI solutions when our competitors often only offer one. Microsoft offers its customers no shortage of choices when it comes to BI solutions. We do...(read more)

Poll Your Friends with Photo Vote

Kevin Donovan [MSFT] — Sat, 05 Nov 2011 02:34:00 GMT

Poll Your Friends with Photo Vote In this post, we'll introduce you to Photo Vote, a new app created by the Office Business Intelligence team in partnership with the Microsoft Office Labs team. With Photo Vote, you can create quick polls in Facebook...(read more)

Using PowerPivot to incorporate publicly available data sets in your PerformancePoint dashboards

Wade Dorrell — Thu, 20 Oct 2011 19:41:00 GMT

Windows Azure Marketplace is a great place to find publicly available data for use in your BI solutions. In this post, I'm going to show how convenient it is to use Windows Azure Marketplace to get public data into your PPS dashboards. You will need...(read more)

Intro to Excel Services Data Sources

Kevin Donovan [MSFT] — Fri, 14 Oct 2011 17:32:00 GMT

Introduction The Excel Services Data Source in PerformancePoint 2010 allows you to create data using familiar Excel tools and methods that you can then surface in PerformancePoint scorecards. To demonstrate the use of the Excel Services Data Source...(read more)

Getting Data in PerformancePoint

Wade Dorrell — Tue, 04 Oct 2011 21:21:00 GMT

PerformancePoint Services 2010 (PPS) provides a variety of ways to use data in analytic solutions. The usage of Analysis Services cubes and SharePoint lists in PerformancePoint is relatively well understood. Upcoming posts from the Office BI team will...(read more)

Creating simple budget KPIs from SharePoint Lists

Office BI — Wed, 28 Sep 2011 19:17:19 GMT

In this post we're going to walk through how our favorite fictional company Litware uses PerformancePoint Services with SharePoint list data to do simple "under budget" KPIs. In Litware's party planning committee we log how much we spend on...(read more)

PerformancePoint Data Connection Libraries and Content Lists in SharePoint

Office BI — Mon, 19 Sep 2011 14:42:12 GMT

All the items you create when you build BI solutions using PerformancePoint are stored in SharePoint. PerformancePoint data sources are stored in specialized SharePoint document libraries. PerformancePoint content is stored in specialized SharePoint lists;...(read more)

Sharing Up-to-Date Information in Convenient Locations

Office BI — Wed, 14 Sep 2011 19:27:50 GMT

As part of the Office Content Publishing group at Microsoft, our team writes Help content about business intelligence capabilities in SharePoint Server. In this blog, we'll look at how you can reuse and share your business intelligence content in team...(read more)

Better with SharePoint

Office BI — Tue, 06 Sep 2011 16:14:00 GMT

PerformancePoint Services in Microsoft SharePoint Server 2010 (PPS) makes it easy and convenient to organize and share your business insights. Upcoming blog posts by Office BI pros will delve into the following topics: Efficiently organize PPS content...(read more)

Using Reporting Services in SharePoint Integrated Mode with PPS 2010 with SQL Server “Denali” CTP3

Office BI — Thu, 18 Aug 2011 15:52:00 GMT

The SQL Server team has made a small change to the location of the web service that PPS calls in order to render SQL Server Reporting Services reports when running in SharePoint Integrated mode. In this blog post, I’m going to go over how to work...(read more)

Analytic Report Samples Published to MSDN Samples Gallery

Office BI — Wed, 10 Aug 2011 21:50:45 GMT

Two samples that work with PerformancePoint analytic reports are now available on MSDN. · PerformancePoint Services 2010: Change Analytic Report Data Source Sample lets users change the data source that an analytic report points to. · PerformancePoint...(read more)

Cascading Filters in PerformancePoint Services

Office BI — Wed, 03 Aug 2011 19:10:09 GMT

One long-awaited and much-requested feature for PerformancePoint, Cascading Filters, is available in SharePoint Server 2010 SP1. I wanted to go through the deployment of a dashboard using Cascading Filters to show both the ease and a little of the power...(read more)

Fast Prototyping with PowerPivot

Wade Dorrell — Tue, 26 Jul 2011 14:00:00 GMT

Today's "better together" post is by Poornima Hanumara, a program manager on the Office BI team. Thank you Poornima, and to our readers, have a great week! I want to quickly prototype a new dashboard to keep track of the products in my sporting goods...(read more)

Using Project Server 2010 with PerformancePoint Services

Wade Dorrell — Tue, 19 Jul 2011 16:00:00 GMT

Today’s post is by Denise Stendera, a writer on the SharePoint business intelligence team. Thanks Denise! Got Project? When you create a Project Web App in SharePoint Server, you automatically get a Business Intelligence Center. If PerformancePoint...(read more)

Better together: PerformancePoint Services and other Microsoft products

Wade Dorrell — Tue, 12 Jul 2011 14:45:00 GMT

This month on the PerformancePoint Services blog we'll focus on techniques involving other Microsoft products. For example, Got Project? When you create a Project Web App in SharePoint Server, you automatically get a Business Intelligence Center...(read more)