logo资料库

在SQL Server中创建数据仓库实例.pdf

发布时间：2022-05-30 发布人：admin 分类：说明书 资料大小：12.12M 资料格式：pdf 举报 版权申诉
第1页 / 共541页
第2页 / 共541页
第3页 / 共541页
第4页 / 共541页
第5页 / 共541页
第6页 / 共541页
第7页 / 共541页
第8页 / 共541页
资料共541页，剩余部分请下载后查看
    Building a Data Warehouse: With Examples in SQL Server
    Contents at a Glance
    Contents
    About the Author
    Preface
    Introduction to Data Warehousing
    What Is a Data Warehouse?
    Retrieves Data
    Consolidates Data
    Periodically
    Dimensional Data Store
    Normalized Data Store
    History
    Query
    Business Intelligence
    Reporting
    Online Analytical Processing (OLAP)
    Data Mining
    Other Analytical Activities
    Updated in Batches
    Other Definitions
    Data Warehousing Today
    Business Intelligence
    Customer Relationship Management
    Data Mining
    Master Data Management (MDM)
    Customer Data Integration
    Future Trends in Data Warehousing
    Unstructured Data
    Search
    Service-Oriented Architecture (SOA)
    Real-Time Data Warehouse
    Summary
    Data Warehouse Architecture
    Data Flow Architecture
    Single DDS
    NDS + DDS
    ODS + DDS
    Federated Data Warehouse
    System Architecture
    Case Study
    Summary
    Data Warehouse Development Methodology
    Waterfall Methodology
    Iterative Methodology
    Summary
    Functional and Nonfunctional Requirements
    Identifying Business Areas
    Understanding Business Operations
    Defining Functional Requirements
    Defining Nonfunctional Requirements
    Conducting a Data Feasibility Study
    Summary
    Data Modeling
    Designing the Dimensional Data Store
    Dimension Tables
    Date Dimension
    Slowly Changing Dimension
    Product, Customer, and Store Dimensions
    Subscription Sales Data Mart
    Supplier Performance Data Mart
    CRM Data Marts
    Data Hierarchy
    Source System Mapping
    Designing the Normalized Data Store
    Summary
    Physical Database Design
    Hardware Platform
    Storage Considerations
    Configuring Databases
    Creating DDS Database Structure
    Creating the Normalized Data Store
    Using Views
    Summary Tables
    Partitioning
    Indexes
    Summary
    Data Extraction
    Introduction to ETL
    ETL Approaches and Architecture
    General Considerations
    Extracting Relational Databases
    Whole Table Every Time
    Incremental Extract
    Fixed Range
    Related Tables
    Testing Data Leaks
    Extracting File Systems
    Extracting Other Source Types
    Extracting Data Using SSIS
    Memorizing the Last Extraction Timestamp
    Extracting from Files
    Summary
    Populating the Data Warehouse
    Stage Loading
    Data Firewall
    Populating NDS
    Using SSIS to Populate NDS
    Upsert Using SQL and Lookup
    Normalization
    Practical Tips on SSIS
    Populating DDS Dimension Tables
    Populating DDS Fact Tables
    Batches, Mini-batches, and Near Real-Time ETL
    Pushing the Data In
    Summary
    Assuring Data Quality
    Data Quality Process
    Data Cleansing and Matching
    Cross-checking with External Sources
    Data Quality Rules
    Action: Reject, Allow, Fix
    Logging and Auditing
    Data Quality Reports and Notifications
    Summary
    Metadata
    Metadata in Data Warehousing
    Data Definition and Mapping Metadata
    Data Structure Metadata
    Source System Metadata
    ETL Process Metadata
    Data Quality Metadata
    Audit Metadata
    Usage Metadata
    Maintaining Metadata
    Summary
    Building Reports
    Data Warehouse Reports
    When to Use Reports and When Not to Use Them
    Report Wizard
    Report Layout
    Report Parameters
    Grouping, Sorting, and Filtering
    Simplicity
    Spreadsheets
    Multidimensional Database Reports
    Deploying Reports
    Managing Reports
    Managing Report Security
    Managing Report Subscriptions
    Managing Report Execution
    Summary
    Multidimensional Database
    What a Multidimensional Database Is
    Online Analytical Processing
    Creating a Multidimensional Database
    Processing a Multidimensional Database
    Querying a Multidimensional Database
    Administering a Multidimensional Database
    Multidimensional Database Security
    Processing Cubes
    Backup and Restore
    Summary
    Using Data Warehouse for Business Intelligence
    Business Intelligence Reports
    Business Intelligence Analytics
    Business Intelligence Data Mining
    Business Intelligence Dashboards
    Business Intelligence Alerts
    Business Intelligence Portal
    Summary
    Using Data Warehouse for Customer Relationship Management
    Single Customer View
    Campaign Segmentation
    Permission Management
    Delivery and Response Data
    Customer Analysis
    Customer Support
    Personalization
    Customer Loyalty Scheme
    Summary
    Other Data Warehouse Usage
    Customer Data Integration
    Unstructured Data
    Search in Data Warehousing
    Summary
    Testing Your Data Warehouse
    Data Warehouse ETL Testing
    Functional Testing
    Performance Testing
    Security Testing
    User Acceptance Testing
    End-to-End Testing
    Migrating to Production
    Summary
    Data Warehouse Administration
    Monitoring Data Warehouse ETL
    Monitoring Data Quality
    Managing Security
    Managing Databases
    Making Schema Changes
    Updating Applications
    Summary
    Normalization Rules
    First Normal Form
    Second Normal Form
    Third Normal Form
    Boyce-Codd Normal Form
    Higher Normal Forms
    Index
    cyan MaGenTa yeLLOW BLack PanTOne 123 c BOOks fOR PROfessIOnaLs By PROfessIOnaLs® The eXPeRT’s VOIce® In sQL seRVeR Building a Data Warehouse: With Examples in SQL Server Dear Reader, This book contains essential topics of data warehousing that everyone embarking on a data warehousing journey will need to understand in order to build a data warehouse. It covers dimensional modeling, data extraction from source systems, dimension and fact table population, data quality, and database design. It also explains practical data warehousing applications such as business intelligence, analytic applications, and customer relationship management. All in all, the book covers the whole spectrum of data warehousing from start to finish. I wrote this book to help people with a basic knowledge of database systems who want to take their first step into data warehousing. People who are familiar with databases such as DBAs and developers who have never built a data ware- house will benefit the most from this book. IT students and self-learners will also benefit. In addition, BI and data warehousing professionals will be interested in checking out the practical examples, code, techniques, and architectures described in the book. Throughout this book, we will be building a data warehouse using the Amadeus Entertainment case study, an entertainment retailer specializing in music, films, and audio books. We will use Microsoft SQL Server 2005 and 2008 to build the data warehouse and BI applications. You will gain experience designing and building various components of a data warehouse, including the architecture, data model, physical databases (using SQL Server), ETL (using SSIS), BI reports (using SSRS), OLAP cubes (using SSAS), and data mining (using SSAS). I wish you great success in your data warehousing journey. Building a Data Warehouse With Examples in SQL Server Companion eBook Available W i t h E x a m p e s i l n S Q L S e r v e r B u i l i d n g a D a t a W a r e h o u s e Sincerely, Vincent Rainardi RELatED titLES Companion eBook See last page for details on $10 eBook version SOURCE CODE ONLINE www.apress.com Shelve in Microsoft: SQL Server User level: Intermediate–Advanced ISBN-13: 978-1-59059-931-0 ISBN-10: 1-59059-931-4 9 0 0 0 0 i R a n a r d i Vincent Rainardi 9 781590 599310 this print for content only—size & color not accurate 7" x 9-1/4" / CASEBOUND / MALLOY (1.0625 INCH BULK -- 544 pages -- 50# Thor)
    9314fmfinal.qxd 11/15/07 1:37 PM Page i Building a Data Warehouse With Examples in SQL Server Vincent Rainardi
    9314fmfinal.qxd 11/15/07 1:37 PM Page ii Building a Data Warehouse: With Examples in SQL Server Copyright © 2008 by Vincent Rainardi All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher. ISBN-13 (pbk): 978-1-59059-931-0 ISBN-10 (pbk): 1-59059-931-4 ISBN-13 (electronic): 978-1-4302-0527-2 ISBN-10 (electronic): 1-4302-0527-X Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1 Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. Lead Editor: Jeffrey Pepper Technical Reviewers: Bill Hamilton and Asif Sayed Editorial Board: Steve Anglin, Ewan Buckingham, Tony Campbell, Gary Cornell, Jonathan Gennick, Jason Gilmore, Kevin Goff, Jonathan Hassell, Matthew Moodie, Joseph Ottinger, Jeffrey Pepper, Ben Renow-Clarke, Dominic Shakeshaft, Matt Wade, Tom Welsh Senior Project Manager: Tracy Brown Collins Copy Editor: Kim Wimpsett Associate Production Director: Kari Brooks-Copony Production Editor: Kelly Winquist Compositor: Linda Weidemann, Wolf Creek Press Proofreader: Linda Marousek Indexer: Ron Strauss Artist: April Milne Cover Designer: Kurt Krames Manufacturing Director: Tom Debolski Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax 201-348-4505, e-mail orders-ny@springer-sbm.com, or visit http://www.springeronline.com. For information on translations, please contact Apress directly at 2855 Telegraph Avenue, Suite 600, Berkeley, CA 94705. Phone 510-549-5930, fax 510-549-5939, e-mail info@apress.com, or visit http:// www.apress.com. The information in this book is distributed on an “as is” basis, without warranty. Although every pre- caution has been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in this work. The source code for this book is available to readers at http://www.apress.com.
    9314fmfinal.qxd 11/15/07 1:37 PM Page iii For my lovely wife, Ivana.
    9314fmfinal.qxd 11/15/07 1:37 PM Page iv
    9314fmfinal.qxd 11/15/07 1:37 PM Page v Contents at a Glance About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv nCHAPTER 1 Introduction to Data Warehousing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 nCHAPTER 2 Data Warehouse Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 nCHAPTER 3 Data Warehouse Development Methodology . . . . . . . . . . . . . . . . . . . . 49 nCHAPTER 4 Functional and Nonfunctional Requirements. . . . . . . . . . . . . . . . . . . . 61 nCHAPTER 5 Data Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 nCHAPTER 6 Physical Database Design. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 nCHAPTER 7 Data Extraction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 nCHAPTER 8 Populating the Data Warehouse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215 nCHAPTER 9 Assuring Data Quality . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273 nCHAPTER 10 Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301 nCHAPTER 11 Building Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329 nCHAPTER 12 Multidimensional Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 377 nCHAPTER 13 Using Data Warehouse for Business Intelligence. . . . . . . . . . . . . . . 411 nCHAPTER 14 Using Data Warehouse for Customer Relationship Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441 nCHAPTER 15 Other Data Warehouse Usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 467 nCHAPTER 16 Testing Your Data Warehouse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 477 nCHAPTER 17 Data Warehouse Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491 nAPPENDIX Normalization Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 505 nINDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 509 v
    9314fmfinal.qxd 11/15/07 1:37 PM Page vi
    分享到：
    赞 收藏

    相关推荐

    资料库

    开发技术

    共收录19306份资料，累计14个分类，关注成员有19位，主要包括：Actionscript,Javascript,PHP,VB,Perl,Delphi,Python,C#,C,Java,C++,Web开发,硬件开发,其它

    热门标签

    Actionscript Javascript PHP VB Perl Delphi Python C# C Java C++ Web开发 硬件开发 其它

    最新资料