Home Business Intelligence Import Energy BI Desktop Mannequin to SSAS Tabular 2016

Import Energy BI Desktop Mannequin to SSAS Tabular 2016

0
Import Energy BI Desktop Mannequin to SSAS Tabular 2016

[ad_1]

Import Power BI Model to SSAS Tabular

Be aware: This text has been up to date on June 2017 to help newest variations of Energy BI Desktop and SSAS Tabular 2017.

Replace July 2020: Whereas this technique nonetheless works with Energy BI Desktop June 2017 launch and SSAS 2017, it probably doesn’t work with the later variations of Energy BI Desktop (Jul 2020) and SSAS 2019 as there are new DAX capabilities which are out there within the Energy BI Desktop however not in SSAS 2019. So use this technique as an experimental technique AT YOUR OWN RISK!

Have you ever created a strong mannequin in Energy BI Desktop and you’re on the lookout for a strategy to import it to an occasion of SQL Server Evaluation Providers Tabular? Hmm, it might be extremely useful in case you might import Energy BI mannequin to SSAS Tabular and it probably saves plenty of growth time and prices. The excellent news is that with SQL Server 2016 and SQL Server Knowledge Instruments for Visible Studio 2015 it’s attainable. On this put up I present you tips on how to 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 possibly can obtain SQL Server 2016 Developer Version totally free. Test this out for extra info
  • SQL Server Administration Studio (SSMS) 2016: Down SSMS 2016 from right here
  • SQL Server Knowledge Instruments for Visible Studio 2015 (SSDT 2015): You possibly can obtain it right here
  • Energy BI Desktop: Obtain Energy BI Desktop from right here

The concept is to

  1. Connect with Energy BI Desktop mannequin from SSMS 2016
  2. Script the mannequin
  3. Modify the script
  4. Execute the scripts in your on-premises occasion of SSAS Tabular 2016
  5. Open the brand new SSAS Tabular database in SSDT 2016
  6. Modify the mannequin
  7. Redeploy and course of the mannequin

Be aware: Don’t shut Energy BI Desktop till we fully import the mannequin to SSAS Tabular.

I assume

  • 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 known as “Large World Importers DW”. You possibly can obtain it from right here and restore it in an occasion of SQL Server 2016.

Be aware: Realistically, in an actual challenge, we all the time have plenty of DAX system, 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 many others… 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.

To date we created a quite simple mannequin. Now we’re going to import it to SSAS Tabular utilizing SSMS 2016.

Import Power BI Desktop Model to SSAS Tabular

Voila!

We efficiently imported the Energy BI Desktop mannequin to SSAS Tabular 2016, however, we aren’t carried out. For those who 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 info sources. In fact you possibly can modify the info sources from SSMS 2016, however, keep in mind, we made some modifications within the “Dimension Worker” desk as effectively. These kind of Energy Question modifications lead the database processing to fail. To resolve these kind of points one of the simplest ways 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 additional consideration to “CompatibilityLevel” line. That is necessary 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, in case you 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. For those who 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 legitimate worth for this aspect.”

Error returned: 1400 is not a valid value for this element

After altering the CompatibilityLevel to 12 the scripts ought to run efficiently.

image

Replace 2:

For those who’re scripting the database utilizing SSMS 2017 and also you attempt to run it on a SSAS Tabular 2016 occasion, you then  would possibly get the next error message:

The JSON DDL request failed with the next error: Unrecognized JSON property: isPrivate. Test path ‘mannequin.tables[0].isPrivate’,

error: Unrecognized JSON property: 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 every other error messages.

The opposite discovering on producing scripts in SSMS 2017 is that in case you allow “Auto Date/Time” beneath “Time Intelligence” setting in Energy BI Desktop choice, it mechanically creates hidden Date dimension for every discipline within the mannequin that has Date or Date/Time knowledge kind. So when choose a column with Date or Date/Time knowledge kind, Energy BI mechanically 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 mechanically. That is made attainable utilizing a options generally known 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 while 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. Test path ‘mannequin.tables[0].columns[1].variations’

error: Unrecognized JSON property: variations.

To resolve this drawback it’s essential to take away all “Variations” blocks from the scripts which may be a time consuming course of.

For those who run the identical scripts in an occasion of SSAS Tabular 2017 you then don’t get any of the above error messages.

Conclusion:

For those who wish 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 want to import the mannequin to SSAS Tabular 2017 then utilizing SSMS 2017 could be alright.

What’s within the Mannequin

To date 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 keep in mind we created some calculated measures and a calculated column in “Truth Gross sales” desk. Let’s see what occurred to these calculated objects after we imported the mannequin to SSAS Tabular server. Opening “Truth Gross sales” desk you’ll instantly discover that each one calculated measures are there. Scrolling to the appropriate you’ll see the calculated column is imported efficiently as effectively. That’s actually cool isn’t it? Smile

Import SSAS Tabular Model to SSDT

Import SSAS Tabular Model to SSDT

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”.

Split Column

Nicely, each “First Identify” and “Final Identify” exist within the SSAS Tabular mannequin.

Import SSAS Tabular Model to SSDT

Let’s see what occurs behind the scene in “Dimension Worker” desk. Click on “Desk Properties” from “Desk” menu.

SSAS Tabular Table Properties

Trying on the SQL assertion you’ll rapidly 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”.

SSAS Tabular Table 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 carried out in Energy Question, are ineffective as we have to redo all of them.

Swap to “Diagram” view to see the mannequin diagram.

SSAS Tabular Model 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 take a look at the system.

Calculated Table in SSAS Tabular

Nicely effectively! 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 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. Take 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 kind. As an example, choose “Dimension Metropolis” desk and take a look each hidden tables linked to it.

Relationships in SSAS Tabular

Let’s transfer ahead and repair all the problems.

Fixing the Points

  • Click on “Present Connections” from “Mannequin” menu

SSAS Tabular Existing Connections

  • Choose a connection from the listing

SSAS Tabular Existing Connections

  • Click on “Edit” and modify the connection to level to you supply database in SQL Server. In my pattern it’s “Large World Importers DW”
  • Don’t forget to setup “Impersonation”
  • Save the connection

SSAS Tabular Existing Connections

  • Repeat the identical course of for all different connections

SSAS Tabular Existing Connections

  • Choose a desk then open “Desk Properties”

SSAS Tabular Edit Table Properties

  • Click on “Validate”
  • You would possibly get an error that signifies there’s something unsuitable with the SQL assertion. Assessment the SQL assertion, repair the difficulty then click on OK

SSAS Tabular Edit Table Properties

  • In my pattern the difficulty is the desk identify. As you see within the above screenshot the SQL assertion is querying [Dimension City] which doesn’t exist within the “Large 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.

SSAS Tabular Edit Table Properties

That you must 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 can be completely different for various situations.

SSAS Tabular Edit Table Properties

SSAS Tabular Data View

After fixing all points we will course of the database.

SSAS Tabular Process Full

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 ought to be thought of in its place strategy to growing a SSAS Tabular 2016 mannequin from scratch. On the finish of the day and as all the time, it actually will depend on your case. The explanations that this technique won’t 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 gained’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 if in case you have complicated Energy Question transformations within the mannequin like a number of consumer outlined capabilities, question parameters and so forth

However, in case your challenge finances is simply too tight otherwise you wouldn’t have sufficient recourses assigned to the challenge and also you wouldn’t have plenty of complicated transformations in your Energy BI Desktop mannequin then utilizing this technique to import the mannequin to SSAS Tabular 2016 will prevent plenty of time and finances.

[ad_2]

LEAVE A REPLY

Please enter your comment!
Please enter your name here