Master Data Services (MDS) is a SQL Server tool for Master Data Management (MDM). It consists of several components, including a web application (Master Data Manager) and an add-in for Microsoft Excel. The first one allows you to create and manage models and business rules, and the add-in allows you to manage data and create new entities and attributes directly from Microsoft Excel.
Through this practical guide you can follow the steps below to create models, entities, attributes and hierarchies and also manage data using the add-in for Excel. But before, it is important to understand some basic concepts:
- Model – Works like a data container. It’s the highest level of data organization, defining its structure. A model contains entities, which contain attributes, hierarchies and collections.
- Entity – Object contained in a model. Each entity contains members, which are the rows of data and attributes, which correspond to columns. Entities can contain explicit hierarchies, collections, or be used to build derived hierarchies.
- Member – Physical data of an entity (e.g.: a given customer in a “Customer” entity) described by the attributes of that entity.
- Attribute – Object contained in an entity. Attributes describe the members of an entity (e.g.: “Name” or “Address” in entity “Customer”) and can be organised in groups.
- Domain-based attribute – Attribute whose values are populated by members from another entity (e.g.: entity “Subcategory” has an attribute “Category” that is a domain-based attribute, whose values come from the same attribute in entity “Category”).
- Explicit hierarchy – Hierarchy based on a single entity, structured in a way specified by the user.
- Derived Hierarchy – Hierarchy derived from the domain-based attribute relationships that already exist in a model. In a derived hierarchy members from an entity are used to group the members of another entity.
- Collection – Group of members from an entity.
- Subscription view – SQL view in MDS database; can be created and maintained from MDS web application. Subscription views are created when it is wanted to create a view from data to use in a subscription system.
- Version flag – Can be set to identify the model version that the subscribers or subscription systems should use.
Below are the basic steps that allow the creation of structures.
Create a Model
In MDS web page, go to “System Administration” area.
Go to “Manage” and click “Models”
Click “add model” (“+”)
Insert a name to the model and click “save model” (create entity with the same name, create hierarchy with the same name and mandatory hierarchy can be optionally selected).
Create an Entity
Click “Manage” > “Entities”
Select a model from the list and click “Add entity” (“+”)
Insert a name to the entity (optionally, another name can be assigned to test tables). You can opt to create code values automatically and the starting value (>=1) and choose if you want collections and hierarchies (can be changed later). Click “save”.
Create an Attribute
In page “Entity maintenance” (“Manage” > “Entities”), choose the model and select the line corresponding to the entity for which you want to create the attribute. Click “Edit selected entity”.
Click “add leaf attribute” (“+”)
Choose between “Free-form” “Domain-based” or “File”. To free-form attributes, choose a name to the attribute, the data type, length and also the pixel width – the width that the column will present. Click “save” and in entity page click “save entity”. “Domain-based” option should be selected if you want to create an attribute based in another entity attribute (see concepts, in the beginning of this article). “File” option should be selected when you want to use a file as an attribute (e.g.: photography).
Create a derived hierarchy
Click “Manage” > “Derived hierarchies”, select the model and click “add derived hierarchy” (“+”). Name the hierarchy and click "save derived hierarchy."
In the page “Edit derived hierarchy”, click on an entity or hierarchy and drag to the section “current levels”. Build the hierarchy. Click “Back”.
Structure management and visualization
Create version flags
In MDS page, click “Version management”.
In the menu bar, pass the mouse through “Manage” and then click “Flags”.
Click “Add”. Choose a name, a description and if you want to flag committed versions only (by choosing false, the flag can be assigned to versions with any status). Click “Save”.
Assign a flag to a version
In “Version Management” page (see above), in the intended version, double-click the cell corresponding to “Flag”
Choose a flag from the list.
Press the "Enter" key to save the changes.
Create a subscription view
In MDS page, go to “Integration Management” area.
Select “Create views” in menu bar.
Click in “add subscription view” (“+”)
Choose a subscription view name, a model, a version or a version flag (this one is recommended and allows to reassign the view to a new open version, when an old one is locked, without updating the subscription view) and choose the remaining parameters on which you want to create the view. Click “Save”.
Data maintenance and management – Master Data Services Add-in for Microsoft Excel
The Add-in for Microsoft Excel, available in the MDS page, allows the management and distribution of organized data through MDS by anyone in a company allowing data loading in Excel. Data can be dealt like any other data in Excel and then loaded back to MDS and stored in a database (SQL Server). An administrator can use the add-in to create entities and attributes and insert data related to them. Data Quality Services (DQS) can be used to validate if data is already in MDS and avoid duplicates.
Create a connection and connect to MDS repository using excel
After installing the add-in, open Microsoft Excel. You will see a new "MasterData" tab. In this tab, click the arrow below the "Connect" and select "Manage Connections".
In the new window select "Create a new connection" and click the "New" button.
Add a description and the MDS Server address and click "OK".
Select the connection and click "Test" to test the connection. Then click "Connect."
Import data from MDS repository
Connect to MDS repository. If you already have created connections click the arrow below the "Connect" and select the desired connection.
Select a model and version in the panel "Master Data Explorer" (if this panel does not appear, click "Show Explorer" in the "MasterData" tab)
You can filter the data before importing, choosing an entity in the panel "Master Data Explorer" and clicking "Filter" in "MasterData" tab. Uncheck the attributes that you do not want to import and add line filters if necessary. At last, click "Load Data". If you do not want to apply filters just double-click on the entity to import.
- Each sheet will represent an entity/table of the model and the “Master Data Explorer” panel is not available if the open sheet already contains MDS data. To import data from another entity, you must create a new sheet.
- You can save a shortcut to a dataset using the “Save Query” button, option “save the query.” To access the data via the shortcut, go to the “Manage Queries” option of the same button and click “Load”. You can also send a shortcut as file by email through the “Send Query” option.
- If there are business rules applied to an entity, click “Apply Rules” to validate the data.
Publish data in MDS repository
When changes are made or data are added to an entity, you can publish the data in the MDS repository. Note that when an entity is set to have codes generated automatically it is not necessary to specify the attribute "code" of the entity in question.
To publish data click "Publish" in "MasterData" tab. If you see the "Publish and anotate" window click "Publish" again (add comments if desired).
Data validation is performed, and if there are errors, you can see the description of the error ("show status" button).
Manage published data
You can review all transactions and changes made to published data, as well as notes that have been added, by selecting a cell in the line whose transaction you want to view and clicking on the right mouse button and selecting "View Transactions".
A window like the one shown below will appear. You can also enter new notes for each line.
Delete Lines (members)
You can delete published data by selecting one or more complete lines (clicking the header /line number) and clicking "Delete" button from the "Master Data" tab. Will pop up a dialog box to confirm whether you want to delete the selected rows. Click yes.
Note: When a member is deleted, the same code will not be available again for a new member. To delete records from the database and not only from the entity you must run a stored procedure generated by MDS, providing the necessary parameters, that will delete or update records from the respective tables based on these parameters (see section “Create, update, delete or inactivate leaf members”).
You can combine MDS data with other data. To do this click the "Match Data" button in the sheet that contains MDS data.
In the dialog box that appears, select the data range to match (in "Range to combine with MDS data" click on the icon to minimize the window (marked with an arrow in the image below), click on the sheet with data to combine and select the data range that you want to combine, including headers; click the icon to expand the dialog box), select the columns to match and click "combine".
A new "Source" column will appear to indicate which data comes from an external source and which comes from MDS.
Create an entity using the excel Add-in
You can create a new entity in an existing model directly from Excel through the "Create entity" button from "Master Data" tab. To do this, create a new sheet with the data to load to MDS, including headers. Select the cells with the data to load. Click the "Create Entity" button of the "Master Data" tab. In the new window select the model and the version to which you want to add the entity. Choose a name to the entity, and select the column corresponding to code (the option "automatically generate code" can be selected) and the column corresponding to name. At last, click "OK".
Create an attribute using the excel add-in
To create a new attribute using Excel, just add a new column to an existing entity and click "Attribute Properties" in "Mater Data" tab. In the new window, choose the name, the attribute type and other properties that depend on the type chosen. Click "ok".
Create a domain-based attribute
To create a domain-based attribute, choose the "Constrained list (Domain-based)" option in the attribute type (in the window mentioned above). In option "Populate the attribute with values from" select the entity from which you want to populate the new attribute. Choose "the selected column" if you want to use data from current sheet. Click "Ok". In each cell of the attribute a "drop-down" will let you choose a value for that attribute.
Create, update, delete or deactivate leaf members
Leaf member staging table
To create, update, delete or deactivate leaf members (Leaf members are members by default, consolidated members exist only when explicit hierarchies and collections are enabled for that entity) is required to fill a staging table ("stg.name_Leaf" where "name" is the name defined when the entity was created). There are also staging tables for the consolidated members and relationships.
Below is described what purpose has each field (column) of the table:
Identifier generated automatically.
- ImportType (Required)
Determines what to do when data in this table already exists in MDS database. Possible values and respective explanation are defined in the table below:
|0||Create new members. Replace existing MDS data with data in the staging table (if the staged data is not NULL). NULL values are ignored.|
|1||Create new members only. Do not update existing MDS data.|
|2||Create new members. Replace existing MDS data with staged data, including NULL values.|
|3||Deactivate the member, based on the Code value, being this no longer available for the user (but not being deleted). If the member is used as a domain-based attribute the deactivation will fail.|
|4||Permanently delete the member, based on the Code value. If the member is used as a domain-based attribute the deletion will fail.|
|5||Deactivate the member, based on the Code value, being this no longer available for the user (but not being deleted). If the member is used as a domain-based attribute, the related values will be set to NULL.|
|6||Permanently delete the member, based on the Code value. If the member is used as a domain-based attribute the related values will be set to NULL.|
- ImportStatus_ID (Required)
The status of the import process. Possible values are:
0: indicates that the record is ready for staging process.
1: it is automatically assigned and indicates that the staging process for the record has succeeded.
2: it is automatically assigned and indicates that the staging process for the record has failed
- Batch_ID (Required by web service only)
An automatically assigned identifier that groups records for staging. All members in the batch are assigned this identifier.
- BatchTag (Required, except by web service)
A unique name for the batch.
Displays an error code.
- Code (Required, except when codes are generated automatically)
A unique code for the member.
- Name (Opcional)
A name for the member.
Used only when you want to change the code of a member.
- <Attribute name>
A column exists for each attribute in the entity. Use this with an ImportType of 0 or 2. For free-form attributes, specify the new value for the attribute. For domain-based attributes, specify the code for the member that will be the attribute
For more details see https://msdn.microsoft.com/en-US/library/ee633854.aspx
Staging Stored Procedure
After the staging table is filled, a stored procedure is used - "stg.udp_
|The name of the version. This may or may not be case-sensitive, depending on your SQL Server settings.|
|Determines whether transactions are logged during the staging process. Possible values are:
0: Do not log transaction;
1: Log transaction.
Required, except by web service
|The BatchTag value specified in staging table.|
Required by web service only
|The Batch_ID value specified in staging table.|
|User Name||Optional parameter.|
|User ID||Optional parameter.|
This stored procedure allows you to create, update, delete or deactivate members, based on the fields filled in the staging table.
For more details see https://msdn.microsoft.com/en-US/library/hh231028.aspx
For easier filling of staging table and run the stored procedure, providing it the required parameters, you may use another stored procedure that performs these tasks, or create a SSIS package that automates the process. This SSIS package will have a structure similar to that shown in the images below.
In the control flow, there should be a data flow and an "Execute SQL Task" object, and in the last one the mentioned stored procedure is executed (example: “EXEC [stg].[udp_Cliente_ClientePai_Leaf] @VersionName = ?, @LogFlag = ?, @BatchTag = ?”), providing it with the appropriate parameters, previously defined as variables in the package:
The data flow will have a data source, an object "Derived Column" and an "OLE DB Destination" and it is where the filling of the staging table is performed.
In derived column, columns are added to data that came from the data source, to fill the staging table with the required values:
The target table is the staging table and the mappings are set on the target object:
In the example above the staging table is populated based on a data source and two added columns (ImportType = 2, which states that you want to add new members and update old members; BatchTag, which identifies the batch) and then a stored procedure run, and according to the BatchTag, the version name and LogFlag will proceed to ImportType defined for each member with these parameters.
Based on the logic applied in this package, you can create a procedure stored to perform the same tasks instead.