How to use Attribute Filters in Master Data Services 2016

By:   |   Updated: 2017-02-15   |   Comments (13)   |   Related: > Master Data Services


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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

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

View all my tips


Article Last Updated: 2017-02-15

Comments For This Article




Tuesday, November 2, 2021 - 8:53:44 PM - Mark latham Back To Top (89402)
Thanks Simon Gough - Purge Entity from the Web UI worked for me
Errors • 200117 : The attribute cannot be updated. The attribute filter is not compatible with one or more current attribute values. First member code: 2, version: VERSION_1, @ErrorNumber = 50000, @ErrorProcedure = "mdm.udpAttributeSave", line 618.

Tuesday, June 18, 2019 - 8:45:56 AM - Koen Verbeeck Back To Top (81509)

Hi Colin,

Attribute filtering would only work if you have one column where you select "steward" and then in another column you can select the actual data stewards. It seems you have setup the hierarchy correctly. In your case, you would need 4 columns: 1 column to select steward as the role, 1 column to select the data steward, 1 column to select owner as the role, 1 column to select the owner. I haven't tried adding the same domain based entity multiple times though.

Koen

Friday, June 14, 2019 - 9:06:24 AM - Colin Back To Top (81469)

Hi Koen,

Great post, thank you. I've tried to apply it to my scenario, but it doesn't seem to work, so I think that I am missing something (I have MDS 2016).

I've created four entities:

  1. Party
  2. Role
  3. Party to Role Mapping (A single party can perform one or more roles)
  4. Data Asset (A Data Asset has to have a Steward and an Owner)

I've populated the first three entities, and have created a Derived Hierarchy. The hierarchy reads:

  1. Steward
    1. Peter
    2. James
    3. John
    4. Andrew
  2. Owner
    1. Paul

I now want to configure the Data Asset entity by creating two attributes:

  1. Steward
  2. Owner

When people populate the Data Asset entity, and they want to select a Data Steward, I want them to only see the names that have the role Steward. The same applies to the Owner attribute.

Is this possible in MDS? Can you combine this with your post to create more complex scenarios?

Looking forward to hearing from you.

Kind regards,

Colin

Thursday, May 23, 2019 - 5:02:28 AM - Simon Gough Back To Top (80165)

Hi,

I had a variation on Bllal's error. Issues for me seemed to be in soft deleted members (possibly due to changes in entity during development).

This worked for me - retrieve code values for soft deleted members from table (need to find the table in MDS)

Stage them with type 6 - ie to permenantly delete even if a domain member

Once deleted I could create the filter on the domain attribute without error.

Tuesday, April 2, 2019 - 1:24:07 PM - Koen Verbeeck Back To Top (79446)

Hi Rickard,

I have a hard time visualizing with your issue exactly is :)
If you think it's a bug, you might have more luck in the MSDN forums.

Regards,
Koen

Tuesday, April 2, 2019 - 7:19:28 AM - Rickard Aronsson Back To Top (79443)

Hi Koen!

I've used your tutorial on attribute filters in MDS 2017 while creating an organisation structure in MDS with six levels. I've placed all six entities in a derived hieararchy and applied all possible attribute filters in the five all child entities, so that level six contains the full hierarchy (5 parents/ parents parent). This works really great and due to the number of members in the lower levels it wouldn't be possible to choose the correct parent without this. Though I'm having a probem which I haven't been able to find a solution to anywhere, and it replicates in several small test models that I've also built so I don't think there's a problem with my organisation model. The problem is that when someone, for instance, change the parent of a level 4 in the level 4 entity that change isn't applied to the level 3 column in the level 6 entity. It seems that changes in a parent doesn't get copied down to it's child, or child's child. Wierdly enough it's also possible to publish the level 6 entity with a level 3 parent's parent which isn't actually the parent of the level 4 member, being the parent of the level 6 member. I hope you understand what I mean, and I would be so very greatful for any input here, because I discovered this after we went live! (oops!)

Monday, February 12, 2018 - 5:32:27 AM - Koen Verbeeck Back To Top (75181)

Hi Stefaan,

as a matter of fact, I just did it last week in SQL Server 2017. It seemed to work fine in Excel.

Koen

Friday, February 9, 2018 - 10:14:51 AM - Stefaan Taeymans Back To Top (75156)

Hi Koen, 

Did you ever try this in master data services 2017? 

Seems to me that everything works fine in the web browser but not in Excel.

regards,

Stefaan

 

 

 

 

 

Wednesday, January 24, 2018 - 11:11:20 AM - Leandro Santos Lima Back To Top (75032)

 Hey Guys, 

 

I had the same issue that BIlal but I could fix it. It was happening due the existing members. I had to map the existing members before set the filter option.

Existing members unmapped was causing some conflict when I tried set the filter.

 

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

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 (55024)

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 (55007)

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 (54663)

 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.

 

 














get free sql tips
agree to terms