Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Master Data Services (MDS) Versions


By:   |   Read Comments   |   Related Tips: More > 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:

Default Master Data Services Version


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:

Master Data Services Version Rename


Lock or Unlock a Master Data Services Version

You can lock or unlock opened versions:

Master Data Services Version Lock or Unlock

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:

Master Data Services Version 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):

Master Data Services Version Flag usage

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:

Master Data Services Version Flag


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:

Master Data Services Version flag assignment

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

Master Data Services Version flag for uncommitted version


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":

Master Data Services Version Copy

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

Master Data Services Version Copy configuration


Learn about other System Settings here.

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

Master Data Services Version Copy 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:

View results of Master Data Services Versions


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:

Master Data Services Versions Copy results

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


Last Update:






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

View all my tips
Related Resources





More SQL Server Solutions











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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools