How to Deploy Master Data Services Models Between Environments

March 17, 2015

If you plan to promote a SQL Server MDS (Master Data Services) model from Dev and/or QA to Production, there's a few particulars you need to know to get it right. In this entry I'm focusing on using the "MDSModelDeploy" utility since it offers the most flexibility. Below I will discuss a two-tier MDS environment, Development and Production, just to keep things simple.

Three Options for MDSModelDeploy

DeployClone: Appropriate for the first time you promote to a new environment, such as from Dev to Prod. You can use DeployClone to deploy structure only, or structure + data. Keeps the unique identifier (MUID) consistent between environments, which is necessary if you want to keep models in sync between Dev and Prod.

DeployUpdate: Appropriate for when you want to push an update to a new environment, such as a new column in an entity. This can deploy structure changes only (ex: a new column), or structure + data (ex: refreshing Dev from Prod occasionally). Will only work when the unique identifier (MUID) is the same between source and destination.

DeployNew: Not used that frequently; only appropriate for creating a brand new model using another model as a starting point. Creates a new unique identifier (MUID) for the new model. This is *not* the choice you want to use for promoting to Production from Dev.

Checking the Service Name

Before you do any deployments, you need to know the Service Name(s), especially if you have more than one MDS instance running on the same server. In most cases this is MDS1 if you only have one MDS web app & one MDS database running on the SQL Server instance.

MDSModelDeploy listservices

In my local environment, I use several MDS databases for testing. From "listservices" we see that my MDS_Development is referred to as MDS3, and MDS_Production is MDS4.

Basic Steps for Using MDSModelDeploy

These steps will be standard for each of the examples in each section below. If MDS is installed locally (i.e., if the MDSModelDeploy utility exists on your local development box), you can omit some of the RDP steps. Also, if any shares to the MDS Configuration paths exist, you can simplify and omit RDP steps that way too. I went ahead and listed the most basic way to get it done, knowing you can simplify once you're familiar with where things are located.

1. Remote Desktop into source server where MDS is installed (ex: Dev).

2. Open an elevated command prompt (CMD > Run as Administrator).

3. In the command prompt, change the directory to the path where the MDSModelDeploy utility resides.

CD C:\Program Files\Microsoft SQL Server\120\Master Data Services\Configuration

4. Run MDSModelDeploy to generate a package from the source MDS server. Locate the deployment package on a file share (this saves you from copying and pasting it from one server's configuration folder to the other). See examples below for generating a package.

5. Remote Desktop into target server where MDS is installed (ex: Prod).

6. Open an elevated command prompt (CMD > Run as Administrator).

7. In the command prompt, change the directory to the path where the MDSModelDeploy utility resides.

CD C:\Program Files\Microsoft SQL Server\120\Master Data Services\Configuration

8. Run MDSModelDeploy to deploy the package on the destination MDS server. This will point to the share used in step 4. See examples below for deploying a package.

9. Run validation for the model just deployed.

DECLARE @ModelName NVARCHAR(50) = 'ModelName'
DECLARE @Model_Id INT
DECLARE @UserName NVARCHAR(50) = 'Domain\User'
DECLARE @User_Id INT
DECLARE @Version_ID INT

SET @User_Id = (SELECT ID FROM mdm.tbluser u WHERE u.UserName = @UserName)
SET @Model_Id = (SELECT TOP 1 model_id FROM mdm.viw_system_schema_version WHERE Model_Name = @modelname)
SET @Version_ID = (SELECT MAX(ID) FROM mdm.viw_system_schema_version WHERE Model_ID = @Model_Id)

EXEC mdm.udpValidateModel @user_id, @Model_ID, @Version_ID, 1

10. Reinstate security for users (if a clone was performed) in the Users and Group Permissions area.

11. Double check all subscription view names are correct in the Integration Management area. I've seen the subscription views come through after a DeployClone, but with new prefixes with the model name in front which needs the prefix removed so that ETL processes can continue to use the subscription views.

Example: Initial Deployment of MDS Model

This technique uses DeployClone to ensure that Dev and Prod can be kept in sync with the same unique identifier (MUID) for the model.

Source MDS server (structure only):

MDSModelDeploy createpackage -model ModelName -service MDS1 -package "X:\Path\FileName"

Source MDS server (including data):

MDSModelDeploy createpackage -model ModelName -service MDS1 -version VERSION_1 -package "X:\Path\FileName" -includedata

Target MDS server:

MDSModelDeploy deployclone -service MDS1 -package "X:\Path\FileName.pkg"

Following the DeployClone, you'll also need to validate the model. Run the validation script from SSMS (see script in step 9 above).

You also need to reinstate permissions for users to see the model. This is done from the User and Group Permissions area in Master Data Manager.

I've noticed sometimes it takes a couple of minutes for everything to show up in the Master Data Manager web interface after the deployment.

Note that after this initial DeployClone, you will always do a DeployUpdate from this point forward. If you try to do another DeployClone you'll get an error that says "A clone of the model cannot be created. There is already a model with the same name or MUID." So in this case, do a DeployUpdate instead.

Example: Deploying MDS Model Changes

This technique uses DeployUpdate which requires the same unique identifier (MUID) for each model. This is often used for promoting a structural model change from Dev to Prod some time after an initial DeployClone was done. Or, the "includedata" option can be used for refreshing Dev from Prod occasionally (i.e., assuming data stewards have been keeping the data in Prod up to date).

Source MDS server (structure only):

MDSModelDeploy createpackage -model ModelName -service MDS1 -package "X:\Path\FileName"

Source MDS server (including data):

MDSModelDeploy createpackage -model ModelName -service MDS1 -version VERSION_1 -package "X:\Path\FileName" -includedata

Target MDS server:

MDSModelDeploy deployupdate -service MDS1 -package "X:\Path\FileName.pkg"

Following the DeployUpdate, you'll also need to validate the model. Run the validation script from SSMS (see script in step 9 above).

Note that if you did a DeployNew initially, then try to do a DeployUpdate, you'll get an error "The model cannot be updated. There is no match for a model with the same name and MUID." If this happens, you'll need to do a DeployClone from the best environment, delete the model from the other environments, deploy the clone, the re-implement any changes and retry the DeployUpdate.

Why It's Important to Validate After MDS Deployment

After deployment with data, the members in the model are deployed but not yet validated. This can be seen in the Master Data Manager web interface:

After you run a validation on the model, then the members will show validated in the interface:

Validation like this should also occur after any staging ETL processes are run. Here's the script to run the MDS stored procedure to validate a model. Note that the two variables at the top need a value input.

DECLARE @ModelName NVARCHAR(50) = 'Product'
DECLARE @Model_Id INT
DECLARE @UserName NVARCHAR(50) = 'COATES_HPENVY\Melissa'
DECLARE @User_Id INT
DECLARE @Version_ID INT

SET @User_Id = (SELECT ID FROM mdm.tbluser u WHERE u.UserName = @UserName)
SET @Model_Id = (SELECT TOP 1 model_id FROM mdm.viw_system_schema_version WHERE Model_Name = @modelname)
SET @Version_ID = (SELECT MAX(ID) FROM mdm.viw_system_schema_version WHERE Model_ID = @Model_Id)

EXEC mdm.udpValidateModel @user_id, @Model_ID, @Version_ID, 1

Deploying Only a Subset of a Model

The MDSModelDeploy utility deploys an entire model. What if you really only want to push one entity and be extra cautious not to touch anything else? That's where the MDSPackageEditor.exe comes in handy.

You launch the MDSPackageEditor after you have created a package from the source MDS server. Then you de-select the individual elements you want to exclude, and it saves the package as a different file name. Then you'd continue on with the deployment to the destination server like normal. Using the MDSPackageEditor.exe is much easier than editing the .pkg XML file manually.

Logging MDS Deployments

By default, trace file logging of MDS deployments is not enabled. Instructions for how to enable it can be found here: Enabling Logging to Troubleshoot MDSModelDeploy.exe. (Don't forget to back up the original config file before editing it, just to be safe.)

Note that in SQL Server 2014, Cumulative Update 6 is required in order to use the trace file. Prior to CU6, a trace file doesn't get generated even if it's been enabled in the config file. This is documented here: No MDSTrace.log file is generated after you enable tracing in the MDS 2014 web.config file. Installing CU6 did indeed solve the problem for me.

Brief example of enabling logging in the MDSModelDeploy.exe.config file:

Where to locate the MDSModelDeployTrace.log file:

You Might Also Like...

Importing Data Into Master Data Services

Importing Data Into Master Data Services 2012 – Part 3

February 16, 2013

Welcome to Part 3 of a 3-part series on using the new Entity-Based Staging Process in Master Data Services (MDS) 2012.

Part 1: Overview of the Staging Process

Part 2: Loading a Model Where All Attributes Come From a Source System

Part 3: Loading a Model Where Some Attributes Are Maintained Directly in MDS

Sample Model

This builds upon the model built in Part 2 (so please review that blog entry if you’ve not done so already).

Conceptually the Account Model looks like this:

The Account Entity in MDS looks like this:

In Part 2 we assumed that all of the attribute values come from a source system. However, in Part 3 we are changing that up a little. We are going to say that the Account Type is maintained directly in MDS.

When MDS is the System of Record

One of the most common use cases for MDS is to augment the data which comes from your source system(s) with additional context. This could be groupings or descriptive information not stored elsewhere.

For purposes of Part 3, the Account Type entity is maintained in MDS only. When an attribute is maintained directly in MDS, we need to alter the process described in Part 2 just a bit to ensure the values are preserved during the import process.

In Part 2 we said all the values come from the source. Put another way, all data is pushed to MDS. Conceptually, that looks like this:

However, if one or more values come from MDS, we need to add a step to retrieve those values. Otherwise, we’ll lose them. Two reasons for this:

If we use an ImportType of 2, which allows updates to occur for existing records, then a null value in the staging table will overwrite an existing value.
The stored procedures provided to populate an MDS Model are not parameterized to allow us to specify which attribute(s) are loaded. Since all attributes are part of the import process, we need to make sure all data is present in staging to preserve the existing values.

Think of it this way: We need to retrieve the values out of MDS – as if it were any other source system – to load staging before the stored procedure executes. Conceptually, that looks like this:

SSIS Package

We’re going to use the same SSIS Package from Part 2. (Below only shows the modifications to it, so please refer back to Part 2 for any details not listed here.)

Note the same 5 steps are present. Loading the Account Type Entity is not present in the SSIS package, because it’s maintained directly in MDS.

Step 2 is where the important change occurs. Within the data flow for the Account entity, we need to query MDS to pull back the existing values for Account Type. If we skip this step and leave it null in staging, those null values will indeed overwrite the existing values because we’re using an ImportType of 2.

How you retrieve the data out of MDS depends on your personal preference. In this example I used the Lookup data flow transformation in SSIS. I don’t want to do a cross-database join in my source query, so I matched up the data from MDS after the source data is in my SSIS pipeline.

Prerequisite for this step: A subscription view must exist for the entity to export the data out of MDS. A subscription view is what’s used to extract data out of MDS. Another reminder we’re not to interact directly with the MDS source tables. In my situation, I want a subscription view for the main Account entity – this is where the accounts are actually mapped to the values. The Account Type entity is just a distinct list of lookup values, and doesn’t give me the mappings to actual accounts.

And that’s it! Once you know to identify the source of all MDS attributes before you begin creation of a staging package, then it’s smooth sailing from there on.

Finding More Information

Part 1 of this series (Overview of the Staging Process)

Part 2 of this series (discusses Loading a Model Where All Attributes Come From a Source System)

MSDN – Importing Data (Master Data Services)

MSDN – Exporting Data (Master Data Services)

Importing Data Into Master Data Services 2012 – Part 2

February 16, 2013

Welcome to Part 2 of a 3-part series on using the new Entity-Based Staging Process in Master Data Services (MDS) 2012.

Part 1: Overview of the Staging Process

Part 2: Loading a Model Where All Attributes Come From a Source System

Part 3: Loading a Model Where Some Attributes Are Maintained Directly in MDS

Sample Model

First, a few quick words about the sample model we’re going to use. It’s a simple Account Model which has 3 entities. The Account Type and Account Class entities exist solely to populate the primary Account entity.

Conceptually the Account Model looks like this:

image

The Account Entity in MDS looks like this:

image

The remainder of this blog entry discusses loading the Account model at the leaf (most detailed) level. All attributes, for all entities, come from a source system.

Leaf Member Staging Tables

To support the Account model, 3 staging tables were created by MDS when the entities and attributes were set up:

image

Recall that the Account Class and Account Type are domain-based attributes, used as follows:

Account Type: Contains a distinct list of Account Type code and name – used as the lookup table for the main Account entity.
Account Class: Contains a distinct list of Account Class code and name – used as the lookup table for the main Account entity.
Account: Contains the accounts, plus the mapped values of Account Type and Account Class.

For purposes of this example, we are going to assume all of the attributes in these entities come from a source system external to MDS. (Note: Part 3 of this series looks at how to handle it if one or more attributes are maintained directly in MDS – i.e., the data doesn’t exist in a source system anywhere.)

SSIS Package

Following is an example of the flow for the SQL Server Integration Services (SSIS) package. Note that in a Production ETL environment a few things will be more dynamic, but the purpose of this example is to be simple & straightforward.

image

Step 1: Truncates each of the 3 staging tables. This is to eliminate the data from the last time this process ran.

image

Step 2: Data flow to populate each entity in the model.

image

In your source query, make sure you pull back the code field for the domain-based attributes rather than the name field. For the Account entity, we’ll only populate Account Type and Account Class fields with the code value. However, if we were populating the Account Class or Account Type entity, we would populate both code and name.

The derived columns include:

ImportType of 2 (which means updates can be accepted vs. a failure). There are 6 codes to choose from to control the behavior. The list of ImportType codes can be found here: http://msdn.microsoft.com/en-us/library/ee633854.aspx
ImportStatus_id of 0 (which means each record is ready).
BatchTag which I chose to create from an ETLExecution_id (i.e., a unique code used for ETL job management) plus a string which refers to this entity. The reason the string is appended is so that each BatchTag is unique for every data flow. MDS will generate an error if > 1 of the same BatchTag executes at the same time. Therefore, each of the 3 data flows in my example append a different string so they are allowed to run in parallel for speed.

image

The data conversions are only needed because my source data was non-Unicode, and MDS is Unicode.

image

Mapping of fields into the Leaf Member Staging Table looks like this:

image

The Code can be ignored if you specified for the Code to be created automatically when the entity was set up (a new feature in MDS 2012).

More information about populating the Leaf Member Staging Table can be found here: http://msdn.microsoft.com/en-us/library/ee633854.aspx

image

Step 3: Load the MDS Model. With this step, we’re going to be invoking the MDS-provided stored procedure that takes the data out of staging and moves it into the actual model.

Each staging table has its own stored procedure. The stored procedure name will be in the format of udp_EntityName_Leaf (unless a different name was chosen when the entity was created).

image

Syntax is as follows:

DECLARE @RC int
DECLARE @VersionName nvarchar(50)
DECLARE @LogFlag int
DECLARE @BatchTag nvarchar(50)
SET @VersionName = N'VERSION_1'
SET @LogFlag = 1
SET @BatchTag = (? + ' Account')
EXECUTE @RC = [stg].[udp_Account_Leaf]
@VersionName
,@LogFlag
,@BatchTag
GO

Note that the BatchTag needs to match what was specified in the Step 2 data flow. I’ve set mine to be unique per entity so the batches can be executed in parallel. The ? in the screen shot above signifies I’m using a parameter (which is mapped to a variable which contains the ETLExecution_id); this helps the BI team managing nightly loads know which run this was associated to.

A LogFlag of 1 specifies logging is enabled. The VersionName also needs to be specified – a slight complication if you do a lot of versioning in your environment.

Execution of this step to load the model is what correlates to the Integration Management page in MDS:

image

More information about loading the MDS Model can be found here: http://msdn.microsoft.com/en-us/library/hh231028.aspx

image

Step 4: Validation of the Model. Until validation is performed, the new inserts and updates are there but in a “waiting validation” status. If any Business Rules have been defined for the model, they will be applied when Validation is run.

The Validation applies to the model as a whole, so it only needs to be done once regardless of how many entities we just loaded.

image

Syntax is as follows:

DECLARE @ModelName nVarchar(50) = 'Account Model'
DECLARE @Model_id int
DECLARE @UserName nvarchar(50)=?
DECLARE @User_ID int
DECLARE @Version_ID int
SET @User_ID = (SELECT ID
                 FROMmdm.tblUser u
                 WHERE u.UserName = @UserName)
SET @Model_ID = (SELECT Model_ID
                 FROM mdm.viw_SYSTEM_SCHEMA_VERSION
                 WHERE Model_Name = @ModelName)
SET @Version_ID = (SELECT MAX(ID)
                   FROM mdm.viw_SYSTEM_SCHEMA_VERSION
                   WHERE Model_ID = @Model_ID)
EXECUTE mdm.udpValidateModel @User_ID, @Model_ID, @Version_ID, 1

More information about validating the Model can be found here: http://msdn.microsoft.com/en-us/library/hh231023.aspx

The @UserName variable in the above validation statement is mapped to a parameter which references the UserName of the system variable. As long as all persons or service accounts have access to the model, that should work.

image

The 5th and final step I have in my package is to check if any errors occurred and then alert the BI Team and/or Data Steward if needed. This is done by querying the views provided by MDS for each entity.

In my process, I just do a simple query that checks if 1 or more error records exist and if so, send an email. Note the ResultSet is set to Single row. The total count is passed to a variable. If the value of the variable is >=1, the next step of generating an email will be kicked off.

image

The syntax is as follows:

;WITH CTE_CheckEachTable AS
(
SELECT Count_MDSErrors = COUNT(*)
FROM stg.viw_Account_MemberErrorDetails
UNION ALL
SELECT Count_MDSErrors = COUNT(*)
FROM stg.viw_Account_Class_MemberErrorDetails
UNION ALL
SELECT Count_MDSErrors = COUNT(*)
FROM stg.viw_Account_Type_MemberErrorDetails
)
SELECT Count_MDSErrors = SUM(Count_MDSErrors)
FROM CTE_CheckEachTable

More information on viewing errors can be found here: http://msdn.microsoft.com/en-us/library/ff486990.aspx

Out Of Scope

The above process excludes the following:

Changing the code for an existing record (i.e., usage of the New Code field in the staging table).
Deactivating or deleting an existing record (i.e., via usage of specific ImportType codes).

Finding More Information

Part 1 of this series (Overview of the Staging Process)

Part 3 of this series (discusses Loading a Model Where Some Attributes Are Maintained Directly in MDS)

Importing Data Into Master Data Services 2012 – Part 1

February 16, 2013

Welcome to Part 1 of a 3-part series on using the new Entity-Based Staging Process in Master Data Services (MDS) 2012.

Part 1: Overview of the Staging Process

Part 2: Loading a Model Where All Attributes Come From a Source System

Part 3: Loading a Model Where Some Attributes Are Maintained Directly in MDS

Introduction to the MDS Entity-Based Staging Structure

One of the new features of MDS in SQL Server 2012 is a new process for staging data to be imported into MDS. Key things to be aware of:

Each entity has its own staging table in the stg schema.
Each entity has its own stored procedure (udp) to load data from staging into the model.
Within the stored procedure, you have an ImportType parameter which specifies how new and updated members are treated. New in 2012 is the ability to update existing values, if you so choose.
If more than one batch will be running at the same time, each batch needs a unique BatchTag.
The model needs to be validated after the stored procedure to load the model is executed.
Each entity has its own view to display errors which occurred while loading the model.

The objective of this process is for us to interact with the staging table (in the stg schema), then allow the MDS-generated stored procedure interact directly with the model (in the mdm schema).

An overview of the process is as follows:

image

Advantages of this new structure include:

Ability to handle updates (as well as inserts and deletes), if you choose the Import Type which permits updates.
Easier to understand ETL processing.
Much faster and efficient ETL processing.
Security may be set up per individual staging table, if necessary. Permission to insert data to the staging table(s) is required.
Security may be set up per stored procedure, if necessary. Permission to execute the stored procedure(s) is required.
Members and attributes may be loaded in single batches related to one specific entity.

Tables, Stored Procedures, and Views in the Staging Schema

For each entity, up to 3 staging tables may exist depending if consolidated members exist, and if explicit hierarchies exist. The leaf member table will always exist once the entity has been created. It would be very common to not use all 3 possibilities.

Note: All names are based on the Entity name unless a different name was chosen when the entity was created.