Integration options - guidance required, please.

[D_PBI]

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.

[FLMike]

Hello,

It's actually a very solid write up thank you for that it helps a lot.

At the high level my answer would be (if you didn't already have Synapse)

• Data Lake (I know I know its Synapse etc but I am answering if you just started)
• Some other over arching things to think about as you read my answers
  • Volume of data
  • Velocity
  • Size of data
  • Structure of data, or no structure
  • The ability to connect to the 3rd party system via API, Bulk Export, Authentication means, and a slew of other things.
• Would I toss it into Dataverse ? The answer again really depends on ill this data impact your transactional decisions in real-time or is it used to determine analytical bi/ai decisions that the answers get recorded into Dataverse to drive business decisions. 
  • Example: So I need to put all the reports in Dataverse, or do I just need the KPIs etc in a rollup so that I can then do XYZ in plugins/workflows/canvas apps etc.
• I would have to understand better these external systems to decide whether I:
  • use Power Automate to run scheduled flows to extract the data and write it into the DataLake, or Synapse or Dataverse or heck some variation of all 3. Essentially in cases where data might come from Excel or a simple web service call.
    • But you have to think of costs of running these. Simple to create(overall), pretty cheap to run, easier to support (usually) and easier to find resources (vendors etc)
  • ADF is great to do this and would also enable ETL etc but to me, if possible Power Automate can be "easier" to use but it really depends on volume, velocity, data size etc
  • Logic Apps
    • Again some as other things, depends on what you want to learn, schedule, hire people for and support

In the end, unfortunately it really boils down to what the data is, how it will be used and WHERE will it be used to provide the value that you need.

Disparate system integration usually means landing it in DataLake/Storage/Synapse versus directly in Dataverse, using it to decide things, shoving rollups into Dataverse.

But... sorry 😞 id really need to understand as I mentioned the data

* Data

* Integration options (provided by 3rd party)

* Security etc

Sorry I know that didn't answer your question exactly but feel free to write back more details.

I also want to be clear that I am not providing what you should do, just discussing things I would think about to get to my decision if I was doing this again for Microsoft and you were their customer, or you were my customer today.

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[D_PBI]

@Admin - is there any reason to why my response to @FLMike keeps disappearing. It's twice now I've replied and both times its disappeared a minute later?

I'll add my response underneath as this post doesn't seem to disappear.
------------------------------------------------------------------------------------------------------------------------------

@FLMike thank you for your response. It's enlightening. Clearly, there are a lot of considerations.
I'm not experienced on this kind of exercise and no where near the level you have by looking at your profile. I'll attempt to respond to some of your points.

--- Volume of data.
Our data isn't large, not in comparison to the typical data size of companies. I've just been trying to find the accurate size of our Serverless SQL Pool (our Dynamics that we consume for reporting) and I'm struggling to locate it.
We're a small organisation. We have a very small I.T. dept. I'm the only data person in the company - hence this kind of thing falling on me when I'm not equipped to really be implementing it, but I you make best of the situation.
On Synapse, we only chose to use it due to Microsoft informing DES customers that Synapse is the substitute. We barely got over the line with it. However, all works. It was one of those exercises that we implement once and then never revisit. Considering the Spark object differences to the SQL I'm familiar with, it means I'm hesitant to use it.

--- Structure of data, or no structure.
It's all structured.

The more I think about this exercise the more I realise how big the project could be and the awareness of not attempting to bite of more than I can chew.
I'm thinking for an initial aim we would simply like to find a single mechanism to ingest data from different databases that are external to our domain. From there it shall be used in PBI reporting and possibly as direct feeds to Dynamics 365. I believe that will be its entire use and not just the initial use.

Bearing in mind, the Dataverse is native to Dynamics 365 and the Power Platform it hinges on, and also considering it seems relatively simple to use (per the YT videos I've seen) and my organisation is familiar with the Power Platform (its make-up, its security, etc..), it all points me to suggesting the use of Power Platform standard Dataflows to load into the Dataverse.

My below, very poor, attempt at a visual to represent the simplest uses of the data should show the possible scenarios of our data. There maybe the desire to move data from a staging table to Dynamics 365, but mostly it will be to use the data for reporting. Hopefully this narrows down your questions on what our aims are.

We may run into hurdles as we implement it but that's just part of the game.

I'm not looking for anything fancy but just the bare minimal to achieve our aims. If the use of Power Platform standard Dataflows into Dataverse is the subtle approach then I shall focus my attention on understanding that.

Thank you.

[FLMike]

Morning,

Apologies I am literally running out for a family members surgery but will respond when I can after. I am sorry your respond can't getting removed/not saved, that stinks, but thank you for the good response. I will come back to you.

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Contractor Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[D_PBI]

No worries, @FLMike  No rush in responding. If you have a moment at some point to reply that'll be great.
You're doing me a favour so no need to apologise. Hopefully, I've demonstrated enough the possible scenarios our data may follow. As I'm learning towards PP Dataflows and the Dataverse, it'll be nice to know if it will be suitable for our company setup (i.e. we already use the Power Platform and Dynamics 365). If it is then I'll learn how to do it.