How to sync an entity in SQL Server 2016 Master Data Services

By: Koen Verbeeck   |   Updated: 2016-03-31   |   Comments   |   Related: > Master Data Services

Problem

I have multiple models in Master Data Services (MDS). Some of the entities occur in all models and keeping them update in all models takes a lot of work. Is there a better way to do this and reduce maintenance?

Solution

Master Data Services has a new feature in the SQL Server 2016 release: Entity Sync. With this feature, you can easily sync an entity from a model to other models. When you update the entity in the base model, the changes are reflected in the synced entities.

SQL Server 2016 Preview

At the time of writing, SQL Server 2016 is still in preview (currently Release Candidate 0 (RC0) has been released). This means functionality or features of Master Data Services might change, disappear or be added in the final release.

Test Set-up

To test this new feature, we'll use the MDS sample models. When MDS is installed on a server, sample models are copied to the installation folder. You can find them in for example in "C:\Program Files\Microsoft SQL Server\110\Master Data Services\Samples\Packages". This location can vary slightly depending on your SQL Server installation and version. You can follow this guide on how to install them in your MDS environment. In total, three models are available:

• Products
• Customers
• Chart of Accounts

We will sync the country entity from the Products model to the Customers model.

It's important to notice that the Country model already exists on the Customers model. This means you can start using Entity Sync in an existing environment without much troubles.

Syncing an Entity

To configure Entity Sync, go to Administration, then click on Manage and finally choose Entity Sync

Next, click on Add to create a new instance of Entity Sync.

The configuration is quite straight forward. You have to specify the source model and entity, along with the version of that model (if applicable). You also need to specify the target model of course, along with its version. You can choose if you want to create a new entity, or if you want to use an existing one as target. If you choose a new entity, the name can't already be taken by another entity.

Since the Country entity already exists, we'll choose Existing Entity from the dropdown menu.

The final option is the frequency of the entity sync. Either you run the synchronization on demand, or you let MDS schedule it.

The schedule can either be expressed in days or in hours.

This schedule is implemented behind the scenes by a SQL Server Agent job that runs every hour.

The Entity Sync is now configured. However, what happens if the target entity is out of sync with the source entity? For example, I added an extra country in the Customers model, which doesn't exist in the Product model. In such a case, the initial sync will fail and the entity sync won't be created.

Let's delete this rogue country and start again.

Now the initial sync succeeds and the entity sync instance is created. It has been created to be run on demand, as it's easier to test it that way.

Let's test this new feature by adding a new member to the source entity in the Product model.

We can now run the Entity Sync by clicking on Execute. You'll get a message stating the sync was successful or not.

Let's verify if the new member has been added to the Country entity in the Customers model.

Notice that in the screenshot above, all the members of the target entity are locked. You cannot create, change or delete a member in the target entity. You can now only modify the source entity. It's also impossible to modify the attributes of the target entity.

A property is added to the entity overview, stating of the entity is synced or not, as can be noticed in the previous screenshot.

Notice it's not possible to sync an entity if the source entity is the target of another sync. In other words, you cannot "chain" syncing entities.

It's possible though to sync one single entity multiple times. We can easily test this by syncing the Country attribute of the Product model to another entity in the Customer model.

The new entity is created without an issue:

Conclusion

Entity Sync is an easy to use new feature in Master Data Services 2016. It always you to synchronize an entity from one model to one or more target models. If you make a change to this source entity, the changes are reflected in the target entities. The changes are either synced on demand, or by a pre-defined schedule (per hour or per day). In existing models where the entities already contain data, the data must already be in sync before the Entity Sync feature can be enabled.

Next Steps

• Try it out yourself! Install the sample models and start syncing some entities!
• You can find more Master Data Services tips in this overview.
• For more SQL Server 2016 tips, you can use this overview.

About the author

Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips