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=https://analysis.windows.net/powerbi/api;;Initial Catalog=dd6049fc-8093-4c70-95ab-dc327c1bcc35;Location=https://wabi-north-europe-redirect.analysis.windows.net/xmla?vs=sobe_wowvirtualserver&db=dd6049fc-8093-4c70-95ab-dc327c1bcc35;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=https://analysis.windows.net/powerbi/api;;Initial Catalog=dd6049fc-8093-4c70-95ab-dc327c1bcc35;Location=https://wabi-north-europe-redirect.analysis.windows.net/xmla?vs=sobe_wowvirtualserver&db=dd6049fc-8093-4c70-95ab-dc327c1bcc35
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:
You probably got an error like the following, Power BI doesn't seem to be able to understand the model metadata:
To get around this problem, you have to write a query before hitting ok, just like the image below.
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:
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:
And that's it! Your fully refreshed, small and simple dataset connects to other Power BI models.
Hmmm, Power BI Inception...
Take care!