After taken all the data from Dynamics into Synapse Middle-Aged Mutable Ninja Tuples wanted to be able to create views and use all data gathered in Synapse.

As a first step doing this we setup a serverless SQL.

This serverless SQL has no storage and only contain of the possibilities to g.ex create Stored Procedures and Views. This will able us to cross data from many sources and get a more rich way to doing analytics without duplicating data.

First thing first: Creating a External Data source for Serverless.

Then we need to create a fileformat

(This is not the final file format we ended up with – remember to update when working)

Next create all the external table(s) we need

!Note we only get the data we need from the CSV file, there are so many fields not needed in the source so let’s get those out of the way..

This would be the “code” way. But taking the time into consideration.. We used the GUI way:

After creating all the external tables we can now create a view in serverless SQL using normal T-SQL

(Please don’t mind the “dirty case”. We tried to fix this in several ways. But could not find a solution)

Now we can use this View in Power BI to get Clean data from Synapse. We can even cross data from several sources without duplicating data.

Less work for those using Power BI, and more control of the data back-end. A win-win situation.

If this turtle had not been sick, he would dived further on, but as of to day, he has to go back to bed again..

Plans (if not sick)

• Getting data from outside Dynamics
  • Weather info
  • Traffic info
• Taken those data into Power BI.
  • Analyze of SOS alert VS weather and time used to solve SOS alert
  • Analyze of time used in traffic – optimalization of resource scheduling
  • ++

Concerns: Synapse VS Data Export Service and it's deprecation After looking at the synapse and comparing it with the previous Data Export Service. (Deprecated) I look forward to the future. There are an incredible number of new possibilities for synapse versus Data export Service. However, there is one thing that worries me a little. How to translate Option set values to Display names in Synapse. This is a known issue from the past g.ex power BI. The data export service had a separate function that created an optionssetmetadata table where you could join together and get the different display names for each language on option sets values. As it is today, from the synapse you only get access to the option values ​​and no longer have this support table to lean on. Ref: https://powerapps.microsoft.com/en-us/blog/do-more-with-data-from-data-export-service-to-azure-synapse-link-for-dataverse/