Introduction

In the first part of this discussion, I discussed step by step instructions pertaining to getting up and running with Microsoft SQL Server Master Data Services.
In this part of the discussions we are going to discuss Entities and Attributes and the members that make up our data.

The MDS Database

Unlike most of the SQL Server databases that we as DBA’s and developers deal with on a day to day basis, the MDS database is another breed of animal. It comes ‘packed’ full of tables and views of which I too, am still discovering. The point being, that the altering of features, tables or views) within the database) is not for the faint of heart. A corrupted database will give you endless sleepless nights.

Entities

Entities are a MDS word for a data table and attributes are another word for columns / fields within the entity (database table). In short, an entity contains one or more attributes. As an example one may have an ‘Invoice’ entity which has quantitative and qualitative characteristics. An invoice has a balance, a date due. It belongs to a customer, and could be overdue (a Boolean) etc.

Attributes

The balance, due date (above) may be classified as attributes of the Invoice Entity.
Before getting started, you must ensure that you have all the necessary rights. Do speak to your system administrator to ensure that you have the necessary privileges.
We first open our Master Data Manager and select System Administration.

To access the Master Data Manager user the following link which you will have to alter to you’re your server and website instance http://anc-sqldw/MDS1/default.aspx

The System Administration screen will come up.

Select ‘Manage’ and click on ‘Model’. Creating a model is the first step in getting the necessary data to your client. VERY ROUGHLY PARAPHRASED, a model is like a ‘database’. Thus a model (database) has one or more entities which have one or more attributes each. In my case, I called my model “Aging” going forward it will be 'Product'.

What we want to do is to “Add a model” (see above). Click on the green plus sign. Give the model a name and uncheck the check boxes (as later we shall be creating an entity with a different name).

Click the “Floppy Disc” (the one on the left above the word ‘Add Model) to save. Our model is now established.

PLEASE NOTE that due to the sensitivity of our financial data I shall be continuing this discussion utilizing the models that Tyler Graham uses in his book ‘Master Data Services’. I shall however show practical examples from our system throughout this series of articles.

Getting started

It is now time to get our hands dirty and to start formulating the layout of the data in the format agreed upon with the end user and IT. There are two types of entities / “tables” that we shall be looking at and creating. Those where the users will work on a day to day bases (for the sake of argument let us call them core entities/tables) and ‘domain based’ entities. I like to think of ‘domain based’ entities as descriptive tables. Once again and using the examples from part one, we look at the users ‘Core’ entity.

‘Product’ and the domain based entity called ‘Color’.

I have taken the liberty of highlighting the first row.

Note that ‘Color’ on the screen above (Right Side) is shown as a ‘drop down box’. The client only has a limited amount of colors that he may choose from. This is really a true benefit, as it prevents user ‘whoopsies’ and ensures data consistency. IF we granted the user, permission to update the color field, then he or she will be able to choose one of the colors shown in the drop down box. IF the user does NOT have sufficient rights then the color will appear in the color box HOWEVER no drop down box will be available to the user and he or she will not be able to alter the value. This is a super PLUS to the product as the data security can be controlled by window domains and all sorts of permutation of rights may be created. Similar to role based security.

The Color domain (used as a domain based entity) may be seen in the screen dump below:
All that it contains is a key and a description. In MDS term, it contains a ‘Code’ (the key) and a ‘Name’, the description. Contrary to what one may think, code and name are the attributes and the colors (themselves) are members / rows of the entity.

Obviously, the examples that I am showing have already been populated with data. We shall be looking at loading our entity attribute members shortly.

Creating our first Entity

In our imaginary example, we shall assume that we have had discussions with the end users and they have given us a list of fields that they would like to see;  in addition to a list of columns  that  may be updated by the various user groups. Call it a ‘report’.

HOWEVER in this ‘report’ we shall be looking at raw data (perhaps summarized) however the critical point being that the users have been empowered to alter certain fields. A typical example could be a field call current status (Married, Divorced, Separated, Widowed etc.). Normally the fields that we permit users to alter are ‘non –earth shattering fields’ for want of a better word.

For those of you whom know me, I am all for user empowerment with their own data. Having the users take ownership of their own data tends to cause the user to reflect before making any alterations.
NATUARLLY, we cannot let users alter critical fields such as client balances etc. These are best kept for conventional processes executed according to well established business rules.
We now shall return to the Master Data Manager home screen. Simply click upon the SQL Server 2012 logo in the top left hand corner.
Once again choose ‘System Administration’, Manage and then click on ‘Entities’.

The manage entity screen will then appear. Note that the model we are using is product.

This time click upon the green plus sign under the words ‘Entity Maintenance’

The ‘add an entity’ screen will appear.

We shall first create an entity that will be used by our mother entity. We shall give our first entity the name ‘color’. One should try to create our ‘domain based’ entities before the mother. Think of it as the mother being the center of a star and ‘color’, ‘fabric’, ‘type’ etc. being on the point of the star. The reason for doing so will become apparent in a few minutes.

We define color as shown above. NOTE that we are only creating the ‘skeleton’ not adding the meat or the values. Click the ‘diskette’ to save the color entity.
At this time and in a similar manner, one would create a ‘fabric’ and ‘type’ entity as well.
As promised above, one may see a screen dump of a real world customer follow up ‘domain based’ entity below:

Having created our entities that will be used as domain based entities we are now in the position to create the ‘mother’ entity. Once again we choose Manage, Entities, and click upon the green plus sign.

Give the entity a name. Let us call it ‘Product’. The completed form should look like the one below:

Note that we shall accept the default for the staging table (to be discussed later on) and we do NOT want to create any hierarchies at this moment. Once again merely save your entity by clicking on the diskette under the words ‘Model View’.

We now are taken out to the main entity screen.

Adding our attributes

Highlight the Product entity and then click the ‘pencil’ next to the green plus sign. We find ourselves in the ‘edit entity’ screen (for Product).

We now want to add a leaf attribute. A field / a ‘column’ to our table.
Simply click upon the green leaf button and the leaf entity screen will appear. To recap: we have created the table (i.e. the entity), NOW it is time to add the fields to the table.

We shall add out first attribute (field)  ‘stockbalance’.  Stock balance can obviously not be predicted as the color of an object may be. In the case of 50.0497 kg, it is not practical to create a domain based entity to populate the ‘stockbalance’ field.

Hence we complete the screen as shown below:

Note that the ‘free form’ radio button has been checked, the data type has been set to a ‘number’ and the amount of decimal places to 2. Thus once we have completed our entry,  we once again click the diskette to save our attribute and we are returned to the ‘maintain entity’ screen.

I have mentioned ‘color’ a number of times and it is now time to add another attribute (field) to our entity (table) . I know that this time there are a limited number of color that we have and we need to ensure consistency of colors. Regardless of how beige the product may look, we want to categorize it as ‘beige’.

Note above, that I have selected the radio button for ‘Domain Based’. Once we set the radio button to domain based a list of existing entities is show (see the drop down box above). This is the reason that we created the potential domain based entities (points of the star) prior to the ‘Mother’ entity (center of the star).

To recap and summarize:

1)      We have created our first practical model.

2)      We have created two types of entities. A mother and the star points such as color, fabric and type.

3)      We have added attributes to these entities.

In our next episode, we shall be seeing how to load our raw data into our entities and do a small amount of reporting.

Happy programming.