Master Data Services Add-in for Microsoft Excel
Applies to: SQL Server (all supported versions) - Windows only Azure SQL Managed Instance
With the SQL ServerMaster Data ServicesAdd-in for Excel, 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. Security determines which data you can view and update.
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.
- Download the Master Data Services Add-in for Excel for SQL Server 2016 SP2 from this Microsoft Download Center page.
- Download the Master Data Services Add-in for Excel for SQL Server 2017 from this Microsoft Download Center page.
- Download the Master Data Services Add-in for Excel for SQL Server 2019 CTP from this Microsoft Download Center page.
The Master Data Services Add-in for Excel requires the Office Automation Security level to be set to 1 (Macros enabled), the default, or 2 (Use application macro security level).
When working with the Add-in, you may encounter the following terms. For more information about these concepts, see Master Data Services Overview (MDS).
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 (a record). 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.||Connect to an MDS Repository (MDS Add-in for Excel)|
|Load MDS-managed data into Excel.||Export Data to Excel from Master Data Services|
|Save a shortcut query that you can use open the currently displayed MDS-managed data in the future.||Save a Shortcut Query File (MDS Add-in for Excel)|
|Share shortcuts with others.||Email a Shortcut Query File (MDS Add-in for Excel)|
|View all changes that have been made to a member.||View All Annotations or Transactions for a Member (MDS Add-in for Excel)|
|Before publishing new data, find out whether duplication exists.||Match Similar Data (MDS Add-in for Excel)|
|Publish data from a worksheet into the MDS repository.||Import Data from Excel to Master Data Services (MDS Add-in for Excel)|
|Create a new entity with data in the worksheet. (Administrators only)||Create an Entity (MDS Add-in for Excel)|
|Create a domain-based attribute, also known as a constrained list. (Administrators only)||Create a Domain-based Attribute (MDS Add-in for Excel)|
|Set properties for loading and publishing data in the Master Data Services Add-in for Excel. (Administrators only)||Setting Properties for Master Data Services Add-in for Excel|