Replace July 2020: Whereas this methodology nonetheless works with Energy BI Desktop June 2017 launch and SSAS 2017, it doubtlessly doesn’t work with the later variations of Energy BI Desktop (Jul 2020) and SSAS 2019 as there are new DAX features which might be out there within the Energy BI Desktop however not in SSAS 2019. So use this methodology as an experimental methodology AT YOUR OWN RISK!
Have you ever created a strong mannequin in Energy BI Desktop and you’re in search of a technique to import it to an occasion of SQL Server Evaluation Providers Tabular? Hmm, it will be extremely useful should you may import Energy BI mannequin to SSAS Tabular and it doubtlessly saves a number of growth time and prices. The excellent news is that with SQL Server 2016 and SQL Server Information Instruments for Visible Studio 2015 it’s attainable. On this submit I present you how you can import Energy BI Desktop mannequin to SSAS Tabular 2016. Sadly, you can’t do the job in any prior variations of SQL Server, SQL Server Administration Studio or SSDT.
- SQL Server 2016 Tabular: You may obtain SQL Server 2016 Developer Version totally free. Examine this out for extra info
- SQL Server Administration Studio (SSMS) 2016: Down SSMS 2016 from right here
- SQL Server Information Instruments for Visible Studio 2015 (SSDT 2015): You may obtain it right here
- Energy BI Desktop: Obtain Energy BI Desktop from right here
The thought is to
- Hook up with Energy BI Desktop mannequin from SSMS 2016
- Script the mannequin
- Modify the script
- Execute the scripts in your on-premises occasion of SSAS Tabular 2016
- Open the brand new SSAS Tabular database in SSDT 2016
- Modify the mannequin
- Redeploy and course of the mannequin
Word: Don’t shut Energy BI Desktop till we fully import the mannequin to SSAS Tabular.
I assume
- You’re conversant in all required instruments listed above
- You’re conversant in SQL Server Evaluation Providers Tabular fashions and any corresponding ideas, safety settings and so forth
- You’re conversant in DAX and Energy Question
Frist of all, I’d relatively creating a brand new pattern Energy BI Desktop mannequin. If you have already got an current mannequin, ignore this half and go to the following part. To create the pattern, I exploit the brand new SQL Server pattern database referred to as “Extensive World Importers DW”. You may obtain it from right here and restore it in an occasion of SQL Server 2016.
Word: Realistically, in an actual challenge, we at all times have a number of DAX method, calculated measures and calculated columns and so forth in our Energy BI Desktop mannequin. We even have some transformations in Energy Question like added conditional columns, break up columns and so forth… So I add some easy calculated measures, a calculated column utilizing DAX. I additionally break up a column into two columns in Energy Question in the course of the subsequent steps.
Up to now we created a quite simple mannequin. Now we’re going to import it to SSAS Tabular utilizing SSMS 2016.
Voila!
We efficiently imported the Energy BI Desktop mannequin to SSAS Tabular 2016, however, we’re not completed. In case you course of the database it fails so we have to resolve the problems. As you may guessed the primary issues we have to change are the info sources. After all you’ll be able to modify the info sources from SSMS 2016, however, bear in mind, we made some modifications within the “Dimension Worker” desk as properly. These kind of Energy Question modifications lead the database processing to fail. To resolve these kind of points the easiest way is to import the SSAS Tabular mannequin to SSDT 2015, repair the problems, deploy and course of the database.
Necessary Updates (June 2017):
Replace 1:
After you copy and paste the scripts, take further consideration to “CompatibilityLevel” line. That is essential because the later variations of Energy BI Desktop are supporting CompatibilityLevel of 14. So if you wish to run the scripts in a SSAS Tabular 2017 occasion, you don’t want to alter the CompatibilityLevel. However, should you run the scripts in a SSAS Tabular 2016 occasion, then you want to change the CompatibilityLevel to 12 which is supported by SSAS 2016. In case you don’t change the CompatibilityLevel to 12 and also you run the generated scripts in SSAS Tabular 2016 you then’ll get the next error message:
“The JSON DDL request failed with the next error: Did not execute a JSON script. Error returned: 1400 will not be a sound worth for this component.”
After altering the CompatibilityLevel to 12 the scripts ought to run efficiently.
Replace 2:
In case you’re scripting the database utilizing SSMS 2017 and also you attempt to run it on a SSAS Tabular 2016 occasion, you then may get the next error message:
“The JSON DDL request failed with the next error: Unrecognized JSON property: isPrivate. Examine path ‘mannequin.tables[0].isPrivate’, ”
As you see the error message complains about “isPrivate” JSON property. Apparently “isPrivate” is a brand new JSON property which is NOT supported in older variations of SSAS Tabular fashions.
So, you want to search “isPrivate” within the scripts and comment that line, then run the scripts and hopefully you don’t get every other error messages.
The opposite discovering on producing scripts in SSMS 2017 is that should you allow “Auto Date/Time” below “Time Intelligence” setting in Energy BI Desktop choice, it routinely creates hidden Date dimension for every area within the mannequin that has Date or Date/Time information kind. So when choose a column with Date or Date/Time information kind, Energy BI routinely picks the associated date column from the auto-generated hidden Date dimensions. It will get extra fascinating when you choose a date column in Energy BI Desktop, it creates a date hierarchy routinely. That is made attainable utilizing a options referred to as “Variation”. This characteristic can be out there in SSAS Tabular 2017. So so far as you generate the scripts of your Energy BI Desktop mannequin in SSMS 2017, it generates JSON codes for “Variation” characteristic. This characteristic is NOT out there in SSAS Tabular 2016, so whenever you run the scripts in an occasion of SSAS Tabular 2016, you get the next error message:
“The JSON DDL request failed with the next error: Unrecognized JSON property: variations. Examine path ‘mannequin.tables[0].columns[1].variations’”
To resolve this downside you want to take away all “Variations” blocks from the scripts which is likely to be a time consuming course of.
In case you run the identical scripts in an occasion of SSAS Tabular 2017 you then don’t get any of the above error messages.
Conclusion:
In case you prefer to import your Energy BI Desktop mannequin to SSAS Tabular 2016 then I like to recommend you employ SSMS 2016 to generate the scripts after which run it in SSAS Tabular 2016. However, if you need to import the mannequin to SSAS Tabular 2017 then utilizing SSMS 2017 can be alright.
What’s within the Mannequin
Up to now we efficiently imported the mannequin from SSAS Tabular server. Within the subsequent steps we resolve all the problems, redeploy the mannequin to the server and course of the database. However, let’s see what we’ve got within the mannequin that was imported from Energy BI Desktop. You bear in mind we created some calculated measures and a calculated column in “Reality Gross sales” desk. Let’s see what occurred to these calculated objects after we imported the mannequin to SSAS Tabular server. Opening “Reality Gross sales” desk you’ll instantly discover that every one calculated measures are there. Scrolling to the precise you’ll see the calculated column is imported efficiently as properly. That’s actually cool isn’t it? 
Let’s see what occurred to the Energy Question modifications we made in “Dimension Worker”. Open “Dimension Worker”. We break up the “Worker” column to 2 columns, “First Identify” and “Final Identify”.
Properly, each “First Identify” and “Final Identify” exist within the SSAS Tabular mannequin.
Let’s see what occurs behind the scene in “Dimension Worker” desk. Click on “Desk Properties” from “Desk” menu.
Trying on the SQL assertion you’ll shortly see the issue whatever the error message we get. We haven’t modified the info supply so it’s trivial to get error message in “Desk Properties”.
It’s a “SELECT * FROM” question so we’ll get all columns from the supply desk and the supply desk doesn’t embrace “First Identify” and “Final Identify”. Due to this fact, what ever we’ve completed in Energy Question, are ineffective as we have to redo all of them.
Change to “Diagram” view to see the mannequin diagram.
As you see there are many hidden tables that weren’t within the unique Energy BI Desktop mannequin both. However, the place they got here from? Click on of these tables and have a look at the method.
Properly properly! Now we have a number of date dimensions created to assist automated date hierarchies which introduced at Energy BI Desktop November replace. The rationale we’ve got that a lot hidden date tables is that Energy BI Desktop creates a calculated date desk for every date column we’ve got within the mannequin. Have a look at the mannequin diagram to see every of those date dimensions have a one-to-many relationship with a column of date/time information kind. As an illustration, choose “Dimension Metropolis” desk and take a look each hidden tables linked to it.
Let’s transfer ahead and repair all the problems.
Fixing the Points
- Click on “Present Connections” from “Mannequin” menu
- Choose a connection from the record
- Click on “Edit” and modify the connection to level to you supply database in SQL Server. In my pattern it’s “Extensive World Importers DW”
- Don’t forget to setup “Impersonation”
- Save the connection
- Repeat the identical course of for all different connections
- Choose a desk then open “Desk Properties”
- Click on “Validate”
- You may get an error that signifies there’s something mistaken with the SQL assertion. Evaluation the SQL assertion, repair the difficulty then click on OK
In my pattern the difficulty is the desk title. As you see within the above screenshot the SQL assertion is querying [Dimension City] which doesn’t exist within the “Extensive World Importers DW” database. That is how I initially named the desk in Energy BI Desktop. Changing the question with the right one fixes the difficulty.
It’s worthwhile to repeat the identical course of for all tables. As I discussed earlier than, all modifications you made in your mannequin utilizing Energy Question are ineffective in SSAS Tabular. So right here is the SQL assertion I needed to write to do the identical factor on the “Worker” desk to separate the “Worker” column to “First Identify” and “Final Identify”. This will probably be completely different for various eventualities.
After fixing all points we are able to course of the database.
Now you can deploy the mounted mannequin to SSAS Tabular server.
Though importing Energy BI Desktop to SSAS Tabular 2016 is doable, however, it’s NOT formally supported by any Microsoft BI instruments. So, importing Energy BI Desktop mannequin to SSAS Tabular ought to be thought of instead technique to growing a SSAS Tabular 2016 mannequin from scratch. On the finish of the day and as at all times, it actually depends upon your case. The explanations that this methodology may not fit your case are:
Energy BI Desktop is up to date extra common than SSAS Tabular 2016, So in some level you may face DAX model incompatibility between the Energy BI and SSAS Tabular 2016
As talked about earlier than, Energy Question transformations received’t get imported to SSAS Tabular 2016 mannequin. Due to this fact, you’ll find yourself with growing a brand new SSAS Tabular mannequin from scratch you probably have complicated Energy Question transformations within the mannequin like a number of consumer outlined features, question parameters and so forth
Alternatively, in case your challenge price range is simply too tight otherwise you don’t have sufficient recourses assigned to the challenge and also you don’t have a number of complicated transformations in your Energy BI Desktop mannequin then utilizing this methodology to import the mannequin to SSAS Tabular 2016 will prevent a number of time and price range.
