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.
[sql] — 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
–These are the actual fields
–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
JOIN PartyRole ON Parties.Party_key = PartyRole.Party_key
[/sql] Step 2 – load the data from the staging table into MDS entity.
[sql] –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
[/sql] 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.
[sql] –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
[/sql] 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.
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.