Tag Archive for Master Data Services

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.

image

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.

image

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

Securing Large Data Models in Master Data Services 2012

I ran into this issue today when experimenting with the security capabilities in SQL Server 2012 Master Data Services.  I am working with a very large Product data model for a Manufacturing client.  The core entity has 391 attributes at this point in time.  I was attempting to understand how I can secure this entity such that users responsible for mass updating a set of attributes can refine their view only to  those attributes.

In the prior version of MDS (SQL Server 2008 R2), Attribute Groups were the solution to this problem. In 2012, it would seem that Attribute Groups are not secured in the same way. But that is a topic for another time.

When I attempted to set a Deny permission on a single attribute in the model, I received a generic error in the Web Application interface: An Unknown Error has occurred. When I turned on the Trace in web.config (located under C:\Program Files\SQL Server\110\Master Data Services\WebApplication), saw the following error:

MDS Error: 0 : LogError : Operation is not valid due to the current state of the object.
    DateTime=2013-01-18T16:46:19.5107689Z

StackOverflow to the rescue!  This error is apparently a common one in ASP.NET web applications where large collections are being serialized.  By adding an appSettings key to the web.config, I was able to increase the limit of collection keys/members:

<appSettings> <add key=”aspnet:MaxHttpCollectionKeys” value=”2001″ /> </appSettings>

http://stackoverflow.com/questions/8832470/operation-is-not-valid-due-to-the-current-state-of-the-object-error-during-pos

And that solved the problem!

Master Data Services API at Code Camp 5

This weekend was a bit of stretch for me: 2 presentations on MDM and 1 production “Go-Live” with MDS, BizTalk and JD Edwards 9.  Stressful.  But Bob and SB who run the CT Code Camps made the second presentation pretty easy – thanks guys!

Code Camp is a great opportunity to step back and try to package “what I do” into bite-size chunks for my fellow .NET solutions developers. It’s also an opportunity to take some time and learn about stuff I don’t have time to read up on.  Metro UI, Speech Recognition, SQL Azure, SharePoint Online … lot’s of valuable information.

My friend John Flannery at Evolution1 did an excellent job covering SQL Server 2012 enhancements.  Nice job, John!

Another shout out to Brian Zebarth and Matt Rankin, my colleagues from BlumShapiro who handled all the arrangements around our sponsorship of the event.  Thanks a million guys!

My presentation was titled “SQL Server Master Data Services API Fundamentals” – aka MDS for Developers.  I wanted to keep this sharply focused on:

  1. What is Master Data Services?
  2. Why would I use it?
  3. How do I setup a Development Environment?
  4. How do I get data in?
  5. How do I get data out?

And for those who would like to review my slides and code samples:

PowerPoint- http://sdrv.ms/Lwbpnc

Code Samples – http://sdrv.ms/LWXEgP

See you next time!

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.

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.