20 Apr

Master Data Services: Security

Because, if the model doesn’t confuse you, the security model surely will.

Model Administrators

These have update permissions on a specific model, or models.   They only have access to areas they are assigned to.  There can be multiple model administrators.

System Administrator

They have update permissions to all models and they can add, update or delete them and there is only a single System Administrator account.

You cannot change the system administrator account using MDS.  You must use SSMS and T-SQL.   The following link explains the process: http://msdn.microsoft.com/en-us/library/ff487048.aspx.


Users and Groups

In order to get access to MDS a user must have either a Windows domain account or an account on the server where MDS is installed.

To give a user access to MDS you either ad their account  to the list of users in MDS or add them to a group that is in the list of groups in MDS.

To give a user, or group,  the ability to work in the explorer they must be given access to the explorer and model objects.

Functional Area Permissions

There are 5 functional areas:

  • Explorer
  • Version Management
  • Integration Management
  • System Administration
  • User and Group Permissions

Within the Explorer, permissions assigned to models and hierarchical objects, determine access.  In the other areas a user must be a model administrator to view and work on a model.


Object Permissions

  • Model: Apply to all entities, and collections within the model.  Permissions assigned to a model can be overridden by object permissions.
  • Entity: Apply to the entity’s attributes, collections and explicit hierarchies.
  • Leaf: Apply to all attribute values for leaf members.
  • Consolidated: Apply to all attribute values for consolidated members of an entity.
  • Collections: Apply to all collections for an entity.

Permissions can be broken down into 3 groups:

  • Read-only: Can’t add, remove or update.
  • Update: Can add, remove and update.
  • Deny:  Can do nothing.

There is so variance amongst the different objects but I’m going to take my chances on those.

It is considered a best practice to grant the update permission to the model object and then explicitly permissions beneath it.  If you do not apply these permissions then the objects inherit permissions.


Navigational Access

This was, to me, a confusing concept.  In essence, what they are doing here is saying, if you get access at a given level, you also get access to some things further up the tree.  These work as follows:

  • Entities: You can read or update the code and name for all members, and read the model name.
  • Attributes: You can read or update the code and name for all members in the entity, and read the model name.
  • Collections: You can read or update the name, code, description and ownerID.  You can also read the model name.


Hierarchy Permissions

Microsoft say’s they’re optional.  I’m going with that.  Here’s a link.


Overlapping User And Group Permissions

  • Deny overrides all other permissions.
  • Update overrides Read-only.

Overlapping Model And Member Permissions

  • Deny overrides all other permissions.
  • Read-only overrides Update.

Here’s to hoping that’s most, if not everything, and that I didn’t blow anything too terribly.

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.

13 Apr

Master Data Services: Installation

I’m honestly not going to spend a lot of time on MDS but I am going to fire a few posts at it.  My hope, and the chance that I’m going to take, is that I won’t get hit with any hard-core configuration issues.  If the test in some fashion asks me to do something step-by-step I’ll just have to hope I get lucky.  My hope is to get it to the point that I can get some, maybe most, questions on it, right.

So, onto installation.  Be careful here, there are a lot of steps to it.

  1. Run SQL Server Setup.

And that’s that.  However, there are a number of requirements to use it.

  • Microsoft Silverlight 5 is required.  The application will prompt you to install it.
  • It only runs on 64-bit editions of SQL Server 2012 Developer, Business Intelligence Edition and Enterprise.
  • You must install IIS 7, 8 or 9.
    • This requires a handful of things be configured.  You can see the full list here:
    • If you are using Windows 7, which I do most of the time, you cannot install Windows Authentication, which means you cannot install MDS.  You will need to either upgrade to  Windows 7 Ultimate, use a virtual server, or find a SQL Server installation on Microsoft Server.
  • .NET 3.51 or greater.

The MDS add-in for Excel is installed separately.  You download it from Microsoft.  There are two versions: 32-bit and 64-bit.  Unfortunately, I have not tested this.  My virtual server doesn’t have Excel and the system that does, my desktop, is only Windows 7.

Finally, you can integrate Data Quality Services with MDS but you have to install it, and the Data Quality Client, on the same instance of SQL Server that you have MDS installed on.