James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

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

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’;”

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