Welcome back! I am currently revisiting my data integration series where I am highlighting data integration best practices that I have learned since my junior consultant days, these best practices are which I abide by when I am designing and building data migrations/integrations with Dynamics 365.
My previous blog discussed the following best practices:
It took several large Data Migration projects to realise that loading large amount of data from the source data set directly to the target system was not the most practical solution, the size and complexity of the data migration profiles was very inefficient. For data migrations where merging of one or more source data sets was also required, it was clear this should not be completed in CRM and often required numerous clear downs until the logic was correct or post migration clean up activities to remove duplicate data were required. I required a solution that sat in the middle of both the source and target which would have the following properties:
With discussion with colleagues and reading recommendations from the CRM community, it was clear that a staging area was required. It would have never occurred to me to add another component to a data migration, my thought process regarding data migrations/integrations stemmed around moving data from source to target (with no via option) only.
When I first started out with CRM, most of my projects where On-Premise based; customers had numerous on premise servers (mostly VM’s) but also dedicated SQL instances for various applications they hosted (including CRM).
Additionally, the data integration tools I used where also on premise (i.e. Scribe Insight), so where else better than to host the new Staging database in SQL! CRM is built on top of it, it reports from it and so does Scribe; perfect – a Staging Database.
When I talk about staging databases, I refer to a new database which is independent of any data system I am integrating with. This will be either hosted in an On-Premise Microsoft SQL Server instance or Online in Azure SQL.
Tip if using Azure SQL – locate the Staging Database in the same (or as close to) the data-center/region of your Dynamics 365 instance to reduce the distance the data has to travel.
Staging Databases are used as a temporary storage locations between two (or more systems), they allow integration consultants to:
Staging Databases should be considered when there is either a large amount of data (high data volume) or multiple data sources need to be merged together and a fair amount of data merging or data transformation is required. Completing these tasks as part of a direct source-target data integration can impact the performance of the integration (and data systems) and data quality of the integrated data sets massively which can be prone to errors and failure.
Performing all the merging and data transformations in the staging database will increase the performance of the data loading into Dynamics 365 (which we all know can take along time); performance will be discussed later on in this series.
Data comes in all shapes, sizes, formats, localisations; throwing this all directly at Dynamics 365 will require significant conversion (e.g. Date Fields, Currencies, related records, Option Set mapping) which will reduce the efficiency of the data load.
Do not under estimate the importance of good quality and timely data, integrations that are very slow and frequently error will never be well received by the customer.
Data Integrations require time and investment, there is importance when designing a good and solid, quality integration. The same applies when designing and planning for Data migrations, these are often under estimated in every project, but these usually lead to the longest delays (time) that a project will experience.
A couple of years ago I attended a Scribe User Conference in Amsterdam, I was talking with one of the European Scribe MVP’s, Martin Buebl, he was discussing his latest data project (which went very well) but coined the phrase “You will never here anyone talk about a good data migration, only bad ones”. But one of the standout reasons (barring his expert knowledge of data integrations and the tool) is that it went very well was that he used a staging database to merge together a few systems (and by a few I mean a lot)!
Not every scenario requires them, for example:
Simple integrations where only one system with a small amount of data is transferred may not warrant the time and investment spent building such an integration. This also may have a Transactional/near real time requirement, where the addition of a staging component may slow down the integration time.
Think about your integration, consider if the design could benefit with a staging database and discuss it with your clients.
Before you can use the staging database, you first need to define the schema of the Tables and Columns required.
When I design an integration which requires a staging database, I try to re-create the schema of the Dynamics 365 Entities and Fields (and data type) inside of the staging database that I will be mapping to. Whilst this may seem overkill and unnecessary to some, there is method to my madness as it actually makes it easier when building up mapping profiles and I find myself rarely having to reach for data mapping document (and this is a future best practice). There is an added bonus if the third party tool has “Auto Map” where similar or identical column names are mapped (Scribe does!).
I also learned how to create (and drop/recreate) database tables with SQL Scripts instead of manually creating these. Re-using scripts between projects can be especially useful for when using the out of the box entities and fields, these will not change and you just need to add the custom columns and datatypes that are bespoke to that customer.
Another benefit to recreating the Dynamics 365 schema in the staging database makes the data mapping profile simpler, you already know the target location and you will be able to reduce the number of target profiles you have (maybe even a single profile!).
This concludes my best practices advise around Staging Database best practice and why you may wish to use one, the last article in this series will focus on:
Thanks for reading,