SQL Server Master Data Services 2016 – Scriptosaurus Rex

Systemutveckling Business Intelligence master data services #Agil BI Automatisering sql server 2016

Lars Johansson Blank 09.02.2017

Jag lovade i mitt förra inlägg om SQL Server MDS att jag skulle komma med exempel och script för att hanteringen av MDS skulle bli lika agil och deploybar som all annan kod. I dagens avsnitt så visar jag hur vi skapar ett T-SQLscript för en Entitet. Scriptet som skapas är det som vi deployar sedan. Vi har alltså en källa (MDS-UTV) som vi vill deploya till en destination (MDS-TEST/PROD).
I detta exempel så förutsätter jag att MDS-modellen redan är skapad i destinationen.
Då så, dags att kavla upp ärmarna och dyka ner i T-SQL:
-- Kör detta script på (källan) -- Genererar T-SQL för MDS Entitet på en miljö som skall deployas till en annan -- Modellen finns på bägge miljöerna, här deployar vi endast en specifik Entitet med tillhörande SubscriptionView

DECLARE @CurrModel nvarchar(50)
DECLARE @CurrEntity nvarchar(50)
SET @CurrModel = 'MyMDSModel' -- <<<<< MDS Model som Entiteten finns i
SET @CurrEntity = 'MyEntity' -- <<<<< MDS ENtiteten som ska genereras script för

DECLARE @ModelOrgId int
DECLARE @ModelName nvarchar(50)
DECLARE @ModelDescription nvarchar(500)
DECLARE @ModelVersion_ID int
DECLARE @ModelVersion_Name nvarchar(50)
DECLARE @EntityOrgID int
DECLARE @EntityID int
DECLARE @EntityName nvarchar(50)
DECLARE @EntityDescription nvarchar(500)
DECLARE @EntityStagingBase nvarchar(50)
DECLARE @EntityIsCodeGenerationEnabled int
DECLARE @EntityCodeGenerationSeed int
DECLARE @EntityTable SYSNAME
DECLARE @SubscriptionView_ID int
DECLARE @SubscriptionView_Name nvarchar(50)
DECLARE @SubscriptionViewDescription nvarchar(500)
DECLARE @ViewFormat_ID int
DECLARE @Attribute_ID int
DECLARE @Attribute_Name nvarchar(50)
DECLARE @Attribute_Description nvarchar(250)
DECLARE @Attribute_DisplayName nvarchar(50)
DECLARE @Attribute_DisplayWidth int
DECLARE @Attribute_IsSystem int
DECLARE @Attribute_IsCode int
DECLARE @Attribute_IsName int
DECLARE @Attribute_Type_ID tinyint
DECLARE @Attribute_DBAEntity_ID int
DECLARE @Attribute_DBAEntity_Name nvarchar(50)
DECLARE @Attribute_DataType_ID tinyint
DECLARE @Attribute_DataType_Name nvarchar(50)
DECLARE @Attribute_DataType_Information int
DECLARE @Attribute_DataMask_Name nvarchar(50)
DECLARE @Attribute_DataMask_ID tinyint
DECLARE @Return_ID INT
DECLARE @EditMode char(1)
DECLARE @EditModeCreate char(1) = '0'
DECLARE @EditModeUpdate char(1) = '1'
PRINT 'USE MDSPRODDB -- DBNAME for target MDS'
PRINT 'GO'
PRINT ''
PRINT 'DECLARE @Return_ID int'
PRINT 'DECLARE @Return_MUID UNIQUEIDENTIFIER;'
PRINT 'DECLARE @SubscriptionView_Name nvarchar(50)'
PRINT 'DECLARE @SubscriptionViewDescription nvarchar(500)'
PRINT 'DECLARE @ViewFormat_ID int'
PRINT 'DECLARE @ModelVersion_ID int'
PRINT 'DECLARE @ModelId int'
PRINT 'DECLARE @ModelName nvarchar(50)'
PRINT 'DECLARE @ModelDescription nvarchar(500)'
PRINT 'DECLARE @EntityID int'
PRINT 'DECLARE @EntityName nvarchar(50)'
PRINT 'DECLARE @EntityDescription nvarchar(500)'
PRINT 'DECLARE @EntityStagingBase nvarchar(50)'
PRINT 'DECLARE @EntityIsCodeGenerationEnabled int '
PRINT 'DECLARE @EntityCodeGenerationSeed int'
PRINT 'DECLARE @EntityTable SYSNAME'
PRINT 'DECLARE @Attribute_ID int'
PRINT 'DECLARE @Attribute_Name nvarchar(50)'
PRINT 'DECLARE @Attribute_Description nvarchar(250)'
PRINT 'DECLARE @Attribute_DisplayName nvarchar(50)'
PRINT 'DECLARE @Attribute_DisplayWidth int'
PRINT 'DECLARE @Attribute_IsSystem int'
PRINT 'DECLARE @Attribute_IsCode int'
PRINT 'DECLARE @Attribute_IsName int'
PRINT 'DECLARE @Attribute_Type_ID tinyint'
PRINT 'DECLARE @Attribute_DBAEntity_ID int'
PRINT 'DECLARE @Attribute_DBAEntity_Name nvarchar(50)'
PRINT 'DECLARE @Attribute_DataType_ID tinyint'
PRINT 'DECLARE @Attribute_DataType_Name nvarchar(50)'
PRINT 'DECLARE @Attribute_DataType_Information int'
PRINT 'DECLARE @Attribute_DataMask_ID tinyint'
PRINT 'DECLARE @Attribute_DataMask_Name nvarchar(50)'
PRINT 'DECLARE @StagingLeafTable SYSNAME'
-- hardcoded variables
PRINT 'DECLARE @IsHierarchyEnabled bit = 0'
PRINT 'DECLARE @IsCollectionEnabled bit = 0'
PRINT 'DECLARE @DataCompression tinyint=0'
PRINT 'DECLARE @TableName SYSNAME'
PRINT 'DECLARE @StagingTableName SYSNAME='''''
PRINT 'DECLARE @MemberType_ID tinyint=1'
PRINT '--'

-- Model
SELECT
@ModelOrgId = [ID]
,@ModelName = [Name]
,@ModelDescription = [Description]
FROM
[mdm].[viw_SYSTEM_SCHEMA_MODEL]
WHERE
Name = @CurrModel
-- Hämta ModelId på Destinations MDS
PRINT 'SELECT @ModelId = ID FROM [mdm].[viw_SYSTEM_SCHEMA_MODEL] WHERE Name = ''' + @CurrModel + ''''
PRINT ''
-- Entitet
SELECT
@EntityOrgID=[Entity_ID]
,@EntityName=[Name]
,@EntityDescription=[Description]
,@EntityStagingBase=[StagingBase]
--,[StagingLeafTable]
,@EntityIsCodeGenerationEnabled=[IsCodeGenerationEnabled]
,@EntityCodeGenerationSeed=[CodeGenerationSeed]
FROM [mdm].[viw_SYSTEM_SCHEMA_ENTITY]
WHERE
[Model_ID] = @ModelOrgId AND
[Name] = @CurrEntity

IF @EntityOrgID IS NULL
BEGIN
PRINT 'Entitet saknas!!'
GOTO AVSLUTA
END
-- Skapa Create Entity Script
PRINT 'set @EntityName = ''' + @EntityName + ''''
PRINT 'set @EntityDescription = ''' + @EntityDescription + ''''
PRINT 'set @EntityStagingBase = ''' + @EntityStagingBase + ''''
PRINT '-- Create new Entity'
IF @EntityIsCodeGenerationEnabled = 1
BEGIN
-- Skapa Code AutoIncrement
PRINT 'EXEC mdm.udpEntitySave @User_ID = 1, @Model_ID = @ModelID, @EntityName = @EntityName,@Description=@EntityDescription,@StagingBase=@EntityStagingBase,@CodeGenSeed=@EntityCodeGenerationSeed, @EditMode = 0, @Return_ID = @EntityID OUTPUT, @Return_MUID = @Return_MUID OUTPUT;'
END
ELSE
BEGIN
PRINT 'EXEC mdm.udpEntitySave @User_ID = 1, @Model_ID = @ModelID, @EntityName = @EntityName,@Description=@EntityDescription,@StagingBase=@EntityStagingBase, @EditMode = 0, @Return_ID = @EntityID OUTPUT, @Return_MUID = @Return_MUID OUTPUT;'
END
print 'SELECT @EntityTable = [EntityTable], @StagingLeafTable=[StagingLeafTable] FROM [mdm].[viw_SYSTEM_SCHEMA_ENTITY] WHERE [Model_ID] = @ModelId AND Entity_ID = @EntityID'
-- För Varje Attribut
-- Skapa Create Attribute SCript
DECLARE Attribute_CUR CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT
[Attribute_ID]
,[Attribute_Name]
,[Attribute_Description]
,[Attribute_DisplayName]
,[Attribute_DisplayWidth]
,[Attribute_IsSystem] -- 1 om det är en System-kolumn (Code/Name)
,[Attribute_IsCode] -- 1 om det är Code-kolumnen
,[Attribute_IsName] -- 1 om det är Name-kolumnen
,[Attribute_Type_ID] -- 1=Freeform, 2=Domain
,[Attribute_DBAEntity_ID] -- Domain Attribute: EntityName
,[Attribute_DBAEntity_Name] -- Domain Attribute: EntityName
,[Attribute_DataType_ID] -- 1=Text, 2= Number, 3=DateTime
,[Attribute_DataType_Name]
,[Attribute_DataType_Information] -- Längd på Text eller Antal Decimaler på Number
,[Attribute_DataMask_ID]
,[Attribute_DataMask_Name] -- MM/dd/yy , -####
FROM [mdm].[viw_SYSTEM_SCHEMA_ATTRIBUTES]
WHERE
[Model_ID] = @ModelOrgId AND
[Entity_ID] = @EntityOrgID AND
[Attribute_Type_Name] <> 'System'
ORDER BY
[Attribute_SortOrder]
OPEN Attribute_CUR
FETCH NEXT FROM Attribute_CUR INTO @Attribute_ID,@Attribute_Name,@Attribute_Description,@Attribute_DisplayName,@Attribute_DisplayWidth,@Attribute_IsSystem,@Attribute_IsCode,@Attribute_IsName,@Attribute_Type_ID,@Attribute_DBAEntity_ID,@Attribute_DBAEntity_Name,@Attribute_DataType_ID,@Attribute_DataType_Name,@Attribute_DataType_Information,@Attribute_DataMask_ID,@Attribute_DataMask_Name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '-- ' + @Attribute_Name
PRINT 'set @Attribute_Name = ''' + @Attribute_Name + ''''
PRINT 'set @Attribute_Type_ID = ' + cast(@Attribute_Type_ID as nvarchar(20))
PRINT 'set @Attribute_Description = ''' + @Attribute_Description + ''''
PRINT 'set @Attribute_DisplayName = ''' + @Attribute_DisplayName + ''''
PRINT 'set @Attribute_DisplayWidth = ' + cast(@Attribute_DisplayWidth as nvarchar(20))
PRINT 'set @Attribute_DataType_ID = ' + cast(@Attribute_DataType_ID as nvarchar(20))
PRINT 'set @Attribute_DataType_Information = ' + cast( @Attribute_DataType_Information as nvarchar(20))
PRINT 'set @Attribute_DBAEntity_ID = ' + cast(@Attribute_DBAEntity_ID as nvarchar(20))
PRINT 'set @Attribute_DataMask_ID = ' + cast(@Attribute_DataMask_ID as nvarchar(20))
IF @Attribute_IsSystem = 1
SET @EditMode = @EditModeUpdate --1 : Update för Code och Name
else
SET @EditMode = @EditModeCreate -- 0 : Create
IF @Attribute_Type_ID = 1
begin
-- Freeform
PRINT '--Create free form attribute'
--@MemberType_ID = 1 -- Leaf
PRINT 'EXEC mdm.udpAttributeSave @User_ID = 1, @Model_ID = @ModelID,@Entity_ID = @EntityID,@IsHierarchyEnabled=@IsHierarchyEnabled, @IsCollectionEnabled=@IsCollectionEnabled, @DataCompression=@DataCompression,@TableName=@EntityTable OUTPUT,@StagingTableName=@StagingLeafTable,@MemberType_ID =@MemberType_ID, @AttributeName = @Attribute_Name, @AttributeType_ID = @Attribute_Type_ID, @Description = @Attribute_Description, @DisplayName = @Attribute_DisplayName, @DisplayWidth = @Attribute_DisplayWidth, @DataType_ID = @Attribute_DataType_ID, @DataTypeInformation = @Attribute_DataType_Information, @InputMask_ID = @Attribute_DataMask_ID,@EditMode = ' + @EditMode + ', @Return_ID = @Return_ID OUTPUT, @Return_MUID = @Return_MUID OUTPUT;'
end
else IF @Attribute_Type_ID = 2
begin
-- Domainbased
PRINT '--Create Domain based attribute'
--@MemberType_ID = 1 -- Leaf
PRINT 'EXEC mdm.udpAttributeSave @User_ID = 1, @Model_ID = @ModelID,@Entity_ID = @EntityID,@IsHierarchyEnabled=@IsHierarchyEnabled, @IsCollectionEnabled=@IsCollectionEnabled, @DataCompression=@DataCompression,@TableName=@EntityTable OUTPUT,@StagingTableName=@StagingLeafTable,@MemberType_ID =@MemberType_ID, @AttributeName = @Attribute_Name, @AttributeType_ID = @Attribute_Type_ID, @Description = @Attribute_Description, @DisplayName = @Attribute_DisplayName, @DisplayWidth = @Attribute_DisplayWidth,@DomainEntity_ID = @Attribute_DBAEntity_ID, @DataTypeInformation = @Attribute_DataType_Information, @EditMode = ' + @EditMode + ', @Return_ID = @Return_ID OUTPUT, @Return_MUID = @Return_MUID OUTPUT;'
end
else
begin
PRINT '@Attribute_Type_ID Not defined'
end
PRINT ''
FETCH NEXT FROM Attribute_CUR INTO @Attribute_ID,@Attribute_Name,@Attribute_Description,@Attribute_DisplayName,@Attribute_DisplayWidth,@Attribute_IsSystem,@Attribute_IsCode,@Attribute_IsName,@Attribute_Type_ID,@Attribute_DBAEntity_ID,@Attribute_DBAEntity_Name,@Attribute_DataType_ID,@Attribute_DataType_Name,@Attribute_DataType_Information,@Attribute_DataMask_ID,@Attribute_DataMask_Name
END
CLOSE Attribute_CUR
DEALLOCATE Attribute_CUR
PRINT '---'

-- Subscription View
SELECT
@EntityID=S.[Entity_ID]
,@SubscriptionView_ID=S.ID
,@SubscriptionView_Name=ISNULL(S.Name,'')
,@ModelVersion_ID=ISNULL( MV.ID,0)
,@ModelVersion_Name=MV.Name
,@SubscriptionViewDescription=ISNULL(S.[Description],'')
,@ViewFormat_ID=[ViewFormat_ID]
FROM mdm.tblSubscriptionView S
INNER JOIN mdm.tblModel M ON S.Model_ID = M.ID
LEFT OUTER JOIN mdm.tblEntity E ON S.[Entity_ID] = E.ID
LEFT JOIN mdm.tblModelVersion MV ON S.ModelVersion_ID = MV.ID
WHERE
M.ID = @ModelOrgId AND
S.[Entity_ID] = @EntityOrgID
IF @EntityID IS NULL
BEGIN
PRINT '-- SubscriptionView för ' + @EntityName + ' saknas'
GOTO AVSLUTA
END
PRINT '-- Subscription View:' + @SubscriptionView_Name
PRINT 'SET @EntityID = ' + cast(@EntityID as varchar(20))
PRINT 'SET @SubscriptionView_Name = ''' + @SubscriptionView_Name + ''''
PRINT 'SET @SubscriptionViewDescription =''' + @SubscriptionViewDescription + ''''
PRINT 'SELECT @ModelVersion_ID = [ID] FROM [mdm].[tblModelVersion] WHERE [Model_ID] = @ModelID'
PRINT 'SET @ViewFormat_ID = ' + CAST(@ViewFormat_ID as varchar(20))
PRINT 'EXEC mdm.udpSubscriptionViewSave @User_ID=' + CAST(USER_ID() as nvarchar(20)) + ',@SubscriptionView_ID= NULL,@Entity_ID=@EntityID,@Model_ID=@ModelID,@ModelVersion_ID=@ModelVersion_ID,@ViewFormat_ID=@ViewFormat_ID,@Levels=1, @SubscriptionViewName=@SubscriptionView_Name,@Description=@SubscriptionViewDescription,@IncludeSoftDeletedMembers=0'
PRINT ''
AVSLUTA:
PRINT '--'
PRINT '-- Klar med Model:' + @CurrModel + ', Entitet:' + @CurrEntity

Detta script hanterar du sedan som din andra kod och tar med den i deployrutinerna, eller så kör du den direkt på destinationsdatabasen (alltså MDS-databasen).

Finns mycket intressant att gå igenom i koden, hör gärna av dig om du har frågor.
Detta är ett första giveaway av användbara script från Scriptosaurus Rex.

Om författaren

Lars Johansson Blank