Overview of Master Data Services SQL Server in SSIS

Master Data Management

Master data management (MDM) is the process an organization goes through to discover and define data with the ultimate goal of compiling a master list of data.

Gartner, the well-known technology research and advisory company, defines Master data management as “a technology-enabled discipline in which business and IT work together to ensure the uniformity, accuracy, stewardship, semantic consistency and accountability of the enterprise’s official shared master data assets”(https://www.gartner.com/itglossary/ master-data-management-mdm).

Along with choosing an MDM technology, any project will include an evaluation phase by the business to check and validate records. A successful MDM solution is reliable, centralized data that can be analyzed, resulting in better and more accurate business decisions.

Having a tool that can handle the management of a consistent data source can ease many common headaches that occur during data warehouse projects. For example, say your company recently acquired a former competitor. As the data integration expert your first task is to merge the newly acquired customer data into your data warehouse. As expected, your former competitor had many of the same customers you have listed in their transactional database. You clearly need a master customer list, which stores the most accurate data about customers from your database and also the most accurate data from the newly acquired data set. This is a very typical scenario where having an MDM solution can retain the data integrity of your data warehouse. Without such a solution the customer table in the data warehouse will start to have less accurate information and even duplicates of data.

Master Data Services

Master Data Services (MDS) was originally released in SQL Server 2008 R2 as Microsoft SQL Server’s solution for master data management. Master Data Services includes the following components and tools to help configure, manage, and administrate each feature:

• Master Data Services Configuration Manager is the tool you use to create and configure the database and web applications that are required for MDS.
• Master Data Manager is the web application that users can access to update data and also where administrative tasks may be performed.
• MDSModelDeploy.exe is the deployment tool used to create packages of your model objects that can be sent to other environments.
• Master Data Services web service is an access point that .NET developers can use to create custom solutions for MDS.
• Master Data Services Add-in for Excel is used to manage data and create new entities and attributes.

Again, because this Tutorial focuses on SSIS, a full MDS tutorial is not included; however, this section provides a brief overview of MDS. To gain more understanding, you can also watch the MDS one day course by the Microsoft MDS Team at https://msdn.microsoft.com/en-us/sqlserver/ff943581.aspx.

To get started you must first run the Master Data Service Configuration Manger with the executable MDSConfigTool.exe. This requires the creation of a configuration database that stores the system settings that are enabled. Once the database is created, you can configure the web application called the Master Data Manager. You can find the full setup instructions on MSDN, https://technet.microsoft.com/en-us/library/ee633884(SQL.120).aspx.

Frequently Asked SSIS Interview Questions & Answers

The majority of the work in Master Data Services post-configuration is done by an information worker. They will use both the Master Data Manager web application and the Master Data Services Add-in for Excel. The Excel Add-in is a free download that is available here, https://go.microsoft.com/fwlink/? LinkId=219530.

The Master Data Manager allows the administrator to create new models and derived hierarchy, while the information worker uses the web interface to work with the model and hierarchy relationships. For example, if your company’s data steward found that an entire subcategory of products were placed under the wrong category, then the data steward could use the Master Data Manger to quickly and easily correct that problem. Below screen shot shows the interface that the data steward would use to drag and drop hierarchy corrections.

After installing the Master Data Services Add-in for Excel (Below the First Screen shot), the information worker has MDS in the environment they’re most comfortable in. Using Excel a user can connect to the MDS server, shown in Below Second Screen shot, and then import appropriate data sets into MDS. To do this the user simply selects a table of data in Excel and then clicks Create Entity using the Excel Master Data Ribbon (Below third screen shot).

Any changes that are made through MDS remain in the MDS database. However, when you’re ready to push these changes back to the destination database you can create a simple MDS view through the Master Data Manger to sync the tables using a T-SQL update statement. You would likely schedule these updates to occur once a day or even more frequently depending on your needs.

Because the heavy lifting with MDS is done by the information worker, there is no direct integration with SSIS. Tables that are updated from MDS views are used with traditional components like the Lookup Transformation to ensure incoming new data fits appropriately into the organization’s master list.

List of Related Microsoft Certification Courses: