Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

More Master Data Services (MDS) Tips and Tricks


By:   |   Read Comments   |   Related Tips: More > Master Data Services

Quickly Resolve Performance Problems for IIS, .NET and SQL Server       >>>   Get Started


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:
    Filter Operators Decimal and Dates in SQL Server Master Data Services
  • domain attributes:
    Filter Operators by Domain attributes in SQL Server Master Data Services
  • text attributes (including URLs):
    Filter Operators by Text and URLs in SQL Server Master Data Services

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:

Filter Match in SQL Server Master Data Services

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

Fuzzy Match Results - Example 1 in SQL Server Master Data Services

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

Fuzzy Match Results - Example 2 in SQL Server Master Data Services

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"
Create Domain based attribute in Excel Add-in for SQL Server Master Data Services

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

Using Domain based attribute in Excel Add-in for SQL Server Master Data Services

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.:

Mass update records in Excel Add-in for SQL Server Master Data Services

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:

Validation failed in SQL Server Master Data Services

Here is an example of the e-mail notification:

Validation E-mail example in SQL Server Master Data Services

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):

Setting value for Master Data Manager URL for notifications in SQL Server Master Data Services
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.


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools