Master Data Services Add-in for Microsoft Excel
With the SQL Server Master Data Services Add-in for Excel, you can load filtered lists of data from Master Data Services (MDS) into Excel and then work with it just as you would any other data. When you are done, you can publish the data back to MDS where it's centrally stored. Security level determines which data you can view and update.
If you want to use Add-in for Excel to view or update data, you must enable ActiveX controls in Microsoft Excel, otherwise the data from the MDS database might not complete the load. For more information, see Enable or disable ActiveX settings in Office files.
If you're an administrator, you can use the Add-in for Excel to create entities and attributes, which you can load with data. This process eliminates the need to use other tools to load data into your models.
With the Add-in for Excel, you can use Data Quality Services (DQS) to match data before loading it into MDS. This feature helps to prevent duplicate data in MDS.
Be aware of the following size limitations when interacting with the workbook in MDS Excel add-in.
- Excel on the web has a payload size limit for requests and responses of 5 MB. A
RichAPI.Errorwill be thrown if that limit is exceeded.
- A range is limited to five million cells for get operations.
- Master Data Services Add-in for Excel for SQL Server 2016 SP2.
- Master Data Services Add-in for Excel for SQL Server 2017.
- Master Data Services Add-in for Excel for SQL Server 2019.
- Master Data Services Add-in for Excel for SQL Server 2022.
The Master Data Services Add-in for Excel requires the Office Automation Security to be set to one of the following:
- Level 1 : Macros enabled (default)
- Level 2 : Use application macro security level
When working with the add-in, you might come across 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's a SQL Server database configured to store MDS data. To work with data from the repository, you load it into Excel. When you're done working with it, you publish the changes back to the repository. Administrators can add new entities and attributes to the repository.
MDS-managed data is data stored in the MDS repository. When you load MDS-managed data into Excel, it's displayed as highlighted rows. You can also add data to your Excel worksheet that's not MDS-managed. Such data won't be affected if you refresh the MDS-managed data.
A model is a data container. You can create versions of these containers. The latest version is usually the most recent. For more information, see Models (Master Data Services).
An entity is a list of data, like 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 record or a row of data. Each entity contains members. For example, Blue could be a member of the Color entity. 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
|Load MDS-managed data into Excel.
|Export data to Excel from Master Data Services
|Save a shortcut query to open the currently displayed MDS-managed data in the future.
|Save a shortcut query file
|Share shortcuts with others.
|Email a shortcut query file
|View all the changes that have been made to a member.
|View all annotations or transactions for a member
|Find duplications before publishing new data.
|Match similar data
|Publish data from a worksheet into the MDS repository.
|Import data from Excel to Master Data Services
|Create a new entity by using data in the worksheet. (Administrators only)
|Create an entity
|Create a domain-based attribute or a constrained list. (Administrators only)
|Create a domain-based attribute
|Set properties for loading and publishing data. (Administrators only)
- Connections (MDS Add-in for Excel)
- Overview: Exporting data to Excel (MDS Add-in for Excel)
- Shortcut query files (MDS Add-in for Excel)
- Refreshing data (MDS Add-in for Excel)
- Overview: Importing data from Excel (MDS Add-in for Excel)
- Validating data (MDS Add-in for Excel)
- Data quality matching in the MDS Add-in for Excel
- Building a model (MDS Add-in for Excel)
- Setting properties for Master Data Services Add-in for Excel
- Security (Master Data Services)