In my previous blog about SQL Server Master Data Services (MDS) I gave an overview of the MDS data management tool from Microsoft. In this blog, I’m going to give a couple of examples of the types of problems MDS could solve.
The simple case is when a user uploads data via the Excel spreadsheet interface into the MDS database. Sometimes users want to extend existing data with extra attributes that don’t exist in the original data source. In this case, MDS could be a data integration point, i.e. a place where source data is combined with user input.
In the example below an MDS table is populated with records from a source, and extra attributes are populated with the defaults by the MDS business rules. Users then just need to edit those fields if they find defaults are not what they wanted.
Data can be loaded into the MDS model automatically using a set of stored procedures. These could be executed as a script or called from an SSIS package (SQL Server Integration Services). There are three steps to execute.
Step 1 – load the data into MDS entity stage table. Stage table is created automatically when you create a new entity.
-- Clear table from previous data loads TRUNCATE TABLE [MDS_Vault_DEV].[stg].[Agent_Leaf] GO WITH Parties AS (SELECT * FROM [ABC].[dbo].[Parties]) , PartyRole AS (SELECT * FROM [XYZ].[dbo].[Roles] WHERE [Role_Type] = 'Agent' AND [ExpiryDate] IS NULL) INSERT INTO [MDS_Vault_DEV].[stg].[Agent_Leaf] --These are default staging table fields ([ImportType],[ImportStatus_ID],[BatchTag], --These are the actual fields [Code],[Name],[ShortName],[GST],[AgentType]) SELECT --Populate staging maintenance fields with the default values 2, 0, CONVERT(VARCHAR(20),GETDATE(), 112) + CAST(CAST(RAND()*100 AS INT) AS VARCHAR(20)) --All the fields meaningful for business are populated from the data source , PartyRole.PartyID , Parties.Full_Name , Parties.Short_Name , Parties.GST_Number , PartyRole.Role_Description FROM Parties JOIN PartyRole ON Parties.Party_key = PartyRole.Party_key
Step 2 – load the data from the staging table into MDS entity.
--Get the batch tag we have generated on the stage data load step DECLARE @BatchaTag varchar(50) SELECT TOP 1 @BatchTag = [BatchTag] FROM [MDS_Vault_DEV].[stg].[Agent_Leaf] EXECUTE [MDS_Vault_DEV].[stg].[udp_Agent_Leaf] @VersionName = 'VERSION_1' , @LogFlag = 1 , @BatchTag = @BatchTag
Step 3 – model validation. At this step the data is checked for data type constraints and business rules are applied. After the validation, data becomes available to all users.
--Verification requires user ID, model ID and model version ID values provided. They could be found in different database tables DECLARE @UserId int SELECT @UserId = ID FROM [MDS_Vault_DEV].mdm.tblUser WHERE DisplayName = 'Kate Loguteva' DECLARE @ModelID int SELECT @ModelID = ID from [MDS_Vault_DEV].mdm.viw_SYSTEM_SCHEMA_MODEL WHERE Name = 'TestModel' DECLARE @VersionID int SELECT @VersionID = ID FROM [MDS_Vault_DEV].mdm.tblModelVersion WHERE Model_ID = @ModelID EXECUTE [MDS_Vault_DEV].[mdm].[udpValidateModel] @User_ID = @UserId , @Model_ID = @ModelID , @Version_ID = @VersionID , @Status_id = 1 GO
Extra attributes could be populated with the default values by MDS business rules. I found the syntax challenging to pick up, although it is quite logical. The rules enable a wide range of simple data transformations. In this example LongDescription is a concatenation of ShortName and Name with a dash between them:
Business rules are only applied when a record is loaded for the first time. If the user has changed the value of the calculated field, it will not be overwritten by the next batch load from source.
Data from MDS database could be accessed by other processes, like ETL or reporting via views. They are not created automatically, and you can choose what type of view to create.
The simple leaf members view shows only a current state of the data.
The leaf members history view shows only the non-current (historical) values. If the record has never been changed it won’t be shown in this view; it is only helpful for auditing as it shows who made changes and when the change was made.
SCD Type 2 view shows current and historical record states in one place. This concept should be familiar to those of you who work in Data Warehousing. However, MDS could be helpful for any type of system. Therefore, SCD Type 2 functionality is available for the reporting tool website. Fields EnterDateTime and LastChgDateTime could be used to find out what the value was as at certain date.
Once data has been loaded into the MDS, there is no delay in availability via views, making MDS a great tool for near real-time operational reporting. As an example, we have considered using MDS for the report parametrising. One of our operational reports should have take a list of product codes which our organisation receives from another company. These lists are received monthly and they potentially could be used by multiple people and reports, so should be shared somehow. Our reporting tool didn’t cope nicely with thousands of codes in parameters box.
One of the options to solve this problem is uploading spreadsheet via the reporting tool’s interface or via file manager to the dedicated folder. However, this functionality could be limited within organisation due to security reasons. With MDS, our users will upload the data into the pre-defined spreadsheet structure, which excludes any security issues. Report query joins MDS view with other source tables, so there is no issue with the number of parameters or report performance. A list of product codes stays current until user replaces it with the next batch, so for a certain time users could re-run the report or a number of reports for the same list. Once the list is replaced, it is still available for reporting using the historical MDS view.