Facebook Twitter LinkedIn YouTube E-mail
Home GNet Group Anand Building a PerformancePoint Dashboard…without a cube?
formats

Building a PerformancePoint Dashboard…without a cube?

Share

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!

Here’s how…

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.

SPList

Here's the SharePoint List we built to replicate the web service feed (click to enlarge)

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 :

http://<SharePointServer>/<Site>/_vti_bin/listdata.svc/<Listname>

PowerPivot Data Feed

Entering the information into the PowerPivot data feed source (click to enlarge)

Step 3: Creating the PowerPivot “cube”

Publishing to SharePoint

Publish to your PowerPivot Gallery (click to enlarge)

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:

PROVIDER=MSOLAP;DATA SOURCE=http://<sharepointserver>/<site>/<powerpivot
gallery>/<powerpivot workbook>.xlsx

The PPS Data Source

Creating your PerformancePoint data source (click to enlarge)

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!

The PPS Dashboard!

The final PPS Dashboard sourced from PowerPivot! (click to enlarge)

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
    PowerPivot
  • “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

Helpful Resources:

 
 Share on Facebook Share on Twitter Share on Reddit Share on LinkedIn
Comments Off on Building a PerformancePoint Dashboard…without a cube?  comments