Background: Master Data Service 定時自動產生追蹤最Current資料的Version。

Step:

1. lock previous version
2. validate previous version
3. copy previous version
4. change the "Current Flag" to the lastest version

4在UI的操作如下。

我們的目標就是，按照以上順序，讓程式來自動化執行，

使用以下程式設定排程，如此一來，就能定時自動產生追蹤最Current資料的Version拉！

EXEC [mdm].[udpValidateModel_Auto] TEST
EXEC [mdm].[udpVersionCopy_Auto] 'TEST'
EXEC [mdm].[udpVersionSave_UpdateVersionFlag_Auto] 'TEST','Current'

-- Auto commit latest version
-- EXEC [mdm].[udpValidateModel_Auto] TEST
CREATE PROCEDURE [mdm].[udpValidateModel_Auto] @ModelName NVARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @v INT, -- versionId
        @Model_ID INT

    SELECT TOP 1 @v = mv.ID,
        @Model_ID = m.[ID]
    -- m.[ID] AS Model_ID
    -- m.[Name] AS Model_Name,
    -- mv.Id AS Version_ID,
    -- mv.Name AS Version_Name
    FROM [GMDS].[mdm].[tblModel] m
    JOIN [GMDS].mdm.tblModelVersion mv ON m.ID = mv.Model_ID
    WHERE m.Name = @ModelName
    ORDER BY mv.Id DESC

    EXEC mdm.udpValidateModel 1,
        @Model_ID,
        @v,
        3
END
GO




-- Auto commit latest version
-- EXEC [mdm].[udpValidateModel_Auto] TEST
CREATE PROCEDURE [mdm].[udpValidateModel_Auto] @ModelName NVARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @v INT, -- versionId
        @Model_ID INT

    SELECT TOP 1 @v = mv.ID,
        @Model_ID = m.[ID]
    -- m.[ID] AS Model_ID
    -- m.[Name] AS Model_Name,
    -- mv.Id AS Version_ID,
    -- mv.Name AS Version_Name
    FROM [GMDS].[mdm].[tblModel] m
    JOIN [GMDS].mdm.tblModelVersion mv ON m.ID = mv.Model_ID
    WHERE m.Name = @ModelName
    ORDER BY mv.Id DESC

    EXEC mdm.udpValidateModel 1,
        @Model_ID,
        @v,
        3
END
GO




-- Auto update latest version to Current Flag
-- EXEC [mdm].[udpVersionSave_UpdateVersionFlag_Auto] 'TEST','Current'
ALTER PROCEDURE [mdm].[udpVersionSave_UpdateVersionFlag_Auto] @ModelName NVARCHAR(255),
@FlagName NVARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @v INT, -- versionId
        @Model_ID INT,
        @AsOfVersion_ID INT,
        @Version_Name NVARCHAR(255),
        @Flag_ID INT

    SELECT TOP 1 @v = mv.ID,
        @Model_ID = m.[ID],
        @Version_Name = mv.Name,
        @Flag_ID = mvf.ID
    -- m.[ID] AS Model_ID
    -- m.[Name] AS Model_Name,
    -- mv.Id AS Version_ID,
    -- mv.Name AS Version_Name
    -- mvf.Name AS FlagName
    FROM [GMDS].[mdm].[tblModel] m
    JOIN [GMDS].mdm.tblModelVersion mv ON m.ID = mv.Model_ID
    LEFT JOIN [GMDS].mdm.tblModelVersionFlag mvf ON m.Id = MVF.Model_ID
    WHERE m.Name = @ModelName AND mvf.Name = @FlagName
    ORDER BY mv.Id DESC

    --Update existing Version  
    DECLARE @Return_ID INT,
        @Return_MUID UNIQUEIDENTIFIER;

    --SET @Return_MUID = NEWID(); SELECT @Return_MUID; --Uncomment to test clone operation  
    EXEC mdm.udpVersionSave 1,
        @Model_ID,
        @v,
        NULL,
        NULL,
        @Version_Name,
        @Version_Name, -- Description
        @Flag_ID,
        0,
        @Return_ID OUTPUT,
        @Return_MUID OUTPUT;

    SELECT @Return_ID,
        @Return_MUID;
END
GO

Reference： https://www.mssqltips.com/sqlservertip/4292/how-to-version-a-sql-server-master-data-services-model/

• ← 投資目標不是賺爛，而是不要吃虧。
• 使用同一個Hierarchy中不同的成員來進行Row的切分 | MDX multiple levels from the same Hierarchyon 1(rows) axis | SSRS →