very recently, when I was part of a pre-sale activity , my manager asked me to propose a data analytic solution with least amount of technologies as well as least effort and cost. So my obvious answer was to start with a Power BI model and to create set of Power BI reports using it. Since Power BI pro allow maximum of 1 GB per PBIX file, and with the amount of compression , I was pretty sure that this model can store up to 4-5 GB of data. However, his follow up question was, what if the client want to add more data sources and want to expand the solution? Then I told him, since a semantic model is a must for this client, they will have to go to a SSAS tabular model in that case. He didn’t stopped there; His next question was “so will we have to do all the model creation part again? which means days to write all those DAX queries, relationships, measure , calculated columns and role…. etc…?” My immediate answer was “No, we can export Power BI model to SSAS”. yet I was not sure how.
Therefore I thought of trying it out and this blog post is all about whether we can transfer a Power BI model to a SSAS tabular model or not and if can, how ? As you may already know (may be not), Power BI desktop uses SSAS to implement Power BI models behind the seen. In other words, a Power BI model is actually a SSAS tabular model. So if I can get a back up of that tabular model or if I can generate the script of that model, I can surely either restore or recreate it using management studio.
First thing is first. I need to connect to Power BI model using SSMS to do any of those.But how can I do that? Although I have covered this is my previous blogpost how to do it, I’m gonna mention same set of steps in here as well.
In order to find out the background SSAS Tabular instance, Open Task Manager and look for “Microsoft SQL Service Analysis Service” in running task list. Check the image bellow. This is how it shows in my machine.
In my PC, I have installed two SSAS Instances, one for Tabular model (MSSQLSERVER) and one for Multidimensional Model(MULTIDIM). As you can see in Image:01, there is a 3rd instance, which does not have any name. That is the instance used by Power BI Desktop. But I don’t know the name of this instance to connect using SSMS. How do I find the name or IP/Port in this instance so that I can connect? For that right click on the process name as in Image:02 and go to Detail and there you can get respective PID(Process ID) for this SSAS service. In my case PID is 9736 (Image:03)
Now open Command Prompt and run following command.
Command : Netstat -anop TCP | findstr [PID]
Make sure you replace [PID] with SSAS Process ID which we found using above steps.This command will give us the TCP IP and the port of SSAS Service. Check Image: 04
As you can see in Image: 04 , in my PC , TCP IP and port is 127.0.0.1:24731.
Now run SSMS and select “Analysis Service” as server type and enter above TCP IP address for the server name. Keep authentication as Windows Authentication. Check Image:05.
Once got connected, you will be able to the how your Power BI model looks as a SSAS tabular model.
This is how my Power BI model looks like in Power BI desktop (Left) and how it look in SSMS (Right). Check Image : 06.
Image : 06
As you can see, model name is a system generated GUID. Apart from that you can notice that there are two system generate tables which are not visible in Power BI Model. Those tables are used to handle “Date” tables in Power BI. lets talk about that in some other time. Next thing I want to do is to take a backup of this SSAS database using SAMS and restore in my SSAS tabular instance so that I can use it for further developments. However, When I try to do so, this is what SSMS says to me.
Yes !!. You can’t take a backup of it because Power BI SSAS instance run in Diskless mode and it is does not allow to take DB backups.
My next try was to take script of this database and execute it in my SSAS server.But it was not straight forward as I thought. Although I was able to generate Database Creation Script, when I execute it in my SSAS 2017 instance, It gave me multiple script errors. The reason is this Power BI SSAS instance is special and it has special features which are not available in my SSAS 2017 instance. When I opened the DB creation script and checked the “Compatibility Level” of this tabular database, It showed me something I have not seen before.
But if you check SSAS versions so far available out there, there is nothing called 1456 in compatibly list. Check the image bellow.
Image : 09
As you can see, the highest compatibility level is 1400 and which means surely I can’t create a SSAS database with 1465 compatibility level in Azure Analysis Service 2017 or Analysis Service 2017. Luckily for me , I have installed SQL Sever 2019 CTP 2.1 and this compatibility level works perfectly fine with that SSAS version. So I ran the database creation script in SSAS 2019 CTP 2.1 instance and this is how my database looks.
Well done, we successfully migrate the Power BI model to a SSAS tabular model. Wait a minute, you can’t use this model yet !! For that you need to process the model again. Remember we created the model using script. Before process the model , we will have to edit connections to data sources. If you check ”Connections” folder of the model, you will see all the connection as GUID names. Check the image bellow.
Unfortunately yes, you will have to open those connection one by one and edit the connection. Because Power BI mashup all the connection information and we can’t identify respective connection by its name now. However there is a way to identify which connection point to which data source. For that you need to generate script of each connection and within the script , at the very end of connection string, you will see something like this. Check the Image: 12.
Image : 12
This is the only readable part of the connection string since all other texts are mashed up by Power BI. Using this “location” attribute, we can understand that this is the connection used to extract “Market Share” table to Power BI. In this scenario it is an MS SQL table. What we can do now is to change this connection to “Market Share” SQL table. (Same process should be followed through each connection)
So I change the connection as in Image 13
Remember you need to set impersonation information when you change connection string.
Then go to Impersonation section of the connection and set the impersonation using windows account as bellow.
Can I process the model now? Yes I can. Finally , I manage to transfer my Power BI model to SSAS Model.
Image : 14
But if you have multiple data Sources, setting up connections will not be easy and there can be lots of changes you will have to do in order to work your model.
Conclusion: The whole blog post was try and error thing. I tried all these steps while I’m writing this blog post. To be honest, I’m not sure how easy or hard will it be to transfer a complex Power BI model to a SSAS tabular model. Some times you might fail to do so. Nevertheless, It’s worth trying and I might be able to share ways to resolve some of those issues if you put them as comments.
Thanks for reading.. Cheers !!