THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Connecting to #powerpivot from an external program (such as #Tableau)

Many people requested me how to connect to Power Pivot from an external program, without publishing the workbook on SharePoint or on Analysis Services Tabular. I always said it is not possible (for both technical and licensing reasons), but someone observed that Tableau is able to extract data from a Power Pivot data model connecting directly to the xlsx file. I wanted to investigate how they solved the limitations that exists.

From a technical point of view, you have to install a Tableau Add-In for Power Pivot Excel (it’s available for both 32 and 64 bit). Then, you connect using the Tableau Desktop software selecting the Microsoft Power Pivot connection. You choose a local Excel file and click Connect. The list of perspective appears in a combo box.

connect

You click ok and you navigate into the Power Pivot data model. But what’s happening? Tableau runs an Excel instance (probably by using Excel object model) and then connects through the Tableau Add-In for Power Pivot that you installed before. Probably this add-in acts as a bridge between the process running Excel and the process running Tableau. This solve the technical problem, and it would be interesting to know how to use the same add-in from other programs without having to write the same add-in again. I know many ISVs that would love to do that!

But before starting your project in Visual Studio to do the same (after all, it shouldn’t be rocket science writing such a connector), consider the license agreement (EULA) of Office. It says that “Except for the permitted use described under "Remote Access" below, this license is for direct use of the software only through the input mechanisms of the licensed computer, such as a keyboard, mouse, or touchscreen. It does not give permission for installation of the software on a server or for use by or through other computers or devices connected to the server over an internal or external network.”. It seems we are in gray area here. The access to Excel is not direct. But at the same time, it is not made on another computer, and technically you are using keyboard, mouse and/or touchscreen when you are using Tableau Desktop.

This is certainly an unsupported scenario (and if the background Excel process hangs for any reason, you have to kill it in Task Manager). But if the licensing allows that, or if Microsoft tolerate this, probably many companies writing software (I have a long list of requests I received…) could be interested in doing the same.

I would love to hear some official word on this topic…

Published Wednesday, February 26, 2014 9:59 AM by Marco Russo (SQLBI)
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Javier Guillen said:

This is a very interesting point and I would also love to hear the official MS opinion about it.   I am thinking, though, that by accessing an instance of Excel (through its object model), it would limit the data model to one concurrent client, which - if true - would be a great limitation compared to the hosted option through SharePoint...

February 26, 2014 2:39 PM
 

Marco Russo (SQLBI) said:

Javier, for Tableau desktop this doesn't seem a great limitation. This technology cannot work on the server version (there you need Tabular or SharePoint).

February 26, 2014 11:23 PM
 

Cathy Dumas said:

A thought came to me on this topic.

If this really was a hard licensing rule, Microsoft would be breaking its own rules with Analysis Services. Tableau is no worse than Microsoft in this regard.

When you import from text, Excel, etc into an Analysis Services tabular model, you use the ACE Provider. I wrote about this a while ago, see http://blogs.msdn.com/b/cathyk/archive/2011/09/29/working-with-the-ace-provider-in-the-tabular-designer.aspx. You can get the provider in one of two ways, either by installing Excel or by downloading the standalone ACE provider components.

Now, imagine you are a tabular model developer. Your dev box will have Excel so you can test your work, and likely will have a dev Analysis Services tabular instance on it. Because you have Excel, there is no need to install the ACE provider separately. Now create a tabular model and import a text file, two things happen:

1. Visual Studio spawns a process that loads the Office components so it can preview the data

2. Analysis Services then uses the Office components to process the data into the server

Analysis Services and Office are licensed separately. I would therefore see Analysis Services's usage of the Office components equivalent to Tableau's.

That scenario is just the desktop UI component in SSDT, we won't talk about the non-interactive scenarios of processing on a production server or testing automated processing in a development environment.

I find it unlikely that Microsoft will comment.

And as to how they do it?

I looked at Tableau's KB for choosing the supported Power Pivot "drivers", see http://kb.tableausoftware.com/articles/knowledgebase/choosing-powerpivot-addin-and-driver.

For pre-Office 2013 models they require an OLE DB driver, this is probably so they can query the specially named PowerPivot connection through Microsoft Office Interop APIs via OLE DB. I did the same thing in this post: http://blogs.msdn.com/b/cathyk/archive/2011/08/17/pointing-powerpivot-pivot-tables-to-tabular-models.aspx. If you are using Office Interop APIs this actually starts an Excel process that is used by your program.

In Excel 2013 the special data connection disappeared, so you can't find the model via OLE DB connection any more. I haven't tried automating Excel 2013. It looks to me though that there is an ADO Connection for querying the model, see http://msdn.microsoft.com/en-us/library/office/jj228545(v=office.15).aspx. You can send it commands via command text.

Automating Power Pivot access is not for the faint of heart, though compatibility between versions should be more stable now that the Power Pivot model is saved in the Office file format.

March 6, 2014 10:34 AM
 

Marco Russo (SQLBI) said:

Cathy,

thank you very very much for your comment.

The link to ADOConnection property is a little gem I wasn't aware of.

I'm wondering whether it could be a bridge to query the data model from an External process or if you still have to create an add-in for that, like Tableau did.

Thanks again!

Marco

March 6, 2014 11:31 AM
 

Cathy Dumas said:

You will need a program of some sort for connectivity, not necessarily an "add-in". If you are extending a third-party program, then you need to use their extensibility framework. If the extensibility framework calls for an add-in then that's what you'll write, but if you're an ISV writing your own code then you just change your code to query this ADO connection.

When you use the Office extensibility APIs to open a workbook, as I did in my code sample, an Excel process starts for you automatically. You need not write an Excel add-in. It's your responsibility to open and close the workbook responsibly so a zombie Excel process isn't lying around locking your workbook. If you open my code sample and step through it in the debugger, you will see exactly when this happens.

Usually you use ADOMD.Net to query Analysis Services cubes of any stripe (either multidimensional or tabular). I have never tried using ADOMD.NET with a ADO connection but if I was interested in writing a program that would be the one thing I tried. Another thing I would do (and I might do this first) is look at Tableau's logs (the tabprotosrv* logs), see the kinds of commands they issue, and copy them. Ideally you will be able to issue MDX/DAX queries via ADOMD.NET and you should be able to reuse logic from other programs.

Hope this helps

March 7, 2014 1:49 PM
 

Marco Russo (SQLBI) said:

Thanks Cathy!

March 7, 2014 1:59 PM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement