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: http://sqlblog.com/blogs/mds_team/archive/2010/02/10/Importing-Data-by-Using-the-Staging-Process.aspx

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;
annotations.Add(a);

// 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 @RC int
DECLARE @UserID int
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
begin
print 'Unknown User: ' + @UserName
return
end

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

/* GET SUCCESSFULLY PROCESSED MEMBERS IN A BATCH */
SET @ErrorCode = 'ERR210007'

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

if @VersionID is null
begin
print 'Unknown Version'
return
end

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
UNION
Select MemberCode, MemberType_ID, EntityName From mdm.tblStgMemberAttribute
Where Batch_ID = @BatchID
AND ErrorCode = @ErrorCode
UNION
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
Begin

    print 'EntityName: ' + convert(nvarchar(255),@EntityName)
   
    /* GET SYSTEM IDs FOR ENTITY, VERSION, USER */
    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
    begin
        print 'Unknown Entity '
        return
    end

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

    if (@MemberID is null) OR (@MemberID =0)
    begin
        print 'Unknown Member Code: ' + @MemberCode
        return
    end

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

    EXECUTE @RC = [mdm].[udpAnnotationSave]
       @UserID
      ,@AnnotationID
      ,@VersionID
      ,@EntityID
      ,@MemberID
      ,@MemberTypeID
      ,@TransactionID
      ,@Comment
 
  FETCH NEXT FROM Member_Cursor into @MemberCode, @MemberTypeID, @EntityName
 
End

An 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!