19 Apr

Master Data Services: The Stuff

Don’t worry, I love that title too.  The truth is I don’t really know how to name this section.  I find MDS confusing.  Maybe not the product, so much, OK, yes I admit it, the product too, but so many of the explanations I’ve read have left me scratching the side of my head.  Since I know there will be questions on this, and the test is so broad, I’m going to try to pile a bit more into my brain.

Here goes.


This is the top, or highest, level in MDS.  In a sense, they are the container for everything else, except, by everything else, I only mean the area that you are trying to model.

Examples include:

  • Customers
  • Products
  • Salespeople



Entities are groupings in the model.  For example, at Higgins Lumber we had a department > class > fineline (D-C-F) categorization to our products.  These would all be an entity.  At Higgins, the dept group was at the top, while the class and fineline categories were beneath it, and the fineline group was a further refinement of the class group.

Examples include:

  • Customer categories
  • Product categories

I’ve read an entity description that compared an entity to a table.  Look, despite all the writing I’m doing, I’m not claiming to be an expert, so maybe I’m wrong on this, but I wouldn’t compare an entity to a table.  It really looks to me more like a group.  The reason I see it that way is that you can have entities that are subcategories of other categories.  On the other hand, Microsoft proceeds to draw up a diagram in its data that is pretty much exactly like a table.  And you could certainly model MDS to match your table structure in this way.

I guess another way of putting it is, entities are flexible, so you can be flexible when modeling.

Entities can also be constrained.  For instance, at Higgins you could only assign a SKU to a predefined list of departments.  You can do the same thing with entities.

An entity can also be defined as a base-entity, or a starting point for navigation in the model.



Attributes define the members of an entity.  Here, I do think it’s fair to think of them, loosely, as a column in a database.  If you have a model named products, and an entity named flooring, then your attributes would be things like width, thickness, species, etc.

All attributes must have a Name and Code value.  The code value must be unique.

There are 3 types of attributes:

  • Free-form: similar to a text box on a web form.
  • Domain-based: You populate them based on another entity.  An easier way of describing this is as referential integrity.
  • File Attributes: Can be used to store files, documents or images.

Numeric free-form attributes use the double data type so beware the floating point number.


 Attribute Groups

Attributes within an entity can be grouped.  This is  to make attributes easier to view and manage in MDS.  Specifically, attribute groups do the following:

  • Attribute groups always include the Name and Code attributes.
  • Each attribute for an entity can belong to one or more attribute groups.
  • All attributes are automatically included on the All Attributes tab in Explorer.
  • There is no way to hide the All Attributes tab.



Well, here we go, it’s time to confuse the issue, especially if you had a table structure in your head.

Actually, this is the single most clarifying thing I’ve read on MDS.

You can think of members as rows in a table. Related members are contained in an entity, and each member is defined by attribute values.

I bet you wish I’d written that first.  I know I wish I had read it first.  So, lets repeat, because it’s important,

You can think of members as rows in a table. Related members are contained in an entity, and each member is defined by attribute values.

My guess is that if all you do is remember that you are better off than everything else I’ve read.

There are three types of members:

  • Leaf: These are the default members of an entity.  In a derived hierarchy (hierarchies coming soon), they are the only type of member.  In an explicit hierarchy they are the lowest member and cannot have children.
  • Consolidated: Used when explicit hierarchies and collections are enabled.  It’s a parent node for an explicit hierarchy.
  • Collection: A group of leaf and consolidated members of an entity.  They are used to group members for reporting purposes.

Per the MSDN, use leaf members when you are importing data but not using staging tables or the Add-In for Excel.


Explicit Hierarchy

An explicit hierarchy is,

In Master Data Services, an explicit hierarchy organizes members from a single entity in any way you specify.

There are two types:

  • Mandatory Explicit: All leaf members are included in the hierarchy tree.
  • Non-mandatory Explicit: You do not have to include all members in the hierarchy tree.

Explicit hierarchies have the following rules:

Explicit hierarchies have the following rules:

  • Each leaf member can be included in the hierarchy only once.
  • All consolidated members must be included in a hierarchy.
  • Consolidated members cannot be in more than one explicit hierarchy.
  • Consolidated members in the hierarchy tree do not have to contain leaf members underneath them.
  • If you delete an explicit hierarchy, all consolidated members that were used in the hierarchy are deleted.
  • If you delete a consolidated member that was in an explicit hierarchy, all leaf members that were grouped by that consolidated member are moved to the root.


Derived Hierarchy

A derived hierarchy is,

A Master Data Services derived hierarchy is derived from the domain-based attribute relationships that already exist between entities in a model.

In a derived hierarchy, leaf members from one entity are used to group another entity.


That’s a lot.  Even writing it, some of it didn’t sink all the way in.  Especially the part about hierarchies.

Leave a Reply

Your email address will not be published. Required fields are marked *