Master Data Services Versions and Views for SQL Server

By: Matteo Lorini   |   Comments (1)   |   Related: > Master Data Services

Problem

Master Data Services (MDS) allows you to setup and control data that is used in several parts of the organization to make sure the data stays consistent throughout your systems.  One of the options for working MDS is to use version control and various views to support different levels of the master data.  In this tip we will look at how views and version control work within Master Data Services.

Solution

For this article, I am using MDS for SQL Server 2016, a basic knowledge of MDS concepts and functionalities is required. It is not the goal of this article to explain how to navigate MDS or how or how to install it. If the reader needs a better understanding of MDS, refer to the links at the bottom of this article.

Create the TEST Entity in SQL Server Master Data Services

My First step consists of the creation of an entity called TEST with two attributes called: LastName and UserName and manually enter 2 records as an example. Please note that MDS assigns VERSION_1 as the default version.

Create View for Export in SQL Server Master Data Services

Let's create a view to export the attributes (LastName and UserName) we have just created. Please note that MDS will assign VERSION_1 to the view.

Query the Data in the View

We can see the output of our newly create View by issuing the following T-SQL statement in SSMS. Please note the value assigned to column VersionName.

USE MDS
GO
select * from mdm.myViewV1

Browse Main MDS Page in SQL Server Master Data Services

Let's browse to the main MDS page to verify that Model TEST Version VERSION_1 is flagged as open. 

Review View Versions in SQL Server Master Data Services

To move to the next version of the View, we need to make a copy of the actual Model and assign a new version to it. In the example below, we can see a new version named "Copy of VERSION_1" was automatically attached to it. Also, note MDS version management shows the presence of both versions. VERSION_1 is flagged as committed while the other version is set as Open.

Add New Attributes in SQL Server Master Data Services

Let's add two more attribute names: PersonalEmail and MobilePhone and assign a few values to the newly created fields.

Please note the Phone and Email values entered to UserName Last1 and Last2 below.

Review Updated Data

Let's execute our SELECT statement one more time and examine what is returned by the query.

Not sure if you are surprised, but the View still shows VERSION_1. The newly created columns PersonalEmail and MobilePhone are present, but no data is shown for UserName Last1 and Last2 and User3 is not present.

Update the View in SQL Server Master Data Services

Let's correct the View myViewV1 to reflect the new version number.

Now, if we execute our SELECT statement, we can see the new values that were previously added.

USE MDS
GO
select * from  mdm.myViewV1

Review View Definition

Finally, let's see the code behind the view myViewV1 to understand how the version influences the behavior of the query.

USE [MDS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [mdm].[myViewV1]
/*WITH ENCRYPTION*/
AS SELECT 
     T.ID AS ID
    ,T.MUID AS MUID 
    ,V.Name AS VersionName
    ,V.Display_ID AS VersionNumber
    ,V.ID AS Version_ID
    ,DV.Name AS VersionFlag
    ,T.[Name] AS [Name]
    ,T.[Code] AS [Code]
    ,T.[ChangeTrackingMask] AS [ChangeTrackingMask]
    ,T.[uda_58_1828] AS [LastName]
    ,T.[uda_58_1829] AS [UserName]
    ,T.[uda_58_1830] AS [PersonalEmail]
    ,T.[uda_58_1831] AS [MobilePhone]
    ,T.EnterDTM AS EnterDateTime
    ,UE.UserName AS EnterUserName
    ,(SELECT Display_ID FROM mdm.tblModelVersion WHERE ID = T.EnterVersionID) AS EnterVersionNumber
    ,T.LastChgDTM AS LastChgDateTime
    ,UC.UserName AS LastChgUserName
    ,(SELECT Display_ID FROM mdm.tblModelVersion WHERE ID = T.LastChgVersionID) AS LastChgVersionNumber
    ,LV.ListOption AS ValidationStatus
FROM mdm.[tbl_12_58_EN] AS T
INNER JOIN mdm.tblModelVersion AS V ON V.ID = T.Version_ID 
LEFT JOIN mdm.tblUser UE ON T.EnterUserID = UE.ID
LEFT JOIN mdm.tblUser UC ON T.LastChgUserID = UC.ID 
LEFT JOIN mdm.tblList LV ON LV.OptionID = T.ValidationStatus_ID AND LV.ListCode = 'lstValidationStatus' 
LEFT JOIN mdm.tblModelVersionFlag AS DV ON DV.ID =  V.VersionFlag_ID
WHERE V.ID in( 12, 13) -- MANUALLY ADDED V.ID in( 12, 13) TO SHOW ALL THE RESULTS
    AND T.Status_ID = 1;
GO

Conclusion

View version helps the development of new code on live data without "breaking" existing processes, moreover it allows for a fast rollback of code.

Next Steps

• Check out these additional resources:
  • What’s New in MDS 2016 Part 1
  • What’s New in MDS 2016 Part 2

About the author

Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

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

View all my tips