Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Master Data Services (MDS) Tips and Tricks


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

Attend a SQL Server Conference for FREE >> click to learn more


Problem

We just recently started using SQL Server Master Data Services (MDS) and sometimes we don't know where things are or how to deal with specific issues. We would like to learn some tricks and find out if there are any valuable resources.

Solution

In this and one of the following tips we will provide several tricks and tips that will help you to get more familiar with SQL Server Master Data Services (MDS), it's functionality and interfaces.

A good place to start to learn some of the tricks is this blog post. Here are some of my favorite tips from it:

  • "Hide" an attribute (the same is applicable to both - Excel add-in and MDS Application)
  • "Refresh cached information" (MDS is known by not refreshing information in some areas)
  • "Create Code values automatically"
  • Display pages from the Master Data Manager Web User Interface within SharePoint
  • Data types awareness.

Another good resource is the Master Data Services Team blog.

Here are some additional tips and tricks that you might find useful.

Hide Columns and Attributes in SQL Server MDS

There is a tip that describes how to hide an attribute by setting the column's size to "0" in the blog with MDS tips we mentioned above. This could be done either in an Excel add-in or in the MDS Application.

Another way of doing this (and probably a cleaner way) is by setting permissions on the attribute.

Here are the steps:

  • In MDS Manager click on the "User and Group Permissions"
  • Click "Manage Groups" (we would like to restrict access to the attribute based on group membership)
  • Click on the edit group icon
  • Click on the "Models" tab and expand the model, entity and leaf so you could see the attributes checkboxes
  • Right click on the model's name and grant read access
  • Grant read access to the entity
  • Right click an attribute you want to hide and click "Deny":

    Deny access to attribute in SQL Server Master Data Services
  • Save the group's permissions.

Now a user who is member of the modified group will not be able to see the attribute/column in the MDS Explorer:

Can't see the attribute in MDS Explorer

Change Column Order in SQL Server MDS

Client side (MDS Web Application) uses Silverlight which is one of the client's requirements. One of the features that you might find useful is changing column order. To change the column order just drag the column's header and drop it where you want it to be.

Change Column Order in SQL Server MDS

Here is the changed columns order:

Changed column order in MDS Explorer

Note that this order will not be saved and next time you re-open the page the columns will be in their initial order.

Freeze Columns in SQL Server MDS

You can freeze columns in the MDS Explorer similar to Excel. To freeze the columns you will need to click the "Settings" icon:

MDS Explorer settings

Then select the number of frozen columns:

Freeze Columns in SQL Server MDS

Note, if you freeze just 1 column then by default only the validation column (green check mark) will be frozen. If you want to freeze, for example, the "Parent" column (next column) you will need to freeze 2 columns.

Freeze Columns in MDS Explorer Example

Now we have the "Parent" column frozen as well.

Search member in a Hierarchy and Find Parent in SQL Server MDS

If you are not sure of the place of a specific member in the hierarchy you can use the search feature followed by the "Find Parent" option.

Open the hierarchy view and type the member's name or code (or part of the name/code - "%4%") in the search box:

Search Member in MDS Hierarchy

After you find it select the check-box next to the member and expand the drop-box next to the "Pin". Click "Find Parent":

Search member in a Hierarchy and Find Parent in SQL Server MDS

The parent of "4" in our case is "2":

Search Parent in MDS Hierarchy

SQL Server MDS Sample Models

If you need a model to practice with, Microsoft provides 3 sample models:

  • Chart of Accounts
  • Customer
  • Product (example below)

    SQL Server MDS Sample Models

The steps for the sample models deployment can be found here.

Next Steps
  • Read all MDS tips here.
  • Check Microsoft resources about MDS.
  • Stay tuned for our next tip with other MDS tips and tricks.


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