Master Data Services Add-in for Microsoft Excel
Applies To: SQL Server 2016 Preview
With the SQL Server Master Data Services Add-in for Excel, master lists of reference data can be distributed to everyone at your organization who uses Excel. Security determines which data users can view and update.
You can load filtered lists of data from MDS into Excel, where you can work with it just as you would any other data. When you are done, you can publish the data back to MDS, where it is centrally stored.
If you are an administrator, use the Add-in for Excel to create entities and attributes and to load them with data. This eliminates the need to use any other tools to load data into your models.
In the Add-in for Excel, you can use Data Quality Services (DQS) to match data before loading it into MDS. This helps to prevent duplicate data in MDS.
You can download the SQL Server 2016 CTP2 Master Data Services Add-in for Excel from the Microsoft Download Center.
When working with the Add-in, you may encounter the following terms.
The MDS repository is where all master data is stored. It is a SQL Server database that is configured to store MDS data. To work with data from the repository, you load data it into Excel; when you’re done working with it, you publish changes back to the repository. Administrators can add new entities and attributes to the repository.
MDS-managed data is data that is stored in the MDS repository and that you load into Excel, where the data is displayed as highlighted rows. You can add data that is not MDS-managed to your worksheet, and it is not affected when you refresh the MDS-managed data.
A model is a container of data. Versions of these containers can be created, and usually the latest version is the most recent. For more information, see Models (Master Data Services).
An entity is a list of data. You might think of an entity as a table in a database. For example, the Color entity might contain a list of colors. For more information, see Entities (Master Data Services).
A member is a row of data. Each entity contains members. An example of a member is Blue. For more information, see Members (Master Data Services).
An attribute is a column of data. Each member has attributes. For example, the Code attribute for the Blue member is B. For more information about attributes, see Attributes (Master Data Services).
Create a connection to a Master Data Services repository.
Load MDS-managed data into Excel.
Save a shortcut query that you can use open the currently displayed MDS-managed data in the future.
Share shortcuts with others.
View all changes that have been made to a member.
Before publishing new data, find out whether duplication exists.
Publish data from a worksheet into the MDS repository.
Create a new entity with data in the worksheet. (Administrators only)
Create a domain-based attribute, also known as a constrained list. (Administrators only)
Set properties for loading and publishing data in the Master Data Services Add-in for Excel. (Administrators only)