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)
- You can display pages from the Master Data Manager Web User Interface within SharePoint and crop the headings and menus so that it’s displayed more naturally. See Display the Master Data Services Web Application in SharePoint
- Master Data Services requires SQL Server’s Service Broker to be running to process batches. If on the Import page, in the Unbatched Staging Records pane, the status remains QueuedtoRun, you need to enable service broker
- MDS uses nvarchar for everything, making things difficult if your source system uses varchar. So you have to convert from varchar to nvarchar in SSIS using the data conversion task to get data into MDS
- MDS stores dates as datetime2(3) which will cause a compare in T-SQL to a field with datetime to not match. So keep this in mind so when doing a compare as both fields should use the CONVERT statement to be converted to the same date type
Thank you for sharing these tipps. Very helpfull.
Hi James,
Did you do an evaluation of the pro/cons betweew Microsoft SQL Serve and MDS? If so can you share your findings.
Thanks,
Frank
Hi Frank,
Not sure I follow. Are you asking the difference between using MDS or trying to provide a MDM solution without using MDS (ie. reinventing the wheel)?
James,
Thanks for getting back so soon.
Looling for the difference between MDS (new product) and using an industrial strength application like SQL Server to impliment an MDM solution
thanks
MDS is built on top of SQL Server. One cannot exist without the other.
Is functionality available in the MDS Web UI that is similar to the “Publish” in the Excel Add-In?
We would like the user to batch his changes in the UI and then click Publish or something to start our notification process. We don’t want to be notified for every change.
Thanks
Hi Antonette,
When making changes via the Web UI, the changes are effective immediately. Hence, there is no need to publish the data, and there is not a batch concept.
Hi James,
I am using MDS to do some heavy data management on a dataset in around 130000 records. My problem is that the Excell add-in is awfull slow when loading the data. Is there any way to speed up the loading other than work with a smaller dataset?
Best Regards
Kenneth
Very handy query to get Error details James, many thanks. JK
Hi
I have a question about staging: i have two rows with the same code, i want to load both of them into MDS UI, but MDS staging will not accept the same member code twice. How should I solve it?
For instance:
Code: 12345, Attribute1: 001
Code:12345, Attibute2:002
I want to load both rows together at the same time.
You will have to transform the data to get both Attribute changes on to the one line.
Hi
Thanks for your reply. I am new with MDS, so I am not sure how to transform the data to get both Attribute changes on to the one line.
Data source(code:12345, type:001; code:12345, type:002)–>conditional split( Code==Code)–>match output->derived column(create new column: type2)
Am I right?
Hi James,
I have been looking at MDS for a specific name-maintenance task and I have worked out how it can cover every requirement except one. If somebody changes the name of a member (same thing, different name), can I use MDS to to see this? I need to report from an anther database and would need both names for continuity using MDS as the reference source.
James, Quick question regarding “change tracking entities attributes” and their relationship to Data Warehouse Type 2 Dimensions. Unless I am missing something MDS cannot support pulishing the changes via views with a datatime stamp. so it would seem the best way to accomplish this is to insert a custom process thta will take the published view (MDS) and update the Data Warehouse Dimension (Type 2) , in essence manually.
Yep Ira, that manual process is the way I have done it in the past.
Hi James,
Are you familiar with the email notification functionality tied to business rule validation. Have you set this up in the past at your organization?
Hi James,
End users would like to capture all the changes to any attributes within an entity.
Currently in the DB I see any change to the attribute value, the record gets updated with User ID and Data-time stamp.
I am trying explore in MDS 2008 to see if its possible to set up a SCD Type-2?
Capture every change in a separate record in the DB.
thanks,
Susheel
Hi James,
I am working on a MDS project that utilizes the Excel add-on as the UI tool for the business users. One of the attributes is domain based, and both the name and code both show up in the same column in Excel (in this case it is store name and store #).
The business users want these separated into two columns so they can sort/filter by either store name or store number. Is there a way to separate this domain attribute into two columns in the Excel add-on?
Thanks,
Tony
As it is a Domain attribute, the code cannot be changed in the referenced entity, so the Name/Code (or just the name is an option) is displayed as there is only one column/Attribute in that Entity.
If they can see both Name and Code in the text displayed, can they not still filter on either ?
Thanks so much for sharing this James – its a dense gold-mine of MDS nuggets. Your point on the domain-based attributes (generating view columns of *_Code and *_Name) got me onto the root cause and workaround for this issue: https://connect.microsoft.com/SQLServer/feedbackdetail/view/958374/mds-domain-based-attribute-name-conflicts-with-other-attributes
Do you know if it is possible to assign different attributes from different entities to the same change attribute group? Or is it only possible to assign several attributes to the same change attribute group, if they are in the same entity?
Thanks for the tips! Any idea how to change the display value of a domain attribute so that it’s not the code value? I have a self-referencing entity and one of the attributes (ParentID) is restricted to the existing records in the entity. But it only displays the code value and I can’t find where to select a display value for the drop-down.
Thanks!
How can I alleviate this error? 229: A database error has occurred. Contact your system administrator. This occurred after my password changed, and I had to update the account under IIS with the new password. I was able to add and view my model before the password change, now I cant! Help!
Hi James,
Is there a way to hide the name and code columns in the Excel add-in?
Setting the pixel width to 0 works to hide other columns, but not name and code.
Many Thanks,
Colin
Wait! Found the answer. Thanks
Colin Daley, How were you able to hide Name and Code in the Excel add in? Thx! Claudia
Hi James,
I want to lock the add/edit options of only certain attributes. Please let me know if there are any options to do that.
Thank you,
Aravindan Rajamani
James,
Thank you so very much for an extremely helpful post.
James,
I am trying to create a business rule in MDS- the combination of 2 columns in the entity must be unique and no duplicates are allowed. I tried all the possible ways but when I try to save it MDS throws a error.
Could you let me know can we assign this business rule on MDS entity or not.
Thanks
put the result of the concatenation in the code column. This column must always be unique.
Hi there. James, what is the best and fastest way to get a lot of records into an entity? I know you can do this via the Excel add-in but this is OK to a point… If you’ve got hundreds of thousands of records, this becomes almost close to impossible. Is there a better way? I have heard about the staging tables but never tried this. Would you point me—if this is *the* way—to some easy-to-follow guide on this? Thanks.
Hi Darek,
The Stage tables in MDS are defo the way to go, especially if you are working with larger datasets. Have a look at
https://msdn.microsoft.com/en-us/library/ee633726.aspx
JK
hi,
When using the web api of MDS to get the entity member, it can only get 50 data. How can I get the whole entity data?
Hi,
I have a custom developed application that uses MDS API to enter data into database. I want to enter only those records that are new. If a record already exists with one or two attributes different then I want to update the existing record in database with these new values. I found out that EntityMembersMerge does a similar thing what I want to do but couldn’t understand how it does. Can you explain how to do it with the use of EntityMembersMerge function?
Pingback:SQL Server Master Data Services (MDS) Tips and Tricks – WebProfIT Consulting
Hi
In response to the new capability to filter a DBA, I am struggling to use this feature to solve a scenario that I am sure many find themselves in. We plan to make wide use of DBA type drop downs to enforce data integrity on some of our main master data entities. Take for example a fictitious client entity that has Gender, Race, Education Level, and Religion. Each of these has a finite list of available values for capture and we don’t want to have these as free format fields because typos and different spelling might cause havoc with our data integrity. So before the new filtering feature came in, we would have to create 4 DBA lists in Master data services, making this 5 separate master data entities to maintain. It would be much easier if we had a common entity which allows us to capture name/value combinations and separate these into different grouping via a third attribute called “Group”. Then when we define the main customer entity, we could make the DBA entry point to the common list and filtered by the “Group” attribute. I was hoping that this new filtering feature would solve this problem but it seems that if you want this to work, you have to have another drop down preceding each of the 4 drop downs to allow the user to select the group to filter on before the second drop down is filtered by the first selected value. I even tried to make use of setting the display width to zero to hide the first drop down and then use a business rule to default to the desired value to almost make the first drop down “hard coded” to the desired group but could not get this right.
Is anyone also having this requirement and found a better way to do this or will this go down as a new feature to be added at some undetermined time in the future.
Help!
Hi,
Is it possible to have an entity A where:
a) main columns (Name, Code, Parent – recursive) are visible for all users
b) extra columns (Country, Profit) are:
– DENY for all users by default
– UPDATE for specific country officers ( users based on Derived Hierarchy (“Country” based).
I have composite key for my entity, i tried various biz rule like must be unique, concat values in the code field, all are throwing error, can you post some samples/screenshot
please
Must be unique -In combination with following
Errors : SQL2012 MDS : The attribute reference matches more than one attribute
SQL2016 MDS : No errors but the rule picks only the first attribute after saving
when i use the Default to a concatenated value on the code column
Errors : 2012 MDS : The attribute object with an id doesnot exists or access is denied
2016 MDS : A default value cannot be set for the Code attribute. Edit the entity to enable automatically generated Code values
Hi,
I am trying to purge(hard delete) all data(717452 records) from one of the MDS entity.
This is part of our requirement .I ran the stored procedure ,after updating the importtype to 6 in the staging table and importstatus_id to 0.
The process is running
The process is running from past 2 days now.
Is there anyway to acheive this quickly?
Hi Swati,
That SP does a line by line delete, so it is indeed very slow, but 2 days seems a lot. Have you run any queries on the data to see if there is any progress?