Handling of NULLs and Blanks in MDS Business Rules

For any given entity in Master Data Services, its only the Code attribute that cannot be left blank when entering data in the front end or via the MDS staging tables. For any of the other attributes, you can then use the MDS business rules to enforce that the attribute must have a value, using the Is Required action, for example.

All this works well in my opinion, but I have found a difference in how blanks and NULLs are handled in the front end Vs in the MDS staging process, which has a knock on impact on the business rules.

Example Model

To explain I’ve created an example model called Supplier, with an entity called Supplier and a test attribute called Attribute1. There is also a simple business rule that will set the value of Attribute1 to the value ‘Name blank’ when the built in Name attribute is left blank:

image

Adding Data via Master Data Manager

My first step is to add a new member to the Supplier entity, which I will just give a code of 1 and intentionally leave the name blank:

image

Clicking the save button runs the single business rule that exists and correctly sets Attribute1 to the value of ‘Name blank’

image

If we determine the entity table for the Supplier entity (which can be done by looking at the [mdm].[viw_SYSTEM_SCHEMA_ENTITY] view) then in my case the entity table is called tbl_24_97_EN. Running a select statement on this table shows the following result:

image

The name has been entered as NULL, which is fine, as we’ve got the behaviour that we want.

Adding Data via the MDS Staging Tables

Now lets look what happens when we do the same operation via the MDS staging tables. First of all, we need to load a supplier member into the mdm.tblStgMember table, which we can carry out with the following SQL:

INSERT INTO mdm.tblStgMember 
( 
                    ModelName,
                    EntityName, 
                    MemberType_ID,
                    MemberName,
                    MemberCode
)
--Insert member with no name and a Code of 2
VALUES
                    ('Supplier',
                    'Supplier',
                    1,
                    NULL,
                    2)

The null has been left in the statement as we’re simulating, for example, not being given a name for the member from our data source. You could then have an MDS business rule set up to handle missing names, perhaps setting them to ‘Unknown Supplier’.

To load from the staging tables into the entity table, we run the staging sweep stored procedure:

DECLARE @ModelName nVarchar(50) = 'Supplier'
DECLARE @Version_ID int

SET @Version_ID = (SELECT MAX(ID)  
                   FROM mdm.viw_SYSTEM_SCHEMA_VERSION  
                   WHERE Model_Name = @ModelName)
                   
--This will cause the members in the staging tables to be loaded into MDS
EXECUTE mdm.udpStagingSweep 1, @Version_ID, 1

To finish up we will also validate the model, which will cause the business rule to fire:

DECLARE @ModelName nVarchar(50) = 'Supplier'
DECLARE @Model_id int 
DECLARE @Version_ID int

SET @Version_ID = (SELECT MAX(ID)  
                   FROM mdm.viw_SYSTEM_SCHEMA_VERSION  
                   WHERE Model_Name = @ModelName)
                   
SET @Model_ID = (SELECT Model_ID 
                 FROM mdm.viw_SYSTEM_SCHEMA_VERSION 
                 WHERE Model_Name = @ModelName) 

--Cause validation to occur
EXECUTE mdm.udpValidateModel 1, @Model_ID, @Version_ID, 1

If we now do a select from the entity table, we will see that the member with the code of 2 doesn’t have a NULL for the name column, but instead has a blank string:

image

If we now go and take a look at the Master Data Manager front end, we will see that the business rule condition hasn’t been met for the the member that we added, as its ‘Attribute1’ attribute is still blank:

image

This has happened because the stored procedure that implements the business rules carries out the ‘Name is equal to Blank’ check that we’re relying on by checking if the name column is NULL.

How this affects you will obviously depend on how you’re using the MDS business rules. It doesn’t affect the other member attributes, only the Name attribute. If you want to handle null Names on entities then a good work around is to use the business rules in the front end, and then to use SSIS to replace null names with ‘Unknown’ or something similar.

Leave a Reply

Your email address will not be published. Required fields are marked *