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