donderdag 9 maart 2017

SQL Server MDS Staging structure rename or _1_leaf tables discovery

Did it happen to you?


Working on MDS for a new customer, writing staging scripts and not able to see the matching _leaf table and staging stored procedure?


When renaming entities in MDS up-until SQL Server 2016 it was not really supported to rename the staging structure as well. (https://msdn.microsoft.com/en-us/library/ff486987(v=sql.130).aspx)


So you could have an entity "IaMEntity" without the "stg.IamEntity_leaf" staging table.


There are scripts out there that make it possible to rename your staging structure in MDS before getting to SQL Server 2016. Bu why do it when it's not supported? [Edit: okay it's fun to figure it out and better to hand over to new ppl]


MDS is not really a very clear structure so what is happening? Just to make it bit easier I just use below query to discover the correct staging base for an entity.


SELECT
[M].[Name] AS [ModelName]
, [M].[MUID] AS [ModelMUID]
, [E].[Name] AS [EntityName]
, [E].[MUID] AS [EnityMUID]
, [E].[LastChgDTM] AS [EnityLastChgDTM]
, [E].[EntityTable]
, [E].[SecurityTable] AS [EntitySecurityTable]
, [E].[StagingBase] AS [EntityStagingBase]
FROM [mdm].[tblEntity] E
INNER JOIN mdm.tblModel M
ON (E.Model_ID = M.ID
)
ORDER BY
[ModelName], [EntityName]