This post is part of a series on this blog that will help me, and hopefully you, pass exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012.  So far, we’ve covered:

• Tables and Schemas in the data warehouse
• Dimensions and Slowly Changing Dimensions
• Fact tables and measures
• Intro to columnstore indexes
• Columnstore indexes and partitioning
• Introduction to SQL Server Data Tools (SSDT)
• Connection Managers
• Control Flow Tasks and Containers
• Data Flow Source Adapters
• Data Flow Destination Adapters
• Data Flow Transformations
• Variables and Parameters
• SSIS Expressions
• Transactions and Checkpoints
• Logging in SSIS 2012
• Deploying projects in SSIS 2012
• Executing Packages in SSIS 2012
• Introduction to Master Data
• Installing Master Data Services

This post is largely about terminology.  I could walk you through the somewhat tedious steps of creating a model in MDS, but an understanding of exactly what you’re creating is far more important.  So here’s my MDS cheat sheet.

MDS Terminology

• Model – a container for MDS objects.  An MDS model usually covers a business area.  For example, a Customers model might include objects for customers themselves, but also related data like country or state information related to a customer’s address.  A Products model might include data about products, categories or subcategories.
• Entities – Models are a collection of entities.  Entities represent the objects or concepts contained in the model.  Keeping with the examples above, the entities in the Customers model would be Customers, States, Countries, and the entities in the Products model would be Products, Categories, Subcategories.
• Attributes – Entities contain one or more attributes that describe the entity.  A Customers entity may contain attributes like Name, Address, City, State, Income, Gender, etc.  Each entity is required to have a Name attribute and a Code attribute.  Both of these are added automatically when the entity is created.  The Code attribute values act as a primary key for the entity and therefore must be unique.  Attributes can be organized into attribute groups to help organized the user interface in Master Data Manager.
  • Domain-based attributes – Attributes can be domain-based, meaning their values come from a different entity, like a lookup table.  An example of this might be the State attribute in the Customers entity, which can be connected to the State entity, to ensure only valid values are used.
  • Free-form attributes – allow the user to enter free-form input like strings, dates, numbers, etc.
  • File attributes – allow you to store documents related to the entity.
• Hierarchies
  • Derived hierarchies – The use of domain-based attributes forms a natural hierarchy between the members of the related entities, much like a primary/foreign key relationship would in a relational database.
  • Explicit hierarchies – Explicit hierarchies are created and maintained manually through the use of consolidated members.  Clear as mud, right?  Yeah, I thought so too.  So here’s an example, shamelessly borrowed from BOL.  The example below shows members of a Products entity.  The yellow members are leaf members, actual products stored in the entity.  But some of these members can be grouped, like BK-M201 and BK-M301 are both kids bikes.  So to do this in our entity, we create a consolidated member C {Children Products}.  These consolidated members can be further summarized by other consolidated members, as is the case with WM-RT and WM-ON being consolidated into MW.  By creating these consolidated members, you’re creating an explicit hierarchy.

Explicit Hierarchy (courtesy of MSDN)

• Collections – Collections aren’t hierarchies, but they are groups of members.  A collection can contain leaf and/or consolidated members, but there’s no hierarchical relationship between members, it’s just a flat list.
• Business rules – Business rules are if/then statements that enforce data integrity in your model.  You can use them to define a set of allowed values, or a particular formatting to your data.  They can generate a default value, change an existing value, perform data validation, or perform an external action, like starting a workflow.

Once you’ve got the concepts down, the actual implementation is pretty straightforward.

1. Create a model for your business area
2. Create the entities in the model
3. Define attributes for each entity
4. Create any attribute groups and add the appropriate attributes
5. Create hierarchies between entities, as necessary
6. Define any business rules
7. Populate your data

Additional Resources

For more information on MDS models, check out the following resources.

• Master Data Services Overview
• Models (Master Data Services)
• Create a Model (Master Data Services) – use the Next Steps links to walk through the entire process