SQL Server Master Data Services (MDS) Versions

By: Svetlana Golovko   |   Updated: 2016-06-17   |   Comments   |   Related: > Master Data Services

Problem

SQL Server Master Data Services (MDS) provides versioning for the MDS models, but what are they used for and what can we do with the versions?

Solution

You can create multiple versions of a model in SQL Server Master Data Services. As per Microsoft's Book Online you can use the versions to:

• Maintain an auditable record of your master data as it changes over time.
• Prevent users from making changes while you ensure all data validates successfully against business rules.
• Lock down a model for use by subscribing systems.
• Test different hierarchies without implementing them right away.

By default the initial model's version in MDS has "VERSION_1" as the name as shown below:

Change a Master Data Services Version Name

To rename a version go to Version Management in the Master Data Manager Web Application, double-click the version name to activate updates in this cell and type the new version name:

Lock or Unlock a Master Data Services Version

You can lock or unlock opened versions:

You may need to lock a version when you want to prevent users that are not model administrators from making changes to the specific version.

Commit a Master Data Services Version

You can commit a locked and validated version to prevent further changes to the model's members and attributes. Committed versions can not be unlocked.

To commit the version go to Version Management in the Master Data Manager Web Application, click the Validate Version menu, select the version, validate it and commit:

Create a Master Data Services Version Flag or Flag Name

Version flags can be used to indicate the version that users or subscribing systems should use (an alternative to the version name):

To create a flag go to the Version Management in the Master Data Manager Web Application, click the Manage menu, select the Flags option. Select the model you need to create the flag for and click "Add". Assign the name. Select if the flag could be used with committed versions only or with all versions:

Assign a Flag to a Master Data Services Version

A flag can be assigned only to one version at the time.

To assign the flag double click the flag cell under the Versions Management in the Master Data Manager Web Application:

Note, that for the uncommitted version only one flag is available:

Copy a Master Data Services Version

To copy a version go to Version Management in the Master Data Manager Web Application, select the version you want to copy and click "Copy":

If the MDS Configuration option is set to copy committed versions only you won't be able to copy uncommitted versions:

Learn about other System Settings here.

Note, that when we selected the uncommitted version below "Copy" was not available:

Delete a Master Data Services Version

To delete a version you will need to use one of the MDS stored procedures.

First, we will need to find the ID of the version we want to delete:

USE MDS
GO

SELECT ID, [Status], VersionNbr, Name 
FROM mdm.viw_SYSTEM_SCHEMA_VERSION 
WHERE Model_Name = 'DEMO_Model'

Note the ID of the version we want to delete:

Now run the MDS stored procedure to delete the version:

USE MDS
GO
EXEC [mdm].[udpVersionDelete] @Version_ID = 33

Create Custom Named Master Data Services Versions

Another example of the MDS stored procedures usage is below.

By default when you create a copy of the version in the Master Data Manager Web Application the version name will have this pattern: "Copy of NNN" (where "NNN" is the name of the original version being copied). For example, "Copy of Copy of VERSION_1".

This script could be used to create a new version with a programmatically assigned name. You can copy the version based on a version number, version name or version ID.

We create in this example the new version as a copy of version number 2 (committed version) and have a datestamp in the version's name. The script returns the new version's ID.

USE MDS
GO

DECLARE @p_Model_Name NVARCHAR(50)

SET @p_Model_Name = 'DEMO_Model'

DECLARE @pVerGUID UNIQUEIDENTIFIER , 
  @pMasterVerName NVARCHAR(50), 
  @pVerName NVARCHAR(50), 
  @pReturn_ID INT

SELECT @pVerGUID = MUID, @pMasterVerName = Name  
 FROM [mdm].viw_SYSTEM_SCHEMA_VERSION 
WHERE Model_Name = @p_Model_Name 
  AND VersionNbr = 2 -- committed version number
  -- AND Name = 'Copy of VERSION_1' -- committed version name
  -- AND ID = 32 -- committed version ID

SELECT   @pReturn_ID = MAX(ID) + 1 
 FROM [mdm].viw_SYSTEM_SCHEMA_VERSION  

SELECT @pVerName = @pMasterVerName + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(25), GETDATE(), 120) , '-',''), ':', ''), ' ', '_')

EXEC mdm.udpVersionCopyByMUID 
   @Version_MUID = @pVerGUID, 
   @VersionName = @pVerName, 
   @VersionDescription = NULL, 
   @User_ID = 1, 
   @Return_ID = @pReturn_ID output,
   @Return_MUID = @pVerGUID

SELECT @pReturn_ID

SELECT ID, [Status], VersionNbr, Name 
 FROM mdm.viw_SYSTEM_SCHEMA_VERSION 
WHERE Model_Name = 'DEMO_Model'

The new version with the timestamp in the name has been created:

This script can be used as part of an automated process. For example, you may want to create a new version before you load data into the model.

Next Steps

• Read these other Master Data Services Tips
• Read this tip about MDS Versions
• Learn more about the Master Data Manager Application
• Check Microsoft resources about Master Data Services

About the author

Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

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

View all my tips