PowerBI virtual data models

Lately, in solving a simple problem, I’ve discovered something about powerBI that offers enormous potential.  I found that the data model within powerBI is available as a tabular model in the cloud, leading to a visualized modelling environment that has no computational cost to the business, and can offer users with poor connections incredible access to large datasets.

I started trying to figure out how to make an excel file available to the business and run it on a refresh schedule.  This used to work in the old powerBI V1, where you could convert an excel online file to a powerBI file.  It remained an excel file, but was then able to refresh using the gateway.  That changed with powerBI V2, where excel online as a presentation layer was somewhat divorced from powerBI.

To clarify the confusion here, I want to manipulate data in excel, not ingest that data in powerBI and present it there.

It seems the idea of connecting to the powerBI model was quite popular, garnering 2400 votes.  The idea ended up as something called Analyze in Excel, which is really just a connection to a virtualized database in azure (the cloud).  You’ll first have to check you have microsoft as OLE DB provider for sql server 2016 installed on your client machine.

Powerpivot as a data connection, is really just a tabular model in excel.  You can find the source connections to powerpivot, and you’ll also find the odc connection within excel itself to the workbook model, in other words, powerpivot.  The powerBI data model is essentially powerpivot in the cloud.  Once the model is been published to powerBI, you can create the office data connection (ODC) from the model:

Analyze in excel

You’ll end up with an excel file with an ODC connection that can then be manipulated.

It works, but it has a few caveats.  Namely;

  1. Numeric fields are non-additive.  Meaning you have to build calculated measures in order to aggregate anything.
  2. Each pivot table in the workbook will execute an OLAP query against the cloud.  A single refresh causes them all to be executed.  You may want to create separate connections for each pivot table.
  3. From the Analyze in Excel documentation – Since users will need to refresh the dataset, and refresh for external connections is not supported in Excel Online, it’s recommended that users open the workbook in the desktop version of Excel on their computer.
  4. The only way to share a dataset, is to share it within a content pack.  Since the model is held on azure, it would seem the only way to access it would be integrated security.

The connection string is really quite telling.  It looks like this is really a cube in the cloud.

Provider=MSOLAP.7;Integrated Security=ClaimsToken;Persist Security Info=True;Initial Catalog=sobe_wowvirtualserver-fc182e6e-a768-41b3-8a88-0e19d3b13d47;Data Source=https://analysis.windows.net/powerbi/api;Location=”https://wabi-us-north-central-redirect.analysis.windows.net/xmla?vs=sobe_wowvirtualserver&db=fc182e6e-a768-41b3-8a88-0e19d3b13d47″;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update Isolation Level=2”

The way to scale this to users is to add the model to a content pack, and have the users add the content pack in powerBI.

content pack

Once the content pack has been added, it can be refreshed from the gateway server.  Since the user has added the data model to their powerBI profile, the model will be authenticated using integrated security from the ODC.  The connection can be refreshed such that it updates when the file is opened, or it will be refreshed as the user filters and performs other manipulations to the model.  None of the raw data is stored in the file, therefore the excel file can be kept very small while serving large amounts of data.  This is ideal for offices with a slow connection, or for applications where the users are not in the internal office network and can use the cloud.

This is another great example of how the world of data warehousing is changing.  You can visualize a model, and leverage the free computation power you get with powerBI without having to buy any other virtualization service.


Far better an approximate answer to the right question, which is often vague, than the exact answer to the wrong question, which can always be made precise. -John Tukey
The plural of anecdote is not data. - John Myles White

Recent Posts

RSS PowerBI blog