Generating Master Data Services Subscription Views

Creating subscription views through the Master Data Services web UI is easy. If not a little slow at times if the Master Data Services installation is on a busy or slow server.  Generally one does not have that many models and entities for which subscription views need to be created.  But what if you do?

I am working for a client where they are planning a large Master Data Services implementation with possibly hundreds of entities. Reverse engineering the entities from a SQL Server relational engine to Master Data Services can be done using the MDSModelImport utility on Codeplex.  Theoretically this utility could be extended to create subscription views too.  However there is an easier way that could be used in a couple of other scenarios e.g.

• Regenerating subscription views when you have updated entities after the subscription view on the entity has been created
• Some of my colleagues reported that subscription views had ‘disappeared’ when deploying models to a new Master Data Services server. A bug?
• Regenerating subscription views for a different version of the model

So I started doing some digging about in the Master Data Services database and came across a nicely named stored procedure called

[mdm].[udpCreateSubscriptionViews]

The content of this stored procedure is very useful and has a wealth of information on what the parameters are.  I put in the required parameters for a leaf level subscription view on one of my entities.  It created the view, but the view did not show up in the Master Data Services web UI. In addition on querying the view no data was returned, even though it had created all the expected attribute columns. Hmm?

So, using the above stored procedure as a starting point, and trawling through a number of stored procedures I came upon:

• the table [mdm].[tblSubscriptionView]. Of course!
• and the stored procedure [mdm].[udpCreateAllViews]

Using information that I gleaned from above mentioned digging, I inserted the required metadata into the table [mdm].[tblSubscriptionView], and then ran the stored procedure [mdm].[udpCreateAllViews]. Voil?! 

Using the sample ‘Customer’ model provided with Master Data Services, I wanted to create leaf format subscription views for all thirteen entities.  So using the below code to populate the metadata to the [mdm].[tblSubscriptionView] table to generated Leaf format views.  Also be sure to create a subscription view name that is unique.  I like ModelName_EntityName to keep all the views from a model together.  Generating other kinds of subscription views would use the same method, the metadata tags would have to change.  See contents of the stored procedure [mdm].[udpCreateSubscriptionViews].

In SQL Server 2012 or SQL Server 2014

In SQL Server 2016 (a few minor changes)

Then in execute the stored procedure [mdm].[udpCreateAllViews] to update all the views.  This stored procedure will use whatever data is in the table [mdm].[tblSubscriptionView] to create any missing subscription view and to update any that might require updating.  So remember this is a very useful stored procedure in the Master Data Services database.

This is what the data looks like in the [mdm].[tblSubscriptionView] table

The subscription views appear in the Master Data Services web UI

In SQL Server 2014

In SQL Server 2016

And data come back from all the views.

In Summary…

So whether this is for ten views or hundreds the process would be the same and just as quick!

It is worth remembering the stored procedure [mdm].[udpCreateAllViews] as it might be best practice to run this as part of the Master Data Services model deployment process to make sure that all subscription views are up to date.

Also if any have ‘disappeared’ but the relevant metadata exists in the table [mdm].[tblSubscriptionView] then running this stored procedure will regenerate them.

Next on the agenda is to start digging through these views…the possibilities are endless.