DAT 307 Metadata Solutions for SQL Server 2005 Integration Services

1. DAT 307Metadata Solutions for SQL Server 2005 Integration Services

2. Microsoft Confidential Agenda • What is metadata? • Traditional metadata solutions • The SQL Server BI Metadata Strategy • Targeted metadata solutions with SQL Server 2005

3. Microsoft Confidential What is Metadata? • “Data about data” • But that does not help much, does it? • Metadata is everywhere • Design tools, management tools, monitoring tools, tables, indexes, partitions, data types, etl processes, data quality applications, mining applications, cubes, reports, analytic applications, portals, spreadsheets, word documents, physical systems … • Just talking about “metadata” is not clear enough, so…

4. Microsoft Confidential What is Metadata For? • Significant BI uses include: • Business Metadata • What does “CustStartDate” mean in this report? • Auditing and Lineage • When was the last data extract? • How many bad records were rejected? How complete is the data? • Where does this value “CustStartDate” come from? • Impact Analysis • If I modify this table, which reports need to change?

5. Microsoft Confidential Traditional Metadata Solutions • The data dictionary • Capture static definitions of business metadata for end users: What does this value mean? • The centralized metadata repository • A “one-stop-shop” for enterprise metadata • Abstract metamodels represent objects and processes • Specialized utilities for maintenance, security, customization, versioning and reporting • Custom metadata store • As simple or complex as needed • May leverage existing data and reporting investments

6. Microsoft Confidential Metadata Failures: Data Dictionaries • Popular in ERP systems • Store business metadata • Enable some systems discovery within the ERP • Limited role in metadata management • Rarely more than predefined metadata for common object types, eg: CustomerID • Typically proprietary • Often specific to a single application • Limited interaction with other technologies • Difficult to manage • Require continuous maintenance to synchronize post-production changes • May quickly become outdated (and so largely useless)

7. Microsoft Confidential Metadata Failures: Repositories • A repository is only as good as its metamodels • Incomplete models are inadequate • Outdated models are misleading and wrong • New technology investments may be unsupported, or difficult to model • Metamodels quickly become meta-muddles • Proprietary repositories may have limited scope • Generic repositories are expensive • $150k to > $1M • High development and management costs • Metadata requires continuous maintenance

8. Microsoft Confidential Metadata Failures: Custom Stores • Design and implementation from scratch • Incur high consultancy or development costs • Maintenance is typically manual • Changes captured ad-hoc by administrators • Poor integration with other technologies • But … • Custom stores are extensible • Unconstrained by a vendor architecture • Can leverage existing skills and investments

9. Microsoft Confidential SQL Server BI Metadata Strategy • We recognize two distinct long-term issues • Metadata management • Metadata intelligence • Metadata management • Maintaining metadata throughout the design and deployment process • Leveraging metadata to enhance design and deployment • Metadata intelligence • Improving users understanding of their BI systems for more effective decision-making • Documenting metadata for more effective administration

10. Microsoft Confidential SQL Server 2005 BI Metadata Strategy

11. Microsoft Confidential SQL Server BI Metadata Management • Concrete and authoritative • Throughout SQL Server BI, deployed metadata is the same as design-time metadata • Self-maintaining • SSAS includes impact reporting for its sources and DSVs • AMO (Analysis Management Objects) provides APIs for impact analysis on the server • SSIS enables parallel population of audit and lineage tables • Leverage existing tools • All our BI metadata is XML • SSIS natively populates audit tables in any supported destination format

12. Microsoft Confidential SQL Server BI End-to-End Metadata • Common metadata objects • Data sources, Data Source Views can be used in Integration, Analysis and Reporting projects • Data sources can be referenced across projects • Design-time and deployed integration • Integration Services can load UDM partitions, dimensions or mining models natively • UDM metadata can be bound to Integration Services from the server, or at design-time • Integration Services can load Reports natively • Metadata seamlessly integrated at design time • Integration services data flow is a Report data source • Report Builder natively imports UDM • Enables end-user reporting with custom semantic views

13. Metadata Intelligence for SQL Server BI • Metadata Samples Reporting Pack • Launched on MSDN in November 2005 • > 4000 downloads • Lineage, Impact analysis • Customizable reports • Embeddable lineage / impact viewer • It's shared source! • Customizable, extensible – and free!

14. Metadata Samples • Download reporting pack and whitepaper • Search http://download.microsoft.com for “ssis metadata” • Whitepaper includes practices for use and extensibility

15. Some futures • Don’t expect a "boil the ocean" repository • Model-based systems could not keep pace with rapid technology change • Either innovation suffered or repositories became outdated quickly • Future directions • Technology specific "repositories" • Bridging will always be necessary between them • Metadata intelligence will help make sense of the relationships • It's social networking for data!

16. Microsoft Confidential Summary • Traditional approaches to metadata are flawed • Microsoft has tried them • A new approach is needed • Concrete, authoritative, self-maintaining, targeted • Leveraging existing tools • SQL Server 2005 is the first step

17. Microsoft Confidential Some recommended reading • Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data • Ralph Kimball, Joe Caserta • ISBN 0764567578, Wiley • Metadata Solutions: Using Metamodels, Repositories, XML, and Enterprise Portals to Generate Information on Demand • Adrienne Tannenbaum • ISBN: 0201719762, Pearson Education

18. © 2006 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.