Skip to content

Latest commit

 

History

History
86 lines (65 loc) · 8.48 KB

master-data-services-add-in-for-microsoft-excel.md

File metadata and controls

86 lines (65 loc) · 8.48 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom
Master Data Services Add-in for Microsoft Excel
Learn how to load data from Master Data Services into Excel, and then publish it back to MDS by using the Master Data Services Add-in for Excel.
CordeliaGrey
jiwang6
03/20/2023
sql
master-data-services
conceptual
microsoft-excel-add-in

Master Data Services Add-in for Microsoft Excel

[!INCLUDE SQL Server Windows Only - ASDBMI]

With the [!INCLUDEssNoVersion] [!INCLUDEssMDSshort] [!INCLUDEssMDSXLS], 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 [!INCLUDEssMDSXLS] 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 [!INCLUDEssMDSXLS], 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 [!INCLUDEssNoVersion] 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).

Related tasks

Task Description Topic
Create a connection to a [!INCLUDEssMDSshort] 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