Master Data Management with SQL Server 2016 Master

Master Data Management with SQL Server 2016 Master Data Services Ravi Gudlavalleti

Poll 1. Do you currently (or planning to) use a Master Data Management System? 2. If so, do you use SQL Server MDS?

Agenda • • What & Why MDS 2016 - What’s new Implementation Demo 1 Security Integration Demo 2 Q&A

What is Master Data Non-transactional data. Ex: Customers, Products, Employees, etc. Master Data Management Involves • Business Policies • Processes • Technology * Credit: Microsoft SQL Server 2012 Master Data Services – Tyler Graham

Why you need it • • Hierarchy Management Validation and Change management Easier Integration with other systems Work flows

Why SQL Server MDS • • • Included in Enterprise Edition SQL Server back-end Web UI & Web services Excel Plug-in Group and Role based security Works with any ETL tool

Architecture *Credit: Hari Yadav - https: //www. simple-talk. com/sql/database-delivery/master-data-services-basics/

Implementation Options Originating System Subscribing System Mapping System MDS ERP MDS EDW EDW ERP CRM

Terms Term Definition Example(s) Model Data Domain Product, Customer Entity Table Product Category Attribute Column Color, Age Member Entry/Row John Doe Domain-based attr. Lookup, Reference Yes/No, Codes Hierarchy Geography, Product Line Collection Area of Ownership Contact info, Tech Specs Version Snapshot of Data Version_1 Business Rule Default, Required

Business Rules • • If then else construct Set defaults Required fields Email notifications Regex User defined scripts/Sprocs External workflows

What’s new in 2016 • • Data and Index Compression More granular security Improved log maintenance Indexes Entity Sync Purge Soft Deleted members Moving away from Sliverlight. . . and much more https: //docs. microsoft. com/en-us/sql/master-data-services/what-s-new-in-master-data-services-mds

Demo Data Model

Demo 1 - Development

Security

Subscription View – Data Out

Staging – Data In Column Name Description Import. Type 0 to 6 Import. Status_ID 0 – Ready 1 – Success 2 - Fail Batch_ID System unique ID Batch. Tag User defined ID Error. Code For failed rows Code Blank for new rows Name New. Code To change code

Demo 2 - Integration

SSIS Package

Considerations & Recommendations • • • Scope – Enterprise wide vs. Business Area Big Bang vs. Incremental Type of Implementation – SOE, SOR, Mapping Who owns what Data Stewards

Questions

Contact Info Ravi Gudlavalleti rgudlavalleti@anexinet. com @Ravi. GBI https: //www. linkedin. com/in/ravigudlavalleti/

Links/Reference Microsoft Docs – MDS https: //docs. microsoft. com/en-us/sql/master-data-services-overview-mds Master Data Services – The Basics https: //www. simple-talk. com/sql/database-delivery/master-data-services-basics/ Microsoft SQL Server 2012 Master Data Services 2/E (Database & ERP - OMG) -- By Tyler Graham

Thanks for Attending