SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Master Data Services Terminology Overview

When moving from a data warehouse world to MDM, there is some new terminology to learn.  Each of the terms below are components of a Master Data Services model.  In brackets is the equivalent term in the data warehouse world:

  • Domain – subject area; what you are managing (i.e. customer, product).  Grouping of related business data that is an area of focus for a master data management solution
  • Model [Schema] – Models are the highest level of organization within MDS.  Models are nothing but containers of related entities.  Only entities within the same model can be related within MDS
  • Entity [Table or SSAS dimension] – a container for a set of members.  Contains attributes. Entities are the base containers for data in MDS. In their simplest form, entities can be thought of as tables in a database.  Users control the attributes (columns) that are managed for each entity.  If explicit hierarchies are enabled for an entity, the entity becomes far more complex, managing parent members and their consolidations as well as collections, their attributes, and the members associated with those collections
  • Member [Rows in a table or SSAS members] – a data element; classified as a leaf, consolidated, or collection member type.  Members are the records that populate all the entities created in MDS.  Leaf members are the primary members of an entity.  If an entity is enabled for explicit hierarchies and collections, then consolidated members can be created, and can have their own attributes
  • Attribute [Column or SSAS attributes] – a property of entity members. Attribute types include free-form, file, or domain-based.  Attributes describe members.  Attributes can be loosely thought of as columns in a table.  Entities contain members and their attribute values
  • Domain-based Attributes – Domain-based attributes are attributes in which the available values are restricted to the members stored in a related entity
  • Hierarchy [SSAS hierarchies] – organization of members into various consolidated groups and levels to support analytic requirements.  There are two management types for hierarchies in MDS: explicit and derived.  Hierarchies enforce rules for member inclusion to ensure consolidations do not lose or double count values in connected applications
  • Collection [SSAS named sets] – user-defined subset of leaf and consolidated members.  Collections provide member grouping flexibility that is not supported in hierarchies
  • Business Rule – business logic that is intended to invoke a certain Master Data Services action
  • Subscription View – provides a layer over the MDM tables, denormalizing the master data and applying recognizable names to columns, making them much easier to work with.  This allows for integration with downstream systems dependent on master data, hierarchies, and collections
  • Canonical – standardized data definitions/common data model

More info:

MDS Architecture Notes

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...