SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Master Data Services (MDS): Miscellaneous tips

I have come across a number of tips while using MDS.  These are tips not big enough for their own blog post, so I list them all here:

  • To “hide” an attribute in the MDS Excel add-in, set the pixel width to “0″
  • If you make a change outside of the  Master Data Manager Web User Interface “UI” (i.e. Master Data Services Configuration Manager, MDSModelDeploy tool, etc), you should go to the home page of the UI and click “Refresh cached information” on the top right of the screen
  • The Master Data add-in for Excel allows you to modify an attributes type and length, but the MDM web UI does not allow this (those boxes are grayed out).  So just fire up your Excel if you find you need to change an attributes type and length
  • There is not a way in the MDM web UI to delete a version.  You must manually delete it: Delete a Version (Master Data Services)
  • To get data into and out of the system and communicate with other applications, Master Data Services uses staging tables for the incoming data flow and subscription views for the outgoing data flow
  • In MDS, to track history of data changes (called Change Tracking), in the MDS web UI go to Version Management -> Transactions (see Transactions).  It’s not at the level of your typical Type 2 slowly changing dimension, but you can reverse transactions and add comments
  • In a subscription view, each domain-based entity shows as three fields: 1) EntityName_Code – This equals the “Code” field in the referenced domain, 2) EntityName_Name – This equals the “Name” field in the referenced domain, 3) EntityName_ID – Internal system key for the member
  • In the MDS web UI, in Explorer, a delete sets a member to de-activated (a soft delete).  You can reverse this via the Transaction screen (on my project, instead of doing soft deletes, we created a field called  MasterDataStatus that contains “Active” and “Deprecated”).  You can hard delete members through the Entity Based Staging feature in MDS via SSIS…you just need to set the ImportType to 4 or 6 when you prepare the staging table.  This is probably going to be enhanced in the first service pack of SQL 2012 by adding a new stored procedure that will hard-delete old soft-deleted members from a given model so you don’t have to use Entity Based Staging
  • There is no easy way via the MDS web UI to do mass soft deletes to members in a entity – they can only be done one-by-one.  So, you can use the Entity Based Staging feature in MDS via SSIS to do a mass soft delete (by setting the ImportType to 3), or even easier use the Master Data Services add-in to Excel to quickly do a mass soft delete.  A soft delete de-activates a member
  • In the MDS web UI, “Integration Management -> Import Data” shows all the batches In mdm.tblStgBatch with a status_id: 1 (QueuedToRun), 3 (Running), 4 (QueuedToClear), 5 (Cleared), 7 (Completed).  You will see batches with a status of “NotRunning” if there are records in any of the staging tables with a batch_ID of NULL (mdm.tblStgBatch is not used for those). So to remove those batches from this screen, delete those records from the staging table, such as: “delete from stg.FMD_TablArea_Leaf where batch_ID is null and BatchTag = ‘Area’;”
  • MDS Staging was vastly improved in the SQL Server 2012 MDS release and is called Entity Based Staging.  Watch this video intro and refer to the product documentation on the new 2012 MDS staging concepts.  Also become familiar with Leaf Member Staging Table
  • You should routinely clear the completed batches via the MDS web UI: Integration Management -> Clear Batches.  Otherwise, these tables will grow large and batching the stage data will be slow
  • To view staging errors, run SQL such as (see Staging Process Errors for error details): SELECT * FROM [MDS2012].[stg].[FMD_TblFacility_Leaf] stg LEFT OUTER JOIN [MDS2012].[mdm].[tblErrorCodesMapping] ecm on stg.ErrorCode = ecm.Bitmask LEFT OUTER JOIN mdm.tblDBErrors dbe ON dbe.ID = ecm.UniqueErrorCode LEFT OUTER JOIN mdm.tblStgErrorDetail sed ON sed.Batch_ID = stg.Batch_ID AND sed.Code = stg.Code AND ecm.UniqueErrorCode = sed.UniqueErrorCode where ImportStatus_ID = 2 and stg.Batch_ID = 1197
  • The “Create Code values automatically” checkbox that is available when creating an entity automatically generates values for an entity’s Code attribute when you want an integer to be automatically assigned to the Code value each time a new member is created.  In the staging process set the Code field to NULL (or in the Excel add-in leave it blank) to have it automatically generate the next integer value.  This is similar to the IDENTITY column in SQL Server.  Note that you are not prevented from entering other values for codes; rather an initial value is automatically set.. See Automatic Code Creation.  This feature is new to SQL Server 2012.  In SQL Server 2008 you needed to use a business rule to automatically generate values for the Code field: See Auto-Generating a Master Data Member Code
  • MDS has a trace logging feature that is useful for error diagnostics and support requests as well as monitoring of application usage and performance.  More info is at How to enable Trace Logging in SQL Server 2008 R2 Master Data Services (NOTE: make sure the directory where the log file will be has write access on it for everyone)

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...