MDS 2012 – Recreate subscriptionviews

In Master Data Services 2012 (MDS) we have the possibility to create an subscription view of the data in our MDS lists. This gives users, applications access to Master Data (MD) through a view, and therefore we have full control over MD, as the user or application is unable to update the MD in the view.

One of the disadvantages is that whenever there is a change in the Meta Model for the MD which the subscriptionview is based – we have to recreate the subscriptionviews. A rather tedious task for the one that have to do it.

As you can see here it takes a good amount of time to delete and recreate the view.

There are a few ways to do it, one is to use a SSIS package to do it, another is to use a T-SQL script, this post will show the latter, but there is a post on the way for SSIS.

So to help with that I created a SQL Script that will do that for you in a matter of seconds, all views in the specified Model and Version will be deleted and recreated – at the moment the script can only handle the simplest form of views.

All you have to do is to rename the Modelname and VersionName to the one you’re using, and as always this is an unsupported way of using MDS, and you should not do it in your Production Environment, and only if you’re confindent with the MDS datamodel.

DECLARE @ModelName as Varchar(50) = 'FinanceMasterData'
DECLARE @versionName as Varchar(50) = 'MonthlyPostings'
DECLARE @ViewName as Varchar(50) 
DECLARE @ModelID as INT
DECLARE @ModelVersionID as INT
DECLARE @EntityID as INT
DECLARE @Rows INT = 1, @Id INT = 0

----Set Model ID
SELECT @ModelID = ID FROM MDM.tblModel WHERE Name = @ModelName

----Set ModelVersionID
SELECT @ModelVersionID = ID FROM mdm.tblModelVersion 
WHERE name = @versionName

--Delete temp table if already created
IF OBJECT_ID('tempdb..#TempSubscriptionViews') 
IS NOT NULL DROP TABLE #TempSubscriptionViews

--Fill temp table with SubscriptionID, EntityID (MDStableEntityID) 
--and SubscriptionViewNames for supscriptionviews
SELECT S.ID, E.ID as EntityID, E.name Into #TempSubscriptionViews 
FROM mdm.tblSubscriptionView S
INNER JOIN MDM.tblEntity E ON E.ID = S.Entity_ID
WHERE S.Model_ID = @ModelID and S.ModelVersion_ID = @ModelVersionID


--Delete all the Subscriptionviews 
DECLARE @DeleteID INT
DECLARE DeleteCursor CURSOR FAST_FORWARD
FOR

SELECT ID FROM #TempSubscriptionViews
OPEN DeleteCursor

FETCH NEXT FROM DeleteCursor
INTO @DeleteID
WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC mdm.udpSubscriptionViewDeleteByID @DeleteID,1
FETCH NEXT FROM DeleteCursor
INTO @DeleteID

END
CLOSE DeleteCursor
DEALLOCATE DeleteCursor

--Recreate all the Subscriptionviews
DECLARE CreateCursor CURSOR FAST_FORWARD
FOR

SELECT EntityID, Name FROM #TempSubscriptionViews
OPEN CreateCursor

FETCH NEXT FROM CreateCursor
INTO @EntityID, @Viewname
WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC MDM.udpSubscriptionViewSave     NULL,
                                         @EntityID,
                                         @ModelID,
                                         NULL,
                                         @ModelVersionID,
                                         NULL,
                                         1,
                                         NULL,
                                         @Viewname,
                                         NULL,
                                         NULL
FETCH NEXT FROM CreateCursor
INTO @EntityID, @Viewname

END
CLOSE CreateCursor
DEALLOCATE CreateCursor