Two major issues have been coming up frequently while using the Master Data Services 2012 Excel add-in. I mentioned the first one earlier: I can’t seem to control the order of attributes in other than the simplest of entities. The MDM move function moves the attributes around, usually, but not to any particular space. When an entity contains dozens of attributes, the add-in can be difficult to work with if the columns are presented in no particular order.

The solution to this problem seems simple: reorder the columns in Excel the way that you wish them to appear, using “cut” followed by “insert cut cells.” Then save the workbook. When you need to work with the entity data again, open that same workbook and refresh the data from MDS. You can keep several entities on different worksheets if you wish.

It is a good idea to be careful to do a “Save” only when you are sure that all the data is in a valid state. If you are publishing changes, make the changes and publish them before saving, making sure that no failures occur. You might want to occasionally back up the workbook to an easily accessible location, just in case.

That works well as long as the add-in doesn’t hang when refreshing. Unfortunately, under the right (wrong) circumstances you may find that the add-in hangs quite a bit, and it offers no “cancel” option. Furthermore, when you re-open Excel, it offers to disable the add-in for you, and if you open it by clicking on the workbook itself, the add-in is disabled without prompting. Once it is disabled you must re-enable it and then close and re-open Excel before you can use it again.

If you encounter a persistent hang when refreshing, try deleting all the data rows in the entity (not the header rows!) using Excel — NOT  using the MDS “Delete” button — before refreshing. So far, that has worked for me.

I do not know yet what will happen when I advance to a new version of the data. At that time it may be necessary to re-create the worksheets, since there doesn’t seem to be an option to change the version. I will have to work that one out when I come to it.

Update: version selection may not be an issue after all. In the add-in settings dialog you can choose the version as newest, oldest, or none. The question now is whether that applies to existing worksheets or only when loading a new one.

Update 7/19/12: The add-in version selection option only applies to new queries. What you can do, however, is create a saved query that specifies the desired column order. You can then edit that query using an XML or text editor and change the version information. It doesn’t preserve column widths, but then you can’t have everything.

The trick is to specify the column order using a filter, instead of physically moving columns around. Once you order the columns that way in Excel, you can save the query and then export it to an XML file. After that, as already noted, you can change the version information in the file, re-import it, and load the new data from the modified query.

The version information includes a display name and a GUID. The easiest way I have found to obtain this information is to load a fresh copy of the desired version of the entity into a new worksheet, save the query, and export it. The column order reverts to that set in the MDS database, but the file contains the new version information elements.

The new name and GUID can then be fished from this file and pasted back into the one needing to be updated — the one that contains the desired column order filter. The XML tags in question, <Name> and <VersionID>, are conveniently located at the end of the file, just before the root node end tag.