Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Publishing SQL Server Master Data Services Data


By:   |   Last Updated: 2016-05-09   |   Comments   |   Related Tips: More > 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.

Create Entity in SQL Server Master Data Services

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.

Create View in SQL Server Master Data Services

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.

SQL Server Master Data Serices Database View T-SQL Code

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.

Querying the Master Data Services 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.


Last Updated: 2016-05-09


next webcast button


next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools