Working with Data in the Master Data Services (MDS) Add-in for Excel

  • Article

The following tips should help when you’re working with data in the MDS Add-in for Excel.

If you're having issues installing the Add-in or need other troubleshooting help, see this article.
 

Undo (CTRL+Z)

As of SQL Server 2012 Release Candidate 0, there is no ability to undo changes to data by using CTRL+Z or the Undo Typing button. If you change data and want to undo the change, publish your changes and click the Load or Refresh button to get the latest data.

Cut and Paste

You can paste data into cells but not into header.

Auto Fill

When using Auto Fill (hovering over the corner of a cell until it becomes a + and then dragging to other cells), be aware that numbers will be incremented automatically. This is especially important when working with domain-based attributes that use numbers for Code values.  See this example:

 

If this happens and you have other changes in your worksheet that you don’t want to lose, click Publish. These particular values will cause an error and will not be published. However, any other changes that can be successfully published will be. Then you can refresh the sheet to get the latest data again.

Workaround: You if you press CTRL while you drag, it will not increment the numbers.

Publishing

Only changes made in Excel are published. If you don't have the latest data in your worksheet it doesn't matter. Outdated data is not published. Only changed data is.

Dates

When working with dates, use your local computer's format for input. For example, in the US, you should enter MM/DD/YY or YYYY format. When you press Enter, the date will automatically be converted to the format specified by the DateTime mask of the attribute. If you do not enter dates in the local format, the date can still be saved to the MDS repository; however the format may not match that of other dates.

Data That’s Loaded

Only 1 million rows can be loaded into Excel from the MDS repository.

In domain-based attribute (constrained) columns, a maximum of 1000 items are displayed in the drop-down lists.