Publishing SQL Server Master Data Services Data

By: Siddharth Mehta   |   Updated: 2016-05-09   |   Comments   |   Related: > Master Data Services

Problem

Once data has been organized and stored in a SQL Server Master Data Services data model, the data also has to be published, so that the subscribing systems can consume the data as required. Extracting the master data from the complex internal data model is neither an optimal or efficient approach. A complex free interface is required that subscribers can use to access master data along with the required details.

Solution

Subscription views encapsulate the complexity of the SQL Server Master Data Services (MDS) schema and provide easy access to the underlying data. These views can be consumed by batch processes or ETL packages. In this tip we will discuss how to create subscription views in Master Data Services.

Consider an example of a Country Code entity. Almost every business maintains an inventory of the geographic locations where it conducts business along with its demographic details. The most common fields in country related master data would be the name of the country and the country code. For the purpose of this exercise, we will assume that a CountryList entity already exists with a few members already added as shown in the below screenshot.

Assuming that we need to publish this data for subscribers such that they can easily consume the data, we will publish this data. To do so, open the Master Data Manager, navigate to the Integration Management section from the home page, and click on the Create Views menu. This should bring up a screen as shown below. Provide a relevant name for the view that will be published and select the entity details to be published. In this case, I have given vCountryCode as the view name, HumanResources is the model in which the CountryList entity is hosted, and all the leaf members are selected to be published. Click on the Save button to save this view.

Once the view is saved, this will create a database view in the MDS internal database. Open SSMS, navigate to the MDS database and you will see the view created under the mdm schema as shown below. If you look at the script of the view, you can see that the view queries different system tables and returns relevant audit and versioning information about the data.

Query this view and you will see the country related master data. Fields like EnterVersionNumber and LastChgVersionNumber show the version when the member was created and the version when it was last changed.

Validation can have various status like: awaiting validation, awaiting revalidation, validation succeeded, validation failed, etc.

Below the data shows that validation succeeded and this is the first version of the data. Subscribers can now easily access this master data by querying the view.

Next Steps

• Try the options available for publishing entities like different formats, derived hierarchies, etc. and check the published view.
• Consider expanding this exercise and publish a complex entity that is composed of multiple parent level entities.
• Read more Master Data Services tips.

About the author

Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

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

View all my tips