An Introduction to Master
Data
Carlton B Ramsey
@eccentricDBA
About Me
•
Application Developer turned DBA with over a decade of
experience in the banking industry.
•
User Group Member
• Ohio North SQL Server User Group (ONSQLSUG)
• Association of Information Technology Professionals (AITP) • Northeast Ohio Information Security Forum (NEOISF)
• Northeast Ohio Oracle User Group (NOOUG)
•
Data Platforms
• Microsoft SQL Server • Oracle
What is Master Data
•
Nouns
• Products • Customers • Employees•
Reference Data
• US Postal AddressesDomains and Hierarchies
•
Domains are groupings of related business data
• Customer (ID, Name, Gender, Birth Date)
•
Hierarchies are relationship between different domains
• Manager -> Employee • Customer -> Products
What is Master Data Management?
•
Master Data Management (MDM) is the process of
centralizing master data so we can do the following:
• Identify Data Discrepancies • Increase Effectiveness
Components of Master Data Management
•
Business Policies
•
Processes
History of Master Data Management
•
Master Files
•
Enterprise Resource Planning (ERP) Systems
•
Specialized Master Data Management Solutions
• Customer Data Intergration (CDI)
The Cloud
• As we move towards a service oriented architecture and mashup
data between various cloud services. How do we...
• Ensure we send only one email to a customer? • Update all systems when an email is changed?
Master Data Management and Data
Warehouses
•
Master Data Management (MDM) facilitates data integration
Typical Data Warehouse
Data Warehouse with MDS
Master Data Services Terminology Overview
• Domain – subject area; what you are managing (i.e. customer, product). Grouping of related business data that is an area
of focus for a master data management solution
• Model – Models are the highest level of organization within MDS. Models are nothing but containers of related entities.
Only entities within the same model can be related within MDS
• Entity [Table] – a container for a set of members. Contains attributes. Entities are the base containers for data in MDS. In
their simplest form, entities can be thought of as tables in a database. Users control the attributes (columns) that are managed for each entity. If explicit hierarchies are enabled for an entity, the entity becomes far more complex, managing parent members and their consolidations as well as collections, their attributes, and the members associated with those collections
• Member [Rows in a table] – a data element; classified as a leaf, consolidated, or collection member type. Members are
the records that populate all the entities created in MDS. Leaf members are the primary members of an entity. If an entity is enabled for explicit hierarchies and collections, then consolidated members can be created, and can have their own
attributes
• Attribute [Column] – a property of entity members. Attribute types include free-form, file, or domain-based. Attributes
describe members. Attributes can be loosely thought of as columns in a table. Entities contain members and their attribute values
• Domain-based Attributes – Domain-based attributes are attributes in which the available values are restricted to the
members stored in a related entity
• Hierarchy – organization of members into various consolidated groups and levels to support analytic requirements. There
are two management types for hierarchies in MDS: explicit and derived. Hierarchies enforce rules for member inclusion to ensure consolidations do not lose or double count values in connected applications
• Collection – user-defined subset of leaf and consolidated members. Collections provide member grouping flexibility that is
not supported in hierarchies
• Business Rule – business logic that is intended to invoke a certain Master Data Services action
• Subscription View – provides a layer over the MDM tables, denormalizing the master data and applying recognizable
names to columns, making them much easier to work with. This allows for integration with downstream systems dependent on master data, hierarchies, and collections
• Canonical – standardized data definitions/common data model
What do I need to use MDS?
Source: http://msdn.microsoft.com/en-us/library/ee633744.aspx
• Web Server (IIS) (See source link for Roles, Features, Accounts
and Permissions required )
Deploying MDS Samples in SQL Server 2012 ( http://blogs.msdn.com/b/
jason_howell/archive/2011/12/15/deploying-mds-samples-in-sql-server-2012.aspx )
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:\Windows\system32>Cd "C:\Program Files\Microsoft SQL Server\110\Master Data Services\Configuration"
C:\Program Files\Microsoft SQL Server\110\Master Data Services\Configuration>MDSModelDeploy.exe listservices MDS services (Service, Website, Virtual Path):
MDS1, Default Web Site, MDS
MDSModelDeploy operation completed successfully. Elapsed time: 00:00:07.3668549
C:\Program Files\Microsoft SQL Server\110\Master Data Services\Configuration>MDSModelDeploy.exe deploynew -package "C:\Program Files\Microsoft SQL Server\110\Master Data Services\Samples\Packages
\chartofaccounts_en.pkg" -model ChartOfAccountsSample -service MDS1
Deploying package C:\Program Files\Microsoft SQL Server\110\Master Data Services\Samples\Packages \chartofaccounts_en.pkg using new model name ChartOfAccountsSample
MDSModelDeploy operation completed successfully. Elapsed time: 00:01:04.9481359
Additional Resources
• Ira Whiteside's Articles ( http://www.sqlservercentral.com/Authors/
Articles/Ira_Whiteside/382777/ )
• James Serra's Blog ( http://www.jamesserra.com/archive/category/
mdm/ )
• Getting Started with Master Data Services (MDS) In SQL Server
2012 By Arshad Ali ( http://www.databasejournal.com/features/ mssql/getting-started-with-master-data-services-mds-in-sql-server-2012.html )
• YouTube Search Master Data Management ( http://
www.youtube.com/results?search_query=master+data +management )
• Master Data Services Portal (http://msdn.microsoft.com/en-us/