Export Data to Excel from Master Data Services
Applies To: SQL Server 2016
In the Master Data ServicesAdd-in for Excel, you must export data from the MDS repository in order to work with it.
If you want to filter the dataset before loading, see Filter Data before Exporting (MDS Add-in for Excel) instead.
To perform this procedure:
- You must have permission to access the Explorer functional area.
Open Excel and on the Master Data tab, connect to an MDS repository. For more information, see Connect to an MDS Repository (MDS Add-in for Excel).
In the Master Data Explorer pane, select a model and version. The list of entities is populated.
If the Master Data Explorer pane is not visible, in the Connect and Load group, click Show Explorer.
If the Master Data Explorer pane is disabled, it is because the existing sheet already contains MDS-managed data. To enable the pane, open a new worksheet.
In the Master Data Explorer pane, in the list of entities, double-click the entity you want to load.
Only the first one million members are loaded into Excel. To filter the list before loading, on the ribbon in the Connect and Load group, click Filter.
In columns that are constrained lists (domain-based attributes), by default only the first 25,000 values are loaded. You can change this number in the MaximumDbaEntitySize property in the excelusersettings.config file located on the computer that Excel is installed on. This file is located in C:\Users\<user>\AppData\Local\Microsoft\Microsoft SQL Server\130\MasterDataServices\.
If a domain-based attribute has number of values that exceeds the MaximumDbEntitySize property setting, the list of values is not loaded.
When you load text-delimited data using the Add-in for Microsoft Excel with 32-bit Excel, and the settings for the Cell Count to Load and Cell Count to Publish properties are both set to the maximum of 1000, an out-of-memory error will occur. You have to use 64-bit Excel to use the maximum settings for Cell Count to Load and Cell Count to Publish.