Peter Myers Presentation Outline Master Data Services Overview

Peter Myers

Presentation Outline Master Data Services Overview New MDS Features Deprecated MDS Features Discontinued MDS Features Upgrading to MDS 2016 Supported MDS Features by Edition

Master Data Services Overview Master Data Services (MDS) is the SQL Server solution for master data management Master Data Management (MDM) enables an organization to discover and define non-transactional lists of data, and to compile maintainable, reliable master lists MDS was first released with SQL Server 2008 R 2

New MDS Features The new MDS features are grouped into three focus areas:

New MDS Features Security and Administration Multiple administrator roles § MDS now allows multiple system administrators, as well as distinct roles for model admins and super users Granular security permissions § Allows read, modify, create, and delete permissions set at: § Attribute (column) level, and § Hierarchy member (row) level

New MDS Features Performance and Scale MDS 2016 delivers massive performance and scale improvements Capabilities: § Heavy performance optimizations for the MDS backend system § Reduced overhead by optimizing and caching security checks § Scale entities to >10 million members § Ability to create custom indexes on attributes, including composite and unique indexes Benefits: § 15 x better performance for data loading in Excel § Entity-based staging is up to 200% faster 15 x

New MDS Features Modeling and Management MDS transaction log retention settings § Configurable settings for retaining the MDS transaction history table to easily enable automatic truncation and clean up § Enables retrieving attribute change history in an easy to consume format Improved hierarchies § Simplified hierarchy types—now just derived hierarchies § Hierarchies can now represent both 1 -∞ and ∞-∞ relationships § It is also now easier to find and manage unused members

New MDS Features Modeling and Management [Continued] Improved model deployment § Faster deployment § Size limitations when deploying models with data have been removed Entity Sync § A new feature to share common entities between models, within the same MDS instance § Configure one time, on-demand, or continuous sync of members

New MDS Features Modeling and Management [Continued] Many-to-many relationships § Create M 2 M relationships in derived hierarchies § Replaces collections (now marked for deprecation) Changesets § A simple approval flow allowing entity admins to specify that changes to a given entity require approval § Changes can be batched and only committed once they are approved

New MDS Features Modeling and Management [Continued] Improved business rules § New business rule definition UI experience—reworked based on user feedback § Addition of NOT operator § Conditional ELSE statements § New negate operators § Ability to use custom T-SQL logic for conditions and actions

New MDS Features Modeling and Management [Continued] Attribute filters § When part of a hierarchy, domain-based attributes can be filtered based on a parent attribute within that entity § Enables easier selection of domain-based attribute values § Helps manage attribute values for large hierarchies Country City

New MDS Features User Experience Improvements The Master Data Manager portal has been improved § Modern look and feel § New system administration pages § Usability improvement for administration tasks § Hierarchy design improvements § New business rule definition experience § Ability to open entity view in Excel § Reduced dependency on Silverlight

New MDS Features User Experience Improvements [Continued] The MDS Excel Add-in has been improved § Performance improvements § General usability improvements: § Change display names for domain-based attributes § Apply business rules § Filter attribute values in domain-based attributes based on another attribute (using hierarchies) § Create and submit changesets § Manage merge conflicts

Deprecated MDS Features Explicit hierarchies, collections, and related components § Members modelled as consolidated member types (explicit hierarchy parent), and collection member types, will be modelled as leaf members in derived hierarchies New MDS 2016 features enable derived hierarchies to take the place of explicit hierarchies: § Recursive derived hierarchies can now be used to assign member security permissions § In Explorer, the derived hierarchy page now shows unassigned (unused) members, and are grouped by hierarchy level § The mdm. udp. Convert. Collection. And. Consolidated. Members. To. Leaf stored procedure, converts explicit hierarchies to recursive derived hierarchies, and converts consolidated and collection members to leaf members

Deprecated MDS Features [Continued] Entity transaction log type "Attribute" is deprecated § Migrate to the "Member" entity transaction log type § Refer to: https: //msdn. microsoft. com/en-us/library/mt 244809. aspx

Discontinued MDS Features The MDS 2008 R 2 staging process has been removed § Use the entity-based staging process added in MDS 2012 The Metadata model is no longer available Metadata can no longer be viewed in the Explorer

Upgrading to MDS 2016 involves: § Upgrading the MDS database schema (with, or without a Database Engine upgrade) § Creating a SQL Server 2016 web application § Upgrading the Master Data Services Add-In for Excel, on each client machine Note: § The upgrade process recreates stored procedures and upgrades tables used by MDS, and so any customizations may be lost § Model deployment packages can be used only in the version of SQL Server in which they were created—those created by a previous version of SQL Server will not work

Supported MDS Features by Edition MDS 2016 is supported only by Enterprise and Developer editions

Summary Fourth release of Master Data Services Numerous compelling new features and enhancements deliver improved security and administration, performance and scale, and modeling and management Upgrade from all prior versions of SQL Server is supported

Resources MSDN: SQL Server Master Data Services § https: //msdn. microsoft. com/en-us/library/ee 633763. aspx MSDN: What’s New in Master Data Services § https: //msdn. microsoft. com/en-us/library/ff 929136. aspx MSDN: Features Supported by the Editions of SQL Server 2016 § https: //msdn. microsoft. com/en-us/library/cc 645993. aspx? #MDS Master Data Services Blog § https: //blogs. msdn. microsoft. com/mds/

Resources [Continued] Tech. Net Virtual Labs: Developing a SQL Server 2016 Master Data Services Solution Exploring What's New in SQL Server 2016 Master Data Services § https: //technet. microsoft. com/en-us/virtuallabs § Use the search pane to locate the lab

© 2016 Microsoft Corporation. All rights reserved. Microsoft, Windows, Microsoft Azure, and other product names are or may be registered trademarks and/or trademarks in the U. S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION