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

 

How to use Attribute Filters in Master Data Services 2016


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

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

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?

Solution

Let's explain the problem using a simple example. Suppose we have the following employee table:

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.

department entity

The Manager entity is straight forward as well, with the Code and Name attribute present but also a domain based attribute of Department.

manager entity

Now let's put some data in those entities using the Excel add-in:

departments

And the managers (code values are automatically generated by the way):

managers

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

managers and departments hierarchy

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.

employee entity

For the EmployeeManager domain based attribute, we can now set the filter in its properties:

department filters manager

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:

department filters manager in addin

In the screenshot above you can see the IT department is selected, which means only Trudy should be available in the Manager dropdown:

department filters manager in addin bis

This functionality also works in the Explorer:

department filters manager in explorer

Conclusion

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.

Next Steps
  • 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:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

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     



Wednesday, April 19, 2017 - 9:06:56 AM - Koen Verbeeck Back To Top

Just guessing here: do all the data types match between the different attributes?


Wednesday, April 19, 2017 - 12:59:47 AM - Bilal Back To Top

Hi Koen,

            Yes i am following the same steps as you have mentioned above, but with different data,  I have commented out the lines in Procedure from where error was coming and now it works fine, i dnt know about the side affects of commented lines but so far it i s working fine. see below the lines  have commented out to resolve the issue .

 

 

 

--Commented lines by Bilal to remove error in SP

--IF @MemberCodeWithInconpatibleValue IS NOT NULL

--Begin

 

-- DECLARE @Message NVARCHAR(MAX) = CONCAT('MDSERR200117|The attribute cannot be updated. The attribute filter is not compatible with one or more current attribute values. First member code: {0}, version: {1}.|', REPLACE(@MemberCodeWithInconpatibleValue, N'|', N''), N'|', REPLACE(@MemberVersionName, N'|', N''))

 -- RAISERROR(@Message, 16, 1);

-- RETURN;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

--END

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  

 

 

 

 


Tuesday, April 18, 2017 - 5:00:52 AM - Koen Verbeeck Back To Top

Hi Bilal,

are you following the steps from the article? (with the same data)


Tuesday, April 11, 2017 - 9:22:55 AM - BIlal Back To Top

 Hi koen,

             i am facing below error when applying filte..can you please give solution. Thanks

 

200117 : The attribute cannot be updated. The attribute filter is not compatible with one or more current attribute values. First member code: 4, version: VERSION_1, @ErrorNumber = 50000, @ErrorProcedure = "udpAttributeSave", line 618.

 

 


Learn more about SQL Server tools