Hi,
I'm attempting to find a suitable approach for integrating disparate systems into our domain. The purpose of this would be to have a consistent method to importing our Business data into a central place, to clean/validate it, so that data can be used by downstream processes. I'm the report developer. I am not an I.T. professional but the recommended approach will be implemented by me - hence reaching out on here.
Our core transactional system is Dynamics 365. This data is exported to a Serverless SQL Pool in Azure Synapse where it has SQL Views sitting on top of it. This particular flow, we'd not look to change.
In addition to Dynamics data, we consume financial data in the form of Excel spreadsheets. The finance dept must log onto to the externally held Sage database to download the data into the Excel spreadsheets.
We are now looking to consume data from four (4) new, disparate data sources to merge with our Dynamics and Sage data.
My thoughts are:
--- If I can find a single integration method to bring in all these data sources. This way it will be one skillset to learn, a single technology to maintain, one set of limitations to understand, and most importantly - a single source of truth. I recently found out that our company is using two methods to consume the same Sage data. One is through Excel spreadsheets (me) and the other is through a direct feed from the external Sage database to our Dynamics solution (I.T.). I believe our I.T. dept chose this route as it was the quickest to achieve that single aim.
--- I'm unsure on the technology that should hold the relevant data from the disparate systems. We already use Azure Synapse but through my brief experience with it, I'm reluctant to rely on it as a DataWarehouse. We were instructed to use Synapse as a substitute for DES but replicating the DES items (Azure SQL Server) over to Synapse was troublesome. I'm not ofay with under-the-hood Spark which arose those troubles. I'm more familiar with standard SQL.
--- I've read of Synapse Pipelines (although I've already stated my reluctance for Synapse). I've read of Azure Data Factory Pipelines. What I'm learning towards is Power Platform Dataflows - the Standard variety (not the Analytical type). My aim is simply to connect to these external databases and extract the contents and load that data to a standard database, a staging area. At this point, it could be loaded into its target, whether that be directly into Dynamics (if I.T. want to do that), or into a Warehouse/PBI Dataset, etc...
--- On the note of a DataWarehouse. I do not expect I will attempt to create one right now. Not the merging of data kind. Our Business data isn't the normal star-schema type. Our data is very bespoke and has various modelling desires. I feel if I were to create a DataWarehouse, merging data on different rules, it would neither suit the varying requirements I've had to perform inside of Power Query, and neither would it leave it open enough to give options in querying the data. In other words, any attempt to aggregate data in creating a DataWarehouse will make it unusable downstream. My thinking is to have a database which essentially has only staging tables. Should that data need to be merged with other data based on a specific requirement, then either a traditional SQL DataMart (not the buzzword Power Platform kind) can be created, or the merging can take place in Power Query, inside the PBI file as my work is doing currently.
Anyway, just throwing out there my thoughts. Based on these, does anyone have a suggestion on the correct route to go down? For example, Power Platform Dataflows loading data into a DataVerse? I'm looking for something simple but robust.
If I don't look to perform something like the asked, then we'll end up having this different sources incorporated using various methods. Each may not allow downstream consumption, each may require their own costs, etc... I think what I'm embarking is the correct way forward.
Thanks.
