Importing data from another Power BI service dataset

Hi everyone,

Today I'm bringing you one more tip that may assist you in your day-to-day with Power BI. Warning: Not supported by Microsoft.

The Situation

Your organization started their BI development with a Sales dataset, a model where they can analyze the company's sales by day, product or even location.

Time goes by and new areas start to need BI developments like, for instance, Human Resources, so a new dataset is created and deployed to Power BI. Soon after that, the administration requests a summary analysis where they can see, side by side, the sales revenue information by month and the active employees count.

How can you build this requested model? It's a very simple analysis, but they want to cross different models, and you probably don't want to merge both datasets into one.

The Solution

If your models were deployed into Analysis Services, there is an import mode option to connect to it, but there is no import option to connect to Power BI service models.

Given that, let's try this unsupported tip.

Step 1: Get the connection string

Firstly, you should download the .odc files for the datasets you want to import from:

  • Go to your dataset and select the option Analyze in Excel to download the .odc files.
  • Open the files with a text editor and locate the <odc:ConnectionString> tag.

Inside it you should have a connection string similar to this one:

Provider=MSOLAP;Integrated Security=ClaimsToken;Data Source=;;Initial Catalog=dd6049fc-8093-4c70-95ab-dc327c1bcc35;Location=;MDX Compatibility= 1; MDX Missing Member Mode= Error; Safety Options= 2; Update Isolation Level= 2; Locale Identifier= 1033  

This was a connection file for excel, so you need to change it a little bit to make it work in Power BI. Everything after MDX compatibility is not really necessary, but you also need to remove the Integrated Security option. In the end, it should resemble this:

Provider=MSOLAP;Data Source=;;Initial Catalog=dd6049fc-8093-4c70-95ab-dc327c1bcc35;Location=  
Step 2: Get the data from the OLE DB provider

Now that you have the connection string, open Power BI Desktop, go to Get Data and select the OLE DB option. Insert the connection string you have just extracted from the file and press OK.

Credentials will be requested, you should introduce the user id (and password if needed) in the Default/Custom field value:
Power BI desktop OLE DB credentials

You probably got an error like the following, Power BI doesn't seem to be able to understand the model metadata:
Power BI desktop OLE DB error To get around this problem, you have to write a query before hitting ok, just like the image below.
Power BI desktop OLE DB query

Step 3: Publish & refresh it!

And just like that, you can now import data from another Power BI model published in the service.

In this case, I imported revenue by month from the Sales model, employee count by month from the HR model and added a Calendar table. I made a simple report that crossed the information before publishing it all to Power BI online:
Power BI service report

Ok, what else is missing? Right, refreshing!

That's a tricky part. At the time of writing, Power BI service doesn't seem to be able to connect directly and, if you try to use enterprise gateway, you'll get the same metadata error as you did in Power BI Desktop before you added the DAX query. Don't despair though, you can still use Personal Gateway to refresh this model.

Go ahead and install the gateway, then go to your dataset and configure credentials just like the following image:
Power BI service gateway credentials

And that's it! Your fully refreshed, small and simple dataset connects to other Power BI models.

Hmmm, Power BI Inception...

Take care!

Joana Barbosa

BI Developer @ Devscope, knocking data out using BI! Mostly using Microsoft Business Intelligence stack like PowerBI and Analysis Services.

Subscribe to Binary Lies

Get the latest posts delivered right to your inbox.

or subscribe via RSS with Feedly!