SQL Server Master Data Services – Model Implementation

The estimated reading time for this post is 6 minutes

In the previous blog post, I already explained the advantage of using SQL Server Master Data Services feature in the end-to-end organizational BI environment. At the center of every Master Data Services solution is a Master Data Services model. To create a master data management solution with Master Data Services, you must know how to create and manage a model. This blog post explains the key concepts you need to understand about Master Data Services models, and describes how to create and manage a model and the master data it contains.

What is Model in Master Data Services?

mds model

The Code and Name attributes are automatically created for each new entity.

A model is the highest level of organization in Master Data Services. It is a container for a related set of business entity definitions. You can create a model for each area of the business for which you want to manage data. Each model has the following characteristics:-

Version:Remember that, Master Data Services models are versioned, meaning you can maintain multiple versions of master data at the same time. This can be useful in scenarios where many business applications require a newer definition of a specific business entity; however, some older applications cannot be upgraded to use the new model.

Entities and Attributes: An entity is a data definition for a specific type of item used in the business. Each entity has attributes that describe it. These attributes are analogous to columns in a database table. When you create an entity, Master Data Services automatically adds two attributes to that entity, which are not removable:

  • Code: It can contain only unique values. When you populate the entity, you must provide a Code value for each member. The value of a Code attribute will frequently be derived from the primary key column in a relational database table.
  • Name: The Name attribute does not require a unique value—you can leave this field blank for members if appropriate.

In addition to the system generated attributes, you can also use Master Data Services to create one of three types of attribute that describe your data:

  • Free-form: Use this type of attribute to enter free-form values as text, numbers, dates, or links. You use free-form attributes for the text-based, numerical, and date and time data in your databases.
  • Domain-based: This type of attribute accepts values only from other entities. You cannot directly enter values into domain-based attributes. You use domain-based attributes to ensure that the values for a particular attribute match code values in an existing entity.
  • File: This type of attribute accepts files, such as documents or images. You can use file attributes to ensure that all files in an attribute have the same file extension.

Attribute Groups: An attribute group is a named grouping of the attributes in an entity. Attribute groups are useful if an entity has a large number of attributes—which makes them difficult to view—or in scenarios where multiple applications will consume entity data from Master Data Services. However, some attributes are only relevant to particular applications. For example, a Customer entity might include attributes that are only used in a CRM application, and other attributes that are only used by an order processing system. By creating application-specific attribute groups, you can simplify the creation of data flows between the master data hub and the applications requiring master data.

Members: Members are individual instances of entities, and are analogous to records in a database table. Each instance of an entity is a member that represents a specific business object, such as an individual customer or product. 

Note that the example used in above figure, and throughout the rest of this blog post, includes a Customer entity. The members based on this entity use the mandatory Name attribute to store the full name of the customer represented by the member (for example, Ben Smith). You should point out that the design of the example in this module is deliberately simplistic to make it easier to explain the key concepts.

In a production solution, the Name attribute is best used as a shorthand value that makes it easier to identify a member than relying on the unique Code value. Using a customer name in this way might be sensible for some organizations, but in most cases, the Customer entity would also have attributes for distinct name parts (for example, FirstName, LastName, Title, and so on), so they can be maintained individually.

Model Implementation

Note: The Model Implementation section is based on SQL Server 2014.

To create a model, you need to access the Master Data Services web portal. and perform the followings:-

  • Click on ‘System Administration’ in the MDS web portal.
  • Click ‘Models’ from ‘Manage’ menu, then click on ‘+’ button.
  • model1
  • Enter the Model name and click on save button.
  • Model2
After you have created a model, you must add entities to represent the business objects for which you want to manage master data. Use the following procedure to create an entity: 
 
  • Click on ‘Entities’ from ‘Model’ menu and click on ‘Add’ button.
  • entitie1
  • Enter the Entity name and other properties.
  • entitie2
  • Click on ‘Save’ button.

After you have created an entity, it will contain the mandatory Code and Name attributes. You can then edit the entity to add more attributes by following this procedure:

mds entity 1

After you have added a member, you can edit its attributes by selecting it and modifying the attribute values in the Details pane. Adding annotations helps document each change made to the data. You can view a history of all edits, and associated annotations by clicking the View Transactions button.

Editing a Model in Microsoft Excel

SQL Server 2014 Master Data Services supports the Master Data Services Add-in for Excel, which you can use to read and manage lists of Master Data Services data. The add-in is a free download for Excel 2007 or later that you can distribute to data stewards and Master Data Services administrators, so they can work with a familiar interface. You can download the Master Data Services Add-In for Excel from the Microsoft download site, or users can install it directly from Master Data Manager. The Master Data Services Add-in for Excel adds the Master Data tab to the Excel ribbon. You can use options on this tab to perform tasks, such as connecting to a Master Data Services server, applying business rules, creating new entities, and publishing changes back to the server. The add-in is securitycontext aware, and will only allow users to view and change data for which they have the appropriate permissions. To view Master Data Services data in Excel, you must first connect to a Master Data Services server. On the Master Data tab in Excel, you can use the Connect option to create a connection to Master Data Services. After connecting, use the Master Data Explorer to select a model and version to work with from those available on the server. You can then load data into an Excel worksheet from the entities listed in the Master Data Explorer and filter that data, so you only see the actual data you want to work with. After you have loaded the required data, you can browse and edit it, just as you would any other data in Excel. You can create new entities, add columns to existing entities, to define new attributes, and edit member data. Most data editing operations are performed locally in the Excel worksheet. Changes are only propagated to the Master Data Services database when you explicitly publish the changes made in Excel. When you publish an entity, you can enter annotations to document the changes made.

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz