Introduction to Master Data Services in SQL Server 2012

What is Master Data Services? Why is it important? - Will discuss Master Data Services capabilities, it's underlying architecture. Will demo creating a model, using SQL Server 2012 MDS add-in for Microsoft Excel, creating hierarchies, business rules and exposing/integrating data with other interfaces (Data Warehouse)

Transcript

1. Master  Data  Services  in  SQL  Server  2012   (An  Introduc9on)               Stéphane  Fréche?e   Thursday  May  30,  2013  
2. Who  am  I?   My  name  is  Stéphane  Fréche2e     I’m  a  Database  &  Business  Intelligence  Professional  and  CEO  |  Founder  of       I  have  a  passion  for  architecEng,  designing  and  building  soluEons  that  ma2er.     Self  proclaimed  Open  Data  Hacker/Advocate  I  founded  GaEneau  Ouverte  a  ciEzen  led   iniEaEve  which  aims  to  promote  open  access  to  civic  data  of  the  city  of  GaEneau.       Twi2er:  @sfreche2e   Email:  stephanefreche2e@ukubu.com   Blog:  stephanefreche2e.com        
3. Session  Outline   •  MicrosoR  Business  Intelligence  (The  Stack)   •  What  is  Master  Data?   •  What  is  Master  Data  Management?   •  Do  you  have  these  issues?   •  SQL  Server  Master  Data  Services  (MDS)   •  MDS  -­‐  Key  CapabiliEes   •  Data  Steward   •  MDS  –  Models,  Improving  Data  Quality,  Moving  Data,  Web  Service   •  Demo   •  Summary   •  Resources  
4. Analysis     Services   ReporEng Services   IntegraEon     Services   Master  Data     Services   SharePoint CollaboraEon   Excel Workbooks   PowerPivot ApplicaEons   SharePoint Dashboards & Scorecards   Data  Quality   Services   OData   Feeds   Line  of  Business   ApplicaEons   Hadoop Big Data MicrosoI  Business  Intelligence  
5. What  is  Master  Data?     “Master  Data  is  data  that  is  at  the  core  of  an  organizaEon  and  used   across  mulEple  systems,  applicaEons,  and/or  processes…”   Requires;   •  Centralized  curated  acEviEes  and  maintenance   •  Data  Quality  Management   •  Easy  access  for  business  users  (not  only  IT)   •  EffecEve  collaboraEon  and  sharing  
6. What  is  Master  Data  Management?     “Are  sets  of  processes,  governance,  policies,  standards  and  tools  that   defines  and  manage  Master  Data…”  
7. Why  is  Master  Data  Management  Important?   • Reduces,  eliminate  duplicate  data  entry  and  maintenance   • Improve  compliance,  reporEng,  profitability,  decision  making  and  data   quality   • Enables  data  stewards  to  manage  criEcal  business  data   • Provide  a  single  view  of  criEcal  informaEon  funnels      
8. Why  is  Master  Data  Management  Important?    
9. Do  you  have  these  issues?   •  Instances  or  sets  of  invalid  data  impacEng  business  processes?   •  Wish  your  business  users  could  manage  the  data  themselves?   •  IT  resources  fixing  data  and/or  managing  hierarchy  definiEons  for  users?   •  Systems  which  could  benefit  from  a  single  source  view  of  domain  data?   SQL  Server  Master  Data  Services  can  help  you!  
10. What  type  of  Data  are  we  taking  about?   People   Things   Places   Abstract   Customers   Vendors   Employees   Partners   PaEents   Products   Business  Units   Bill  of  Materials   Parts   Equipment   LocaEons   Stores   Power  Lines   Geographic   Areas   Warehouses   Accounts   WarranEes   Time   Metrics   Contracts  
11. Domain,  En99es  (Data  warehouse)   Here   Here  
12. SQL  Server  Master  Data  Services  (MDS)   SQL  Server  Master  Data  Services  provides  a  central  data  hub  that  ensures  the  integrity  of   informaEon  and  consistency  of  data  is  constant  across  different  applicaEons.  Enables   cleansing,  matching,  standardizing  and  enriching  data.   •  New  in  SQL  Server  2012;   §  Redesigned  web  interface  to  add,  delete,  and  move  members  quickly.   §  Excel  front-­‐end  (MDS  add-­‐in)  allowing  business  users  to  autonomously  add  and  edit   data  in  the  underlying  systems  on  their  own.   §  Improved  performance,  security,  robustness,  and  scalability.   §  Integrated  with  Data  Quality  Services  (DQS)  to  do  data  matching  before  loading.   §  InstallaEon  part  of  SQL  Server.  
13. Master  Data  Services  –  Key  Capabili9es   Create  Master  Data  By  Standardizing  Data  DefiniEons  For  Key  Business  EnEEes   •  Collect  and  maintain  accurate  and  complete  master  data  to  ensure  standardized  data   definiEons  of  key  business  enEEes  across  all  of  your  IT  assets.   §  Manage  data  consistency  across  different  environments.      (Oracle,  MicrosoR  SQL  Azure,  HP,  and  IBM)   §  Manage  the  superset  of  all  data  a2ributes  across  all  systems.   §  Create  explicit  hierarchies  based  on  tradiEonal  parent-­‐child  relaEonships.   §  Produce  derived  hierarchies  from  pre-­‐exisEng  data  relaEonships  found  within  master   data  models.  
14. Master  Data  Services  –  Key  Capabili9es   Create  a  Master  Data  Hub  for  Your  Enterprise     •  Implement  a  master  data  management  hub  to  manage  the  master  data  that  is  stored  in   the  database  and  keep  it  synchronized  with  the  transacEonal  systems  that  use  the  master   data.   §  EffecEvely  track  all  known  a2ributes  across  the  enterprise.   §  Create  versions  for  each  model  at  different  Eme  intervals.   §  Commit  versions  of  master  data  only  aRer  business  rules  have  been  met.  
15. Master  Data  Services  –  Key  Capabili9es   Empower  Business  Users  to  Manage  Data  Governance     •  Trust  the  experience  of  business-­‐knowledgeable  users,  so  organizaEons  can  improve   master  data  maintenance  and  avoid  the  delays  and  mistakes  that  occur  when  non-­‐ business  specialists  manage  and  maintain  data.   §  End  users  can  directly  manage  the  underlying  database  and  data  warehouse   dimensions  and  hierarchies  using  Excel.   §  Free  IT  responsibiliEes  so  they  can  focus  on  the  oversight  of  the  overall  data   warehouse.  
16. Data  Steward   •  Key  role  -­‐  Is  usually  a  Business  User  and  not  from  the  InformaEon  Technology  side   •  Nutshell:  Responsible  for  maintaining  data  elements  in  a  metadata  registry…   •  Data  Steward  -­‐>  MDS  Client  (Web  based  and  Excel  add-­‐in)   •  Create  and  edit  Models,  Hierarchies,  Business  Rules…   •  Run,  process  and  validate  data  conEnually,  iteraEvely,  improving…     •  Maintain  high  level  of  data  quality     MDS/DQS   Data  Steward   MDS/DQS   Data  Steward   DQS   SSIS  Developer   Matching   Cleansing   Create  &  Edit  
17. Master  Data  Services  -­‐  Type  of  Users     Business  users  –  Data   stewards  -­‐  FuncEonal   area   Administrators  -­‐   AdministraEve  area    
18. Master  Data  Services  –  Models   Models  are  the  highest  level  of  data  organizaEon  in  Master  Data  Services.  A  model  defines   the  structure  of  data  in  your  master  data  management  soluEon.  A  model  contains  the   following  objects:     •  EnEEes   •  A2ributes  and  a2ribute  groups   •  Explicit  and  derived  hierarchies   •  CollecEons   Accounts;   which  could  include  enEEes  such  as  balance  sheet  accounts,  income  statement     accounts,  staEsEcs,  and  account  type.   Customer;   which  could  include  enEEes  such  as  gender,  educaEon,  occupaEon,  and  marital  status.   Geography;   which  could  include  enEEes  such  as  postal  codes,  ciEes,  counEes,  states,  provinces,  regions,     territories,  countries,  and  conEnents.  
19. Master  Data  Services  –  Improving  Data  Quality   In  order  to  ensure  the  quality  and  accuracy  of  your  master  data  these  are  the  following   features  that  are  available  to  you:     •  Business  Rules   (AutomaEcally  update  data,  send  email,  start  a  business  process  or  workflow)   •  ValidaEon   (Business  rule  –  Data  type  content  validaEons)   •  Versions   (Audit  records,  prevent  from  making  changes,  lock  down  models)   •  NoEficaEons   (Send  email  when  biz  rule  fails  or  model  version  status  changes)   •  Security    
20. Master  Data  Services  –  Moving  Data   •  ImporEng  Data   §  Import  data  into  Staging  Tables  and  process  the  staged  data  as  batch   o  stg_nameLeaf   o  stg_nameConsolidated   o  stg_nameRelaEonship   •  ExporEng  Data   §  Subscribe  systems  can  view  data  through  subscripEon  views     •  Deploying  Models   §  Package,  XML  format  which  contains  a  deployable  model  structure   o  Tools:  MDSModelDeploy,  Model  Deployment  wizard,  Model  Package  Editor    
21. Master  Data  Services  –  Extending  with  Web  Services   •  Master  Data  Manager  Web  Service   •  WCF  service  that  enables  you  to  control  MDS  programmaEcally     •  Custom  Workflows   •  A  custom  workflow  calls  code  that  you  write,  which  can  take  whatever  acEon  you   require  to  process  the  workflow.     Developer’s  Guide  (Master  Data  Services)   h2p://msdn.microsoR.com/en-­‐us/library/hh230994.aspx  
22. Demo   Master  Data  Services  
23. Enterprise  Informa9on  Management  (EIM)   The  EIM  Stack  as  a  whole  is  the  ‘Master  Data  Management’  soluEon  from  MicrosoR  and   consist  of  the  following:   •  SQL  Server  Data  Quality  Services  (DQS)  -­‐  Capture  and  record  knowledge,  rules,  and  acEons   •  SQL  Server  Master  Data  Services  (MDS)  -­‐  Master  Data  Management  repository,  Dimension  data     •  SQL  Server  IntegraEon  Services  (SSIS)  –  Moves  data,  integraEon           Enterprise  Informa9on  Management  (EMI)   ‘Master  Data  Management’  
24. Summary   Master  Data  Management  issues  are  pervasive  within  organizaEons   and  will  always  exist.     SQL  Server  Master  Data  Services  is  built  to  be  deployed  rapidly,  the   intent  is  to  make  it  accessible  for  all  type  of  organizaEons  and  enable   them  to  create  soluEons  for  themselves.  An  implementaEon  can  be   successfully  achieved  by  business  users  without  any  programming   knowledge.  
25. Resources   •  SQL  Server  2012  Master  Data  Services  -­‐  h2p://bit.ly/16ei8w0   •  MSDN,  Master  Data  Services  -­‐  h2p://bit.ly/z8crur   •  TechNet,  Master  Data  Services  -­‐  h2p://bit.ly/12BfwBH   •  Channel  9,  Master  Data  Services  -­‐  h2p://bit.ly/19ab2Y4   •  Master  Data  Services  Team  Blog  -­‐  h2p://bit.ly/170Ecuc   •  James  Serra’s  Blog  -­‐  h2p://bit.ly/M3MYI2   •  SQL  Chick’s  Blog  -­‐  h2p://bit.ly/ietII4   •  Amazon,  MicrosoR  SQL  Server  2012  Master  Data  Services  -­‐   h2p://amzn.to/UtVHaO  
26. What  Ques9ons  Do  You  Have?  
27. Thank  You   For  a?ending  this  session