EndtoEnd SQL Server Master Data Services Peter Myers

End-to-End SQL Server Master Data Services Peter Myers M 355

peter. myers@bitwisesolutions. com. au http: //www. linkedin. com/in/peterjsmyers

Master Data Management “Master Data Management (MDM) comprises a set of processes, governance, policies, standards and tools that consistently defines and manages the master data (i. e. nontransactional data entities) of an organization (which may include reference data). ” -Wikipedia

Continued

SQL Server Enterprise Information Management Integration Services Master Data Services Data Quality Services Complete, Clean, Consistent and Current Data

The new capabilities and features of SQL Server 2016 MDS will be introduced later in this presentation

Architecture

User interfaces: Master Data Manager

User interfaces: Excel Add-in

Sample implementation MDS Source System(s) Administration WCF Data Service SSIS SS Data Changes IS No tifi cat ion Data Warehouse Data Steward via MDM or Excel SSIS Outlook

Business rules

Hierarchies

Permission management

Entity-based staging Only available if the entity defines an explicit hierarchy

Version management

Version management Open § Default status § Authorized users can make changes Lock § Only administrators can make changes Validate § Business rules must be applied § All members must be validated before the version can be committed Commit § No user can make changes § Optionally, a validation flag can be assigned Copy § New version is available for users to continue managing the master data

Subscription views

Subscription views (continued)

1. 2. 3. 4. 5. 6. 7. 8. Create a model Create entities and attributes Define business rules Define hierarchies Import data to initialize/populate entity memberships Create subscription views to export master data Configure permission requirements Manage model versioning

Focus areas

Performance and scale: Do more faster 15 x

Modeling and management: Do more, achieve more

In response to customer feedback

Public preview so far (CTP 2. 2) Performance and Scale Excel add-in Modeling and Mgmt Transaction log retention Simplified hierarchies Custom indexes Display Name for attributes Security and Admin Granular security permissions Transaction history maintenance Multiple administrator roles Entity-based staging Model deployment Subscription views for historical data API optimization Entity sharing/sync Smarter caching of permissions

Coming in CTP 2. 3!

http: //www. microsoft. com/en-us/server-cloud/solutions/modern-data-warehouse/dataenrichment. aspx http: //technet. microsoft. com/en-us/sqlserver/jj 737651. aspx

http: //www. microsoft. com/en-us/server-cloud/products/sql-server-2016 http: //channel 9. msdn. com/Events/Ignite/2015/BRK 2578

Free Online Learning http: //aka. ms/mva Subscribe to our fortnightly newsletter http: //aka. ms/technetnz http: //aka. ms/msdnnz Sessions on Demand http: //aka. ms/ch 9 nz

© 2015 Microsoft Corporation. All rights reserved. Microsoft, Windows and other product names are or may be registered trademarks and/or trademarks in the U. S. and/or other countries. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.