1 / 10

Managing your Data Warehouse/ Data Mart Dimensions with MDS

Managing your Data Warehouse/ Data Mart Dimensions with MDS. Andi Comisioneru Principal Group Program Manager Microsoft Corporation. Microsoft SQL Server 2012. Agenda. MDS and BI – Dimensions Management Why?. Add MDS to you DW implementation. MDS Features for BI.

kiara
Download Presentation

Managing your Data Warehouse/ Data Mart Dimensions with MDS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Managing your Data Warehouse/ Data Mart Dimensions with MDS Andi Comisioneru Principal Group Program Manager Microsoft Corporation Microsoft SQL Server 2012

  2. Agenda MDS and BI – Dimensions Management Why? Add MDS to you DW implementation MDS Features for BI Approaches to Implementing MDS

  3. Data Warehouse / Data Marts • Reporting Database of operational transactions • Two Types of Data • Fact Tables • Dimensions • Reports are built on the depth and variety of the dimensions. DW Schema Example

  4. Why Managing Dimensions? Typical Execs Meeting: most of the time spent arguing data validity rather than analyzing and draw conclusions from it? • Why? • Dimensions are slowly changing over time (structural changes) • Attributes are changing (added or retired) • Where? • Manage the source (upstream / producer) • Not always under same org control • Requires multiple expertizes • Not always allowed (multiple DW needs) • Not always part of any source system, attributes required only for reporting • Manage the reports (downstream / consumer) • Changes in one consumption (BI) tool do not propagate to other tools • Changes do not propagate to source (not sticky) • Manage at the DW / Data Mart • Best of all worlds • Who? • Data experts, close to the business EMPOWER THE BUSINESS USER!

  5. What does it Mean to Manage Dimensions For BI tools to be accurate in long-term (throughout the life-cycle) • The dimensions and related hierarchies need to be accurate • The data needs to be properly validated • Changes need to be approved • Changes need to be audited • Proper permissions need to be defined

  6. MDS-Data Warehouse Scenario From To MDS DW DW • MDS – all in a simple UI • Data Editing and Errors Handling • Hierarchy Modification • Transactions Annotation (Auditing) • Versioning • Business Rules Validation • Permissions / Security SSAS SSAS PowerPivot PowerPivot

  7. MDS Capabilities ValidationAuthoring business rules to ensure data correctness Role-based Security and Transaction Annotation • MDS Web UI Excel Add-In • Master Data Stewardship Versioning Enabling Integration & Sharing Loading batched data through Staging Tables Consuming data through Views Registering to changes through APIs

  8. Products Dim Products Dim Sub Category Dim Sub Category Dim Category Dim Category Dim DataMart Manage Dimension in 30 min Sales Fact ETL Subscription views MDS demo

  9. Summary • Define your Model • Create Entities and relations • Define Hierarchies • Define validation rules (business rules) • Integrate with other systems • Create Subscription views and ETLs • Load data to MDS (EBS, Excel, Web Services) • Add Process • Define roles and permissions • Approval workflows • Version • Add versions

  10. © 2011 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista 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.

More Related