Master Data Services In SQL Server Denali Jeremy

Master Data Services In SQL Server Denali Jeremy Kashel jeremy. kashel@adatis. co. uk http: //blogs. adatis. co. uk/blogs/jeremykashel/

Agenda • • Definitions Master Data Issues in BI Master Data Services Overview Changes in Denali Typical Architecture in BI Demo Questions

Master Data Defined • The reference data or nouns of the business, e. g. Product, Customer, Supplier • Master data entities • Non-transactional data of the business • Found in: – – – ERP systems HR systems Other LOB systems Share. Point lists Excel

Master Data Issues in BI • Data quality / Duplicate data – E. g. Vodafone, Vodafone Plc and Vodaphone – Missing data – blanks • Enriching data for reporting – Create a “Business Type” attribute for reporting – Not available in source systems • Hierarchy Management – Maintain a sort order • Maintain reports / cubes – E. g. Named set definitions in Analysis Services

Master Data Services Overview • • • Enterprise, Data Centre & Developer Editions (64 Bit) SQL Server database Web front end (and now Excel add-in) Modelling capability Data entry for master data entities Business rules & workflow Share. Point integration Versioning and transactions Security Web Service API

Changes in Denali • • • Updated web front end New Excel Add-In Staging table changes Data Quality Services (DQS) integration Now included in the main installation

Excel Add-In • • Add/update/delete MDS data from within Excel Domain-based attribute and business rule aware Create MDS models from Excel data Data Quality Services (DQS) integration

Typical MDS Architecture in BI

Demo • Updated web front end overview • Excel Add-In overview • Data integration story – Update data in Excel – SSIS – Load from MDS into data warehouse – Cube processed, report updated • Create master data entities in Excel

Summary • • Master data – reference data or nouns of the business Use MDS to manage hierarchies for reporting Master Data Manager web front end New Excel add-in – Access MDS data from Excel – Data Quality Services (DQS) integration – Create new MDS entities from Excel

Questions ?