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 obtainable 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 could be extremely helpful if you happen to may import Energy BI mannequin to SSAS Tabular and it doubtlessly saves a number of improvement time and prices. The excellent news is that with SQL Server 2016 and SQL Server Information Instruments for Visible Studio 2015 it’s doable. On this publish I present you the best way to import Energy BI Desktop mannequin to SSAS Tabular 2016. Sadly, you can not do the job in any prior variations of SQL Server, SQL Server Administration Studio or SSDT.
- SQL Server 2016 Tabular: You’ll be able to obtain SQL Server 2016 Developer Version without cost. Verify this out for extra data
- SQL Server Administration Studio (SSMS) 2016: Down SSMS 2016 from right here
- SQL Server Information Instruments for Visible Studio 2015 (SSDT 2015): You’ll be able to 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
Notice: Don’t shut Energy BI Desktop till we fully import the mannequin to SSAS Tabular.
- You’re aware of all required instruments listed above
- You’re aware of SQL Server Evaluation Providers Tabular fashions and any corresponding ideas, safety settings and so forth
- You’re aware of DAX and Energy Question
Frist of all, I’d moderately creating a brand new pattern Energy BI Desktop mannequin. If you have already got an present 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 “Broad World Importers DW”. You’ll be able to obtain it from right here and restore it in an occasion of SQL Server 2016.
Notice: Realistically, in an actual undertaking, we at all times have a number of DAX components, 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 on… So I add some easy calculated measures, a calculated column utilizing DAX. I additionally break up a column into two columns in Energy Question through the subsequent steps.
Thus far we created a quite simple mannequin. Now we’re going to import it to SSAS Tabular utilizing SSMS 2016.
We efficiently imported the Energy BI Desktop mannequin to SSAS Tabular 2016, however, we’re not performed. Should you course of the database it fails so we have to resolve the problems. As you would possibly guessed the primary issues we have to change are the information sources. In fact you’ll be able to modify the information sources from SSMS 2016, however, bear in mind, we made some adjustments within the “Dimension Worker” desk as nicely. These form of Energy Question adjustments lead the database processing to fail. To resolve these form of points one of the best ways is to import the SSAS Tabular mannequin to SSDT 2015, repair the problems, deploy and course of the database.
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 vary the CompatibilityLevel. However, if you happen to run the scripts in a SSAS Tabular 2016 occasion, then it’s essential to change the CompatibilityLevel to 12 which is supported by SSAS 2016. Should you don’t change the CompatibilityLevel to 12 and also you run the generated scripts in SSAS Tabular 2016 then you definitely’ll get the next error message:
“The JSON DDL request failed with the next error: Didn’t execute a JSON script. Error returned: 1400 will not be a sound worth for this ingredient.”
After altering the CompatibilityLevel to 12 the scripts ought to run efficiently.
Should you’re scripting the database utilizing SSMS 2017 and also you attempt to run it on a SSAS Tabular 2016 occasion, then you definitely would possibly get the next error message:
“The JSON DDL request failed with the next error: Unrecognized JSON property: isPrivate. Verify path ‘mannequin.tables.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, it’s essential to search “isPrivate” within the scripts and comment that line, then run the scripts and hopefully you don’t get some other error messages.
The opposite discovering on producing scripts in SSMS 2017 is that if you happen to allow “Auto Date/Time” below “Time Intelligence” setting in Energy BI Desktop possibility, it robotically creates hidden Date dimension for every area within the mannequin that has Date or Date/Time knowledge sort. So when choose a column with Date or Date/Time knowledge sort, Energy BI robotically 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 robotically. That is made doable utilizing a options often called “Variation”. This function can also be obtainable 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” function. This function is NOT obtainable in SSAS Tabular 2016, so if 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. Verify path ‘mannequin.tables.columns.variations’”
To resolve this downside it’s essential to take away all “Variations” blocks from the scripts which is likely to be a time consuming course of.
Should you run the identical scripts in an occasion of SSAS Tabular 2017 then you definitely don’t get any of the above error messages.
Should you prefer to import your Energy BI Desktop mannequin to SSAS Tabular 2016 then I like to recommend you utilize 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 could be alright.
What’s within the Mannequin
Thus far 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 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 suitable you’ll see the calculated column is imported efficiently as nicely. That’s actually cool isn’t it?
Let’s see what occurred to the Energy Question adjustments 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.
Wanting on the SQL assertion you’ll rapidly see the issue whatever the error message we get. We haven’t modified the information 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 embody “First Identify” and “Final Identify”. Due to this fact, what ever we’ve performed in Energy Question, are ineffective as we have to redo all of them.
Swap to “Diagram” view to see the mannequin diagram.
As you see there are many hidden tables that weren’t within the authentic Energy BI Desktop mannequin both. However, the place they got here from? Click on of these tables and take a look at the components.
Properly nicely! We now have a number of date dimensions created to help computerized date hierarchies which introduced at Energy BI Desktop November replace. The rationale we’ve that a lot hidden date tables is that Energy BI Desktop creates a calculated date desk for every date column we’ve 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 knowledge sort. 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 “Current Connections” from “Mannequin” menu
- Choose a connection from the checklist
- Click on “Edit” and modify the connection to level to you supply database in SQL Server. In my pattern it’s “Broad 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 would possibly get an error that signifies there’s something incorrect 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 “Broad 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 is advisable repeat the identical course of for all tables. As I discussed earlier than, all adjustments 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 might be totally different for various eventualities.
After fixing all points we are able to course of the database.
Now you can deploy the fastened 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 must 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 will depend on 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 would possibly 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 advanced Energy Question transformations within the mannequin like a number of person outlined features, question parameters and so forth
Alternatively, in case your undertaking price range is simply too tight otherwise you wouldn’t have sufficient recourses assigned to the undertaking and also you wouldn’t have a number of advanced 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.