Author: Anand Pandya
“We love PerformancePoint, but we’ve made a lot of investments in SAP, BW and our BI Accelerator – we don’t want to build SSAS cubes, but we want to use PerformancePoint…”
Imagine my surprise when a customer recently asked us to help build them a PPS dashboard, but specifically requested that we not use SSAS. It made sense, after all, they had invested a lot of time and a lot of money in their existing infrastructure – they didn’t want to replicate data, or, more importantly, re-model , re-design and re-learn a new platform. But they liked SharePoint and the visualizations and capabilities of PerformancePoint.
So…what to do?
Well, we utilized SharePoint lists and Microsoft PowerPivot to power their PerformancePoint dashboard!
Step1: Storing the data:
They didn’t want us to connect directly to the SAP infrastructure, but preferred that we utilize queries and web services to receive flattened query results from their data model. As well, our customer didn’t want to create a separate database or long term storage solution – they simply want to land data from these web services in a temporary fashion and “dump and chase” that data nightly.
We created a SharePoint list named “All Data” that captured the purchase order data provided from their system.
PowerPivot as the “cube”:
Our customer didn’t want to replicate data in a stand-alone SSAS cube, nor did they want to re-model and re-design a large/complex cube environment. However, since PerformancePoint grids, charts and decomposition trees require a multidimensional OLAP source, how could we source the data?
The answer – PowerPivot!
Rather than using PowerPivot as the end game tool (fully modeling a set of data and building the dashboards, slicers, etc.), we used PowerPivot as the multidimensional structure.
Step 2: Loading the data
One of the PowerPivot data options is the “data feed” option that allows you to connect to an RSS feed on a website. SharePoint can automatically generate an RSS feed for any list. Therefore, we create a single tab in PowerPivot that was sourced from the RSS feed of the list we created in step 1.
Now, you can’t simply click the “RSS feed” icon in SharePoint and copy the URL to connect, rather, you have to use the atom data feed link for each list created in SharePoint :
Step 3: Creating the PowerPivot “cube”
Upon publishing the workbook to a PowerPivot gallery in SharePoint, the PowerPivot service in SharePoint creates a “Sandbox” cube in the localhost/POWERPIVOT instance on the SharePoint content DB server that powers the cube capabilities.
It isn’t a “fully-blown” SSAS cube, per se, but the Analysis Services Vertipaq cube that powers the PowerPivot capability.
By publishing the workbook to the gallery, the PowerPivot cube is instantiated on the SP server.
(For those curious, you can connect to the server your SharePoint Content DB lives and connect to “localhostPOWERPIVOT” to see the workbook databases and sandbox cubes)
Step 4: Connecting the PerformancePoint Dashboard
As discussed before, PerformancePoint grids and charts can only utilize a multidimensional OLAP data source. You can utilize the PowerPivot workbook as
that OLAP data source! Create a new data source in PerformancePoint and select Analysis Services as the option.
Rather than inputting the “traditional server” information, select the “connection string” option and enter the following:
The cube name created by PowerPivot is “Sandbox”
Step 5: Now, you’ll have a multidimensional source, and you can build your full PerformancePoint dashboard!
Limitations on a PPS dashboard sourced from PowerPivot rather than SSAS:
- No authentication via “Unattended Service Account and add authenticated user name in connection string” option
- Time Intelligence is NOT supported
- Formatting is NOT carried through to PPS from
- “Show Details” is NOT available as drillthrough
is not an option in PowerPivot today (the Decomposition tree CAN be used)
- Named Set references are NOT available
- User defined hierarchies are NOT available