Microsoft SQL Server 2008 R2 MDS: Creating and Using Models

Microsoft SQL Server 2008 R2 Master Data Services

Manage and maintain your organization’s master data effectively with Microsoft SQL Server 2008 R2 Master Data Services

MDS object model overview

The full MDS object model contains objects such as entities, attributes, members, collections, as well as a number of other objects. The full MDS object model can be summarized by the following diagram:

As indicated in the diagram, the Model object is the highest level object in the MDS object model. It represents an MDM subject area, such as Product or Customer. Therefore, it would be normal to have a model called Product, which would contain several different Entities, such as Color, Brand, Style, and an entity called Product itself.

Entities have one or more Attributes and instances of entities are known as Members.

Instead of an entity just containing attributes, it is possible within MDS to categorize the attributes into groups, which are known as Attribute Groups.

There are two types of hierarchies within MDS, namely Derived Hierarchies and Explicit Hierarchies. Derived hierarchies are hierarchies that are created based upon the relationships that exist between entities. For example, the Customer entity may have an attribute called City that is based upon the City entity. In turn, the City entity itself may have a State attribute that is based upon the State entity. The State entity would then have a Country attribute, and so on. This is shown below:

Using the relationships between the entities, a Derived Hierarchy called Customer Geography, for example, can be created that would break customers down by their geographic locations. A visual representation of this hierarchy is as follows:

A Derived Hierarchy relies on separate entities that share relationships between one another. This is in contrast to an Explicit Hierarchy, whose hierarchy members must all come from a single entity. The point to note though is that the members in an Explicit Hierarchy can be both Leaf Members and also Consolidated Members.

The idea behind Explicit Hierarchies is that they can represent ragged data structures whereby the members cannot be easily categorized as being a member of any one given entity. For example, Profit and Loss accounts can exist at different levels. Both the accounts “Cash in Transit” and “Net Profit” are valid accounts, but one is lowlevel and one is a high-level account, plus there are many more accounts that sit in between the two. One solution is to create “Net Profit” as a consolidated member and “Cash In Transit” as a leaf member, all within the same entity. This way, a full ragged Chart of Accounts structure may be created within MDS.

Models

The first step to get started with Master Data Services is to create a model, as the model is the overall container object for all other objects.

We are going to start by creating a practice model called Store—meaning that it will be our single source of retail stores in our fictitious company.

Models are created in the Master Data Manager application, within the System Administration functional area. Carry out the following steps to create our Store model:

1. Ensure that the Master Data Manager application is open. To do this, type the URL into your web browser, which will typically be http://servername/MDS/.
2. Click on the System Administration option in the home page, which will take you to the following screen:

   

3. The tree view that is shown breaks down each of the models that are installed in the MDS environment, showing entities, attributes and other objects within the system. The tree view provides an alternate way to edit some of the MDS objects. Rather than using the tree view, we are going to create a model via the menu at the top of the screen. Hover over the Manage menu and choose the Models menu item.
4. The resulting screen will show a list of the current models within MDS, with the ability to add or edit a model. Click on the familiar Master Data Manager green plus icon in order to add our new model, which will produce the following screen:

   

5. Enter Store as the Model name.
6. Next there are some settings to configure for the model, via three checkboxes. The settings are:
   • Create entity with same name as model—This is essentially a shortcut to save creating an entity manually after creating the model. We want an entity called Store to be created, so leave this option checked.
   • Create explicit hierarchy with same name as model—Another shortcut, this time to create an Explicit Hierarchy. We will manually create an Explicit Hierarchy later on, so uncheck this box.
   • Include all leaf members in mandatory hierarchy—This setting only applies if we are creating an Explicit Hierarchy. If checked, it will ensure that all leaf members must exist within an Explicit Hierarchy. By unchecking the previous option, this option will be unchecked.
7. Click the “Disk” icon to save and create the new model.

Entities and attributes

After creating the new model, we are now in a position to create all the entities and attributes that we need to manage our store data. However, before doing this, we need to plan what entities and attributes are needed. This can be summarized by the following table:

Attributes

Attributes are the building blocks of entities in MDS, and there are three different types, which we need to cover before we can start creating and altering entities. The different types are explained in the following table:

Domain attributes and relationships between entities

As we left the Create entity with same name as model option checked when creating the model, we will have an entity already created called Store. The task now is to edit the Store entity, in order to set up the attributes that we have defined previously. However, before we can complete the Store entity, we actually need to create several of the other entities. This is because we want some of the Store’s attributes to be based on other entities, which is exactly why we need domain attributes.

Domain attributes are the method that allow us to create relationships between entities, which we need, for example, in order to create Derived Hierarchies. In addition, they also allow for some good data entity validation as, for example, it would not be possible for a user to enter a new attribute value of “New York” in the City attribute if the member “New York” did not exist in the City entity.

If we look at our table of entities and attributes again, there is a column to indicate where we need a domain attribute. If we were building an entity relationship diagram, we would say that a Store has a City and that a City belongs to a State.

Creating an entity

To focus on the Store entity first, before we can say that its structure is complete, we firstly need to create any entities that the Store entity wants to use as domain attributes. We can clearly see from the previous table that these are City and StoreType. The Country and State entities are also required as domain attributes by other entities. Therefore, carry out the following steps in order to create the remaining entities:

1. Ensure that you are in the System Administration function.
2. Hover over the Manage menu and click on the Entities menu item.
3. Ensure that the Store model is selected from the Model drop-down.
4. Click the green “plus” icon. This will open the Add Entity screen.
5. Enter the following information on the Add Entity screen, as shown below:

   

   • Enter City as the Entity Name.
   • Choose No to not Enable explicit hierarchies and collections.
6. Click the save icon to create the entity and return to the Entity Maintenance screen.
7. Repeat this process to create the StoreType, Country, and State entities.

Once you have finished the Entity Maintenance screen should look as follows: