• Article

Exploring SQL Server 2012 Master Data Services (RTM Update)

Version: 1.5

Description

In this lab, you will create a Master Data Services model and entities to store a master list of geographic data. The entity and attribute creation will be achieved using the Master Data Services Add-in for Excel.

Overview

In this lab, you will create a Master Data Services model and entities to store a master list of geographic data. The entity and attribute creation will be achieved using the Master Data Services Add-in for Excel.

Once the data is loaded, Master Data Manager (Web application) will be used to refine the entity definitions, explore the members loaded into the entities, and update attribute values. A derived hierarchy will be created, and also subscription views to support the efficient and convenient retrieval of the master data.

Finally, an Integration Services solution will be opened that includes the LoadDimGeography package. You will first review the package design to appreciate that it uses the subscription views to source the master data. Then you will execute the package to see your data changes flow through to the DimGeography dimension table.

Objectives

The objectives of this lab are to:

  • Create a Model in Master Data Manager
  • Create Entities in Excel
  • Publish data updates in Excel
  • Explore and update entity members in Master Data Manager
  • Create a derived hierarchy
  • Create subscription views
  • Use master data in an Integration Services solution

System Requirements

You must have installed the following items to complete this lab:

  • Microsoft Office 2010
    • Excel
  • Microsoft SQL Server 2012
    • Database Engine
    • Integration Services
    • Master Data Services
    • Master Data Services Add-in for Excel
    • SQL Server Management Studio
    • SQL Server Business Intelligence Development Studio
  • Adventure Works for SQL Server 2012
    • AdventureWorksDW2012
  • You will need to know the URL for the Master Data Manager Web application and the name of the MDS Repository database.

Setup

The setup and configuration for this lab involves running a Setup script included with the training kit. To run all setup tasks for this lab:

Note:
This process may require administrative privileges.
  1. Open a Windows Explorer window and browse to the lab’s Source folder.
  2. Double-click the Setup.cmd file in this folder to launch the setup script and run all setup tasks for this lab.
  3. If the User Account Control dialog is shown, confirm the action to proceed.
  4. When the Server Alias dialog shows up, click OK.
  5. Click Close after the alias is created.
  6. Press any key once the script finishes updating the databases.

Cleanup

There is no need to cleanup if you intend to continue the sequence of labs in this training kit.

  1. To restore the original state of the AdventureWorksDW2012 database, open a Windows Explorer window and browse to the lab’s Source\Setup folder.
  2. Double-click the Cleanup.cmd file in this folder to launch the cleanup script.
  3. If the User Account Control dialog is shown, confirm the action to proceed.
  4. When the Server Alias dialog shows up, click OK.
  5. Click Close after the alias is created.
  6. Press any key once the script finishes updating the databases.

Exercises

This lab comprises the following exercises:

  1. Creating the Model
  2. Creating and Managing Entities in Excel
  3. Managing and Exploring the Model
  4. Extracting Master Data

Estimated time to complete this lab: 30 minutes

Feedback

Your feedback is welcome! Please, post it on our UserVoice forums.

next >

Downloads

Download The Offline Training Kit
Download Hands-on Lab Document (DOCX)
Download Hands-on Lab Source Files

Contents