Enabling Tableau to read PowerPivot model updates

One of those interesting opportunities that opens up when using PowerPivot in conjunction with SharePoint 2010 is that of leveraging the PowerPivot cube-like interface of its inherent data model in order to extend the analytical arsenal of an organization.

Tableau is a great tool that can extend the reach of PowerPivot deployments due to its advanced visualization capabilities.

Its typical for data analysts to go over many iterations of a PowerPivot report (model) before they consider it good enough to share it with others.  At this point, though, Tableau 7.0 does not by default grab the latest and greatest version of the PowerPivot analytical database, the one with the must current data model and DAX calculations.

Lets walk through a typical scenario.  A data analyst creates a PowerPivot report and publishes it to SharePoint. He then wants to create additional visual analysis using Tableau against the data model that was loaded into PowerPivot.

A typical PowerPivot report URL location would look like this:

http://myServer/PowerPivot Gallery/MyPowerPivot.xlsx

image

In Tableau, he can then use this URL to connect to the cube-like interface of the underlying PowerPivot data model.  To do this, he uses the Microsoft Analysis Server option, and instead of typing a server name simply he uses the URL location of the PowerPivot workbook:

image

To be clear, you are not connecting to the Excel pivot table, but to the underlying PowerPivot model.  This normally contains more data and metadata than the actual Excel report rendered in the PowerPivot gallery.  At this point, he is able to create interesting Tableau analytics using PowerPivot as the source:

image

Now he wants to add additional DAX measures to extend the analysis. He adds the following expression to PowerPivot:

% of Total Sales:=[Sum of SalesAmount] /

CALCULATE([Sum of SalesAmount], ALL(DimSalesTerritory) )

And republishes to SharePoint.  By interacting with the newly published PowerPivot report by either selecting a new filter, invoking the workbook through a client via the SharePoint URL or simply by scheduling a data refresh, an updated version of the PowerPivot underlying model is stored in the dedicated instance of Analysis Services that SharePoint uses.

At this point, If you imagine being the data analyst in question, you may now attempt to re-open the Tableau workbook but you will get an error message:

image

And the report is wiped out.  Hmm not good!   Interestingly, other clients like SSRS, Report Builder and Excel can get the latest version of the PowerPivot model without an issue.  So how can we enable Tableau to leverage the PowerPivot model changes seamlessly?

The solution is fairly simple, though not very intuitive.  Prior to doing any changes to the PowerPivot model, we must save the Tableau connection:

image

This process creates an actual file that we can then edit in notepad.  We must remove the database name from the connection file (highlighted below) and re-save:

image

This is necessary as Tableau is creating a connection to a specific version of the PowerPivot model which is not valid anymore.  By removing the database name, SharePoint is able to point Tableau to the newest PowerPivot model available, which will then enable the report to work without errors and with all the changes to the underlying model.  After implementing the solution, the data analyst can now use this saved connection to create Tableau workbooks that will automatically update the metadata to match that one of the newest PowerPivot model.

image

In the scenario discussed, he would see the new DAX measure in the Tableau measures pane and use it as part of the visualization:

image

It is possible to save these connection files to Tableau server and share them with others, so newly created views will benefit from automatic synching with the most updated PowerPivot hosted models.

image

Keep in mind that one of the great benefits of connecting to PowerPivot workbooks hosted in SharePoint is that you get additional features like automatic data refresh and security, along with the ability to centralize one model that can serve many reports across multiple platforms and tools.

In my opinion, this technique allows data analysis to benefit from the best of both worlds when conducting rapid iteration self-service BI.

Advertisements

10 Responses to Enabling Tableau to read PowerPivot model updates

  1. 100tsky says:

    Hi, Javier!

    thank you for your work! PowerPivot is great!

    I need to understand clearly: now we can’t link latest PowerPivot 11.0.2100.60 and Tableau 7.0 (Desktop pro edition)

    Thank you!

  2. javierguillen says:

    Hi 100tsky,

    You can’t read PowerPivot 11 (SQL Server 2012) from Tableau directly (by connecting to the actual Excel workbook). However, you can connect to that version of PowerPivot if you publish it to SharePoint and then connect through its URL location (as described on the blog post).

    Note that you *can* connect from Tableau directly to PowerPivot 10 (SQL Server 2008 R2), without SharePoint being involved on the picture

    Does that help?

  3. 100tsky says:

    Yes, thank you! I think should I upgrade my Tableau to version 7.0

  4. Pingback: PowerPivot and Beyond: Expanding your analytical repertoire « Javier Guillén

  5. Kurt says:

    Is there any chance you live near Redmond? I want to work with you on a project involving EVERYTHING you are talking about above.

  6. javierguillen says:

    Hi Kurt

    Hehe, I actually live in Charlotte, NC 🙂 In case you still want to discuss the project you have in mind, my email is javiguillen@hotmail.com

  7. James says:

    Hi Javier,

    This is exactly what I’m looking for, with one exception. I do not use PowerPivots with SharePoint, but instead just connect Tableau to my local PowerPivot file (which contains an Access data connection as well as some additional Linked Tables I’ve created in PowerPivots). This works beautifully with the exception that you highlight here, I can’t refresh it when I make changes to the Model.

    I’ve tried your solution, but the TDS file is different when it references a local file. Its difficult to know how much of the filename=… to delete (or if it would even work in this scenario). Any thoughts?

  8. javierguillen says:

    Hi James. I have not tried this solution with a local PowerPivot file. When I get a free moment I will try it out and if I find something I will surely post it here.

  9. Miguel Chavez Garcia says:

    Espero que hables espanol . Felicidades ! Excelente trabajo

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: