Found a possible bug in MDS

image.png

UPDATE : Upvote the connect task

UPDATE : Tested on SQL 2016 CTP3.1

Today, working on Master Data Services 2014 (MDS) at a client, I ran into some strange behavior from both the MDS website and the MDS Excel Add-In.

For some reason there is a limit to the length on attributes in an entity, in my opinion there should not be limitations on the attribute length, as it should be up to the users to decide. Continue reading →

SQL Server 2014 CU5 Master Data Service Excel Addin

image This update was released yesterday, and it is a highly anticipated update, as it will introduce performance factor 4 in the Excel Addin.

This performance upgrade will come in very handy when working on larger datasets, and should speed the user experience up. Continue reading →

SQLHangout #27 – Masterdata Management

10464090_597745237028589_7075839414948005691_nBack in the early spring of 2014 I was speaking at SQLSaturday Copenhagen, I gave a speak about Masterdata Management and automation of some of the processes in this area. And while there, I ran in to Boris Hristov ( t | b ) and we had a great talk about SQL server, SQLSaturdays and a generally had a good time. Continue reading →

I’ll be speaking at #SQLSAT275 in Copenhagen

Just recieved an email from Régis Bacarro (Twitter | Blog) who is the organizer of SQLSaturdays in Denmark. He informed me that my session on Master Data Service Automation had been chosen for the SQL Saturday 275 event, 29th of March 2014. Needless to say that i’m thrilled to be given this opportunity, and it’ll be my debut as a SQLSAT speaker, but hey, there’s a first for everything. I look very much forward to catch up with all of the other speakers, and to further develop my SQL network.

 

So I hope to see you at the event, if you haven’t already signed up for a full day packed with SQL info, hurry up and get at seat.

Register Here

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

MDS 2012 – Change Attribute order with SQL

If you need to change the order of attributes in an MDS entity, there is the correct way to do it, and then there is the easy way to do it ;o)

First the correct way:

Locate your Entity i MDS System Administration, select the Entity and Edit the Entity

Now select the attribute that needs another sorting, and use the arrow up/down to select the correct place.

 

 

The much easier method

This method involves a knowledge of the table structure in MDS, and it is advised not to do this in a production environment!

Open SQL Server Management Studio, select you MDS database.

Find your entity with this SQL :

SELECT ENTITY_ID, NAME FROM MDM.VIW_SYSTEM_SCHEMA_ENTITY
WHERE NAME LIKE '%_MONTHLYPOSTINGS'

Now you have the Entity_ID for the entity you need to change attribute order on.

Run this script:

SELECT 'UPDATE MDM.TBLATTRIBUTE 
SET SORTORDER = ' + CAST(A.SORTORDER AS VARCHAR(3)) + ' 
WHERE ENTITY_ID = ' + CAST(A.ENTITY_ID AS VARCHAR(4)) + ' 
AND NAME = ''' + A.NAME + '''' 
FROM MDM.TBLATTRIBUTE A WHERE A.ENTITY_ID = 156

This will generate a list of updates just like this list:

UPDATE MDM.TBLAttribute SET SortOrder = 1 WHERE Entity_ID = 156 and NAME = 'ID'
UPDATE MDM.TBLAttribute SET SortOrder = 2 WHERE Entity_ID = 156 and NAME = 'Version_ID'
UPDATE MDM.TBLAttribute SET SortOrder = 3 WHERE Entity_ID = 156 and NAME = 'Status_ID'
UPDATE MDM.TBLAttribute SET SortOrder = 4 WHERE Entity_ID = 156 and NAME = 'ValidationStatus_ID'
UPDATE MDM.TBLAttribute SET SortOrder = 5 WHERE Entity_ID = 156 and NAME = 'EnterDTM'
UPDATE MDM.TBLAttribute SET SortOrder = 6 WHERE Entity_ID = 156 and NAME = 'EnterUserID'
UPDATE MDM.TBLAttribute SET SortOrder = 7 WHERE Entity_ID = 156 and NAME = 'EnterVersionID'
UPDATE MDM.TBLAttribute SET SortOrder = 8 WHERE Entity_ID = 156 and NAME = 'LastChgDTM'
UPDATE MDM.TBLAttribute SET SortOrder = 9 WHERE Entity_ID = 156 and NAME = 'LastChgUserID'
UPDATE MDM.TBLAttribute SET SortOrder = 10 WHERE Entity_ID = 156 and NAME = 'LastChgVersionID'
UPDATE MDM.TBLAttribute SET SortOrder = 11 WHERE Entity_ID = 156 and NAME = 'LastChgTS'
UPDATE MDM.TBLAttribute SET SortOrder = 12 WHERE Entity_ID = 156 and NAME = 'Name'
UPDATE MDM.TBLAttribute SET SortOrder = 13 WHERE Entity_ID = 156 and NAME = 'Code'
UPDATE MDM.TBLAttribute SET SortOrder = 15 WHERE Entity_ID = 156 and NAME = 'MUID'
UPDATE MDM.TBLAttribute SET SortOrder = 16 WHERE Entity_ID = 156 and NAME = 'AsOf_ID'
UPDATE MDM.TBLAttribute SET SortOrder = 17 WHERE Entity_ID = 156 and NAME = 'ChangeTrackingMask'
UPDATE MDM.TBLAttribute SET SortOrder = 18 WHERE Entity_ID = 156 and NAME = 'Description'
UPDATE MDM.TBLAttribute SET SortOrder = 30 WHERE Entity_ID = 156 and NAME = 'Comment'
UPDATE MDM.TBLAttribute SET SortOrder = 14 WHERE Entity_ID = 156 and NAME = 'Item'
UPDATE MDM.TBLAttribute SET SortOrder = 31 WHERE Entity_ID = 156 and NAME = 'Approved'

Now just adjust the number in SortOrder, and execute the updates.

Remember to update any subscription views on the Entity

This is way easier than using the System Administration website.