donderdag 7 juli 2011

Master Data Services – Find your way through the MDS database schema

Introduction

A Master Data Services database consists of a lot of tables where information is stored about models, entities, attributes, hierarchies, business rules, permissions, etc. In this article I will focus on the tables and views that you need when you want to get or update attribute values directly via T-SQL.

Schema

All the existing models, entities, and attributes are stored in the tables “mdm.tblModel”, “mdm.tblEntity”, and “mdm.tblAttribute”. For every entity two separate tables are dynamically created by MDS: the table postfixed by “_EN” contains the entity’s data, the table postfixed by “_MS” contains security information. In this article we only focus on the table postfixed by “_EN”.




Every entity is based on a table, where the name is comprised of the “Model ID” and the “Entity ID”, that can be found in the tables “mdm.tblModel” and “mdm.tblEntity”. An entity’s table consists of several standard columns plus the columns matching the entity’s attributes. The name of those columns is comprised of the “Entity ID” and the “Attribute ID” that can be found in the tables “mdm.tblEntity” and “mdm.tblAttribute”. When an entity contains a domain-based attribute, the matching column contains the unique ID in the referenced entity table and not the unique attribute “Code”.

Every entity has a related view that collects all the information of the above mentioned tables; the name is comprised of the “Model ID” and “Entity ID” that can be found in the tables “mdm.tblModel” and “mdm.tblEntity”.

Get IDs

The following snippet of T-SQL shows how to get the “Model ID”, “Entity ID”, and “Attribute ID”, of the attribute “Item Number” that belongs to the “Items” entity in the “Items” model.

-- Variables
DECLARE @modelID AS INT;
DECLARE @entityID AS INT;
DECLARE @attributeID AS INT;

-- Get model ID
SELECT @modelID = [ID]
FROM   [mdm].[tblModel] AS models
WHERE   models.[Name] = 'Items';

-- Get entity ID
SELECT @entityID = [ID]
FROM   [mdm].[tblEntity] AS entities
WHERE   entities.[Model_ID] = @modelID AND entities.[Name] = 'Items';

-- Get attribute ID
SELECT @attributeID = [ID]
FROM   [mdm].[tblAttribute] AS attributes
WHERE   attributes.[Entity_ID] = @entityID AND attributes.[Name] = 'Item Number';

Get attribute value

If you want to get an attribute’s value, you first have to get the IDs of the model, entity, and attribute, and then dynamically build your T-SQL “SELECT” statement. The following snippet of code shows how to get the value of the “Item Number” attribute of a particular member in the “Items” entity in the “Items” model.

-- Variables
DECLARE @sqlCommand AS NVARCHAR(1024);
DECLARE @parameters NVARCHAR(1024);
DECLARE @attributeValue AS NVARCHAR(1024);

-- Built dynamic T-SQL statement with output parameter
SET @sqlCommand =  'SELECT @attributeValueOutput = parent.[Item Number] ' +
                   'FROM [mdm].[viw_SYSTEM_' + CAST(@modelID AS NVARCHAR(16)) + '_' +
                   CAST(@entityID AS NVARCHAR(16)) + '_CHILDATTRIBUTES] parent ' +
                   'WHERE parent.[Code] = ''1''';                                         
SET @parameters = '@attributeValueOutput NVARCHAR(1024) OUTPUT';

-- Execute T-SQL statement
EXEC sp_executesql @sqlCommand, @parameters, @attributeValueOutput = @attributeValue OUTPUT;

-- Select attribute value
SELECT @attributeValue;

Update attribute value

If you want to update an attribute’s value, you first have to get the IDs of the model, entity, and attribute, and then dynamically build your T-SQL “UPDATE” statement. The following snippet of code shows how to update the value of the “Item Number” attribute of a particular member in the “Items” entity in the “Items” model.

-- Variables
DECLARE @sqlCommand AS NVARCHAR(1024);

-- Built dynamic T-SQL "UPDATE" statement
SET @sqlCommand = 'UPDATE [mdm].[tbl_' + CAST(@modelID AS NVARCHAR(16)) + '_' +
                  CAST(@entityID AS NVARCHAR(16)) + '_EN] ' +
                  'SET [uda_' + CAST(@entityID AS NVARCHAR(16)) + '_' +
                  CAST(@attributeID AS NVARCHAR(16)) + '] = ''My value'' ' +
                  'WHERE [Code] = ''1''';                                          

-- Execute T-SQL statement
EXEC sp_executesql @sqlCommand;


Conclusion

Knowing the schema of the MDS database it’s pretty quick and simple to get and update attribute values and you can get around the permissions set in the Master Data Manager. Because your code could break when a newer version of the MDS database is released, it’s not the preferred way to directly access the database schema; therefore it’s better to use the Master Data Services WCF API as described in the article "Master Data Services: Working with attributes using WCF API".

References

Geen opmerkingen:

Een reactie plaatsen