Flash Sale: Save 15% on IT Training

closeClose

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

  • Tuition USD $2,066 Save $230
  • Reviews star_rate star_rate star_rate star_rate star_half 1508 Ratings
  • Course Code 55073
  • Duration 3 days
  • Available Formats Classroom

Studies show that data decays at about 2% a month All organizations need to work from reliable information while ensuring that the integrity of information and consistency of data is constant across different applications. Microsoft SQL Server 2012-2014 Master Data Services is a powerful feature designed to help an organization create a structure and hierarchy for data, so that data collected and used across multiple departments, silos and databases is standardized and aligned. This ensures that different databases are interoperable. When combined with Data Quality Services and Excel a complete end to end system is available.

Skills Gained

  • Have a basic understanding of the concepts behind master data management.
  • Configure Master Data Services.
  • 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.
  • 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.
  • Create and configure versions.
  • Navigate the business rules interface.
  • Create a business rule.
  • Populate a value based on another value.
  • Explore and fix validation issues.
  • Set up security in MDS.
  • Create and configure a subscription view.
  • 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.
  • Create a new knowledge base.
  • Create a DQS Quality Cleansing Project.

Who Can Benefit

This course is intended for SQL Professionals, Business Intelligence Developers, Data Analysts and Data Stewards and Project Managers that need to learn how to utilize Master Data Services.

Prerequisites

  • None

Course Details

Outline


Module 1: COURSE OVERVIEW
This module explains how the class will be structured and introduces course materials and additional administrative information.
Lessons

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

Lab 1: COURSE OVERVIEW
  • There are no exercises for this module.
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.

Module 2: MASTER DATA MANAGEMENT CONCEPTS
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.
Lessons
  • 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

Lab 1: MASTER DATA MANAGEMENT CONCEPTS
  • There are no exercises for this module.
After completing this module, students will be able to:
  • Have a basic understanding of the concepts behind master data management.

Module 3: MICROSOFT MASTER DATA SERVICES OVERVIEW
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.
Lessons
  • Project Implementation
  • Master Data Services Key Terms
  • Master Data Services Web Interface
  • Installing Master Data Services

Lab 1: MICROSOFT MASTER DATA SERVICES OVERVIEW
  • Configuring Master Data Services
After completing this module, students will be able to:
  • Configure Master Data Services.

Module 4: UNDERSTANDING DATA MODELS
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.
Lessons
  • Modeling Process
  • MDS Object - Model
  • The Model Object

Lab 1: UNDERSTANDING DATA MODELS
  • 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.

Module 5: IMPORTING DATA
The objective of this module is to learn how to load data into MDS.
Lessons
  • MDS Entity-Based Staging

Lab 1: IMPORTING DATA
  • 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.

Module 6: VERSION MANAGEMENT
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.
Lessons
  • Understanding Versions
  • Web Interface
  • Version Statuses
  • Copying a Version
  • Locking. Unlocking and Committing Versions
  • Version Flags
  • Ancestry
  • Validating Versions

Lab 1: VERSION MANAGEMENT
  • Versions
After completing this module, students will be able to:
  • Create and configure versions.

Module 7: BUSINESS RULES
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.
Lessons
  • Business Rules Overview
  • How to Create a Business Rule
  • Conditions and Actions
  • Publish a Business Rule
  • Validation Issues

Lab 1: BUSINESS RULES
  • 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.

Module 8: MDS SECURITY
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.
Lessons
  • Master Data Services Security Overview
  • Users and Groups
  • Administrators
  • Permission Levels
  • Best Practices

Lab 1: MDS SECURITY
  • Setting up Security in MDS
After completing this module, students will be able to:
  • Set up security in MDS.

Module 9: PUBLISHING DATA
The objective of this module is that you understand the types of subscription views that are available.
Lessons
  • Subscription Views
  • Formats
  • How Subscribing Systems Can Extract Data
  • Creating Views

Lab 1: PUBLISHING DATA
  • Creating a Subscription View
After completing this module, students will be able to:
  • Create and configure a subscription view.

Module 10: EXCEL ADD-IN
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.
Lessons
  • 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.

Module 11: DATA QUALITY SERVICES
The objective of this module is for you to understand Data Quality Services and how to use it in conjunction with Master Data Services.
Lessons
  • Architecture
  • Data Quality Cleansing
  • Microsoft Data Quality Solution

Lab 1: DATA QUALITY SERVICES
  • 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.

How do I get a Microsoft exam voucher?

Pearson Vue Exam vouchers can be requested and ordered with your course purchase or can be ordered separately by clicking here.

  • Vouchers are non-refundable and non-returnable. Vouchers expire 12 months from the date they are issued unless otherwise specified in the terms and conditions.
  • Voucher expiration dates cannot be extended. The exam must be taken by the expiration date printed on the voucher.

Do Microsoft courses come with post lab access?

Most Microsoft official courses will include post-lab access ranging from 30 to 180 calendar days after instructor led course delivery. A lab training key in class will be provided that can be leveraged to continue connecting to a remote lab environment for the individual course attendee.

Does the course schedule include a Lunchbreak?

Lunch is normally an hour-long after 3-3.5 hours of the class day.

What languages are used to deliver training?

Microsoft courses are conducted in English unless otherwise specified.

Fantastic instructor, good overview of content and ability to answer questions

Both course material and instructor demonstrated a sound foundation on Maximo material

First remote course and I am pleasantly surprised. Love the on site facilities in Ottawa, but the online course format was very well received.

Attended a Power BI class in McLean with this company. The instructor (Mike Staves) was very good and attentive to the groups' learning needs. The facility was very good and the staff was more than accommodating.

Exactly what we needed for our organization, at the right time, at the right price. We'll be back!

0 options available

There are currently no scheduled dates for this course. If you are interested in this course, request a course date with the links above. We can also contact you when the course is scheduled in your area.

Contact Us 1-800-803-3948
Contact Us Live Chat
FAQ Get immediate answers to our most frequently asked qestions. View FAQs arrow_forward