Cloud-native data modelling, development and deployment with SqlDBM
A practical use-case using SqlDBM for a recent customer challenge
Many of our customers leveraged Data Export Service to replicate data to Azure SQL Database. The service intelligently synchronized the entire data initially and thereafter synchronizes on a continuous basis as changes occur (delta changes) in the system. This allowed to create a continuous copy of data from various sources to an Azure SQL Database, which can then be used for analytics, reporting, or other purposes. Unfortunately for many, Data Export Service was deprecated on Nov-2021. As such, customers were encouraged to export data from Dataverse to Azure Synapse Analytics and/or Azure Data Lake Gen 2 with Azure Synapse Link for Dataverse.
Synapse Link is a feature in Azure Synapse Analytics that enables real-time analytics on operational data stored in various data sources. Dataverse is a Microsoft Power Platform service that allows you to store and manage data entities, such as tables and fields, and use them in various applications.
So, just to recap, stop using DES and start using Synapse Link for Dataverse? Seems to make sense right… This presented several challenges including rethinking and redeveloping data fetching patterns, deciding on various export modes and partitioning. Specifically, one challenge that I came across, was around developing new data models as data sinks in new modern data platform. For seasoned veterans who spent decades exporting and modelling database schemas to create logical and physical data models using DES, which is now being deprecated, proved to be a real problem.
Working closely on this challenge and trying to reverse engineer data model from Microsoft Dataverse, which proved to be somewhat challenging and was really only possible using XrmlToolBox or C# in the Dataverse SDK or XrmToolBox Metadata Document Generator, I had a Eureka moment! What if there was a tool that I could use to showcase an easier way to do reverse-engineering using Synapse Serverless endpoint once the table has been exported, data modelling and forward engineering to Synapse Analytics to Dedicated Pool. I remembered one of my greenfield projects in previous life, and leveraging this PaaS tool called SqlDBM, which was successfully used to accelerate model development and deployment.
So we introduced SqlDBM, Online Data Modelling Tool into the mix to see if we can leverage it’s capabilities to harness an easy, convenient way to not only reverse engineer the output from Datataverse but to develop target data model in Data Vault 2.0 standard, absolutely anywhere on any browser!
SqlDBM is a SaaS based Data Modeling tool that provides collaborative environment for Data Warehouse/Database professionals and enables them to develop DB/DW without writing a single line of code.
SqlDBM supports the following DB/DW Types — Azure Synapse; MS SQL Server, Snowflake, Redshift, MySQL, and PostgreSQL and is available from Azure Marketplace.
The pattern
We started with drafting a high level architectural pattern to prove and with the help of SqlDBM team and proceeded to test out our thinking.
Setting up Synapse Link for Dataverse
For starters, we needed to set up the Synapse Link for the Dataverse to sync a handful of tables.
Accessing exported files in Azure Data Lake
You can then access Azure data lake from Power Platform’s (Go to Azure data lake) or directly from Azure Synapse Workspace as shown below.
Reverse engineering
To reverse engineer a table or set of tables, we have 3x options available to us. Firstly, we can manually generate the table by connecting to a serverless end point via SSMS then pasting the script directly into sqlDBM web UI. As a second option, we can generate a file with several tables in it and load the file in sqlDBM and upload SQL Script to see the modelled tables. Or third option, my favorite, we can Connect to DW and entering our serverless end point account information to Connect to Azure Synapse Analytics, generating and uploading the script that way. Once Reverse Engineering diagram is completed/populated, we can commence using that as a set of base tables to start modelling our Hubs, Sats and Links.
Data Vault sample model
Using sqlDBM’s friendly UI for modeling, we can now copy the tables from the Main Diagram Pane and create new tables quickly in a very intuitive way, define relationships between these tables (Hubs, Sats and Links) easily and copy or move columns across tables in seconds. We are on our way to develop our Data Vault sample model, below.
Forward engineering
Once we were happy with our 5x table model, we forward engineered our ready made script or push to GitHub. For this exercise, we chose to run the script against a Synapse Dedicated Pool, below.
Validation in Synapse Dedicated Pool
From within Synapse Analytics, we verified the tables were created and written a basic view over the top to make sure all the Hash Keys were present, below.
Conclusion
Still some work to be done on populating the data, but as a recap, SqlDBM is easy to use Cloud-native data modelling, development and deployment tool. We successfully reversed engineered tables that are available to us via Dataverse and developed target data model in Data Vault 2.0 standard that we deployed to Azure Synapse Dedicate Pool.
As our next steps, we would go onto populating data that would see us creating a Pipeline from exported Dataverse data to land into a curated/staged datastore for prepping, then populating data vault pattern to load Hubs, Links and Satellites. May be this can be my next installment on Medium.
Until then, does this resonate with you and your data modelling ambitions? I’d be keen to hear your thoughts and reflections — please get in touch!