Archive for SQL Server Master Data

Master Data Maestro 3.0 Released

If you are working with SQL Server Master Data Services 2012 to develop real-world Master Data Models for your enterprise, then you have likely struggled with the model design environment provided out of the box by Microsoft.  This environment does not support large data models well.

Here are some common scenarios:

1. When adding new attributes to an existing entity, the design environment displays a very short list box containing all existing attributes.  Its very difficult to see the complete list of attributes, their Master Data types, and re-ordering for ease of browsing is accomplished with up down arrows.

2. When organizing attributes into attribute groups, a different interface is used.  Again, its a web interface and again a short list box is the only means of organizing the attribute group.

I am currently working with a Product model which includes over 75 entities and over 1000 attributes; the largest entity contains nearly 400 attributes.  After the initial design session with the data governance team, my team brainstormed how best to create the model in Master Data Services.  Each of us knew that working directly in the web design interface would be extremely painful.

We finally resolved to use SQL Server itself.  We created a database with tables and columns, each  annotated with Extended Properties.  For example, a property of the database recorded the name of the Model which the database represents, each table included an Entity name property to tell us the name of the entity, and each column had several properties to tell us the name of the attribute, any attribute groups to which it belonged, the type of attribute (FreeForm, Domain Based or File), the sort order for the attribute, the entity to which it referred (if it was a Domain-Based one) etc.  Finally, we created an application which read the schema for the database and, using the MDS API, generated the desired model.

These issues are now fully addressed with Advanced Modeling in Master Data Maestro: Profisee announced the GA release of Master Data Maestro 3.0 last week.  The Advanced Modeling tool is built directly into Maestro and shows all attributes for an entity in a grid.


This allows you to see and change data types easily, drag and drop attributes in order to adjust the order in which the attributes are presented.

You also get a rich UI for adding attributes to an Attribute Group and ordering the attributes in that group correctly.


Master Data Maestro is a critical tool for enterprises looking to deliver real-world master data models using Microsoft’s Master Data Services.

Record Bulk Annotations in SQL Server Master Data Services

I had a request from a client last week which I thought was worth sharing. Essentially, the data stewards would like the technology team to set an attribute for over 2000 customer accounts programmatically, as well as recording an annotation on the master data stating the rationale and authorization for the change.

If you have worked with SQL Server Master Data Services, you are aware that Members carry a list of Annotations.  These are typically Notes fields – used by Data Stewards to record anomalies or idiosyncrasies about the data, and record them so that the group can retain that information.  Unlike members, attributes or relationships, they cannot be set in a bulk manner by using the Data Import functionality.  Check out this blog for more details on how the Master Data Services Data Import functionality works:

My first thought was to use the Master Data Services WCF API to record a standard annotation to all Members in a  list of Members.  What I discovered is that annotations are always written one member at a time.  This code snippet demonstrates what a WCF call would look like in this scenario.  Notice that you can write a list of Annotations, but to only one member per call.

// MDS is prefix for Service Proxy

// set the identifiers for the call

MDS.Identifier ModelId = new MDS.Identifier { Name = CORE_MODEL };
MDS.Identifier EntityId = new MDS.Identifier() {  Name = entityName };
MDS.Identifier VersionId = new MDS.Identifier() { Name = CORE_VERSION};
MDS.MemberIdentifier MemberId = new MDS.MemberIdentifier() { Code = code };

// create a list of annotations

List<MDS.Annotation> annotations = new List<MDS.Annotation>();
MDS.Annotation a = new MDS.Annotation();
a.Comment = comment;

// write the annotations

MDS.OperationResult opResult = Proxy.EntityMemberAnnotationsCreate(International, annotations, EntityId, MemberId, ModelId, VersionId);


While possible, this solution was very unattractive because of the expensive nature of a WCF call: over 2000 WCF operations in quick succession seemed a poor solution.

Microsoft recommends that customers avoid calling stored procedures or functions directly in the hub, and generally speaking I agree with this approach. But here I am making an exception.  When I took a look under the hood, I saw that there was a stored procedure named mdm.uspAnnotationSave which seemed to fit the bill.

In the end, I developed this SQL script to apply annotations on the back end.  The idea is that we can make the desired changes using the out-of-the-box Data Import process.  Once  the batch has been processed, a Batch ID has been generated in mdm.tblStgBatch, and that Batch ID is recorded on each staged record.  Using that, we can get a set of members which were updated as a result of the batch, and using a simple cursor, record a standard annotation to each record.

Note that you do need to get the MemberId value in order to call the stored procedure – the code itself will not work.  Why? Because the MemberId is version specific and the code is unique to the version.  The annotation is made a discrete version of the master data.

Here is the SQL script:

DECLARE @BatchID int
DECLARE @ErrorCode nvarchar(15)

DECLARE @ModelName nvarchar(50)
DECLARE @EntityName nvarchar(50)
DECLARE @VersionName nvarchar(255)
DECLARE @MemberCode nvarchar(255)
DECLARE @UserName nvarchar(255)

DECLARE @AnnotationID int
DECLARE @VersionID int
DECLARE @EntityID int
DECLARE @MemberID int
DECLARE @MemberTypeID int
DECLARE @TransactionID int
DECLARE @Comment nvarchar(500)


/* The Batch to use as a source */
SET @BatchID = 9315

/* User Name */
SET @UserName = ‘BLUM\bberry’

/* The Annotation to write */
SET @Comment = ‘Annotation with SQL’


SELECT @UserID = mdm.tblUser.ID
From mdm.tblUser
Where mdm.tblUser.UserName = @UserName

if @UserID is null
print ‘Unknown User: ‘ + @UserName

print ‘UserId: ‘ + convert(nvarchar(255),@UserId)


SET @ErrorCode = ‘ERR210007’

SELECT @VersionID = Version_ID
From mdm.tblStgBatch
Where ID = @BatchID

if @VersionID is null
print ‘Unknown Version’

print ‘VersonId: ‘ + convert(nvarchar(255),@VersionId)


DECLARE Member_Cursor Cursor for (
select MemberCode, MemberType_ID, EntityName from mdm.tblStgMember
Where Batch_ID = @BatchID
AND ErrorCode = @ErrorCode
Select MemberCode, MemberType_ID, EntityName From mdm.tblStgMemberAttribute
Where Batch_ID = @BatchID
AND ErrorCode = @ErrorCode
Select MemberCode, MemberType_ID, EntityName From mdm.tblStgRelationship
Where Batch_ID = @BatchID
AND ErrorCode = @ErrorCode)

OPEN Member_Cursor

/* Member Code to Annotate */
FETCH NEXT FROM Member_Cursor into @MemberCode, @MemberTypeID, @EntityName
while @@FETCH_STATUS = 0

    print ‘EntityName: ‘ + convert(nvarchar(255),@EntityName)
    SELECT @EntityID = mdm.tblEntity.Id
    From mdm.tblEntity
        INNER JOIN mdm.tblModelVersion ON mdm.tblEntity.Model_ID = mdm.tblModelVersion.Model_ID
    Where mdm.tblEntity.Name = @EntityName
        AND mdm.tblModelVersion.ID = @VersionID

    if @EntityId is null
        print ‘Unknown Entity ‘

    print ‘EntityId: ‘ + convert(nvarchar(255),@EntityId)
    print ‘Member Type: ‘ + convert(nvarchar(255),@MemberTypeID)
    print ‘Member Code: ‘ + convert(nvarchar(255),@MemberCode)
    EXECUTE @RC = [mdm].[udpMemberIDGetByCode]
      ,@MemberID OUTPUT

    if (@MemberID is null) OR (@MemberID =0)
        print ‘Unknown Member Code: ‘ + @MemberCode

    print ‘MemberID: ‘ + convert(nvarchar(255),@MemberID)

    EXECUTE @RC = [mdm].[udpAnnotationSave]
  FETCH NEXT FROM Member_Cursor into @MemberCode, @MemberTypeID, @EntityName

A firm understanding of Data Stewardship Best Practices is becoming a significant element to successful MDM.  Enrichment of Master Data can take many forms: metadata management, annotations, etc.  Make sure your technology team remains responsive to the business as they grapple with data quality issues.  Providing automation services on demand, such as this, can be a big help.

Good luck!

Brian Berry is a Director of Technology Consulting with BlumShapiro, focusing on Microsoft Business Intelligence solutions, with a strong focus on Systems Integration, Master Data Management and PerformancePoint Services. He has been helping companies optimize their investments in Microsoft technology for over 12 years.

Member Level Security in SQL Server Master Data Services

SQL Server Master Data Services provides a rich security model for authorizing discrete access to the Master Data Hub.  I believe strongly that Master Data should be widely visible to the broadest possible audience.  Unless your organization is subject to regulatory compliance which restricts one business unit from viewing customer information in another business unit, I advise that your hand picked Data Stewards be responsible for as much Master Data governance as is prudent, and certainly access to everything.

Having said that, some organizations are “not there yet”.  While they recognize the value of federating their customer master into a single hub, they have appointed some Data Stewards management responsibility for some of the attributes in the customer master, or even some of the customers.  To accomplish this, we need to do more than grant access to the entire data model.

If you want to deliver member level access to a master data entity (i.e. row level security), you will need to do so my securing a Derived Hierarchy which includes the entity to be secured.

As an example, let’s say that you have a customer entity.  The customer members are all accounts from several business units.  Let’s say that you have one group of users who work for one of the business units and are responsible for managing only those business unit accounts.

If you have a domain based attribute on the customer account named “Business Unit” which refers to an entity named “Business Unit”, then you only need to create a Derived Hierarchy named “Customer Accounts By Business Unit” and include the Customer and Business Unit Entities.

Once this is done, you can grant access along any path within this hierarchy.  For a given windows group, you may grant read access to the root of the hierarchy, then override  this with an update permission to a particular business unit.

Brian Berry is a Director of Technology Consulting with BlumShapiro, focusing on Microsoft Business Intelligence solutions, with a strong focus on Systems Integration, Master Data Management and PerformancePoint Services. He has been helping companies optimize their investments in Microsoft technology for over 12 years.

Introduction to SQL Server Master Data Services – Importing Source Data

In my last post, I described the Architecture of SQL Server 2008 R2 Master Data Services, and discussed some Key concepts in the MDM solution. In this post, I’ll describe the requirements for getting source data into the Master Data Services database.

The Staging Tables

There are four staging tables in the MDS database.

· mdm.tblStgBatch – this is an internal table for the Import process and should not be loaded by custom ETL

· mdm.tblStgMember – this is where we load “Member” data: Leaf Members or Consolidated Members. We also load Collections and Derived Hierarchies – essentially, if it has a Code, we load it here.

· mdm.tblStgMemberAttribute – this is where we load Member Attributes. Note that members may be loaded into the database without any attributes – this is OK as far as the Batch Loading process is concerned

· mdm.tblStgRelationship – this is where we load relationships of things to one another. For example, we can include a Member in a Named Collection by adding a row here.

Let’s take a look at some simple SQL I used to load a Customer Member into the sample Customer Model provided by Microsoft.

The following SQL will add a member to the Customer Entity in the Customer Model:


MemberType_ID lets MDS know that this is a Leaf Member, as opposed to a Consolidated Member or Collection.

· 1= Leaf Member

· 2=Consolidated Member

· 3=Collection

Note further that since we are loading a Leaf Member, we are not naming a Hierarchy – this is a required column in tblStgMember for Consolidated Members only, not for Leaf Members or Collections

Having done this, we can see that the Master Data Manager UI is ready to load new data. You can find this information by browsing to the Integration Management section of Master Data Manager.


We’ll wait on loading for the time being, because I want to add an attribute as well (note that this one will result in the member being loaded into a distinct placement within the Geography Derived Hierarchy –more on Derived Hierarchies in a later post)


Now MDS is ready to load your member and attribute as well.


Finally, just to demonstrate the Relationship Staging, we’ll add our prospective customer to a sample collection:


Ok, now we are ready to Load the data. Click the Process button directly beneath Unbatched Staging Records.


Immediately, you’ll see that the Load process is ‘Queued to Run’:


You’ll know it is completed when it has a completed date/time stamp:


In this case the first column has the value 8, which is the Batch Id which will appear in the tblStgBatch table.

Confirming the Load Process in Master Data Manager

We can see that our customer record was loaded. Also, that the Business Rules have notyet been run against them. The Yellow Question Mark indicates that the validity of the data is unknown – you need to run your business rules in order to find out.


Also, if you explore the Derived Hierarchy named “Geography”, you will see that our customer appears beneath the Northeastern US branch. This confirms that the Sales Location attribute was correctly loaded.

Finally, confirm that the member was added to our sample collection. Go to the Explorer section of Master Data Manager and click the collection name on the right.


Data Quality and Business Rules

Since data quality is the problem that MDS is designed to solve, one question we should ask is: how do we ensure that the data we start with is good data?

Business Rulesare designed to govern our Master Data. As part of your solution design, your team should work to define beforehand the rules which should be enforced, and also think about which ones you want to enforce on your initial data load.

Common business rules describe:

1. Required Attributes

2. Inconsistent Values across Attributes

More complex rules might mandate:

1. Automated Changes to Attributes when another Attribute is Updated

2. Notifications of new members or Updated Attributes

3. Approval Workflows around new members – this can be implemented with SharePoint Workflow

The good news is that Business Rules can be authored first and Published later, making it easier to manage the implementation of these rules. With the exception of simple Required Value Business Rules, I recommend that you consider waiting to publish your Business Rules until after you have completed your initial load of source data.


Loading Source Data into the MDS Hub is extremely straightforward and can be done either using SSIS or simple Transact-SQL statements

Brian Berry is a Director of Technology Consulting with BlumShapiro, focusing on Microsoft Business Intelligence solutions, with a strong focus on Systems Integration, Master Data Management and PerformancePoint Services. He has been helping companies optimize their investments in Microsoft technology for over 12 years.