精通數據倉庫設計

1、精通數據倉庫設計（Mastering Data Warehouse Design）中英對照精通數據倉庫設計（Mastering Data Warehouse Design）中英對照第一部分 基本概念我們發現，理解為什么采納某個具體的方法，能幫助我們理解這個方法的價值并應用這個方法。因此，這一節的開始，我們先介紹企業信息工廠（Corporate Information Factory CIF），這種已經被證明的、穩定的體系結構。在這種體系結構下，商業智能（BI），包含兩種形式的數據存貯，每一種都有一個BI環境下具體的角色。第一類數據存貯是數據倉庫，數據倉庫主要的角色是擔當數據知識庫，存貯來自不同

2、數據源的數據，使它能被另一類數據存貯訪問。另一類數據存貯就是數據集市。總的來說，設計數據倉庫最有效的方法是基于實體-關系數據模型和范式技術（由Code 和 Date 最初在1970，90，90年代為關系數據庫創建）。PA數據集市的主要角色是提供企業用戶一個容易的訪問優良的、集成的信息的方法。在第1章描述有幾種類型的數據集市，最常用的數據集市是創建聯機分析處理（OLAP），OLAP最有效的設計方法是維度數據模型。在第2章，我們繼續這個基本的主題，解釋最重要的關系建模技術，介紹所需要的不同類型的模型，提供建立關系模型的過程，同時，我們解釋為企業構建一個堅固的基礎時，商業數據型、系統數據、技術數據等

3、模型等各類數據模型之間的關系，并解釋他們之間是如何互相共享或繼承特性。第1章 介紹歡迎閱讀本書，這是第一本徹底描述構建一個多用途的、穩定的、可持續的，支持商業智能的數據倉庫建模技術的書。這一章介紹BI及數據倉庫的目標，解釋他們如何組合成一個整體的企業信息工廠體系結構，討論數據倉庫建設的迭代性，論證數據倉庫數據模型的重要性，以及采用這種數據模型形式的理由。我們討論這種模型形式為什么應該基于關系設計技術，闡明是為了滿足最小冗余，最大穩定性和可維護性的需要。這一章的另一節列出了可維護的數據倉庫環境的特點。最后討論這種建模方法對最終交付數據集市的影響。這一章，讓讀者理解后續章節的基本原理，后續章節會描

4、述創建數據倉庫模型的細節。 Chapter 1 Introduction CHAPTEWelcome to the first book that thoroughly describes the data modeling techniques used in constructing a multipurpose, stable, and sustainable data warehouse used to support business intelligence (BI). This chapter introduces the data warehouse by describing

5、 the objectives of BI and the data warehouse and by explaining how these fit into the overall Corporate Information Factory (CIF) architecture. It discusses the iterative nature of the data warehouse constructionand demonstrates the importance of the data warehouse data model and the justification f

6、or the type of data model format suggested in this book. We discuss why the format of the model should be based on relational design techniques, illustrating the need to maximize nonredundancy, stability, and maintainability. Another section of the chapter outlines the characteristics of a maintaina

7、ble data warehouse environment. The chapter ends with a discussion of the impact of this modeling approach on the ultimate delivery of the data marts. This chapter sets up the reader to understand the rationale behind the ensuing chapters, which describe in detail how to create the data warehouse da

8、ta model. 1.1商業智能概述商業智能，在數據倉庫領域，指的是一個企業學習過去的行為與活動，理解組織的過去，確定組織的現狀，預計或者改變將來會發生的事情的能力。BI的概念已經提出20年了，讓我們簡短的回顧過去令人興奮的、不斷創新的10年。Overview of Business IntelligenceBI, in the context of the data warehouse, is the ability of an enterprise to study past behaviors and actions in order to understand where the o

9、rganization has been, determine its current situation, and predict or change what will happen in the future. BI has been maturing for more than 20 years. Lets briefly go over the past decade of this fascinating and innovative history. 也許你熟悉技術采納曲線，最早采用新技術的公司叫創新者，下一類叫作早期采納者，然后有前半數成員、后半數成員，最后是落伍者。這個曲線是

10、傳統的鐘型曲線，在開始的時候成指數增長，在后半周期市場緩慢下降。新技術一旦被引進，往往價錢昂貴且不完善，而很難應用；經過一段時間，性價比可以接受。手機（蜂窩電話）就是一個很好的例子。曾經，只有革新者（醫生和律師？）帶著手機，又笨重又昂貴，信號不連續，經常丟失通話?，F在，你只要花60美元，隨處可以擁有一個手機，且服務非常的可靠。Youre probably familiar with the technology adoption curve. The first companies to adopt the new technology are called innovators. The n

11、ext category is known as the early adopters, then there are members of the early majority, members of the late majority, and finally the laggards. The curve is a traditional bell curve, with exponential growth in the beginning and a slowdown in market growth occurring during the late majority period

12、. When new technology is introduced, it is usually hard to get, expensive, and imperfect. Over time, its availability, cost, and features improve to the point where just about anyone can benefit from ownership. Cell phones are a good example of this. Once, only the innovators (doctors and lawyers?)

13、carried them. The phones were big, heavy, and expensive. The service was spotty at best, and you got “dropped” a lot. Now, there are deals where you can obtain a cell phone for about $60, the service providers throw in $25 of airtime, and there are no monthly fees, and service is quite reliable.數據倉庫

14、是這種采納曲線另一個很好的例子。事實上，如果你還沒有開始你的第一個數據倉庫項目，那沒有比現在更好的開始時間了。今天管理人期望得到大多數好的，及時的信息，用于領導企業進入下一個年代的、基于知識的決策，他們經常做到了，然而，并不是每次都這樣。Data warehousing is another good example of the adoption curve. In fact, if you havent started your first data warehouse project, there has never been a better time. Executives toda

15、y expect, and often get, most of the good, timely information they need to make informed decisions to lead their companies into the next decade. But this wasnt always the case.就在在10年前，同樣的管理者批準開發決策信息系統（Executive information systems EIS）來滿足他們的需要。發起人后面的基本概念是合理的：以實時的方式，提供給管理者容易訪問的關鍵性能信息。然而，很多這類系統沒有實現它們目

16、標，大多數是因為基本的體系結構不能快速響應企業環境的變化。早期EIS系統另一個顯著的缺點是需要花費大量的精力去提供管理者所需要的數據。數據獲取，即提取、轉換、裝載（ETL）過程是一系列復雜的活動，它們的唯一目的是獲取最準確的、集成的數據，然后通過數據倉庫或者操作型數據存貯（ODS）讓企業訪問。Just a decade ago, these same executives sanctioned the development of executive information systems (EIS) to meet their needs. The concept behind EIS in

17、itiatives was soundto provide executives with easily accessible key performance information in a timely manner. However, many of these systems fell short of their objectives, largely because the underlying architecture could not respond fast enough to the enterprises changing environment. Another si

18、gnificant shortcoming of the early EIS days was the enormous effort required to provide the executives with the data they desired. Data acquisition or the extract, transform, and load (ETL) process is a complex set of activities whose sole purpose is to attain the most accurate and integrated data p

19、ossible and make it accessible to the enterprise through the data warehouse or operational data store (ODS).整個過程以手工密集的活動開始：硬編碼“數據吸管”是唯一從操作型系統獲取數據的方法，用于商業分析師的訪問。這有點類似于早期的電話，穿著輪滑來回穿梭的操作員很難通過插入正確的線繩，連接你呼叫的電話。The entire process began as a manually intensive set of activities. Hard-coded “data suckers” w

20、ere the only means of getting data out of the operational systems for access by business analysts. This is similar to the early days of telephony, when operators on skates had to connect your phone with the one you were calling by racing back and forth and manually plugging in the appropriate cords.

21、 幸運的是，我們已經比那個年代前進了很多，數據倉庫行業已經開發了太多的工具和技術支持數據的獲取過程?，F在，大多數ETL過程都已經自動化，就像今天的電話系統。同時，類似于電話的發展，這個過程保留了一些困難的，或者說本身決定的，復雜的問題。沒有兩個公司有同樣數據獲取過程，甚至不會有同樣的問題。今天，大多數擁有重要數據倉庫的大公司，嚴重依賴于 ETL工具，用于設計，構建和維護他們的BI環境。過去十年，另一個主要的改變是建模技術和工具的引入，帶到了“容易使用”的階段。由RalphKimball博士等人提出的維度建模概念，對全球的支持聯機分析處理（OLAP）多維模型數據集市造成很大影響。Fortunat

22、ely, we have come a long way from those days, and the data warehouse industry has developed a plethora of tools and technologies to support the data acquisition process. Now, progress has allowed most of this process to be automated, as it has in todays telephony world. Also, similar to telephony ad

23、vances, this process remains a difficult, if not temperamental and complicated, one. No two companies will ever have the same data acquisition activities or even the same set of problems. Today, most major corporations with significant data warehousing efforts rely heavily on their ETL tools for des

24、ign, construction, and maintenance of their BI environments.Another major change during the last decade is the introduction of tools and modeling techniques that bring the phrase “easy to use” to life. The dimensional modeling concepts developed by Dr. Ralph Kimball and others are largely responsibl

25、e for the widespread use of multidimensional data marts to support online analytical processing. 除了多維分析，還開發了其它一些復雜的技術用于支持數據挖掘、統計分析、探索等需要。現在，一個成熟的BI環境需要比星型模式多得多：平文件、無偏數據統計子集，規范化數據結構模式等，除了星形模式，所有這些都屬數據倉庫必須支持的、重要的數據需求。當然，我們不能低估互聯網對數據倉庫的影響?；ヂ摼W消除了計算機的神秘性，管理者在日常生活中使用互聯網，不再對觸摸鍵盤心存芥蒂。終端用戶工具公司認識到了互聯網的影響，且大多數

26、都利用了這種成就：它們的界面都復制了流行的互聯網瀏覽器與搜索引擎的視覺特性。這些工具的強大及直觀，導致商業分析師和管理者廣乏使用BI。In addition to multidimensional analyses, other sophisticated technologies have evolved to support data mining, statistical analysis, and exploration needs. Now mature BI environments require much more than star schemas flat files, s

27、tatistical subsets of unbiased data, normalized data structures, in addition to star schemas, are all significant data requirements that must be supported by your data warehouse.Of course, we shouldnt underestimate the impact of the Internet on data warehousing. The Internet helped remove the mystiq

28、ue of the computer. Executives use the Internet in their daily lives and are no longer wary of touching the keyboard. The end-user tool vendors recognized the impact of the Internet, and most of them seized upon that realization: to design their interface suchthat it replicated some of the look-and-

29、feel features of the popular Internet browsers and search engines. The sophisticationand simplicityof these tools has led to a widespread use of BI by business analysts and executives.發生最近幾年的另一個重要事件是：發生了從技術追趕業務到業務驅使技術的轉變。在BI的早期，信息技術（IT）部門認識到了BI的價值，并努力向商業團體兜售這些價值。不幸的是，有時IT伙計向商業團體兜售的是構建數據倉庫的希望。今天，復雜的決

30、策支持環境的價值在商業界得到廣發的認同。例如，一個有效的客戶關系管理程序不能離開戰略（含有相關數據集市的數據倉庫）和戰術（操作型數據存貯和操作型集市）的決策支持能力。（見圖1.1）：Another important event taking place in the last few years is the transformation from technology chasing the business to the business demanding technology. In the early days of BI, the information technology (

31、IT) group recognized its value and tried to sell its merits to the business community. In some unfortunate cases, the IT folks set out to build a data warehouse with the hope that the business community would use it. Today, the value of a sophisticated decision support environment is widely recogniz

32、ed throughout the business. As an example, an effective customer relationship management program could not exist without strategic (data warehouse with associated marts) and a tactical (operational data store and oper mart) decision-making capabilities. (See Figure 1.1) BI體系結構過去十年最重要的發展是提出了廣為接受的BI體系

33、結構，支持所有的技術需求。這種體系結構認識到EIS方法有不少重大缺陷，最嚴重的缺陷是EIS數據結構常常從源系統直接獲取數據，導致需要非常復雜的數據獲取環境，需要大量的人力和計算機資源去維護。CIF（見圖1.2）體系，現在已經有大多數決策支持系統使用，通過把數據隔離成主要的5個數據庫（操作型系統，數據倉庫，操作型數據存貯，數據集市，操作集市）來解決這個問題，把從源系統到商業用戶的數據移動過程合并為一個高效的過程。rBI ArchitectureOne of the most significant developments during the last 10 years has been th

34、e introduction of a widely accepted architecture to support all BI technological demands. This architecture recognized that the EIS approach had several major flaws, the most significant of which was that the EIS data structures were often fed directly from source systems, resulting in a very comple

35、x dataacquisition environment that required significant human and computer resources to maintain. The Corporate Information Factory (CIF) (see Figure 1.2), the architecture used in most decision support environments today, addressed that deficiency by segregating data into five major databases (oper

36、ational systems, data warehouse, operational data store, data marts, and oper marts) and incorporating processes to effectively and efficiently move data from the source systems to the business users.(翻轉90度之后的圖：)這些組件進一步分為兩個主要的組?！叭祿搿苯M從操作型系統獲取數據，集成，清洗并推入數據庫，以方便使用。在CIF中包含如下組件：操作型系統數據庫（源系統）包含公司日常的商業數據

37、，這仍然是決策支持系統最主要的數據來源。 數據倉庫是集成的、包含明細的、包含歷史數據的數據集合，用于支持戰略決策。操作型數據存貯是集成的，明細的，現在的數據集合，用于支持戰術決策。These components were further separated into two major groupings of components and processes: Getting data in consists of the processes and databases involved in acquiring data from the operational systems, int

38、egrating it, cleaning it up, and putting it into a database for easy usage. The components of the CIF that are found in this function: The operational system databases (source systems) contain the data used to run the day-to-day business of the company. These are still the major source of data for t

39、he decision support environment. The data warehouse is a collection or repository of integrated, detailed, historical data to support strategic decision-making. The operational data store is a collection of integrated, detailed, current data to support tactical decision making.“數據獲取”組是一系列的過程和程序，用于從操

40、作型系統抽取數據到數據倉庫和操作型數據存貯。數據獲取過程執行數據集成、清洗功能，把數據轉換為企業統一的格式。這種企業級的格式，反映了一個企業商業規則的集成的集合。數據獲取層是CIP體系中最復雜的一部份。除了清洗和轉換外，數據獲取層還包含審計和控制過程，保證進入數據倉庫或操作型數據存貯系統數據的完整性。“取信息出”由一系列過程和數據庫組成，用于把BI交付給最終的企業用戶和分析師，在CIF中包括如下組件：從數據倉庫分離出的數據集市，用于提供商業團體各種各樣的決策分析支持。從ODS 分離出的操作集市，用于提供商業團體對現在的操作型數據進行多維訪問。把數據從數據倉庫轉移到操作集市的過程叫數據交付。類似

41、于數據獲取層，在移動數據的同時也制造數據。只是在數據交付時，來源是數據倉庫或ODS，這里已經包含了高質量的，集成的數據，且數據符合企業的商業規則。 Data acquisition is a set of processes and programs that extracts data for the data warehouse and operational data store from the operational systems. The data acquisition programs perform the cleansing as well as the integrat

42、ion of the data and transformation into an enterprise format. This enterprise format reflects an integrated set of enterprise business rules that usually causes the data acquisition layer to be the most complex component in the CIF. In addition to programs that transform and clean up data, the data

43、acquisition layer also includes audit and control processes and programs to ensure the integrity of the data as it enters the data warehouse or operational data store. Getting information out consists of the processes and databases involved in delivering BI to the ultimate business consumer or analy

44、st. The components of the CIF that are found in this function: The data marts are derivatives from the data warehouse used to provide the business community with access to various types of strategic analysis. The oper marts are derivatives of the ODS used to provide the business community with dimen

45、sional access to current operational data. Data delivery is the process that moves data from the data warehouse into data and oper marts. Like the data acquisition layer, it manipulates the data as it moves it. In the case of data delivery, however, the origin is the data warehouse or ODS, which alr

46、eady contains high quality, integrated data that conforms to the enterprise business rules.CIF體系并不是一開始就如此。一開始，它由數據倉庫和一些輕量級的匯總數據、高度匯總數據組成最開始，需要歷史數據的集合用來支持戰略決策。一段時間后，產生了操作型數據存貯，用于支持戰術決策支持系統；輕量級與高度匯總的數據存放在現在所謂的數據集市里。讓我們看看CIF的運轉情況?？蛻絷P系管理（CRM）是一個普通的需求驅動器，驅動了戰術信息部件（操作型系統，操作型數據存貯，操作型集市），戰略信息部件（數據倉庫和各種類型的數據

47、集市）。當然，對CRM來說，這些技術是必須的，但遠遠不止這些技術，除了為客戶和組織提供長期價值外，它還需要商業策略，企業文化與架構，客戶信息等。提供的架構非常適合環境，在這個體系架構里，每一個部件都有專門的設計和功能。The CIF didnt just happen. In the beginning, it consisted of the data warehouse and sets of lightly summarized and highly summarized datainitially a collection of the historical data needed t

48、o support strategic decisions. Over time, it spawned the operational data store with a focus on the tactical decision support requirements as well. The lightly and highly summarized sets of data evolved into what we now know are data marts.Lets look at the CIF in action. Customer Relationship Manage

49、ment (CRM) is a highly popular initiative that needs the components for tactical information (operational systems, operational data store, and oper marts) and for strategic information (data warehouse and various types of data marts). Certainly this technology is necessary for CRM, but CRM requires

50、more than just the technology it also requires alignment of the business strategy, corporate culture and organization, and customer information in addition to technology to provide long-term value to both the customer and the organization. An architecture such as that provided by the CIF fits very w

51、ell within the CRM environment, and each component has a specific design and function within this architecture. 在這一章，我們會更詳細的描述每個部件。雖然CRM是數據倉庫和操作型數據存貯常見的應用，但是還有很多其他的應用，如企業資源計劃系統（ERP）的提供商，如SAP，ORACLE，PeopleSoft等公司都有數據倉庫產品，并增加新的工具套件提供需要的功能。許多軟件公司現在都提供各種插件，包含一般的分析應用，例如，如利率分析、關鍵績效指標分析（KPI）等。我們會在本章的后面章節詳細

52、的介紹CIF組件。數據倉庫的改進非常重要的幫助公司對客戶提供更好的服務及提高公司效益。數據倉庫在技術不斷變化的同時，擁有一個穩定的體系結構。構建數據倉庫環境的工具已經發展了很長時間，他們非常復雜，對企業必需的數據提供設計、實現、維護、訪問等極大的便利。CIF架構利用這些技術和工具的革新，創建了一個環境，把數據分成5個不同的存貯，每一種存貯擔當一特定的角色，以正確的時間、正確的地點、正確的格式提供給企業團體正確的信息。想一想，你想成為數據倉庫建設的后半部分還是落伍者？這值得等待。We describe each component in more detail later in this cha

53、pter. CRM is a popular application of the data warehouse and operational data store but there are many other applications. For example, the enterprise resource planning (ERP) vendors such as SAP, Oracle, and PeopleSoft have embraced data warehousing and augmented their tool suites to provide the nee

54、ded capabilities. Many software vendors are now offering various plug-ins containing generic analytical applications such as profitability or key performance indicator (KPI) analyses. We will cover the components of the CIF in far greater detail in the following sections of this chapter.The evolutio

55、n of data warehousing has been critical in helping companies better serve their customers and improve their profitability. It took a combination of technological changes and a sustainable architecture. The tools for building this environment have certainly come a long way. They are quite sophisticat

56、ed and offer great benefit in the design, implementation, maintenance, and access to critical corporate data. The CIF architecture capitalizes on these technologyand tool innovations. It creates an environment that segregates data into five distinct stores, each of which has a key role in providing

57、the business community with the right information at the right time, in the right place, and in the right form. So, if youre a data warehousing late majority or even a laggard, take heart. It was worth the wait.什么是數據倉庫在我們開始描述建模技術前，我們先統一一些術語的定義：什么叫數據倉庫，它在BI中的角色和用途，支持它的構造和使用的各種部件 。 數據倉庫的角色和用途我們在本章的第一節

58、已經看到，BI 體系結構在過去的十年發生了極大的變化，從簡單的報表和EIS系統，到多維分析，到數據挖掘，到數據探索?，F在又引進了可定制的分析應用，這些技術是一個強壯的、成熟的BI環境的一部份。圖1.3顯示了這些技術發展的時間框架。考慮這些重要的、明顯不同的技術和數據格式的需求，很明顯，必須從一開始就有一個貯藏室，用于存貯高質量的、可信任的、靈活的、可重用的格式的數據，這些數據用于支持和維護BI環境。從一開始，數據倉庫就是BI體系結構的一部份，不同的方法學及數據倉庫大師給與這個部件不同的名字，如：籌備區：一個數據倉庫的變種是“后勤”籌備區，在這里從操作型系統來的數據首先被帶到一起，是數據一種不正式的設計和維護分組，唯一的目的是給多維數據集市提供數據。信息倉庫：IBM公司早期對數據倉庫的命名，不象籌備區定義那樣清晰，在它的定義里，不僅包含歷史數據倉庫，還包含數據集市。What Is a Data Warehouse?Before we get started with the actual description of the modeling techniques, we need to make sure that all