55073: Master Data Services, Data Quality Services with SQL 2012 -2014 and Excel Training




Per Participant

Course Description

According to studies, data ages at a rate of 2% per month. All companies must rely on accurate information and make sure that data consistency and integrity are maintained across all applications. Microsoft SQL Server 2012-2014 includes Master Data Services, a powerful tool designed to help an organization construct a framework and hierarchy for data so that information gathered and used across numerous departments, silos, and databases are uniform and coordinated. This ensures that several databases may communicate with one another. Excel and Data Quality Services can be integrated to create a whole end-to-end system.

Prerequisites for this training

There are no prerequisites for this course.

Who should attend this course?

Project managers who need to understand how to use Master Data Services as well as SQL professionals, data analysts, business intelligence developers, and data stewards are the target audience for this course.

Microsoft Certified Partner

Microtek Learning is a Microsoft Certified Partner for Learning Solutions. This class uses official Microsoft courseware and will be delivered by a Microsoft Certified Trainer (MCT).



Feb 22, 2023

9:00 am - 5:30 pm EST

Mar 08, 2023

9:00 am - 5:30 pm EST

Mar 22, 2023

9:00 am - 5:30 pm EST
Request Batch

What you will learn

  • Possess a fundamental knowledge of the ideas underlying master data management.
  • Master Data Services configuration.
  • Establish entities and models.
  • Add attributes, such as those that are domain-based.
  • Utilizing the web interface, add a member.
  • Put an action back.
  • Establish and set up attribute groups.
  • Construct and set up derived hierarchies.
  • Construct and set up collections.
  • Using T-SQL, create a leaf member.
  • Utilize the web interface to remove a member, then check the outcomes in SSMS.
  • View batch file errors.
  • Use flat files and SQL Server Integration Services to load leaf members.
  • Versions can be made and configured.
  • Navigate the interface for business rules.
  • Make a corporate rule.
  • Fill a value with data based on another value.
  • Examine and address validity problems.
  • Set up MDS security.
  • Set up and customize a subscription view.
  • Excel can be used to establish a connection to an MDS database.
  • Data can be published from Excel to an MDS database.
  • Utilize Excel to filter data.
  • Query saved to Excel.
  • Excel data can be used to create an entity.
  • Assemble a fresh knowledge foundation.
  • DQS Quality Cleansing Project creation


This module explains how the class will be structured and introduces course materials and additional administrative information.


  • Introduction
  • Course Materials
  • Facilities
  • Prerequisites
  • What We'll Be Discussing

    After completing this module, students will be able to:

  • Successfully log into their virtual machine.
  • Have a full understanding of what the course intends to cover.
  • In this introductory module, we will introduce the need for Master Data Management and discuss a number of high-order concepts introducing the subject area.


  • What is Master Data Management?
  • The Need for Master Data Management
  • Data Quality
  • Master Data vs. Transactional Data
  • Master Data Management Team
  • Master Data Management Architectures
  • The MDM Extract-Transform-Load

    After completing this module, students will be able to:

  • Have a basic understanding of the concepts behind master data management.
  • In this module we will examine a master data management project implementation and describe the concept of the different systems involved. We will then move into key objects within MDS that are critically important for understanding MDS.


  • Project Implementation
  • Master Data Services Key Terms
  • Master Data Services Web Interface
  • Installing Master Data Services

  • Configuring Master Data Services
  • After completing this module, students will be able to:

  • Configure Master Data Services.
  • We touched on the basics of the MDS object model already and covered core concepts such as entities, attributes, members and hierarchies. In this module we will expand on that and take an in-depth look at all the objects in the MDS model. In addition we will examine modeling concepts and deployment.


  • Modeling Process
  • MDS Object - Model
  • The Model Object

  • Create Model and Entities
  • Adding Attributes Including Domain-Based Attributes
  • Adding a Member Via the Web Interface
  • Reversing a Transaction
  • Attribute Groups
  • Derived Hierarchies
  • Collections
  • After completing this module, students will be able to:

  • Create models and entities.
  • Add attributes including domain-based attributes.
  • Add a member via the web interface.
  • Reverse a transaction.
  • Create and configure attribute groups.
  • Create and configure derived hierarchies.
  • Create and configure collections.
  • The objective of this module is to learn how to load data into MDS.


  • MDS Entity-Based Staging

  • Create a Leaf Member with T-SQL
  • Delete a Member with the Web Interface and Examine Results in SSMS
  • Viewing Errors in Batch Files
  • Loading Leaf Members with SQL Server Integration Services and Flat Files
  • After completing this module, students will be able to:

  • Create a leaf member with T-SQL.
  • Delete a member with the web interface and examine results in SSMS.
  • View errors in batch files.
  • Load leaf members with SQL Server Integration Services and flat files.
  • In this module we will learn how to work with versions. The ability to version MDS models enables functionality such as what if scenarios, testing and others.


  • Understanding Versions
  • Web Interface
  • Version Statuses
  • Copying a Version
  • Locking. Unlocking and Committing Versions
  • Version Flags
  • Ancestry
  • Validating Versions

  • Versions
  • After completing this module, students will be able to:

  • Create and configure versions.
  • The goal of this module is to gain an understanding of the business rules interface and how to implement business rules into your model. It is important that you know the interface requires some getting used to and is perhaps not the friendliest interface that you have ever used. Pay particular attention to how often you need to save your business rules.


  • Business Rules Overview
  • How to Create a Business Rule
  • Conditions and Actions
  • Publish a Business Rule
  • Validation Issues

  • Business Rules Interface
  • Create a Business Rule
  • Populate a Value Based on Another Value
  • Validation Issues
  • After completing this module, students will be able to:

  • Navigate the business rules interface.
  • Create a business rule.
  • Populate a value based on another value.
  • Explore and fix validation issues.
  • The goal this module is to gain an understanding of the security in MDS. The security in some ways follows other security paradigms that you may be used to. Such as the concept of read, write, and deny permissions. However, the concepts such as granting permission to the model object for all groups of users so they can view the model may be foreign to you. Also make sure that you understand that MDS does not control security; that it is controlled by active directory. It is within active directory that the passwords and usernames are stored along with the user groups.


  • Master Data Services Security Overview
  • Users and Groups
  • Administrators
  • Permission Levels
  • Best Practices

  • Setting up Security in MDS
  • After completing this module, students will be able to:

  • Set up security in MDS.
  • The objective of this module is that you understand the types of subscription views that are available.


  • Subscription Views
  • Formats
  • How Subscribing Systems Can Extract Data
  • Creating Views

  • Creating a Subscription View
  • After completing this module, students will be able to:
  • Create and configure a subscription view.
  • The objective of this module is to help you understand the Excel Master Data Services Add-in, which is the best feature that was added with the SQL 2012 release.


  • Creating Connections
  • Publishing Data
  • Filtering Data
  • Saving a Query
  • Create an Entity From Data
  • Settings
  • Lab 1: EXCEL ADD-IN

  • Creating a Connection
  • Publishing Data
  • Filtering Data
  • Saving a Query
  • Create an Entity From Data
  • After completing this module, students will be able to:

  • Create a connection to an MDS database using Excel.
  • Publish data to an MDS database using Excel.
  • Filter data using Excel.
  • Save a query to Excel.
  • Create an entity from data in Excel.
  • The objective of this module is for you to understand Data Quality Services and how to use it in conjunction with Master Data Services.


  • Architecture
  • Data Quality Cleansing
  • Microsoft Data Quality Solution

  • Create a New Knowledge Base
  • DQS Quality Cleansing Project
  • After completing this module, students will be able to:

  • Create a new knowledge base.
  • Create a DQS Quality Cleansing Project.
  • With Microtek Learning, you’ll receive:

    • Certified Instructor-led training
    • Industry Best Trainers
    • Official Training Course Student Handbook
    • Pre and Post assessments/evaluations
    • Collaboration with classmates (not available for a self-paced course)
    • Real-world knowledge activities and scenarios
    • Exam scheduling support*
    • Learn and earn program*
    • Practice Tests
    • Knowledge acquisition and exam-oriented
    • Interactive online course.
    • Support from an approved expert
    • For Government and Private pricing*

    * For more details call: +1-800-961-0337 or Email: info@microteklearning.com

    Request Call

    Our Clients

    For many years, Microtek Learning has been helping organizations, leaders, and professionals to reach their maximum performance by addressing the challenges they are facing.

    • 300+ enterprise clients
    • 100,000+ professionals trained
    • Service 70 of the Fortune 100
    • 96% of our clients would recommend us
    our clients

    Our Awards

    our awards
    why choose us



    I was sceptical at first whether to enrol with Microtek Learning or not, however, I am glad that I did- I got everything that was promised (maybe more). The trainer was very patient and knowledgeable and with his effort and mine, I was able to clear the exam with ease! Keep up the good work everyone.



    • (5)

    I'm really impressed with the storytelling skills of the instructor. She makes the session exciting by keeping things simple and easy to understand.

    Prince N.


    • (5)

    I was recommended the ITIL 4 Foundation course by an IT professional who had completed the same course at Microtek Learning. The training gave me a thorough understanding of service management that I felt I could take back to my job as an IT Project Management and apply it to improve the value of products and services.

    Marsh George


    • (5)
    Accredited By

    Course Details

    • Start Date: Feb 22, 2023
    • Duration: 3 Days
    • Skill Level: Intermediate
    • Enrolled: 1685
    • Price: $1595
    side post side mode

    Talk to Learning Advisor