| Size: | 12 MB (12707025 bytes) | Extension: |
| Author(s): Vincent Rainardi Series: Expert's Voice Publisher: Apress, Year: 2007 ISBN: 1590599314,9781590599310,1590599314 Description: Building a Data Warehouse: With Examples in SQL Server describes how to build a data warehouse completely from scratch and shows practical examples on how to do it. Author Vincent Rainardi also describes some practical issues he has experienced that developers are likely to encounter in their first data warehousing project, along with solutions and advice. The RDBMS used in the examples is SQL Server; the version will not be an issue as long as the user has SQL Server 2005 or later. The book is organized as follows. In the beginning of this book (Chapters 1 through 6), you learn how to build a data warehouse, for example, defining the architecture, understanding the methodology, gathering the requirements, designing the data models, and creating the databases. Then in Chapters 7 through 10, you learn how to populate the data warehouse, for example, extracting from source systems, loading the data stores, maintaining data quality, and utilizing the metadata. After you populate the data warehouse, in Chapters 11 through 15, you explore how to present data to users using reports and multidimensional databases and how to use the data in the data warehouse for business intelligence, customer relationship management, and other purposes. Chapters 16 and 17 wrap up the book: After you have built your data warehouse, before it can be released to production, you need to test it thoroughly. After your application is in production, you need to understand how to administer data warehouse operation. What you'll learn
Who is this book for?There are three audiences for the book. The first are the people who implement the data warehouse. This could be considered a field guide for them. The second is database users/admins who want to get a good understanding of what it would take to build a data warehouse. Finally, the third audience is managers who must make decisions about aspects of the data warehousing task before them and use the book to learn about these issues. |
| Table of contents: Building a Data Warehouse: With Examples in SQL Server……Page 1 Contents at a Glance……Page 7 Contents……Page 9 About the Author……Page 15 Preface……Page 17 What Is a Data Warehouse?… …Page 19 Retrieves Data……Page 22 Consolidates Data……Page 23 Periodically……Page 24 Dimensional Data Store……Page 25 Normalized Data Store……Page 26 History……Page 28 Query……Page 29 Business Intelligence……Page 30 Reporting……Page 31 Other Analytical Activities……Page 32 Updated in Batches……Page 33 Other Definitions……Page 34 Business Intelligence……Page 35 Customer Relationship Management……Page 36 Data Mining……Page 37 Master Data Management (MDM)……Page 38 Customer Data Integration……Page 41 Unstructured Data……Page 42 Search……Page 43 Service-Oriented Architecture (SOA)……Page 44 Summary……Page 45 Data Flow Architecture……Page 47 Single DDS……Page 51 NDS + DDS……Page 53 ODS + DDS……Page 56 Federated Data Warehouse……Page 57 System Architecture……Page 60 Case Study……Page 62 Summary……Page 65 Waterfall Methodology……Page 67 Iterative Methodology……Page 72 Summary……Page 77 Identifying Business Areas……Page 79 Understanding Business Operations……Page 80 Defining Functional Requirements……Page 81 Defining Nonfunctional Requirements……Page 83 Conducting a Data Feasibility Study……Page 85 Summary……Page 88 Designing the Dimensional Data Store……Page 89 Dimension Tables……Page 94 Date Dimension……Page 95 Slowly Changing Dimension……Page 98 Product , Customer, and Store Dimensions……Page 101 Subscription Sales Data Mart……Page 107 Supplier Performance Data Mart……Page 112 CRM Data Marts……Page 114 Data Hierarchy……Page 119 Source System Mapping……Page 120 Designing the Normalized Data Store……Page 124 Summary……Page 129 Hardware Platform……Page 131 Storage Considerations……Page 138 Configuring Databases……Page 141 Creating DDS Database Structure……Page 146 Creating the Normalized Data Store……Page 157 Using Views……Page 175 Summary Tables……Page 179 Partitioning……Page 180 Indexes……Page 184 Summary……Page 189 Introduction to ETL…… Page 189 191 ETL Approaches and Architecture……Page 192 General Considerations……Page 195 Whole Table Every Time……Page 198 Incremental Extract……Page 199 Fixed Range……Page 203 Related Tables……Page 204 Extracting File Systems……Page 205 Extracting Other Source Types……Page 208 Extracting Data Using SSIS……Page 209 Memorizing the Last Extraction Timestamp……Page 218 Extracting from Files……Page 226 Summary……Page 232 Populating the Data Warehouse……Page 233 Stage Loading……Page 234 Data Firewall……Page 236 Populating NDS……Page 237 Using SSIS to Populate NDS……Page 246 Upsert Using SQL and Lookup……Page 253 Normalization……Page 260 Practical Tips on SSIS……Page 267 Populating DDS Dimension Tables……Page 268 Populating DDS Fact Tables……Page 284 Batches, Mini-batches, and Near Real-Time ETL……Page 287 Pushing the Data In……Page 288 Summary…… Page 289 Assuring Data Quality……Page 291 Data Quality Process……Page 292 Data Cleansing and Matching……Page 295 Cross-checking with External Sources……Page 308 Data Quality Rules……Page 309 Action: Reject, Allow, Fix……Page 311 Logging and Auditing……Page 314 Data Quality Reports and Notifications……Page 316 Summary……Page 318 Metadata in Data Warehousing……Page 319 Data Definition and Mapping Metadata……Page 321 Data Structure Metadata……Page 326 Source System Metadata……Page 331 ETL Process Metadata……Page 336 Data Quality Metadata……Page 338 Audit Metadata……Page 341 Usage Metadata……Page 342 Maintaining Metadata……Page 343 Summary……Page 345 Data Warehouse Reports……Page 347 When to Use Reports and When Not to Use Them……Page 350 Report Wizard……Page 352 Report Layout……Page 358 Report Parameters……Page 360 Grouping, Sorting, and Filtering……Page 369 Simplicity……Page 374 Spreadsheets……Page 375 Multidimensional Database Reports……Page 380 Deploying Reports……Page 384 Managing Report Security……Page 388 Managing Report Subscriptions……Page 390 Managing Report Execution ……Page 392 Summary……Page 393 What a Multidimensional Database Is……Page 395 Online Analytical Processing……Page 398 Creating a Multidimensional Database……Page 399 Processing a Multidimensional Database……Page 406 Querying a Multidimensional Database……Page 412 Administering a Multidimensional Database……Page 414 Multidimensional Database Security……Page 415 Processing Cubes……Page 417 Backup and Restore……Page 423 Summary……Page 427 Using Data Warehouse for Business Intelligence……Page 429 Business Intelligence Reports……Page 430 Business Intelligence Analytics……Page 431 Business Intelligence Data Mining……Page 434 Business Intelligence Dashboards……Page 450 Business Intelligence Alerts……Page 455 Business Intelligence Portal……Page 456 Summary……Page 457 Using Data Warehouse for Customer Relationship Management……Page 459 Single Customer View……Page 460 Campaign Segmentation……Page 465 Permission Management……Page 468 Delivery and Response Data……Page 472 Customer Analysis……Page 478 Customer Support……Page 481 Personalization……Page 482 Customer Loyalty Scheme……Page 483 Summary……Page 484 Customer Data Integration……Page 485 Unstructured Data……Page 488 Search in Data Warehousing……Page 492 Summary……Page 494 Testing Your Data Warehouse……Page 495 Data Warehouse ETL Testing……Page 496 Functional Testing……Page 498 Performance Testing……Page 500 Security Testing……Page 503 User Acceptance Testing……Page 504 Migrating to Production……Page 505 Summary……Page 507 Data Warehouse Administration… …Page 509 Monitoring Data Warehouse ETL……Page 510 Monitoring Data Quality……Page 513 Managing Security……Page 516 Managing Databases……Page 517 Making Schema Changes……Page 519 Summary……Page 521 Second Normal Form……Page 523 Boyce-Codd Normal Form……Page 524 Higher Normal Forms……Page 525 Index……Page 527 |
Comments
Post a Comment