For an instructor lead, in-depth look at learning SQL click below.
Master Data Services, or MDS, is Microsoft’s solution to problems arising from managing and maintaining master data. Master data can be loosely defined as data that is essential for the operations of a business. It does not change often but forms a common reference point for various, often disparate, systems and data.
What is SQL Server Master Data Services?
MDS is part of the Microsoft SQL Server platform, a system designed to handle extensive data management and analysis. It is essentially a master data management (MDM) solution that creates a central repository for master data. The primary objective is to provide a comprehensive method of standardizing data, enforcing data governance policies, and ensuring data stewardship.
Example of SQL Server Master Data Services Code
Before we delve into the code, it is essential to emphasize that MDS is not just about writing SQL code. MDS primarily provides a graphical interface to create models, entities (like a table), attributes (like a column), and create relationships between different entities.
However, underneath that interface hides a whole system of database objects like tables, views, and stored procedures that you can query and manipulate to load and extract data from MDS.
|
1 2 3 4 5 6 7 |
-- Create a new entity (table) in MDS EXEC [mdm].[udpEntityCreate] @ModelName = N'Your Model', @EntityName = N'Your Entity', @<a href="mailto:EntityCreated_ID=@EntityCreated_ID" >EntityCreated_ID=@EntityCreated_ID</a> OUTPUT |
In this example, we are creating a new entity in MDS by calling a stored procedure.
Loading Data into MDS
Data can be loaded into MDS using a variety of methods including the MDS web application, MDS Excel Add-in, and directly into the database. The following script shows how to insert data into an entity.
|
1 2 3 4 5 |
-- Insert data into an entity INSERT INTO [Your Model].[Your Entity] (Code, Name) VALUES ('001', 'New Data') |
Retrieving Data
Data in MDS can be queried using standard SQL language. Once the data models have been structured, data analysts can retrieve specific data using SELECT statements similar to below:
|
1 2 3 4 5 |
-- Retrieve data from an entity SELECT * FROM [Your Model].[Your Entity] WHERE Code = '001' |
The key element of SQL Server Master Data Services is the enhancement of data reliability across different disciplines by maintaining a reliable and consistent source of data. The nature of master data requires it to be accurate, consistent, complete and most importantly, up-to-date. MDS provides these functionalities, and with a little SQL knowledge, you can automate and integrate MDS into any existing SQL Server infrastructure.
In summary, MDS combined with SQL Server can master your data management and security. Whether you’re an aspiring data analyst or a seasoned DBA looking to brush up your skills, MDS and SQL can be robust tools in your data management toolkit.