Thursday, April 17, 2014

Directly Updating Master Data Services Tables

Although Microsoft provides two mechanisms for modifying data withing your 2012 Master Data Services implementation - the web based MDS Manager and the Excel MDS plugin - I've found there are times when you may want to update data though general SQL commands:

  • You may want to update a large subset of data in batch mode
  • You may want to implement business rules, through a stored proc for instance, that runs during an ETL process
  • You may just like to do things your own way

Have you ever looked at the non-system related tables in the MasterDataServices database?  It's hard to tell if your data is in mdm.tbl_3_9_EN or maybe mdm.tbl_1_28_MS.  Or who knows where.  An how about the columns within those table?  Are my ProductNames in uda_10_349 or uda_11_8784?

Here's what you do:

  • Browse to the web based Master Data Services manager and click Integration Management
  • On the Integration tab, click Create Views
  • Create a View
  • Open the database in SQL Server Management Console, drill down to the view you created, and view it's DDL.

Viola!  You you can see where MDS is holding (or hiding) your data.  I've found its perfectly safe to update these MDS tables directly.  You'll loose the built in MDS change management and tracking functionality, and probably violate some basic MDM rules updating fields this way but sometimes you have to spill milk to make a frappe (or something like that).