Master Data Management & Microsoft Master Data Services

1. Master Data Management & Microsoft Master Data Services Presented By: Jeff Prom Data Architect MCTS - Business Intelligence (2008), Admin (2008), Developer (2008)

2. Agenda • What is Master Data Services? • What is Master Data Management? • Installation & Configuration • MDS Modeling Concepts • Features & Functionality • Excel Add-in • Demo • Questions / Wrap up

3. What Is? Master Data Services Master Data Management 10 | 43

4. What is Master Data Services? • Microsoft's solution to Master Data Management • Traditionally MDM solutions have been expensive, complex, and focused on a single domain. • Domain = Group of related data in a focused area. • MDS is flexible and can be used with multiple domains. It is relatively inexpensive and easy to use. 11 | 43

5. What is Master Data Management? • Master Data Management (MDM) • The management of non-transactional data. • Managing relationships between data groups. • Managing hierarchies within data groups. • Empower data stewards, not IT, to ensure data quality. • Provide an accurate source of data for other systems. • Data security. • Even though Master Data manages non-transactional data, it is almost always used within transactions. • Data Stewards help manage the data. • Technically savvy • Subject matter experts 12 | 43

6. Why do I need MDM? • Master data brings together multiple data sources throughout the organization into one centrally managed system. • Different business units within your organization may be managing similar sets of data. • Who has the most accurate set of data? • How much time and effort goes into managing the same data? • Ever receive two pieces of mail from the same company, or a letter asking you to open an account when you already have one? • That’s a company who could use a Master Data solution! 13 | 43

7. Common Domains in MDM • Customer Data • Financials • Product Information • Vendor Information • Employee Data • Locations 14 | 43

8. Company A (Before using MDM) 15 | 43

9. Company A (After using MDM) 16 | 43

10. Questions? 17 | 43

11. Master Data Services Let’s Dig In… 18 | 43

12. MDS Background • June, 2007 - Microsoft acquires Atlanta based Stratature • Stratature +EDM product becomes MDS. • Many Stratature employees have since moved on to a company called Profisee which is also based in Atlanta. • Version 1.0 in SQL Server 2008 R2 • Version 2.0 in SQL Server 2012 • Available in Enterprise & Business Intelligence Editions 19 | 43

13. How do you use MDS? • MDS User Interface • Web Based • Full functionality • Data Management • Excel Add in (New in SQL Server 2012) • Limited MDS functionality • Very useful when working with data 20 | 43

14. MDS Data Flow 21 | 43

15. Installation and Configuration 1. Install Prerequisites • PowerShell • IIS 2. Create a new database 3. Web Configuration • Website & Web Application • Associate to the MDS database • Enable DQS Integration (optional) • Enables Matching Functionality 22 | 43

16. DemoInstall & Configuration 23 | 43

17. MDS Modeling Concepts • Models (Database) • Highest level of organization. Containers of related entities. • Entities (Table) • Base containers for data. • Attributes (Columns) • Attributes that describe members. • Members (Records) • Records that populate the entities. 24 | 43

18. Models (Databases) • Highest level of data organization • Models contain the following objects: • Entities • Attributes and attribute groups • Explicit and derived hierarchies • Collections 25 | 43

19. Entities (Tables) • Contained in MDS models • Groupings of similar data • Contains members • Typically one or more related entities per model 26 | 43

20. Attributes (Columns) and Attribute Groups • Attribute values describe the members of the entity. • Attribute Groups help to manage many attributes and can be assigned to roles for easy security management. • Three types of attributes: • Free-form • Domain-based • File attributes 27 | 43

21. Members (Records/Rows) • Members are the physical master data. • Three types of members: • Leaf (default) • Consolidated (Exist only when explicit hierarchies and collections are enabled for the entity) • Collection 28 | 43

22. Excel Add-in • New in SQL 2012 • Office 2007 or higher • 32-bit and 64-bit available • Good for working with 1million records or less • Has most functionality, except: • Business Rules • New Models • Hierarchies 29 | 43

23. Demo 30 | 43

24. Explicit (Ragged) Hierarchies • Organizes members from a single entity in any way you specify. • Are not based on domain-based attribute relationships. • No limitation on where you insert the consolidate members. • Each member can only be used once. • You need to create/enable explicit hierarchies on an entity to create a collection. 31 | 43

25. Derived Hierarchies • Derived from the domain-based attribute relationships. • The leaf members from one entity are used to group leaf members of another entity. • Based on relationships between entities. • Sorted by code. You cannot change the sort order. • Always contains a consistent number of levels. 32 | 43

26. Collections • A group of leaf and consolidated members from a single entity. • Use collections when you do not need a complete hierarchy but want groupings. • Doesn’t limit the number or types of members as long as they are within the same entity. • Doesn’t create a hierarchical structure, but a flat list of members. • A collection can also contain other collections. 33 | 43

27. Business Rules • If/Then statements that result in an action • Rules help ensure the quality and accuracy of your data. • Possible actions: • Default Value • Change Value • Validation • Email • External Action 34 | 43

28. Staging MDS • Good for working with over 1 million records • At least one table is created per entity • Use the staging process to: • Create, update, deactivate, and delete leaf and consolidate members. • Update attribute values. • Designate relationships in explicit hierarchies. • Load data into the staging tables. • stg.name_Leaf • stg.name_Consolidated • stg.name_Relationship • Use stored procedures, or the MDS site, to load data from staging tables into MDS. • stg.udp_name_Leaf • stg.udp_name_Consolidated • stg.udp_name_Relationship 35 | 43

29. Publishing Data • Create subscription Views in MDS • Subscribing Systems Can: • Use data directly from MDS • Pull and Process data from MDS to it’s own system. • Truncate local and copy from MDS • Use merge statements to update the source data • Use triggers to update the source data 36 | 43

30. Versions & Flags • Versions allow you to create copies of the master data within a model. • Can be locked while you validate your data, and then committed after the data has been validated. • Use versions to: • Create an auditable record of your data as it changes over time. • Prevent changes while you ensure data validates against business rules. • Lock down a model for use by subscribing systems. • Test different hierarchies without implementing them right away. • Use flags to mark and identify versions, such as ‘Latest’. 37 | 43

31. Security • Use to secure & restrict data • Based on local or Active Directory users and groups. • Grant MDS administrators • Able to specify security down to individual attributes. 38 | 43

32. Demo

33. Links • Microsoft MDS Overview • http://msdn.microsoft.com/en-us/library/ee633763.aspx • Microsoft Installation Guide • http://msdn.microsoft.com/en-us/library/ee633752(v=sql.110).aspx • Microsoft MDS Blog • http://blogs.msdn.com/b/mds/ • Microsoft MDS Forums • http://social.technet.microsoft.com/Forums/en-US/sqlmds/threads • Microsoft Instructional Videos • http://msdn.microsoft.com/en-us/sqlserver/ff943581

34. MDS Books • Free eBook with a chapter on MDS: • http://go.microsoft.com/FWLink/?Linkid=229829

35. Questions? Jeff Prom • Contact Info • Blog: http://jeffprom.wordpress.com • Email: jeffprom@comcast.net • LinkedIn: www.linkedin.com/pub/jeff-prom/7/1b7/bb1/

36. Thank You! Jeff Prom