Master data Services: Excel add-in explored

Master data services is Master data management tool by Microsoft. In latest release SQL Server 2012(RC0), there are few new features added into it. Here is the list:

1)      Excel add in usability improved
2)      Many to Many mapping improved
3)      Collection UI updated, Weights added
4)      Security simplified
5)      Codes automatically
6)      Model deployment performance improved
7)      SharePoint integration introduced

I am going to cover each topic in my posts. Let’s start today by “Excel add in usability improved”.

Before we understand what has improved, let’s look what was there in SQL Server 2008 MDS.

1)      Create Entity: Entity can be created in MDS using Excel add-in. Create data set in excel and then use “Create Entity” from Excel ribbon. If you want to create MDS entity from SQL Server DB table, make connection to DB, import the data into excel and create entity using the same.

2) Add/Update data into existing entities: Excel add-in provide you way to load new data as well as well update the existing data. For it, you need to make connection to MDS and then load the existing data. Now once data is loaded, make changes and use “Publish” functionality in Excel ribbon to load the same into MDS.

3) Combine Data: Excel Add-in can be used to combine data from different source. You can use Data quality component to know duplicate data from other source. For this, load data from MDS entity and new data into separate spreadsheet. Use “Combine Data” functionality present in ribbon to start duplicate analysis using DQ component. Once you specify parameter on DQ wizard, it will give you score(similarity) of new source data rows. Based on it, you can add new record or delete as it may be duplicate.

4) Apply Rules: business rules can be applied on MDS data.

5) Save & Send MDS Queries: MDS data can be filtered/altered/added in excel and then it can be saved in query format. It can be send to another person for validation/verification or making corrections.

Here is list of new changes in Master data services 2012 from MSDN,

The Master Data Services Add-in for Excel has been improved for better usability.

1) In columns that display domain-based attributes, both the name and code are now automatically displayed.
2) The workflow for loading data has changed slightly. In order to load data, you must now open a new worksheet.
3) Changes have also been made to increase the visible workspace. The Master Data Explorer pane now closes when you load data, and information about the active worksheet has been combined into a single row at the top of the sheet.
4) It is now easier to share shortcut query files, which contain information about the server, the model, version, entity, and any applied filters. A new toolbar button allows you to send the shortcut query file to another user via Microsoft Outlook.