Microsoft Master Data Services is a great tool for managing master data, and giving business user the control over master data as well as the ability to centrally manage their master data.

Using Business Rules, it becomes possible to act on data within master data, set automated validation and ensure a basic level of data quality and consistency.

In this article, I want to show how to automate the detection of new meta data appearing in source systems that drive your master data.

Scenario

Imagine that you use the integration capabilities of MDS to load new product data appearing in a source system into MDS (1) by populating the staging table of MDS in your ETL flow and calling the MDS stored procedure to load the new products in your Product Entity.

During the normal ETL process (3), the Product dimension is read from MDS (2) with enriched data to load the DW. New products will be presented to MDS and we want a way to notify users of this event and enable them to complete additional configuration for this new product (e.g. custom mappings that need to be defined, which are not available from the source).

Product Entity

For this example, I’ve created a simple Product Entity, which has an additional Product Line attribute.

Name and Code will be loaded from the source system, but Product Line will have to be setup when a new product comes in. All these attributes are send to the DW in the daily ETL flow, which build the Product dimension. To avoid that the new product will fall into the Unknown/Others Product Line and reports are lacking this new products data, a notification should be send to the users to complete the configuration of this product in MDS.

Business Rule

  • Go to System Administration / Manage / Business Rules and select your model and the Product Entity. Member Type is Leaf.
  • Click on Add to create a Business Rule in MDS
  • Specify that the attribute “Product Line” is required, and enable notification for this rule. This will email the target user or group of all the Products that have yet to have their Product Line filled in.

A new rule will appear

  • Click Publish All, to make sure the rule becomes active

TEST 1: Web Explorer

As a basic test, we can manually enter a product, before verifying the automated flow is working. Go to the Explorer and select the Product entity from the Entities menu. Add a new product manually to test the business rule.

The rule ran fine, and we also have an email indicating this product needs to be configured.

The product is recorded, but until the Product Line is configured, it will remain with status “Validation Failed”.

Test 2 : Import data through integration

To simulate the final flow, we’ll:
– insert a record in MDS staging table for the Product Entity
– run the import stored procedure
– verify errors
– validate the model

INSERT INTO [MDS].[stg].[Product_Leaf]
(ImportType, ImportStatus_ID, BatchTag, Code, [Name]) VALUES
(1, 0, 'Notification Test', 'ST12', 'Scuba Tank (12 liter)')

ImportType 1 will only import new members. This depends on your specific flow.
ImportStatus_ID 0 set the status of the import to ‘ready for staging’.
BatchTag is assigned arbitrarily, to designate which records belong to the same batch and reference it in the next step.
More detail on the specific values can be found in the MDS documentation.

EXEC MDS.[stg].[udp_Product_Leaf]
@VersionName = N'VERSION_1',
@LogFlag = 1,
@BatchTag = N'Notification Test'

This procedure will process the specified batch, import in your MDS model and update the status of the records. More info in the MDS documentation.

SELECT *
FROM stg.viw_Department_MemberErrorDetails

We can check for errors with above view, which return nothing this time.
Finally, we will trigger the model validation to run the business rules.

EXECUTE mdm.udpValidateModel
@User_ID = 1,
@Model_ID = 6,
@Version_ID = 6,
@Status_ID = 1

The User_ID can be found in mdm.tblUser
The Model_ID can be found in mdm.viw_SYSTEM_SCHEMA_VERSION
The Version_ID can be found in mdm.viw_SYSTEM_SCHEMA_VERSION
The Status_ID is not clear from the documentation but seems to be 1.
Also now, and email is send to notify of a new Product that is submitted.

Conclusion

We were able to configure our model to notify the data steward of new master data created in the source system, which allows him to act immediately on it.

This article just scratched the surface of using Business Rules in MDS. We were able to implement a real-world scenario which allows to work more proactively, rather then having an outdated DW reporting structure, which is only discovered by accident, weeks after the new product were added.