microsoft master data services with sql server 2012
DESCRIPTION
Author: Mark Gschwind, DesignMind San Francisco, CA Master Data Services has had a major upgrade in SQL Server 2012. BI Consultant Mark Gschwind will take you through the new Excel interface, the new Silverlight look and feel, and integration improvements. Knowing how to use this tool can be a valuable addition to your repertoire as a BI professional, allowing you to address data quality and other challenges. Mark will demo the product, show how to create a model, add columns and rows, manage security, and create hierarchies. He'll demo the new Excel interface and discuss how to allow you to manage master data yourself. He'll touch on how to integrate with a DW, migrating from Dev to Production. You will learn: How to let users manage dimensions and hierarchies for your DW How to create workflows to improve data quality in your DW Tips from real-life implementations to help you achieve a successful implementation Mark Gschwind, Partner at DesignMind, is an expert on data warehousing, OLAP, and ERP migration. He has authored three enterprise data warehouses and over 80 OLAP cubes for 46 clients in a wide range of industries. Mark has certifications in SQL Server and Oracle Essbase.TRANSCRIPT
Master Data Services inSQL Server 2012
Mark GschwindVP, Business IntelligenceDesignMind
Agenda What is Master Data? Why is it important? Overview of SQL 2012 MDS
Capabilities Underlying Architecture
Demo1. Creating a model2. Using the new Excel interface3. Creating a hierarchy 4. Exposing MDS data to the DW 5. Using business rules
Case Study Tips on successful implementations
About Mark Gschwind VP of Business Intelligence at DesignMind PASS (BAADD) member for over 10 years BI Consultant since 1995 BI implementations for over 46 clients
Datawarehouse/Cubes/Reporting/Data Mining Focus on delivering BI using an agile methodology MCP, certified in Oracle Essbase Used MDS since 2008 (EDM by Stratature)
find me on
DesignMind
San Francisco based, 30 people, 3 Microsoft MVPs Microsoft Gold Certified Partner Capabilities include Custom .NET Development,
SharePoint Development, and Business Intelligence Data Warehouses, Master Data Management, Reporting,
Analytics, Dashboards, Mobile
www.designmind.com
What is Master Data?
Centralized maintenance (or curation)Continuous quality management Ease of access for business users (not just IT)Effective sharing (producing and consuming)
Master Data contains different attributes for different departments (marketing, finance, operations, business groups…)
MDS enables users to curate Master Data. This capability can be powerful in a number of scenarios across an organization.
Main ScenariosData Management Solutions
Data Warehouse/ Data Marts Mgmt
Enable business users to manage the dimensions and hierarchies of DW / Data Marts
• BI scenarios
Canonical form System A System BProduct ID
Product Name Color Size ID Name Price ID Name
The IT department has built a data warehouse and reporting platform, but business users need more agility in making updates.MDS empowers the business users to manage dimensions themselves while IT can govern the changes
Provides storage and management of the objects and metadata used as the application knowledge
• Object mappings
• Reference Data / managed object lists
• Metadata management / data dictionary
Table containing information on mapping objects between different systems. ETL processes are referencing the table making transformation decisions. MDS enables business users to manage the objects mapping
Where is Master Data (in a DW)?
Why Master Data Is Important
Why Master Data Is Important
Why Master Data Is Important
Versioning
ValidationAuthoring business rules
to ensure data correctness
ModelingEntities, Attributes,
Hierarchies
Enabling Integration & Sharing
MDS Capabilities
Role-based Security and Transaction Annotation
Master Data Stewardship
External (CRM, ..)Excel DWH
Loading batched data through
Staging TablesConsuming data through Views
Registering to changes through
APIs
Excel Add-In Web UI
Workflow / Notifications
Data Matching (DQS Integrated)
MDS Architecture
Business Rules
Business Rules are expressions and actions that can govern the conduct of business processes
Enable data governance by:-- Enforcing data standards-- Alerting users to data quality issues-- Creating simple workflows
Have limitations, but can be extended to SharePoint
Security
Functional area permissions Model/Entity level permissions provide column-
level security Hierarchy permissions allow row-level security
Use AD groups, not individual users Only use Hierarchy permissions if row-level
security is required
Managing MDS Environments Use MDS Configuration Manager to create a Dev
website and DB Use web UI to deploy model structure only
Use MDSModelDeploy.exe to deploy model+data• To package an existing model
• To deploy an existing package
• To deploy an update
MDS Tips
Start small and build incrementally
Create and use a development environment Engage the business users and get them to
own the process
Additional Resources
http://www.mdsuser.com/
http://www.msdev.com/Directory/SeriesDescription.aspx?CourseId=155
http://msdn.microsoft.com/library/ee633763%28SQL.110%29.aspx
http://social.msdn.microsoft.com/Forums/en/sqlmds/threads
Contact Us
Custom Software, Database, and Business Intelligence Solutions
465 California StreetSan Francisco
www.designmind.com