Archive for April 19, 2012

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: 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

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.