More Master Data Services (MDS) Tips and Tricks

By: Svetlana Golovko   |   Updated: 2016-08-15   |   Comments   |   Related: > Master Data Services

Problem

In our last tip, we provided links to SQL Server Master Data Services (MDS) tips and provided a couple of small tips that might be useful for new MDS users. In this tip we will provide several more tips and tricks that will help you get more familiar with MDS, its functionality and interfaces.

Solution

In the previous tip, we provided tips and tricks related to hiding columns, changing column order, freezing columns, searching for members and sample models to get started. In this tip we will continue with additional tips and tricks to get you started with MDS.

Filter Members by Matches in SQL Server MDS

You can filter a members list from different areas of MDS Explorer (from Entity view or from Hierarchy view) which allows you to view the members that match specific criteria.

You select an attribute that you want to filter on (for example: Name, Code or Description). Then you select an operator.

The following operators are available and depend on an attribute's data type:

• decimal and dates attributes:
  
• domain attributes:
  
• text attributes (including URLs):
  

The ones I discovered recently and are my favorites are "Matches" and "Does not match".

You can configure the "Fuzzy Match Settings" where you can select similarity and algorithm:

Here are results for the Name attribute matches by the criteria "Sveta":

Here are results for the Code attribute matches by the criteria "1":

Create SQL Server MDS Domain Attributes in Excel Add-in

You can create in MDS additional members' attributes using MDS Manager or using Excel Add-in. The advantage of using Excel Add-in is that you can create the new domain attribute and mass update all of the members without jumping from screen to screen.

Here are the steps:

1. Type the name of the new attribute that you want to create ("New Domain Attribute" in our example) at the header row on the right from the last attribute
2. Click on the "Attribute Properties"
3. Select Domain-based attribute type from the first drop-down list
4. Select an entity with members that will be used as domain attributes
5. Click "OK"

Now you can select the new attribute's values from the drop-down list that is automatically populated from the selected entity:

SQL Server MDS Mass Updates Using Excel Add-in

It's much easier to do mass updates with the Excel Add-in. In the MDS Web Application you have to update each member one by one if  you need to update a new attribute for multiple members.

In the Excel Add-in you just do what you usually do in any other Excel spreadsheet - copy text and paste it to multiple cells, drag the cell's value etc.:

Delete/Purge Members in SQL Server MDS

This is probably one of the most common challenges that MDS users face. Deleted members are not permanently deleted from MDS, they are inactivated and users can not recreate the members again.

MDS administrator can purge deleted members for the user, but this usually involves multiple steps and is not always straight forward for new MDS Administrators.

Read our previous tip about a custom stored procedure for purging deleted members. You can also find another trick in the same tip on how to delete a member by renaming the member's code.

Broken URL in E-mail Notifications from SQL Server MDS

This is something that might be overlooked during initial MDS Configuration.

When you configure e-mail notifications for business rules you will get an e-mail with links to the members that failed validations.

Here is an example of the failed member validation:

Here is an example of the e-mail notification:

If you have not configured "Master Data Manager URL for notifications" then the links in these e-mail notifications will be broken.

Make sure the URL is setup in the MDS Configuration Manager (under Database Configuration):

Next Steps

• Read all MDS tips here.
• Learn more about Excel Add-in in this tip.
• Read another tip about Excel Add-in.
• Check Microsoft resources about MDS.

About the author

Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips