Maintaining a “single source of truth” is critical for all organizations. There are many Master Data Management (MDM) tools out there to help achieve this. In this article, we’re going to look at some Master Data Management tools for SQL Server.
Introduction
Master Data Management (MDM) is an information management discipline focusing on data quality and data governance. Its goal is to mitigate problems caused when Master Data is inconsistent, fragmented, or out of date. These problems can range from minor irritations, such as duplicate entry of data in systems to larger organizational issues relating to data ownership, integrity, and efficiency.
The over-arching goal for MDM is to create a “single version of the truth” for your Master Data (i.e. customers, products, employees, and physical assets). This is normally easy to achieve when organizations are very small, but as they grow, it becomes clear that some sort of strategy needs to be identified to manage data. A simple example is when a small organization uses its accounting software to keep information on customers, but what happens when they grow and add some other tools such as a CRM – do they duplicate customer information in both places, or try and integrate the two?
Many organizations use SQL Server as their database platform and have in-house skills in this area. It is therefore tempting to use SQL as a tool to help achieve Master Data Management objectives as opposed to a specific MDM platform like Profisee. In this article, we’ll provide a brief overview of the Master Data Management tools for SQL Server.
What is Master Data Management?
Master Data Management (MDM) is a discipline that focuses on data quality and data governance in an organization. An MDM initiative typically has the following main themes:
- It relates to a company’s Master data (e.g. business entities such as customers/suppliers, employees, SKUs, product descriptions, and locations)
- The over-arching goal is to create a “single version of the truth” for your Master Data (e.g. customers, products, employees, and physical assets)
- It is a set of disciplines, processes, and technologies
- It covers applications, systems, and databases
- It spans both business and IT realms
- There is a focus on continuous data improvement
- It requires a set of data quality dimensions to be defined (e.g. timeliness, accuracy, completeness, meaning) by the business
- It often involves unifying data from multiple disparate systems or sources
- It is hard to do
The last point relates to some of the difficulties associated with implementing MDM. The main reasons for the failure of MDM initiatives are a lack of executive support and an underestimation of the complexity of what is involved. The first critical step in the process is therefore to identify what business problems you are trying to solve with MDM and make sure that senior stakeholders buy into the process and give it their full support.
It is also important not to be over-awed by the process, and to start small with a simple strategy with realistic objectives. In a later article, we’ll look at some simple lightweight MDM strategies.
What are the main Master Data Management tools for SQL Server?
There are several options when it comes to master data management tools for SQL Server:
- Master Data Services
- In-house application using SQL Server as a backend
- SQL Server Management Studio
- Excel and SQL Spreads
In the following sections, we’ll look at the main features of each of the tools and the benefits/drawbacks of each.
Master Data Services (MDS)
Features
Master Data Services (MDS) is Microsoft’s platform for supporting the discipline of MDM. It ships as part of the Microsoft SQL Server and can be enabled as a feature when you run the SQL Server setup.exe.
MDS allows you to manage a master set of your organization’s data, which typically involves the following:
- Organizing master data using a model data structure
- Creating rules for updating the data
- Controlling who updates the data
- Sharing the master data set with key stakeholders
The MDS installation consists of the actual MDS database, as well as a web UI which is used to configure the database and manage the MDM processes such as creating models and business rules and assigning permissions. There is also an Excel Add-In that allows users to perform updates to data.
Benefits
The benefits of using MDS are:
- If you already use SQL Server in your organization, then you already have access to MDS – it just needs to be installed and set up.
- It is an enterprise-grade solution for a Master Data Management initiative.
- The release of the Master Data Services Excel Add-In makes it easy for a wide range of users to manage their master data.
Drawbacks
The drawbacks of using MDS are:
- The installation process is not particularly straightforward and could be more user-friendly.
- MDS may be too much for some organizations that are just starting out and would like to perform a simplified version of MDM; conversely, it is not feature-rich enough for larger, more mature organizations.
- Microsoft has started to discontinue certain MDS features, which means full End of Support is probably not far away.
In-house application using SQL Server as a backend
Features
Microsoft’s MDS basically consists of an MDM database and a web UI to configure and administer models, rules, permissions, etc. For some organizations that are comfortable building in-house applications, it would be relatively straightforward to create something similar to MDS but tailored specifically to their needs. This could include focusing on certain UI aspects to make the tool more user-friendly to use, or process-related features such as approval workflows.
Advantages
The benefits of building an in-house application are:
- In-house applications can be tailored specifically to an organization’s needs.
- An iterative approach to the development of the in-house application has the benefit of being able to start small and add features as required.
Drawbacks
The drawbacks of building an in-house application are:
- There is a risk of diverging from MDM best practices.
- Although it can be relatively inexpensive and quick to get an initial version of an in-house application released, the total cost over a number of years can end up being more than purchasing an off-the-shelf solution.
- There is a reliance on an in-house development team (or 3rd party consultants) and a risk that knowledge is limited to a few team members as opposed to a large community of users for commercial solutions.
- New features or fixes will need to be managed as part of a change/release process that requires resources.
SQL Server Management Studio
Features
Some organizations choose to take certain elements of Microsoft’s MDS solution and replicate them in a bespoke MDM database. The process of managing data in the MDM database is then performed from within SQL Server Management Studio. This requires a fair amount of database expertise (to create the relevant jobs and stored procedures to take data from staging to final tables, for example).
This option obviously has no web UI as the MDS solution does, and so the process of creating data models and rules needs to be designed and implemented by a team consisting of BAs and database developers.
Advantages
The benefits of designing an MDM database solution and managing it via SQL Server Management Studio (SSMS) are:
- A simple MDM database solution can be created relatively quickly.
- For organizations that need to update relatively small amounts of Master Data, or who do so infrequently, this can be an appropriate solution.
Drawbacks
The drawbacks of creating a solution solely with SSMS are:
- Certain technical skills (i.e. database development) are required to build the solution
- Updates to the data in the MDM database need to be performed by people with the relevant SQL skills via SSMS
- There is little visibility for general users into the structure of data models and update rules
Excel and SQL Spreads
Features
This solution extends on the SQL Server Management Studio option by incorporating Excel into the design via the SQL Spreads Excel Add-In. SQL Spreads allows non-technical users (with the relevant permissions) to connect to SQL Server and view and modify data in tables – all from the familiar Excel environment.
SQL Spreads also has features such as data validation and change tracking so that these important aspects can be managed from Excel, rather than SSMS, allowing a wider range of users to use the MDM solution.
In the screenshot below, I’ve loaded the Product table from the AdventureWorks database into Excel using the SQL Spreads Add-In. I can now make updates to certain columns in this table, with the changes being tracked and validated against business rules.
Advantages
The benefits of using a solution comprising SQL Server and Excel (via SQL Spreads) are
- A simple MDM database solution can be created relatively quickly.
- It is very quick and easy to update data or add new tables
- For organisations that need to update relatively small amounts of Master Data, or who do so infrequently, this can be an appropriate solution.
- A wide range of users can view and update master data directly through Excel
Drawbacks
The drawbacks of using a SQL Server and Excel (via SQL Spreads) solution are
- Basic database skills are required to build the solution.
Summary
Master Data Management (MDM) is a discipline that almost all organizations follow to some extent. As businesses grow, it becomes necessary to formalize an MDM strategy, which includes putting in place tools and procedures. For organizations that use SQL Server as their database platform, it can make sense to use a SQL-based tool to kick-start an MDM initiative, rather than purchasing a commercial solution like Profisee.
In this article, we’ve provided a brief overview of some Master Data Management tools for SQL Server.
- Microsoft’s Master Data Services (MDS) is a good place to start as it comes packaged with the SQL Server suite – you simply need to install and configure it. It has the main components of an MDM system such as data models, business rules, and permissions/roles. Be aware, however, that Microsoft has started to deprecate certain features and so it may not be supported for too much longer. MDS may also be overkill for small organizations that just want a simple way to view and update a small set of master data.
- For organizations that are comfortable with developing in-house applications, it is tempting to look at MDS and make a more lightweight and customized version of it. This could be a database application created and administered through SQL Server Management Studio (SSMS) or a SQL database with a web UI (similar to MDS). This approach needs clear guidance from senior management to make sure that best practices are maintained and business goals are achieved. The total cost over the full life can also end up being much higher than originally anticipated.
- Finally, we looked at how the SQL Spreads Excel Add-In can be used to view and manage master data that is stored in tables in SQL Server. This option has the advantage that a wide range of users can manage their master data (through a permissions model) from the familiar environment of Excel, whilst maintaining governance through change tracking and validation rules.
If you have any questions about using SQL Server to start an MDM initiative, please contact us. You can also download a free trial of SQL Spreads and see how easy it is to manage your SQL data from within Excel.
