SQL Server Master Data Services Terminology

By: Siddharth Mehta

Overview

In the next chapter we will start with hands-on exercises to develop MDS data structures. In order to work in a MDS team environment, its necessary to speak the same language and understand MDS jargon and constructs. We will go over different MDS related terms commonly used while working with MDS. This will help us develop a comfort zone in communicating in a MDS team environment as well as develop the base for working with tools like Master Data Manager. Once we are thorough about MDS data organization concepts and database objects, we will be ready to start shaping our first model. So consider learning these constructs in detail. This chapter will provide the simplified definition as well as reference links for detailed study of each term.

Explanation

• Models - A model is similar to a database in SQL Server. It hosts other database objects like entities, attributes, collections, etc. Models are generally scoped by an area of practice. For example, a manufacturing industry may have a product model, customer model, sales model, etc. For detailed understanding of models, consider reading this article.
• Entities - An entity is contained into an entity, and is similar to a table in SQL Server. It hosts the actual structure and data of the model. A model is designed in a way similar to defining and normalizing tables. For example, a product model may have a product, product category, and product subcategory entity. For detailed understanding of entities, consider reading this article.
• Attributes - An attribute is similar to a field of a table. Attributes are contained inside an entity. A product entity can have attributes like product code, color, unit, size, type, etc. For detailed understanding of attributes, consider reading this article.
• Members - A member is the actual physical data and value of an attribute. Each value in an entity is called its member. For example, A123 can be the member of the product code attribute in a product entity. For detailed understanding of members, consider reading this article.
• Domain-based Attributes - Domain based attributes can be perceived as child tables having a field that is a foreign key from another master table. For example a sub-category entity can have a category attribute that is populated from members of the category attribute in the category entity. In other words, members of domain-based attributes are populated using the members of another attribute in another entity. For detailed understanding of domain based attributes, consider reading this article.
• Attribute Groups - An entity can have tens or even hundreds of attributes over a period of time. Accessing these attributes can become tedious with limited screen space. These attributes can be logically classified into attribute groups. This group information is used by master data manager to conveniently display these attributes in a tabbed and grouped fashion. For detailed understanding of attribute groups, consider reading this article.
• Transactions - In simple words, whenever data is changed (i.e. member values are created / updated / deleted or members are moved), the change is recorded by MDS. These transactions are available for roll-back to administrators and for viewing to users so the users can track and understand the change history. For detailed understanding of transactions, consider reading this article.
• Annotations - Annotations are basically the description that one provides while making changes to the data or metadata in MDS. For detailed understanding of annotations, consider reading this article.
• Hierarchies - A hierarchy is a way to organize and associate attributes from one or more entities in a hierarchical relationship. Hierarchies can be explicit / derived. For example, in a product model, you can create a category -> product hierarchy where products are grouped a category. This kind of data structure is very useful for reporting, as they enable easy navigation of data. For detailed understanding of hierarchies, consider reading this article.
• Collections - A collection is similar to a view in SQL Server. One can create different filter conditions to select the members of an entity and add it to a collection. For example, a set of reports regularly require product members that have a stock level value attribute less than zero or a manufacturing time attribute more than 10. For such requirements, one can create a collection in an entity that contains members according to this criteria. After the collection is created, reports can easily query this collection. For detailed understanding of collections, consider reading this article.
• Business Rules - A business rule is an action that is executed once the attribute value meets the defined criteria. For example, if the stock level attribute value is zero, the business rule is to send an email to notify a particular group. Business rules can be defined, published and then applied against the intended entities and attributes. For detailed understanding of business rules, consider reading this article.
• Validations - The process of validating data against the schema in which the data is being hosted and the business rules set for the entity / attribute in which data is being hosted, is known as validation. When data is updated in any attribute, the corresponding configured validation process takes place. For detailed understanding of validations, consider reading this article.
• Versions - In any normal SDLC, once we are ready to release a version of deliverables from a lower environment to a higher environment, we create a build and deploy. In a similar manner, once data is ready to be shared with users, a version of the data is generally created and published for users to consume the data. Versions in MDS means marking a state of data with a version number and other version specific information. For detailed understanding of versions, consider reading this article.
• Notifications - As an action of business rule validation, MDS can be configured to send email notifications to users using the database mail feature. For detailed understanding of notifications, consider reading this article.

We looked at some of the major terms and constructs in MDS. In the next chapter we will develop a sample MDS model and learn about loading data into MDS from external data sources.

Additional Information

• Consider exploring the above mentioned constructs using the sample models that we have installed on MDS. Try to create new constructs or modify existing constructs from the sample models, using the steps explained in the above links provided with each construct.