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:
