17 June 2015

Office Manager Budget View

vRecently in a project for an Insurance Company, we had the opportunity to implement an MDS solution to prevent the administration and integration of nearly 80 Excel spreadsheets.

Our MDS solution has 4 models, the General, Accounting, Life and Non-Life models. The general is used for support and mapping purposes and it rarely changes over time, whereas the other models are constantly changing, either by updating or inserting new values.

Since the requirements are a living organism, today we are loading into the Data warehouse the last open versions of the aforementioned models. One of the requirements that still endures from day one is the manageability of the models and versions, and our client isn’t very fond of the UI provided by the MDS system.

In order to ease his pain, we developed a Stored Procedure that by executing the MDS’s SP and sending the name of the model and the new version’s name to our SP it creates a new empty version. This last step is very import in our system, because as you might notice, when you copy a version into a new one, the old values come attached. Next I will try to explain the procedure:

CREATE PROCEDURE mdm.UdpAddEmptyVersion ( @MODEL_NAME VARCHAR(75) , @NEW_VERSION_NAME VARCHAR(100) ) AS BEGIN SET NOCOUNT ON; DECLARE @MODEL_ID INT DECLARE @USER_ID INT DECLARE @ROW_NUMBER INT , @INCR INT DECLARE @VERSION_NAME VARCHAR(50) , @VERSION_ID INT DECLARE @NEW_VERSION_ID INT DECLARE @INSERT_SQL NVARCHAR(MAX) DECLARE @TRUNCATE_LEAF NVARCHAR(MAX) DECLARE @EXECUTE_SYS_SP NVARCHAR(MAX) --Used later in the script in the loop SET @INCR = 0 --Get MODEL_ID from model name from input parameter SELECT @MODEL_ID = ID FROM [mdm].[viw_SYSTEM_SCHEMA_MODEL] WHERE NAME = @MODEL_NAME --Get the USER_ID who executed the SP SELECT @USER_ID = [ID] FROM [MDS].[mdm].[tblUser] WHERE UserName = SUSER_NAME() AND Status_ID = 1 --Get last VERSION_ID and Name to copy the model from SELECT TOP 1 @VERSION_ID = id , @VERSION_NAME = NAME FROM mdm.tblModelVersion WHERE MODEL_ID = @MODEL_ID ORDER BY ID DESC EXECUTE [mdm].[udpVersionCopy] @USER_ID , @VERSION_ID , @NEW_VERSION_NAME , @VERSION_NAME , @Return_ID = @NEW_VERSION_ID OUTPUT --the @NEW_VERSION_ID var gets the version_id to delete old records --For iteration purposes, # of entities in the given model SELECT @ROW_NUMBER = COUNT(*) FROM [mdm].[tblEntity] WHERE MODEL_ID = @MODEL_ID WHILE @INCR < @ROW_NUMBER BEGIN WITH CTE AS ( --Get the entity names presented in the model --New id and model name SELECT ID , NAME , 'mdm.' + EntityTable AS EntityTable , version_id = @NEW_VERSION_ID , @NEW_VERSION_NAME AS version_name , ROW_NUMBER() OVER ( ORDER BY id ASC ) order_row FROM [mdm].[tblEntity] WHERE MODEL_ID = @MODEL_ID ) SELECT @TRUNCATE_LEAF = N'TRUNCATE TABLE [stg].[' + REPLACE(NAME, ' ', '_') + '_Leaf];' , @INSERT_SQL = N'INSERT INTO [stg].[' + REPLACE(NAME, ' ', '_') + '_Leaf] ([ImportType] ,[ImportStatus_ID] ,[BatchTag] ,[Code] ) SELECT 4 AS ImportType ,0 AS ImportStatus_ID ,''Hard Delete''' + ' AS BatchTag ,[Code] FROM ' + EntityTable + ' where [Version_ID] = ' + CAST(version_id AS VARCHAR(3)) + '; ' , @EXECUTE_SYS_SP = N'EXEC [stg].[udp_' + REPLACE(NAME, ' ', '_') + '_Leaf] ''' + version_name + ''',NULL,''Hard Delete'',NULL;' FROM CTE WHERE order_row = @INCR + 1 EXEC sp_executesql @TRUNCATE_LEAF EXEC sp_executesql @INSERT_SQL EXEC sp_executesql @EXECUTE_SYS_SP EXEC sp_executesql @TRUNCATE_LEAF SET @INCR = @INCR + 1 END; --Version Status Codes --Status_IDStatus --3Committed (point of no return) --2Locked --1Open --lock previous version EXEC mdm.udpVersionSave @USER_ID, @MODEL_ID, @Version_ID, null,1(2 old value), null, null, null; SET NOCOUNT OFF;

As I said before a living organism, in the first version of this code, after all the changes and deletes and copies, we should lock the previous version in order to prevent further updates to the entities, value 2 (locked).

As you might notice I am using the staging process to delete the records from Master Data Services that is why I am truncating all staging leaf tables, for each entity in the model and inserting into those table the records I want to be hard deleted. Every entity has its own leaf table and its own stg.udp_Leaf procedure, in this case used for ‘Hard Delete’. You can find out more about this the MDS staging process here: https://msdn.microsoft.com/en-us/library/ff487040.aspx.