Master Data Services Add-in for Microsoft Excel

  • Article

Applies to: SQL Server - Windows only Azure SQL Managed Instance

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.

Note

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.

Note

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.Error will be thrown if that limit is exceeded.
  • A range is limited to five million cells for get operations.

Downloads

Note

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

Terms

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).

Task Description Topic
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) Setting properties

Related content