Have you created a robust model in Power BI Desktop and you are looking for a way to import it to an instance of SQL Server Analysis Services Tabular? Hmm, it would be highly beneficial if you could import Power BI model to SSAS Tabular and it potentially saves lots of development time and costs. The good news is that with SQL Server 2016 and SQL Server Data Tools for Visual Studio 2015 it is possible. In this post I show you how to import Power BI Desktop model to SSAS Tabular 2016. Unfortunately, you cannot do the job in any prior versions of SQL Server, SQL Server Management Studio or SSDT.
- SQL Server 2016 Tabular: You can download SQL Server 2016 Developer Edition for free. Check this out for more information
- SQL Server Management Studio (SSMS) 2016: Down SSMS 2016 from here
- SQL Server Data Tools for Visual Studio 2015 (SSDT 2015): You can download it here
- Power BI Desktop: Download Power BI Desktop from here
How it works?
The idea is to
- Connect to Power BI Desktop model from SSMS 2016
- Script the model
- Modify the script
- Execute the scripts on your on-premises instance of SSAS Tabular 2016
- Open the new SSAS Tabular database in SSDT 2016
- Modify the model
- Redeploy and process the model
Note: Do not close Power BI Desktop until we completely import the model to SSAS Tabular.
- You’re familiar with all required tools listed above
- You’re familiar with SQL Server Analysis Services Tabular models and any corresponding concepts, security settings and so forth
- You’re familiar with DAX and Power Query
Creating a New Power BI Desktop Sample Model
Frist of all, I’d rather creating a new sample Power BI Desktop model. If you already have an existing model, ignore this part and go to the next section. To create the sample, I use the new SQL Server sample database called “Wide World Importers DW”. You can download it from here and restore it in an instance of SQL Server 2016.
- Open Power BI Desktop
Get data from SQL Server
Select “Fact.Sales”, click “Select Related Tables” then click “Load”
Note: Realistically, in a real project, we always have lots of DAX formula, calculated measures and calculated columns and so on in our Power BI Desktop model. We also have some transformations in Power Query like added conditional columns, split columns etc… So I add some simple calculated measures, a calculated column using DAX. I also split a column into two columns in Power Query during the next steps.
Add the following measures to the “Fast Sales” table:
- Total Profit = sum(‘Fact Sale'[Profit])
- Total Quantity = sum(‘Fact Sale'[Quantity])
- Total Sales Tax Exc = sum(‘Fact Sale'[Total Excluding Tax])
- Total Sales Tax Inc = sum(‘Fact Sale'[Total Including Tax])
Add a calculated column as below:
- Sales = ‘Fact Sale'[Quantity]*’Fact Sale'[Unit Price]
- Open “Query Editor”
- Click “Dimension Employee”
Split “Employee” column to separate employee first name and employee last name
- Close & Apply
- Save the model as PBIX
Import Power BI Desktop Model to SSAS Tabular 2016
So far we created a very simple model. Now we’re going to import it to SSAS Tabular using SSMS 2016.
Connect to Power BI Desktop model from SSMS 2016. If you don’t know how to do that check this out.
Script the database
As you see the database name is a random GUID and you don’t want to create a SSAS Tabular database with a GUID name. So replace the database name and id with a meaningful string
- Copy the scripts
- Connect to an instance of SSAS Tabular
- Open a new XMLA query
- Paste the scripts (the generated script is NOT XMLA anymore, it is JSON indeed)
- Execute the scripts
We successfully imported the Power BI Desktop model to SSAS Tabular 2016, but, we are not done. If you process the database it fails so we need to resolve the issues. As you might guessed the first things we need to change are the data sources. Of course you can modify the data sources from SSMS 2016, but, remember, we made some changes in the “Dimension Employee” table as well. These sort of Power Query changes lead the database processing to fail. To resolve these sort of issues the best way is to import the SSAS Tabular model to SSDT 2015, fix the issues, deploy and process the database.
Import SSAS Tabular Model to SSDT 2015
Open SSDT 2015
Create a new project
Select “Import from Server (Tabular)
Enter SSAS Tabular server name in “Tabular Model Designer”
What is in the Model
So far we successfully imported the model from SSAS Tabular server. In the next steps we resolve all the issues, redeploy the model to the server and process the database. But, let’s see what we have in the model that was imported from Power BI Desktop. You remember we created some calculated measures and a calculated column in “Fact Sales” table. Let’s see what happened to those calculated objects after we imported the model to SSAS Tabular server. Opening “Fact Sales” table you’ll immediately notice that all calculated measures are there. Scrolling to the right you’ll see the calculated column is imported successfully as well. That’s really cool isn’t it?
Let’s see what happened to the Power Query changes we made in “Dimension Employee”. Open “Dimension Employee”. We split the “Employee” column to two columns, “First Name” and “Last Name”.
Well, both “First Name” and “Last Name” exist in the SSAS Tabular model.
Let’s see what happens behind the scene in “Dimension Employee” table. Click “Table Properties” from “Table” menu.
Looking at the SQL statement you’ll quickly see the problem regardless of the error message we get. We haven’t modified the data source so it is trivial to get error message in “Table Properties”.
It’s a “SELECT * FROM” query so we’ll get all columns from the source table and the source table doesn’t include “First Name” and “Last Name”. Therefore, what ever we’ve done in Power Query, are useless as we need to redo all of them.
Switch to “Diagram” view to see the model diagram.
As you see there are lots of hidden tables that weren’t in the original Power BI Desktop model either. But, where they came from? Click of those tables and look at the formula.
Well well! We have several date dimensions created to support automatic date hierarchies which announced at Power BI Desktop November update. The reason we have that much hidden date tables is that Power BI Desktop creates a calculated date table for each date column we have in the model. Look at the model diagram to see each of these date dimensions have a one-to-many relationship with a column of date/time data type. For instance, select “Dimension City” table and have a look both hidden tables linked to it.
Let’s move forward and fix all the issues.
Fixing the Issues
- Click “Existing Connections” from “Model” menu
- Select a connection from the list
- Click “Edit” and modify the connection to point to you source database in SQL Server. In my sample it is “Wide World Importers DW”
- Do not forget to setup “Impersonation”
- Save the connection
- Repeat the same process for all other connections
- Select a table then open “Table Properties”
- Click “Validate”
- You might get an error that indicates there is something wrong with the SQL statement. Review the SQL statement, fix the issue then click OK
In my sample the issue is the table name. As you see in the above screenshot the SQL statement is querying [Dimension City] which doesn’t exist in the “Wide World Importers DW” database. This is how I originally named the table in Power BI Desktop. Replacing the query with the correct one fixes the issue.
You need to repeat the same process for all tables. As I mentioned before, all changes you made in your model using Power Query are useless in SSAS Tabular. So here is the SQL statement I had to write to do the same thing on the “Employee” table to split the “Employee” column to “First Name” and “Last Name”. This will be different for different scenarios.
After fixing all issues we can process the database.
You can now deploy the fixed model to SSAS Tabular server.
Although importing Power BI Desktop to SSAS Tabular 2016 is doable, but, it is NOT officially supported by any Microsoft BI tools. So, importing Power BI Desktop model to SSAS Tabular should be considered as an alternative way to developing a SSAS Tabular 2016 model from scratch. At the end of the day and as always, it really depends on your case. The reasons that this method might not suit your case are:
Power BI Desktop is updated more regular than SSAS Tabular 2016, So in some point you might face DAX version incompatibility between the Power BI and SSAS Tabular 2016
As mentioned before, Power Query transformations won’t get imported to SSAS Tabular 2016 model. Therefore, you’ll end up with developing a new SSAS Tabular model from scratch if you have complex Power Query transformations in the model like several user defined functions, query parameters and so forth
On the other hand, if your project budget is too tight or you do not have enough recourses assigned to the project and you do not have lots of complex transformations in your Power BI Desktop model then using this method to import the model to SSAS Tabular 2016 will save you lots of time and budget.