Master Data Services Versions and Views for SQL Server


By:   |   Updated: 2019-11-26   |   Comments   |   Related: More > 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.

sql server 2016 master data services

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.

master data services add subscription 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
query master data services

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. 

master data services versions

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.

master data services copy version

 

master data services versions

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.

master data services version details

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

master data services details

Review Updated Data

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

query master data services

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.

master data services edit subscription view

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

USE MDS
GO
select * from  mdm.myViewV1
query master data services

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
query master data services view

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


Last Updated: 2019-11-26


get scripts

next tip button



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

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.






download

























get free sql tips

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