Last week, at the SQL PASS summit, Microsoft publicly unveiled their Business Intelligence Reporting Roadmap to widespread critical acclaim. The vision is, in my opinion comprehensive, and complete. It’s probably the most comprehensive articulation of vision that we’ve seen in the Microsoft BI world in 10 years. It spells out the investments being made in Reporting Services, the integration of Datazen, and the importance of Power BI in the overall structure. There is one BI technology that is rather conspicuous by its absence
PerformancePoint isn’t mentioned in the roadmap at all. As far as I know, it wasn’t mentioned during the PASS summit either. I haven’t heard it mentioned by anyone at Microsoft since the Ignite conference last May, when Bill Baer confirmed that it would be a part of SharePoint Server 2016. Of course, as I mentioned in my post at the time, inclusion doesn’t mean that the product has a future. Although it will be included, its for backward compatibility reasons – there are absolutely no new investments in the product, it’s exactly the same as it was in SharePoint 2013.
This has happened to multiple products at Microsoft, Silverlight and InfoPath are the most recent examples. Once a product is ignored, the next step is often not an announcement of its demise, its just allowed to slowly fade away into obscurity. They go out with a whimper, not a bang. In my opinion, this is exactly what has been happening with PerformancePoint, but really, you don’t need to take my word for it. The section header from the roadmap blog post says it all.
“Reporting Services is our on-premises solution for BI report delivery”
Reporting Services… not PerformancePoint. I do realize that there is still one use case that can only be served by PerformancePoint, and that is Scorecards. Scorecards are rolled up KPIs, and no other product in the BI stack does this out of the box. However, with the release speed of the Power BI team, I can only conclude that scorecards will only be a matter of time.
At this point, I would strongly dissuade anyone from using PerformancePoint for any new projects. If you have an existing investment in PerformancePoint, you might want to start thinking about alternate methods of delivering that capability.
During the Microsoft Ignite conference in Chicago, Bill Baer delivered the first public details on the inner workings of SharePoint 2016. He was discussing how non-Office 365 services were implemented in SharePoint 2016, and made the following statement:
“For capabilities such as PerformancePoint Services, we have brought those services forward into SharePoint 2016 by back porting them into the product itself.”
This is quite significant as it represents the first time that any statement has been made about PerformancePoint by someone at Microsoft in quite some time. There has been a fair bit of speculation as to whether the product would even be included in SharePoint 2016, for example here, here, and here. This statement should clear up any confusion for those heavily invested in PerformancePoint as to whether they will be supported into the new release. You can see it for yourself below, and if you can, I recommend watching the entire session – Bill did a great job. The PerformancePoint statement occurs at about 9:50.
However, just because it will be there, doesn’t mean that it has a bright shiny future. I suspect that it is being included for compatibility reasons only so that those with PerformancePoint can move forward to SharePoint 2016. In terms of new features, I believe that PerformancePoint, much like InfoPath is a dead end. Time will of course tell.
Scorecards, KPIs, and analytic charts and grids are at the core of PerformancePoint’s value proposition. In order to effectively work with them we need to work with a multidimensional data source, which means Analysis Services. Traditionally, this has meant building OLAP cubes, which is a daunting prospect for those unfamiliar with the process. More recently, the appearance of the tabular data model in Analysis Services has lowered the bar significantly, but still requires a connection to a full blown Analysis Services server.
At the same time, the proliferation of PowerPivot, and PowerPivot for SharePoint has democratised the development and sharing of multidimensional data models for the power user. Unfortunately there is no “PowerPivot” connection type in PerformancePoint, so it would appear that the advanced PerformancePoint tooling is beyond the reach of our shared Power pivot models. The good news is that it’s not – its just not obvious as to how it can be done.
First, we need to step back a bit and talk about how PowerPivot for SharePoint works.
PowerPivot for SharePoint actually consists of two components. First, there is the Service Application that runs in the SharePoint farm that is responsible for performing data refreshes, and usage analytics. The main part however is actually an instance of Analysis Services using the tabular engine. It’s properly referred to as Analysis Services SharePoint Mode, and as of SharePoint 2013/SQL Server 2012 SP1, it can be installed standalone. However, it is most commonly installed on SharePoint front end servers.
You can see this in action by opening up SQL Server Management Studio, and connecting to the PowerPivot Instance on a SharePoint front end server. The instance is normally named PowerPivot:
In the case above, the SharePoint front end server is named NautilusSP. You can also see that there is a model being hosted by the server already. The model is named by taking a workbook, and adding a GUID to it. This is done by Excel Services the first time that a model is interacted with. For example, if we add the file Health.xlsx, which contains an embedded PowerPivot model, and immediately refresh the object explorer in Management Studio, we will see that nothing has changed. However, if we then interact with the model at all, by clicking a slicer, or opening a pivot table category, we will see that the model has been automatically created for us.
The first interaction with the model will be noticeably slower than all subsequent interactions for this reason.
Now, since this is actually an instance of Analysis Services, we should be able to connect Excel to this model, and do analyses from it. In fact, we should also be able to create a PerformancePoint data connection that points to this model, allowing to use PerformancePoint Scorecards, and analytic charts and grids. We can in fact do both things, but there is a major problem with doing so.
These models are temporary. If they haven’t been used for a period of time, they get deleted. Also, if the source workbook is updated, a new model is automatically create upon first interaction. This can be seen if we edit, and save our Health.xlsx workbook, and then open it in the browser and interact with it.
The original model will be deleted in a garbage collection process. We therefore cannot reliably target these models, as any reference will become invalid relatively quickly.
The good new is that we can use Excel to analyze these models by using the “Open New Excel Workbook” button in the PowerPivot Gallery. This is the leftmost of the three icons to the right of any workbook in the gallery.
Clicking on this action will download an odc (Office Data Connection) file, which will open up Excel, and establish a connection with the underlying model, allowing us to do further analysis on it as if it was hosted in Analysis Services (because it IS hosted in Analysis Services). This connection will work no matter what the name of the underlying model, and if the model doesn’t yet exist, it will be created.
Unfortunately, nothing like this automated connection creation exists for PerformancePoint.
To see what’s going on, we can open the connection itself within Excel, and then view its properties to find the connection string.
We can see that it is a standard Analysis Services connection string, with an interesting twist. While the value for “Initial Catalog” is in fact our temporary model, the value for “Data Source” is the URL of the Workbook. Excel Services will automatically direct calls to this workbook to the appropriate data model. if the model has been changed, it knows, and will serve the appropriate content, so our new analysis workbooks will not become invalid.
The good news is that we can use this within PerformancePoint as well. All we need to do is to open up PerformancePoint Dashboard designer, create (or edit) a connection using the Analysis Services type, and select “Use the following connection” which allows for a connection string.
For the connection string, all that we really need is the data source parameter, which is the URL to the workbook. Once entered, we see the appropriate model name in the dropdown for “Cube”. Once selected, this data connection will work like any other Analysis Services data connection. We can now build KPIs, Scorecards, and Analytic charts and grids from the model embedded in out Excel workbook, hosted in SharePoint. If the workbook changes, or the temporary model gets deleted, Excel Services will take care of recreating it on the next interaction.
It is possible to use PowerPivot for SharePoint with PerformancePoint.
We’ve been observing a problem in a few PerformancePoint installations that we’ve done recently. You create a new Business Intelligence Center, and then you want to create a new dashboard. You navigate to the PerformancePoint content library and select the Add new item link. You get the “Loading Dashboard Designer” Ajax prompt, but then nothing happens. If you open up the ribbon, and try to create any of the PerformancePoint items, you see the same behaviour.
If there’s existing PerformancePoint content, there is no problem, but new stuff won’t work. As far as I can tell, the problem appears to be that the SharePoint URL is not yet set. You can access the Server setting by clicking the pearl in the upper left of the designer surface, then clicking the Designer Options button, and then the Server Tab
The trouble is, how can you get Dashboard Designer to launch? We’ve found that although it won’t launch from the PerformancePoint Content library, it will from the Data Connections library if you create a new PerformancePoint data connection. You will however need to use the ribbon to launch it.
First,navigate to the Data Connections library and click on the Documents tab in the ribbon. The ribbon should open up,and then you want to click the New Document dropdown (not the button) and select PerformancePoint data source.
At this point, Dashboard Designer should launch. You should then be able to set the SharePoint URL setting.
We have seen issues with setting up the URL however. Occasionally the following error will be displayed:
You can’t then save the value. Our solution to this problem thus far has been to close designer, repeat the process above, and the value gets set automatically.
Once this is done, you can create new PerformancePoint items from the PerformancePoint content library.
If anyone has any additional insight to this, I’m all ears – I consider this to be a workaround.