Master Data Services Add-in for Microsoft Excel

  • Article

With the SQL ServerMaster Data ServicesAdd-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.

Important

You can continue using the SQL Server 2012 SP1 version of Master Data Services Add-In for Excel after upgrading Master Data Services and Data Quality Services to SQL Server 2014 CTP2. However, any earlier version of the Master Data Services Add-In for Excel will not work after upgrading to SQL Server 2014 CTP2. You can download the SQL Server 2012 SP1 version of Master Data Services Add-In for Excel from here.

Terms

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

Task Description Topic
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. Load Data from MDS into Excel
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. Publish Data from Excel to MDS (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

Related Content