At our company we're using Master Data Services (MDS) 2016 to manage and store our master data. We have a rather large entity storing our employees. For each employee, we need to indicate his or her manager using a dropdown menu. However, the list of managers is also quite large, making it difficult to find a particular manager. Is there any way to filter this list?
Let's explain the problem using a simple example. Suppose we have the following employee table:
Each employee belongs to a certain department and has a manager assigned. A manager is assigned to one or more employees, but all in the same department. When we want to select the manager for a certain employee, we only want to see a list with the applicable managers. For example, we want to assign a manager for Greg. In that case, the dropdown should only show Gordon and Selina, the managers of the Engineering department. It should not show Bob, Alice or Trudy, because they belong to other departments.
This type of functionality can be achieved in the SQL Server 2016 release of Master Data Services. The feature is called attribute filters. We can set a filter on a domain based attribute so the dropdown list for the attribute is pre-filtered (for more information on domain based attributes, please refer to the tip How to standardize attribute values in Master Data Services). There are a couple of prerequisites in order for this to function:
- There is a base entity (the employee in this example) and two other entities which will serve as domain based attributes (Manager and Department). They are all in the same model of course.
- The entity that will filter the domain based attribute, must be a domain based attribute itself of the other entity. In other words, Department must be a domain based attribute in the Manager entity.
- The very same entity must also be a domain based attribute in the base entity. Thus, Department is also a domain based attribute in the Employee entity.
- There is a derived hierarchy between the two entities which are used for the domain based attributes. Or, there should be a hierarchy with Department as the parent of Manager.
It may seem redundant that Department is a domain based attribute in both Manager and Employee, but it is necessary in order for attribute filtering to function and to make sure data is consistent. Let's try this out in MDS. First of all we need to create the entities for the domain based attributes. The Department entity is very simple, with only the Code and the Name attributes. The Name attribute is assigned a different Display Name.
The Manager entity is straight forward as well, with the Code and Name attribute present but also a domain based attribute of Department.
Now let's put some data in those entities using the Excel add-in:
And the managers (code values are automatically generated by the way):
Now we can create our derived hierarchy (for more information on derived hierarchies, please refer to the tutorial SQL Server Master Data Services Constructing Hierarchies).
The next step is to create our Employee entity. We use the Code and Name attributes and two domain based attributes as well. Important is the Department attribute is created before the Manager entity, as it makes sense to select the department first before we select our manager when we create a new employee.
For the EmployeeManager domain based attribute, we can now set the filter in its properties:
The name of the filter is the domain based attribute which will filter this domain based attribute, with the name of the derived hierarchy between brackets. To be honest, the first time I couldn't select a filter; the dropdown was empty. I exited the entity screen, restarted the browser and then it worked. Seems like a small bug.
When we now create a new employee, the Manager dropdown list is filtered when we have selected a Department. This is indicated in the Excel add-in when you hover over the EmployeeManager dropdown with the mouse:
In the screenshot above you can see the IT department is selected, which means only Trudy should be available in the Manager dropdown:
This functionality also works in the Explorer:
Attribute filtering is new functionality added in Master Data Services 2016. It's important for better usability when you have domain based attributes of very large entities. Having an attribute filter in place will filter the dropdown list, making it easier to find the correct member.
- Try it out yourself! You can follow the steps in this tip to test this new functionality.
- You can find more Master Data Services tips in this overview.
- For more SQL Server 2016 tips, you can use this overview.
Last Update: 2/15/2017
About the author
View all my tips