數(shù)據(jù)倉庫建模與ETL的實(shí)踐技巧PPT課件

1、二、數(shù)據(jù)倉庫的架構(gòu)(一)、什么是數(shù)據(jù)倉庫？(二)、數(shù)據(jù)倉庫和OLTP數(shù)據(jù)庫的區(qū)別?(三)、數(shù)據(jù)倉庫的架構(gòu)模型有哪些？三、構(gòu)建企業(yè)級數(shù)據(jù)倉庫五步法 (一)、確定主題 (二)、確定量度 (三)、確定事實(shí)數(shù)據(jù)粒度 (四)、確定維度1.如何理解維度？及維度的層次和級別?2.什么是緩慢變化維度？3.緩慢變化維度 幾種基本情況？ 幾種情況的利弊？ (五)、創(chuàng)建事實(shí)表第1頁/共56頁四、什么是ETL(ETL中的關(guān)鍵技術(shù)) (一)、數(shù)據(jù)抽取(1)全量抽取 (2)增量抽取 增量數(shù)據(jù)抽取中常用的捕獲變化數(shù)據(jù)的方法:a.觸發(fā)器b.時(shí)間戳c.全表比對d.日志對比 (二)、數(shù)據(jù)轉(zhuǎn)換和加工 (1)ETL引擎中的數(shù)據(jù)轉(zhuǎn)換和

2、加工 (2)在數(shù)據(jù)庫中進(jìn)行數(shù)據(jù)加工 (三)、數(shù)據(jù)裝載(1)直接SQL語句進(jìn)行insert、update、delete操作 (2)采用批量裝載方法，如bcp、bulk、關(guān)系數(shù)據(jù)庫特有的批量裝載工具或api (四)、主流ETL工具介紹第2頁/共56頁一、什么是BI（Business Intelligence）(一)、什么是BI的定義？(二)、BI的誕生？(三)、基本技術(shù)(四)、數(shù)據(jù)處理(五)、數(shù)據(jù)展現(xiàn)(六)、常用的BI 廠商和產(chǎn)品第3頁/共56頁一、什么是BI（Business Intelligence）(一)、什么是BI（Business Intelligence） a、什么是BI的定義？ BI

3、是Business Intelligence的英文縮寫，中文解釋為商務(wù)智能，用來幫助企業(yè)更好地利用數(shù)據(jù)提高決策質(zhì)量的技術(shù)集合，是從大量的數(shù)據(jù)中鉆取信息與知識的過程。簡單講就是業(yè)務(wù)、數(shù)據(jù)、數(shù)據(jù)價(jià)值應(yīng)用的過程。 用圖解的方式可以理解為下圖：這樣不難看出，傳統(tǒng)的交易系統(tǒng)完成的是Business到Data的過程，而BI要做的事情是在Data的基礎(chǔ)上，讓Data產(chǎn)生價(jià)值，這個(gè)產(chǎn)生價(jià)值的過程就是Business Intelligence analyse的過程。第4頁/共56頁一、什么是BI（Business Intelligence） b、如何實(shí)現(xiàn)Business Intelligence analyse

4、的過程 從技術(shù)角度來說，是一個(gè)復(fù)雜的技術(shù)集合，它包含ETL、DW、OLAP、DM等多環(huán)節(jié)，基本過程可用下圖描述。上圖流程，簡單的說就是把交易系統(tǒng)已經(jīng)發(fā)生過的數(shù)據(jù)，通過ETL工具抽取到主題明確的數(shù)據(jù)倉庫中，OLAP后生成Cube或報(bào)表，透過Portal展現(xiàn)給用戶，用戶利用這些經(jīng)過分類（Classification）、聚集（Clustering）、描述和可視化（Description and Visualization）的數(shù)據(jù)，支持業(yè)務(wù)決策。第5頁/共56頁一、什么是BI（Business Intelligence）說明： BI不能產(chǎn)生決策，而是利用BI過程處理后的數(shù)據(jù)來支持決策。哪么BI所謂的智

5、能到底是什么呢？ （理清這個(gè)概念，有助于對BI的應(yīng)用。）BI最終展現(xiàn)給用戶的信息就是報(bào)表或圖視，但它不同于傳統(tǒng)的靜態(tài)報(bào)表或圖視，它顛覆了傳統(tǒng)報(bào)表或圖視的提供與閱讀的方式，產(chǎn)生的數(shù)據(jù)集合就象玩具“魔方”一樣，可以任意快速的旋轉(zhuǎn)組合報(bào)表或圖視， 有力的保障了用戶分析數(shù)據(jù)時(shí)操作的簡單性、報(bào)表或圖視直觀性及思維的連慣性。實(shí)例: c、商業(yè)智能(BI)在電信行業(yè)所處的商業(yè)作用和價(jià)值？(什么是電信BI) BI應(yīng)用在電信行業(yè)又稱作電信經(jīng)營分析系統(tǒng)。 隨著競爭的加劇和數(shù)據(jù)業(yè)務(wù)的發(fā)展，電信業(yè)BI市場將進(jìn)入一個(gè)高速發(fā)展的時(shí)期。在電信市場競爭愈演愈烈的背景下，各個(gè)運(yùn)營商已經(jīng)從業(yè)務(wù)為中心向以服務(wù)為中心轉(zhuǎn)變，原來賴以競爭

6、的手段如價(jià)格戰(zhàn)、行業(yè)壟斷優(yōu)勢、促銷策略等以無法適應(yīng)新形勢的需要。 為了保住客戶資源，運(yùn)營商需要一套業(yè)務(wù)分析支持系統(tǒng)，以從自身市場數(shù)據(jù)中獲得能夠真正反映企業(yè)運(yùn)營狀況的有效信息，從而為市場經(jīng)營決策提供科學(xué)支持，商業(yè)智能(BI)所處的商業(yè)作用和價(jià)值顯而易見。第6頁/共56頁一、什么是BI（Business Intelligence）(一)、什么是BI的定義？(二)、BI的誕生？(三)、基本技術(shù)(四)、數(shù)據(jù)處理(五)、數(shù)據(jù)展現(xiàn)(六)、常用的BI 廠商和產(chǎn)品第7頁/共56頁一、什么是BI（Business Intelligence）(二)、BI的誕生？ 典型的案例有超市，便利店“尿片和啤酒”的故事(三)

7、、基本技術(shù) a、BI(Business Intelligence) 是一種運(yùn)用了數(shù)據(jù)倉庫、在線分析和數(shù)據(jù)挖掘等技術(shù)來處理和分析數(shù)據(jù)的嶄新技術(shù)，目的:是為企業(yè)決策者提供決策支持。 b、哪么BI技術(shù)涉及了哪些方面呢？ 其核心技術(shù)中ETL、DW及OLAP?；蛘哒f是“數(shù)據(jù)處理技術(shù)”與“數(shù)據(jù)展現(xiàn)技術(shù)”更加容易理解。 c、為什么要在操作型數(shù)據(jù)庫和 OLAP 之間加一層“數(shù)據(jù)倉庫”呢？ 花空間買時(shí)間(資源與效能的問題) 說一千道一萬都計(jì)算機(jī)資源與效能惹的禍，操作型數(shù)據(jù)庫以快速響應(yīng)業(yè)務(wù)為主要目標(biāo)，而OLAP的時(shí)候要占用大量的硬件資源，在OLAP的時(shí)候，業(yè)務(wù)操作很難快速響應(yīng)，無法保證業(yè)務(wù)的順利進(jìn)行，從業(yè)務(wù)、數(shù)據(jù)

8、、數(shù)據(jù)的價(jià)值的邏輯來看，沒有業(yè)務(wù)就談不上OLAP；零星分散的數(shù)據(jù)一般存在有多個(gè)應(yīng)用，對應(yīng)多個(gè)業(yè)務(wù)操作型數(shù)據(jù)庫，訪問效能極其低下。 最高效的方法：就是將數(shù)據(jù)先整合到數(shù)據(jù)倉庫中，而 由OLAP應(yīng)用統(tǒng)一從數(shù)據(jù)倉庫里取數(shù)，以解決快速響應(yīng)業(yè)務(wù)與OLAP的矛盾。第8頁/共56頁一、什么是BI（Business Intelligence）(一)、什么是BI的定義？(二)、BI的誕生？(三)、基本技術(shù)(四)、數(shù)據(jù)處理(五)、數(shù)據(jù)展現(xiàn)(六)、常用的BI 廠商和產(chǎn)品第9頁/共56頁一、什么是BI（Business Intelligence）上圖流程，簡單的說就是把交易系統(tǒng)已經(jīng)發(fā)生過的數(shù)據(jù)，通過ETL工具抽取到主題

9、明確的數(shù)據(jù)倉庫中，OLAP后生成Cube或報(bào)表，透過Portal展現(xiàn)給用戶，用戶利用這些經(jīng)過分類（Classification）、聚集（Clustering）、描述和可視化（Description and Visualization）的數(shù)據(jù)，支持業(yè)務(wù)決策。DB2、InformixOracleSysbase其他數(shù)據(jù)結(jié)構(gòu)類型第10頁/共56頁一、什么是BI（Business Intelligence）(四)、數(shù)據(jù)處理(1)、 ODS 層的作用？ 1)、在業(yè)務(wù)系統(tǒng)和數(shù)據(jù)倉庫之間的數(shù)據(jù)過渡離層。 如果業(yè)務(wù)數(shù)據(jù)來源比較復(fù)雜，一般采用構(gòu)造ODS的方法來實(shí)現(xiàn)收集當(dāng)前需要處理的數(shù)據(jù)。如下述數(shù)據(jù)來源：a、業(yè)務(wù)數(shù)

10、據(jù)庫種類繁多。業(yè)務(wù)交易系統(tǒng)使用了不同種的數(shù)據(jù)庫， 如DB2、Informix、Oracle、SQL server、文本等。b、不同的應(yīng)用系統(tǒng)、不同的地理位置。c、訂閱數(shù)據(jù)源。d、批量還原非傳統(tǒng)數(shù)據(jù)庫數(shù)據(jù)。. .等等。用于存放從業(yè)務(wù)系統(tǒng)直接抽取出來的數(shù)據(jù)，這些數(shù)據(jù)從數(shù)據(jù)結(jié)構(gòu)、數(shù)據(jù)之間的邏輯關(guān)系上都與業(yè)務(wù)系統(tǒng)基本保持一致。 2)、保存當(dāng)前或接近當(dāng)前的細(xì)節(jié)數(shù)據(jù)，以供查詢或ETL檢錯(cuò)使用。 3)、數(shù)據(jù)存儲周期性。ODS中存儲的數(shù)據(jù)都是臨時(shí)的，每次ETL之前都要清空ODS中存儲的數(shù)據(jù)。(2)、ETL(Extract Transform Load)操作型業(yè)務(wù)數(shù)據(jù)庫（DB）到數(shù)據(jù)倉庫（DW）的過程稱之為ET

11、L，它實(shí)現(xiàn)數(shù)據(jù)的抽取，轉(zhuǎn)換及裝載工作。抽?。簩?shù)據(jù)從各種原始的業(yè)務(wù)系統(tǒng)中讀取出來。 轉(zhuǎn)換：按照預(yù)先設(shè)計(jì)好的規(guī)則將抽取得數(shù)據(jù)進(jìn)行轉(zhuǎn)換、清洗，以及處理一些冗余、歧義的數(shù)據(jù)，使本來異構(gòu)的數(shù)據(jù)格式能統(tǒng)一起來。 裝載：將轉(zhuǎn)換完的數(shù)據(jù)按計(jì)劃增量或全部的導(dǎo)入到數(shù)據(jù)倉庫中。 在技術(shù)上主要涉及增量、轉(zhuǎn)換、調(diào)度和監(jiān)控等幾個(gè)方面的處理。 (3)、DW,(Data Warehouse) 數(shù)據(jù)倉庫的官方定義是一個(gè)面向主題的(Subject Oriented)、集成的(Integrate)、相對穩(wěn)定的(Non-Volatile)、反映歷史變化(Time Variant)的數(shù)據(jù)集合，用于支持管理決策。(4)、OLAP（On

12、-Line Analytical Processing）即聯(lián)機(jī)分析處理，是BI的一種全新的數(shù)據(jù)封裝方式，直接產(chǎn)物是報(bào)表或Cube，是使分析人員、管理人員或執(zhí)行人員能夠從多角度對信息進(jìn)行快速、一致、交互地存取,從而獲得對數(shù)據(jù)的更深入了解的一類軟件技術(shù)。第11頁/共56頁一、什么是BI（Business Intelligence）(一)、什么是BI的定義？(二)、BI的誕生？(三)、基本技術(shù)(四)、數(shù)據(jù)處理(五)、數(shù)據(jù)展現(xiàn)(六)、常用的BI 廠商和產(chǎn)品第12頁/共56頁一、什么是BI（Business Intelligence）上圖流程，簡單的說就是把交易系統(tǒng)已經(jīng)發(fā)生過的數(shù)據(jù)，通過ETL工具抽取到

13、主題明確的數(shù)據(jù)倉庫中，OLAP后生成Cube或報(bào)表，透過Portal展現(xiàn)給用戶，用戶利用這些經(jīng)過分類（Classification）、聚集（Clustering）、描述和可視化（Description and Visualization）的數(shù)據(jù)，支持業(yè)務(wù)決策。DB2、InformixOracleSysbase其他數(shù)據(jù)結(jié)構(gòu)類型第13頁/共56頁一、什么是BI（Business Intelligence）(五)、數(shù)據(jù)展現(xiàn) 數(shù)據(jù)查詢是最簡單的 BI 應(yīng)用，輸出報(bào)表是BI最直接的產(chǎn)物，根據(jù)數(shù)據(jù)連接，加工過程及用途，應(yīng)用模式大致可以分為四種：格式報(bào)表；在線分析；數(shù)據(jù)可視化；數(shù)據(jù)挖掘。1、格式報(bào)表：帶格式

14、的數(shù)據(jù)集合，如：交叉表等。2、在線分析：多維數(shù)據(jù)集合，如：Cube等。3、數(shù)據(jù)可視化：信息以盡可能多的形式展現(xiàn)出來，目的是使決策者通過圖形這種直觀的表現(xiàn)方式迅速獲得信息中蘊(yùn)藏的知識，如柱圖，儀表盤等。4、數(shù)據(jù)挖掘：從大量的數(shù)據(jù)中，抽取出潛在的、有價(jià)值的知識（模型或規(guī)則）的過程。(六)、常用的BI 廠商和產(chǎn)品ETL：Informatica, SQL Server Analysis ServerDW：IBM DB2，Oracle，NCR Teradata ，Sybase IQ等等；OLAP： Business Objects， Hyperion， Cognos，MicroStrategy，IBM

15、Data Mining：IBM，SAS，SPSS 現(xiàn)在很多的數(shù)據(jù)庫提供商都開始綁定BI的開發(fā)組件到自己的數(shù)據(jù)庫產(chǎn)品中，他們都瞄準(zhǔn)了這其中的肥肉，磨拳檫掌，各論長短。第14頁/共56頁一、什么是BI（Business Intelligence）上圖流程，簡單的說就是把交易系統(tǒng)已經(jīng)發(fā)生過的數(shù)據(jù)，通過ETL工具抽取到主題明確的數(shù)據(jù)倉庫中，OLAP后生成Cube或報(bào)表，透過Portal展現(xiàn)給用戶，用戶利用這些經(jīng)過分類（Classification）、聚集（Clustering）、描述和可視化（Description and Visualization）的數(shù)據(jù)，支持業(yè)務(wù)決策。DB2、InformixOr

16、acleSysbase其他數(shù)據(jù)結(jié)構(gòu)類型InformaticaSQL Server Analysis ServerIBM DB2OracleNCR TeradataBusiness ObjectsHyperionCognosMicroStrategy第15頁/共56頁一、什么是BI（Business Intelligence）(一)、什么是BI的定義？(二)、BI的誕生？(三)、基本技術(shù)(四)、數(shù)據(jù)處理(五)、數(shù)據(jù)展現(xiàn)(六)、常用的BI 廠商和產(chǎn)品第16頁/共56頁二、數(shù)據(jù)倉庫的架構(gòu)(一)、什么是數(shù)據(jù)倉庫？(二)、數(shù)據(jù)倉庫OLAP和OLTP數(shù)據(jù)庫的區(qū)別?(三)、數(shù)據(jù)倉庫的架構(gòu)模型有哪些？三、構(gòu)建

17、企業(yè)級數(shù)據(jù)倉庫五步法 (一)、確定主題 (二)、確定量度 (三)、確定事實(shí)數(shù)據(jù)粒度 (四)、確定維度1.如何理解維度？及維度的層次和級別?2.什么是緩慢變化維度？3.緩慢變化維度 幾種基本情況？ 幾種情況的利弊？ (五)、創(chuàng)建事實(shí)表第17頁/共56頁一、數(shù)據(jù)倉庫的架構(gòu)(一)、什么是數(shù)據(jù)倉庫？ 數(shù)據(jù)倉庫(Data Warehouse DW)是為了便于多維分析和多角度展現(xiàn)而將數(shù)據(jù)按特定的模式進(jìn)行存儲所建立起來的關(guān)系型數(shù)據(jù)庫，它的數(shù)據(jù)基于OLTP源系統(tǒng)。 數(shù)據(jù)倉庫中的數(shù)據(jù)是細(xì)節(jié)的、集成的、面向主題的，以O(shè)LAP系統(tǒng)的分析需求為目的。 四個(gè)基本特征:1.DW的數(shù)據(jù)是面向主題的2.DW的數(shù)據(jù)是集成的3.

18、DW的數(shù)據(jù)是非易失的(相對穩(wěn)定的)4.DW的數(shù)據(jù)是隨時(shí)間不斷變化的(反映歷史變化(Time Variant)第18頁/共56頁二、數(shù)據(jù)倉庫的架構(gòu)(一)、什么是數(shù)據(jù)倉庫？(二)、數(shù)據(jù)倉庫OLAP和OLTP數(shù)據(jù)庫的區(qū)別?(三)、數(shù)據(jù)倉庫的架構(gòu)模型有哪些？三、構(gòu)建企業(yè)級數(shù)據(jù)倉庫五步法 (一)、確定主題 (二)、確定量度 (三)、確定事實(shí)數(shù)據(jù)粒度 (四)、確定維度1.如何理解維度？及維度的層次和級別?2.什么是緩慢變化維度？3.緩慢變化維度 幾種基本情況？ 幾種情況的利弊？ (五)、創(chuàng)建事實(shí)表第19頁/共56頁(二)、數(shù)據(jù)倉庫OLAP和OLTP數(shù)據(jù)庫的區(qū)別?比較項(xiàng)目比較項(xiàng)目OLTPOLTPOLAPOL

19、AP特征特征操作處理信息處理面向面向事務(wù)分析用戶用戶DBA,數(shù)據(jù)庫專業(yè)人員業(yè)務(wù)分析員功能功能日常操作長期信息需求，決策支持DBDB結(jié)構(gòu)結(jié)構(gòu)基于E-R，面向應(yīng)用星型/雪花，面向主題數(shù)據(jù)數(shù)據(jù)當(dāng)前的歷史的匯總匯總原始的，高度詳細(xì)匯總的，統(tǒng)一的視圖視圖一般關(guān)系多維的查詢查詢簡單事務(wù)復(fù)雜查詢存取存取讀/寫讀操作操作主關(guān)鍵字上索引或散列大量掃描訪問數(shù)據(jù)量訪問數(shù)據(jù)量數(shù)筆數(shù)百萬筆用戶數(shù)用戶數(shù)數(shù)千數(shù)百DB2DB2規(guī)模規(guī)模MB/GBGB/TB優(yōu)先級優(yōu)先級高性能，高可用性高靈活性，端點(diǎn)用戶自治度量度量事務(wù)吞吐量查詢吞吐量，響應(yīng)時(shí)間第20頁/共56頁二、數(shù)據(jù)倉庫的架構(gòu)(一)、什么是數(shù)據(jù)倉庫？(二)、數(shù)據(jù)倉庫OLAP和

20、OLTP數(shù)據(jù)庫的區(qū)別?(三)、數(shù)據(jù)倉庫的架構(gòu)模型有哪些？三、構(gòu)建企業(yè)級數(shù)據(jù)倉庫五步法 (一)、確定主題 (二)、確定量度 (三)、確定事實(shí)數(shù)據(jù)粒度 (四)、確定維度1.如何理解維度？及維度的層次和級別?2.什么是緩慢變化維度？3.緩慢變化維度 幾種基本情況？ 幾種情況的利弊？ (五)、創(chuàng)建事實(shí)表第21頁/共56頁(三)、數(shù)據(jù)倉庫的架構(gòu)模型有哪些？1、星型架構(gòu)圖1.1(某零售分析系統(tǒng)) 星型架構(gòu)的中間為事實(shí)表，四周為維度表，類似星星2、 雪花型架構(gòu)圖1.2(某分析系統(tǒng)) 雪花型架構(gòu)的中間為事實(shí)表，兩邊的維度表可以再有其關(guān)聯(lián)子表，從而表達(dá)了清晰的維度層次關(guān)系。第22頁/共56頁3、兩者比較？ 從O

21、LAP系統(tǒng)的分析需求和ETL的處理效率兩方面來考慮：星型結(jié)構(gòu)聚合快，分析效率高;而雪花型結(jié)構(gòu)明確，便于與OLTP系統(tǒng)交互。 因此，在實(shí)際項(xiàng)目中，我們將綜合運(yùn)用星型架構(gòu)與雪花型架構(gòu)來設(shè)計(jì)數(shù)據(jù)倉庫。(三)、數(shù)據(jù)倉庫的架構(gòu)模型有哪些？第23頁/共56頁二、數(shù)據(jù)倉庫的架構(gòu)(一)、什么是數(shù)據(jù)倉庫？(二)、數(shù)據(jù)倉庫OLAP和OLTP數(shù)據(jù)庫的區(qū)別?(三)、數(shù)據(jù)倉庫的架構(gòu)模型有哪些？三、構(gòu)建企業(yè)級數(shù)據(jù)倉庫五步法 (一)、確定主題 (二)、確定量度 (三)、確定事實(shí)數(shù)據(jù)粒度 (四)、確定維度1.如何理解維度？及維度的層次和級別?2.什么是緩慢變化維度？3.緩慢變化維度 幾種基本情況？ 幾種情況的利弊？ (五)、

22、創(chuàng)建事實(shí)表第24頁/共56頁三、構(gòu)建企業(yè)級數(shù)據(jù)倉庫五步法(一)、確定主題 即確定數(shù)據(jù)分析或前端展現(xiàn)的主題。 (以上海煙草集團(tuán)零售分析系統(tǒng)為例，23家有限公司進(jìn)銷存情況) 例如：我們希望分析某月某有限公司某卷煙銷售情況，這就是一個(gè)主題。 主題要體現(xiàn)出某一方面的各分析角度(維度)和統(tǒng)計(jì)數(shù)值型數(shù)據(jù)(量度)之間的關(guān)系，確定主題時(shí)要綜合考慮。統(tǒng)計(jì)數(shù)值型數(shù)據(jù)(量度)存在于中間的事實(shí)表;分析角度是各個(gè)維度;我們將通過維度的組合，來考察量度。 那么，“某年某月某一地區(qū)的卷煙銷售情況”這樣一個(gè)主題，就要求我們通過時(shí)間，地區(qū)和卷煙三個(gè)維度的組合，來考察銷售情況這個(gè)量度。 從而，不同的主題來源于數(shù)據(jù)倉庫中的不同子集

23、，我們可以稱之為數(shù)據(jù)集市。數(shù)據(jù)集市體現(xiàn)了數(shù)據(jù)倉庫某一方面的信息，多個(gè)數(shù)據(jù)集市構(gòu)成了數(shù)據(jù)倉庫。(二)、確定量度 在確定了主題以后，我們將考慮要分析的技術(shù)指標(biāo)，諸如:年銷售額之類。它們一般為數(shù)值型數(shù)據(jù)。 我們或者將該數(shù)據(jù)匯總，或者將該數(shù)據(jù)取次數(shù)、獨(dú)立次數(shù)或取最大最小值等，這樣的數(shù)據(jù)稱為量度。 量度是要統(tǒng)計(jì)的指標(biāo)，必須事先選擇恰當(dāng)，基于不同的量度可以進(jìn)行復(fù)雜關(guān)鍵性能指標(biāo)(KPI)等的設(shè)計(jì)和計(jì)算。第25頁/共56頁三、構(gòu)建企業(yè)級數(shù)據(jù)倉庫五步法第26頁/共56頁二、數(shù)據(jù)倉庫的架構(gòu)(一)、什么是數(shù)據(jù)倉庫？(二)、數(shù)據(jù)倉庫OLAP和OLTP數(shù)據(jù)庫的區(qū)別?(三)、數(shù)據(jù)倉庫的架構(gòu)模型有哪些？三、構(gòu)建企業(yè)級數(shù)據(jù)倉

24、庫五步法 (一)、確定主題 (二)、確定量度 (三)、確定事實(shí)數(shù)據(jù)粒度 (四)、確定維度1.如何理解維度？及維度的層次和級別?2.什么是緩慢變化維度？3.緩慢變化維度 幾種基本情況？ 幾種情況的利弊？ (五)、創(chuàng)建事實(shí)表第27頁/共56頁(三)、確定事實(shí)數(shù)據(jù)粒度 在確定了量度之后，我們要考慮到該量度的匯總情況和不同維度下量度的聚合情況?？紤]到量度的聚合程度不同，我們將采用“最小粒度原則”，即將量度的粒度設(shè)置到最小。 例如：假設(shè)目前的數(shù)據(jù)最小記錄到包卷煙，即數(shù)據(jù)庫中記錄了每包卷煙的交易額。 那么，如果我們可以確認(rèn)，在將來的分析需求中，卷煙只需要精確到包就可以的話，我們就可以在ETL處理過程中，按

25、包為單位來匯總進(jìn)銷存數(shù)據(jù)，此時(shí)，數(shù)據(jù)倉庫中量度的粒度就是“包”; 反過來，如果我們不能確認(rèn)將來的分析需求在卷煙上是否需要精確到包，那么，我們就需要遵循“最小粒度原則”，在數(shù)據(jù)倉庫的事實(shí)表中保留每一支的進(jìn)銷存數(shù)據(jù)，以便日后對“支”進(jìn)行分析。 舉例:卷煙零售分析系統(tǒng)中，需求變更要求以萬支為單位分析卷煙進(jìn)銷存情況。 附加:DDS層 銷售，庫存及進(jìn)貨事實(shí)表結(jié)構(gòu) 和 卷煙單位代碼表三、構(gòu)建企業(yè)級數(shù)據(jù)倉庫五步法DDS.F_XS銷售事實(shí)表DDS.F_KC庫存事實(shí)表DDS.F_SY損益事實(shí)表DDS.F_JH進(jìn)貨事實(shí)表DDS.F_DH訂貨事實(shí)表第28頁/共56頁三、構(gòu)建企業(yè)級數(shù)據(jù)倉庫五步法第29頁/共56頁表名

26、表中文名稱表中文名稱序號字段名稱數(shù)據(jù)類型業(yè)務(wù)含義DDS.F_SY損益事實(shí)表1SYDMINTEGER NOT NULL損益代碼DDS.F_SY損益事實(shí)表2DAY_IINTEGER時(shí)間代碼DDS.F_SY損益事實(shí)表3KHDMINTEGER客戶代碼DDS.F_SY損益事實(shí)表4JYDMINTEGER卷煙代碼DDS.F_SY損益事實(shí)表5SYSLSRCDECIMAL(16 4)損益數(shù)量條DDS.F_SYDDS.F_SY損益事實(shí)表損益事實(shí)表6 6SYSLSYSLDECIMAL(16 4)DECIMAL(16 4)損益數(shù)量損益數(shù)量 支支 DDS.F_SY損益事實(shí)表7SYJEDECIMAL(16 2)損益金額D

27、DS.F_SY損益事實(shí)表8SYMLDECIMAL(16 2)損益毛利表名表中文名稱表中文名稱序號字段名稱數(shù)據(jù)類型業(yè)務(wù)含義DDS.F_JH進(jìn)貨事實(shí)表1JHDMINTEGER NOT NULL進(jìn)貨代碼DDS.F_JH進(jìn)貨事實(shí)表2DAY_IINTEGER時(shí)間代碼DDS.F_JH進(jìn)貨事實(shí)表3KHDMINTEGER客戶代碼DDS.F_JH進(jìn)貨事實(shí)表4JYDMINTEGER卷煙代碼DDS.F_JH進(jìn)貨事實(shí)表5JHSLSRCDECIMAL(16 4)進(jìn)貨數(shù)量條DDS.F_JHDDS.F_JH進(jìn)貨事實(shí)表進(jìn)貨事實(shí)表6 6JHSLJHSLDECIMAL(16 4)DECIMAL(16 4)進(jìn)貨數(shù)量進(jìn)貨數(shù)量 支支

28、DDS.F_JH進(jìn)貨事實(shí)表7JHJEDECIMAL(16 2)進(jìn)貨金額DDS.F_JH進(jìn)貨事實(shí)表8JHMLDECIMAL(16 2)進(jìn)貨毛利表名表中文名稱表中文名稱序號字段名稱數(shù)據(jù)類型業(yè)務(wù)含義DDS.F_DH訂貨事實(shí)表1DHDMINTEGER NOT NULL訂貨代碼DDS.F_DH訂貨事實(shí)表2DAY_IINTEGER時(shí)間代碼DDS.F_DH訂貨事實(shí)表3KHDMINTEGER客戶代碼DDS.F_DH訂貨事實(shí)表4JYDMINTEGER卷煙代碼DDS.F_DH訂貨事實(shí)表5DHSLSRCDECIMAL(16 4)訂貨數(shù)量條DDS.F_DHDDS.F_DH訂貨事實(shí)表訂貨事實(shí)表6 6DHSLDHSLDE

29、CIMAL(16 4)DECIMAL(16 4)訂貨數(shù)量訂貨數(shù)量 支支 DDS.F_DH訂貨事實(shí)表7DHJEDECIMAL(16 2)訂貨金額DDS.F_DH訂貨事實(shí)表8DHMLDECIMAL(16 2)訂貨毛利表名表中文名稱表中文名稱序號字段名稱數(shù)據(jù)類型業(yè)務(wù)含義DDS.F_XS銷售事實(shí)表1XSDMINTEGER NOT NULL銷售代碼DDS.F_XS銷售事實(shí)表2DAY_IINTEGER時(shí)間代碼DDS.F_XS銷售事實(shí)表3KHDMINTEGER客戶代碼DDS.F_XS銷售事實(shí)表4JYDMINTEGER卷煙代碼DDS.F_XS銷售事實(shí)表5XSSLSRCDECIMAL(16 4)銷售數(shù)量條DDS

30、.F_XSDDS.F_XS銷售事實(shí)表銷售事實(shí)表6 6XSSLXSSLDECIMAL(16 4)DECIMAL(16 4)銷售數(shù)量銷售數(shù)量 支支 DDS.F_XS銷售事實(shí)表7XSJEDECIMAL(16 2)銷售金額DDS.F_XS銷售事實(shí)表8XSMLDECIMAL(16 2)銷售毛利表名表中文名稱表中文名稱序號字段名稱數(shù)據(jù)類型業(yè)務(wù)含義DDS.F_KC庫存事實(shí)表1KCDMINTEGER NOT NULL庫存代碼DDS.F_KC庫存事實(shí)表2DAY_IINTEGER時(shí)間代碼DDS.F_KC庫存事實(shí)表3KHDMINTEGER客戶代碼DDS.F_KC庫存事實(shí)表4JYDMINTEGER卷煙代碼DDS.F_

31、KC庫存事實(shí)表5KCSLSRCDECIMAL(16 4)庫存數(shù)量條DDS.F_KCDDS.F_KC庫存事實(shí)表庫存事實(shí)表6 6KCSLKCSLDECIMAL(16 4)DECIMAL(16 4)庫存數(shù)量庫存數(shù)量 支支 DDS.F_KC庫存事實(shí)表7KCJEDECIMAL(16 2)庫存金額DDS.F_KC庫存事實(shí)表8KCMLDECIMAL(16 2)庫存毛利三、構(gòu)建企業(yè)級數(shù)據(jù)倉庫五步法第30頁/共56頁二、數(shù)據(jù)倉庫的架構(gòu)(一)、什么是數(shù)據(jù)倉庫？(二)、數(shù)據(jù)倉庫OLAP和OLTP數(shù)據(jù)庫的區(qū)別?(三)、數(shù)據(jù)倉庫的架構(gòu)模型有哪些？三、構(gòu)建企業(yè)級數(shù)據(jù)倉庫五步法 (一)、確定主題 (二)、確定量度 (三)、

32、確定事實(shí)數(shù)據(jù)粒度 (四)、確定維度1.如何理解維度？及維度的層次和級別?2.什么是緩慢變化維度？3.緩慢變化維度 幾種基本情況？ 幾種情況的利弊？ (五)、創(chuàng)建事實(shí)表第31頁/共56頁三、構(gòu)建企業(yè)級數(shù)據(jù)倉庫五步法(四)、確定維度 1、如何理解維度？及維度的層次(Hierarchy)和級別(Level) a、維度是指分析的各個(gè)角度。例如我們希望按照時(shí)間，或者按照地區(qū)，或者按照卷煙名稱進(jìn)行分析，那么這里的時(shí)間、地區(qū)、卷煙就是相應(yīng)的維度?；诓煌木S度，我們可以看到各量度的匯總情況，也可以基于所有的維度進(jìn)行交叉分析。 b、維度的層次(Hierarchy)和級別(Level)層次:時(shí)間維表結(jié)構(gòu) 為例，

33、年-半年-季度-月-半月-旬-周-日級別:卷煙維表結(jié)構(gòu) 為例，卷煙價(jià)格分類一類(如:中華，熊貓)、二類(如:紅雙喜等)、三類、 四類、五類(如:女士煙)、雪茄煙等等 2、什么是緩慢變化維度？維度表隨時(shí)間變化的問題，我們稱其為緩慢變化維度。以客戶維表結(jié)構(gòu)為例，每月所屬有限公司發(fā)生變化，客戶代碼，客戶標(biāo)識，源客戶標(biāo)識，客戶全稱，分公司編號,分公司名稱,歷史標(biāo)識(T/F)等變化第32頁/共56頁三、構(gòu)建企業(yè)級數(shù)據(jù)倉庫五步法DM.T_DIM_SNKH市內(nèi)客戶維表1LSBHINTEGER NOT NULL流水編號DM.T_DIM_SNKH市內(nèi)客戶維表2KHDMKHDMINTEGER客戶代碼DM.T_DI

34、M_SNKH市內(nèi)客戶維表3KHBSKHBSVARCHAR(50)客戶標(biāo)識DM.T_DIM_SNKH市內(nèi)客戶維表4YKHBSYKHBSVARCHAR(50)源客戶標(biāo)識DM.T_DIM_SNKH市內(nèi)客戶維表5KHQCKHQCVARCHAR(120)客戶全稱DM.T_DIM_SNKH市內(nèi)客戶維表6ZYYTBHVARCHAR(10)主營業(yè)態(tài)編號DM.T_DIM_SNKH市內(nèi)客戶維表7ZYYTBZVARCHAR(50)主營業(yè)態(tài)備注DM.T_DIM_SNKH市內(nèi)客戶維表8YTXXBHVARCHAR(10)業(yè)態(tài)細(xì)項(xiàng)編號DM.T_DIM_SNKH市內(nèi)客戶維表9YTXXBZVARCHAR(50)業(yè)態(tài)細(xì)項(xiàng)備注DM

35、.T_DIM_SNKH市內(nèi)客戶維表10XSNLBHVARCHAR(10)銷售能力編號DM.T_DIM_SNKH市內(nèi)客戶維表11XSNLBZVARCHAR(50)銷售能力備注DM.T_DIM_SNKH市內(nèi)客戶維表12CYNLBHVARCHAR(10)出樣能力編號DM.T_DIM_SNKH市內(nèi)客戶維表13CYNLBZVARCHAR(50)出樣能力備注DM.T_DIM_SNKH市內(nèi)客戶維表14YSFWBHVARCHAR(10)延伸服務(wù)編號DM.T_DIM_SNKH市內(nèi)客戶維表15YSFWBZVARCHAR(50)延伸服務(wù)備注DM.T_DIM_SNKH市內(nèi)客戶維表16KHJBBHVARCHAR(10)

36、客戶級別編號DM.T_DIM_SNKH市內(nèi)客戶維表17KHJBBZVARCHAR(50)客戶級別備注DM.T_DIM_SNKH市內(nèi)客戶維表18FGSBHFGSBHVARCHAR(10)分公司編號DM.T_DIM_SNKH市內(nèi)客戶維表19FGSBZFGSBZVARCHAR(50)分公司備注DM.T_DIM_SNKH市內(nèi)客戶維表20XZJWBHVARCHAR(10)行政居委編號DM.T_DIM_SNKH市內(nèi)客戶維表21XZJWBZVARCHAR(100)行政居委備注DM.T_DIM_SNKH市內(nèi)客戶維表22XZQYBHVARCHAR(10)行政區(qū)域編號DM.T_DIM_SNKH市內(nèi)客戶維表23XZ

37、QYBZVARCHAR(100)行政區(qū)域備注DM.T_DIM_SNKH市內(nèi)客戶維表24XZJDBHVARCHAR(10)行政街道編號DM.T_DIM_SNKH市內(nèi)客戶維表25XZJDBZVARCHAR(100)行政街道備注DM.T_DIM_SNKH市內(nèi)客戶維表26KSRQDATE開始日期DM.T_DIM_SNKH市內(nèi)客戶維表27JSRQDATE結(jié)束日期DM.T_DIM_SNKH市內(nèi)客戶維表28ZSWDBHVARCHAR(10)直屬網(wǎng)點(diǎn)編號DM.T_DIM_SNKH市內(nèi)客戶維表29ZSWDBZVARCHAR(50)直屬網(wǎng)點(diǎn)備注DM.T_DIM_SNKH市內(nèi)客戶維表30JZCHARACTER(6)

38、價(jià)值DM.T_DIM_SNKH市內(nèi)客戶維表31ZCCHARACTER(6)忠誠DM.T_DIM_SNKH市內(nèi)客戶維表32GXCHARACTER(6)貢獻(xiàn)DM.T_DIM_SNKH市內(nèi)客戶維表33FGSYTXXVARCHAR(50)分公司業(yè)態(tài)細(xì)項(xiàng)DM.T_DIM_SNKH市內(nèi)客戶維表34YXBJCHARACTER(1)營銷標(biāo)記DM.T_DIM_SNKH市內(nèi)客戶維表35YXMCVARCHAR(50)營銷名稱DM.T_DIM_SNKH市內(nèi)客戶維表36XXWDBHCHARACTER(10)信息網(wǎng)點(diǎn)編號DM.T_DIM_SNKH市內(nèi)客戶維表37XXWDBZCHARACTER(50)信息網(wǎng)點(diǎn)備注DM.T_

39、DIM_SNKH市內(nèi)客戶維表38CKTAGCKTAGCHARACTER(1) DEFAULT T 歷史與否標(biāo)識客戶維表結(jié)構(gòu)第33頁/共56頁三、構(gòu)建企業(yè)級數(shù)據(jù)倉庫五步法3、緩慢變化維度 幾種基本情況？ 幾種情況的利弊？ (1)、緩慢變化維度第一種類型：(UPDATE)-覆蓋歷史數(shù)據(jù)需要修改。這種情況下，我們使用UPDATE方法來修改維度表中的數(shù)據(jù)。例如：分公司編號=I0，分公司名稱=閘北,后來發(fā)現(xiàn)該客戶所屬區(qū)域已經(jīng)發(fā)生遷移，從閘北區(qū)遷移到了寶山區(qū)，處理方法:在ETL處理時(shí)，直接修改維度表中分公司編號=Q0，分公司名稱=寶山 (2)、緩慢變化維度第二種類型：(UPDATE / INSERT) -

40、分歷史選擇歷史數(shù)據(jù)保留，新增數(shù)據(jù)也要保留。這時(shí)，要將原數(shù)據(jù)更新，將新數(shù)據(jù)插入.例如：分公司編號=I0，分公司名稱=閘北,后來發(fā)現(xiàn)該客戶所屬區(qū)域已經(jīng)發(fā)生遷移，從閘北區(qū)遷移到了寶山區(qū)，處理方法:在ETL處理時(shí)，先將原先的客戶代碼，客戶標(biāo)識，源客戶標(biāo)識，客戶全稱，分公司編號,分公司名稱,歷史標(biāo)識 修改,然后插入新信息。UPDATE:客戶代碼 不變，客戶標(biāo)識 不變，源客戶標(biāo)識=客戶標(biāo)識，客戶全稱=*客戶全稱，分公司編號 不變,分公司名稱 不變,歷史標(biāo)識=F INSERT:客戶代碼=新客戶代碼，客戶標(biāo)識=新客戶標(biāo)識，源客戶標(biāo)識=update的客戶標(biāo)識，客戶全稱=客戶全稱，分公司編號=Q0，分公司名稱=寶

41、山,歷史標(biāo)識=T。 (3)、緩慢變化維度第三種類型：-交替實(shí)體新增數(shù)據(jù)維度成員改變了屬性。例如：某一維度成員新加入了一列，該列在歷史數(shù)據(jù)中不能基于它瀏覽，而在目前數(shù)據(jù)和將來數(shù)據(jù)中可以按照它瀏覽，那么此時(shí)我們需要改變維度表屬性，即加入新的字段列。那么，我們將使用存儲過程或程序生成新的維度屬性，在后續(xù)的數(shù)據(jù)中將基于新的屬性進(jìn)行查看。第34頁/共56頁二、數(shù)據(jù)倉庫的架構(gòu)(一)、什么是數(shù)據(jù)倉庫？(二)、數(shù)據(jù)倉庫OLAP和OLTP數(shù)據(jù)庫的區(qū)別?(三)、數(shù)據(jù)倉庫的架構(gòu)模型有哪些？三、構(gòu)建企業(yè)級數(shù)據(jù)倉庫五步法 (一)、確定主題 (二)、確定量度 (三)、確定事實(shí)數(shù)據(jù)粒度 (四)、確定維度1.如何理解維度？及

42、維度的層次和級別?2.什么是緩慢變化維度？3.緩慢變化維度 幾種基本情況？ 幾種情況的利弊？ (五)、創(chuàng)建事實(shí)表第35頁/共56頁三、構(gòu)建企業(yè)級數(shù)據(jù)倉庫五步法(五)、創(chuàng)建事實(shí)表 (1)、了解 在確定好事實(shí)數(shù)據(jù)和維度后，我們將考慮加載事實(shí)表。 事實(shí)表裝有企業(yè)的度量數(shù)據(jù)，度量以及事實(shí)表才是最終用戶真正想要看的內(nèi)容，維表是事實(shí)表數(shù)據(jù)的入口，事實(shí)只有通過維度解釋才會變得有意義。 (2)、如何創(chuàng)建？ 做法:將原始表(OLTP)與維度表進(jìn)行關(guān)聯(lián)，生成事實(shí)表. 注意點(diǎn):在關(guān)聯(lián)時(shí)有為空的數(shù)據(jù)時(shí)(數(shù)據(jù)源臟)，需要使用外連接，連接后我們將各維度的代理鍵取出放于事實(shí)表中，事實(shí)表除了各維度代理鍵外，還有各量度數(shù)據(jù)，這

43、將來自原始表，事實(shí)表中將存在維度代理鍵和各量度，而不應(yīng)該存在描述性信息，即符合“瘦高原則”，即要求事實(shí)表數(shù)據(jù)條數(shù)盡量多(粒度最小)，而描述性信息盡量少。事實(shí)數(shù)據(jù)表是數(shù)據(jù)倉庫的核心，需要精心維護(hù)，在JOIN后將得到事實(shí)數(shù)據(jù)表，一般記錄條數(shù)都比較大，我們需要為其設(shè)置復(fù)合主鍵和索引，以實(shí)現(xiàn)數(shù)據(jù)的完整性和基于數(shù)據(jù)倉庫的查詢性能優(yōu)化。事實(shí)數(shù)據(jù)表與維度表一起放于數(shù)據(jù)倉庫中，如果前端需要連接數(shù)據(jù)倉庫進(jìn)行查詢，我們還需要建立一些相關(guān)的中間匯總表或物化視圖，以方便查詢.舉例: 附加:HDS層，到 DDS層 銷售實(shí)事表結(jié)構(gòu)第36頁/共56頁三、構(gòu)建企業(yè)級數(shù)據(jù)倉庫五步法第37頁/共56頁三、構(gòu)建企業(yè)級數(shù)據(jù)倉庫五步法

44、表名表中文名稱表中文名稱序號序號字段名字段名數(shù)據(jù)類型數(shù)據(jù)類型業(yè)務(wù)含義業(yè)務(wù)含義HDS.TSL00_DAYHISHDS.TSL00_DAYHIS日結(jié)數(shù)據(jù)表1SERIALINTEGER NOT NULL 序列號HDS.TSL01_DAYHIS日結(jié)數(shù)據(jù)表2IMPORTDATEDATE 導(dǎo)入日期HDS.TSL02_DAYHIS日結(jié)數(shù)據(jù)表3ORIGINCHARACTER(4) 有限公司編號HDS.TSL03_DAYHIS日結(jié)數(shù)據(jù)表4CUSTCDCHARACTER(8) NOT NULL 門店編號HDS.TSL04_DAYHIS日結(jié)數(shù)據(jù)表5CIGCDCHARACTER(6) NOT NULL 卷煙編號HDS

45、.TSL05_DAYHIS日結(jié)數(shù)據(jù)表6INVDATEDATE NOT NULL 業(yè)務(wù)日期HDS.TSL06_DAYHIS日結(jié)數(shù)據(jù)表7INITQTYDECIMAL(15 4) 期初數(shù)量(條)HDS.TSL07_DAYHIS日結(jié)數(shù)據(jù)表8PCQTYDECIMAL(15 4) 收購數(shù)量(條)HDS.TSL08_DAYHIS日結(jié)數(shù)據(jù)表9SALQTYDECIMAL(15 4) 零售數(shù)量(條)HDS.TSL09_DAYHIS日結(jié)數(shù)據(jù)表10OVQTYDECIMAL(15 4) 損益數(shù)量(條)HDS.TSL10_DAYHIS日結(jié)數(shù)據(jù)表11INVQTYDECIMAL(15 4) 結(jié)余數(shù)量(條)HDS.TSL11_

46、DAYHIS日結(jié)數(shù)據(jù)表12SINITQTYDECIMAL(15 4) 期初數(shù)量(包)HDS.TSL12_DAYHIS日結(jié)數(shù)據(jù)表13SPCQTYDECIMAL(15 4) 收購數(shù)量(包)HDS.TSL13_DAYHIS日結(jié)數(shù)據(jù)表14SSALQTYDECIMAL(15 4) 零售數(shù)量(包)HDS.TSL14_DAYHIS日結(jié)數(shù)據(jù)表15SOVQTYDECIMAL(15 4) 損益數(shù)量(包)HDS.TSL15_DAYHIS日結(jié)數(shù)據(jù)表16SINVQTYDECIMAL(15 4) 結(jié)余數(shù)量(包)HDS.TSL16_DAYHIS日結(jié)數(shù)據(jù)表17CHECKQTYDECIMAL(15 4) 校驗(yàn)數(shù)量HDS.TSL

47、17_DAYHIS日結(jié)數(shù)據(jù)表18ACCPTTIMEDATE 接收日期HDS.TSL18_DAYHIS日結(jié)數(shù)據(jù)表19POSTDATEDATEPOS機(jī)上傳數(shù)據(jù)日期HDS層表結(jié)構(gòu)第38頁/共56頁三、構(gòu)建企業(yè)級數(shù)據(jù)倉庫五步法表名表中文名稱表中文名稱序號字段名稱數(shù)據(jù)類型業(yè)務(wù)含義DDS.F_XSDDS.F_XS銷售事實(shí)表1XSDMINTEGER NOT NULL銷售代碼DDS.F_XS銷售事實(shí)表2DAY_IINTEGER時(shí)間代碼DDS.F_XS銷售事實(shí)表3KHDMINTEGER客戶代碼DDS.F_XS銷售事實(shí)表4JYDMINTEGER卷煙代碼DDS.F_XS銷售事實(shí)表5XSSLSRCDECIMAL(16

48、 4)銷售數(shù)量條DDS.F_XS銷售事實(shí)表6XSSLDECIMAL(16 4)銷售數(shù)量支DDS.F_XS銷售事實(shí)表7XSJEDECIMAL(16 2)銷售金額DDS.F_XS銷售事實(shí)表8XSMLDECIMAL(16 2)銷售毛利表名表中文名稱表中文名稱序號字段名稱數(shù)據(jù)類型業(yè)務(wù)含義DDS.F_KCDDS.F_KC庫存事實(shí)表1KCDMINTEGER NOT NULL庫存代碼DDS.F_KC庫存事實(shí)表2DAY_IINTEGER時(shí)間代碼DDS.F_KC庫存事實(shí)表3KHDMINTEGER客戶代碼DDS.F_KC庫存事實(shí)表4JYDMINTEGER卷煙代碼DDS.F_KC庫存事實(shí)表5KCSLSRCDECIM

49、AL(16 4)庫存數(shù)量條DDS.F_KC庫存事實(shí)表6KCSLDECIMAL(16 4)庫存數(shù)量支DDS.F_KC庫存事實(shí)表7KCJEDECIMAL(16 2)庫存金額DDS.F_KC庫存事實(shí)表8KCMLDECIMAL(16 2)庫存毛利DDS層表結(jié)構(gòu)第39頁/共56頁二、數(shù)據(jù)倉庫的架構(gòu)(一)、什么是數(shù)據(jù)倉庫？(二)、數(shù)據(jù)倉庫OLAP和OLTP數(shù)據(jù)庫的區(qū)別?(三)、數(shù)據(jù)倉庫的架構(gòu)模型有哪些？三、構(gòu)建企業(yè)級數(shù)據(jù)倉庫五步法 (一)、確定主題 (二)、確定量度 (三)、確定事實(shí)數(shù)據(jù)粒度 (四)、確定維度1.如何理解維度？及維度的層次和級別?2.什么是緩慢變化維度？3.緩慢變化維度 幾種基本情況？ 幾

50、種情況的利弊？ (五)、創(chuàng)建事實(shí)表第40頁/共56頁四、什么是ETL(ETL中的關(guān)鍵技術(shù)) (一)、數(shù)據(jù)抽取(1)全量抽取 (2)增量抽取 增量數(shù)據(jù)抽取中常用的捕獲變化數(shù)據(jù)的方法:a.觸發(fā)器b.時(shí)間戳c.全表比對d.日志對比 (二)、數(shù)據(jù)轉(zhuǎn)換和加工 (1)ETL引擎中的數(shù)據(jù)轉(zhuǎn)換和加工 (2)在數(shù)據(jù)庫中進(jìn)行數(shù)據(jù)加工 (三)、數(shù)據(jù)裝載(1)直接SQL語句進(jìn)行insert、update、delete操作 (2)采用批量裝載方法，如bcp、bulk、關(guān)系數(shù)據(jù)庫特有的批量裝載工具或api (四)、主流ETL工具介紹第41頁/共56頁四、什么是ETL(ETL中的關(guān)鍵技術(shù))(零)、什么是ETL a、在數(shù)據(jù)倉

51、庫的構(gòu)建中，ETL貫穿于項(xiàng)目始終，它是整個(gè)數(shù)據(jù)倉庫的生命線，包括了數(shù)據(jù)清洗、整合、轉(zhuǎn)換、加載等各個(gè)過程。如果說數(shù)據(jù)倉庫是一座大廈，那么ETL就是大廈的根基。ETL抽取整合數(shù)據(jù)的好壞直接影響到最終的結(jié)果展現(xiàn)。所以ETL在整個(gè)數(shù)據(jù)倉庫項(xiàng)目中起著十分關(guān)鍵的作用，必須擺到十分重要的位置。 b、ETL是數(shù)據(jù)抽取(Extract)、轉(zhuǎn)換(Transform)、加載(Load )的簡寫， 它是指：將OLTP系統(tǒng)中的數(shù)據(jù)抽取出來，并將不同數(shù)據(jù)源的數(shù)據(jù)進(jìn)行轉(zhuǎn)換和整合，得出一致性的數(shù)據(jù)，然后加載到數(shù)據(jù)倉庫中。 c、簡而言之，通過ETL，我們可以基于源系統(tǒng)中的數(shù)據(jù)來生成數(shù)據(jù)倉庫。ETL為我們搭建了OLTP系統(tǒng)和OL

52、AP系統(tǒng)之間的橋梁。 如下圖:第42頁/共56頁四、什么是ETL(ETL中的關(guān)鍵技術(shù)) (一)、數(shù)據(jù)抽取(1)全量抽取 (2)增量抽取 增量數(shù)據(jù)抽取中常用的捕獲變化數(shù)據(jù)的方法:a.觸發(fā)器b.時(shí)間戳c.全表比對d.日志對比 (二)、數(shù)據(jù)轉(zhuǎn)換和加工 (1)ETL引擎中的數(shù)據(jù)轉(zhuǎn)換和加工 (2)在數(shù)據(jù)庫中進(jìn)行數(shù)據(jù)加工 (三)、數(shù)據(jù)裝載(1)直接SQL語句進(jìn)行insert、update、delete操作 (2)采用批量裝載方法，如bcp、bulk、關(guān)系數(shù)據(jù)庫特有的批量裝載工具或api (四)、主流ETL工具介紹第43頁/共56頁四、什么是ETL(ETL中的關(guān)鍵技術(shù))(一)、數(shù)據(jù)抽取 數(shù)據(jù)抽取是從數(shù)據(jù)源中

53、抽取數(shù)據(jù)的過程。實(shí)際應(yīng)用中，數(shù)據(jù)源較多采用的是關(guān)系數(shù)據(jù)庫。(1)全量抽取 全量抽取類似于數(shù)據(jù)遷移或數(shù)據(jù)復(fù)制，它將數(shù)據(jù)源中的表或視圖的數(shù)據(jù)原封不動(dòng)的從數(shù)據(jù)庫中抽取出來，并轉(zhuǎn)換成自己的ETL工具可以識別的格式。全量抽取比較簡單。例如: 數(shù)據(jù)源表HDS.SYC_TSL00_DAYHIS(日結(jié)數(shù)據(jù)表)第44頁/共56頁四、什么是ETL(ETL中的關(guān)鍵技術(shù)) (一)、數(shù)據(jù)抽取(1)全量抽取 (2)增量抽取 增量數(shù)據(jù)抽取中常用的捕獲變化數(shù)據(jù)的方法:a.觸發(fā)器b.時(shí)間戳c.全表比對d.日志對比 (二)、數(shù)據(jù)轉(zhuǎn)換和加工 (1)ETL引擎中的數(shù)據(jù)轉(zhuǎn)換和加工 (2)在數(shù)據(jù)庫中進(jìn)行數(shù)據(jù)加工 (三)、數(shù)據(jù)裝載(1)直

54、接SQL語句進(jìn)行insert、update、delete操作 (2)采用批量裝載方法，如bcp、bulk、關(guān)系數(shù)據(jù)庫特有的批量裝載工具或api (四)、主流ETL工具介紹第45頁/共56頁四、什么是ETL(ETL中的關(guān)鍵技術(shù))(2)增量抽取 增量數(shù)據(jù)抽取中常用的捕獲變化數(shù)據(jù)的方法: a.觸發(fā)器 (不談) 在要抽取的表上建立需要的觸發(fā)器，一般要建立插入、修改、刪除三個(gè)觸發(fā)器，每當(dāng)源表中的數(shù)據(jù)發(fā)生變化，就被相應(yīng)的觸發(fā)器將變化的數(shù)據(jù)寫入一個(gè)臨時(shí)表，抽取線程從臨時(shí)表中抽取數(shù)據(jù)，臨時(shí)表中抽取過的數(shù)據(jù)被標(biāo)記或刪除。觸發(fā)器方式優(yōu)缺點(diǎn):優(yōu)點(diǎn):是數(shù)據(jù)抽取的性能較高.缺點(diǎn):是要求業(yè)務(wù)表建立觸發(fā)器，對業(yè)務(wù)系統(tǒng)有一定

55、的影響。 b.時(shí)間戳(談) 它是一種基于快照比較的變化數(shù)據(jù)捕獲方式，在源表上增加一個(gè)時(shí)間戳字段，系統(tǒng)中更新修改表數(shù)據(jù)的時(shí)候，同時(shí)修改時(shí)間戳字段的值。 當(dāng)進(jìn)行數(shù)據(jù)抽取時(shí)，通過比較系統(tǒng)時(shí)間與時(shí)間戳字段的值來決定抽取哪些數(shù)據(jù)。有的數(shù)據(jù)庫的時(shí)間戳支持自動(dòng)更新，即表的其它字段的數(shù)據(jù)發(fā)生改變時(shí)，自動(dòng)更新時(shí)間戳字段的值。有的數(shù)據(jù)庫不支持時(shí)間戳的自動(dòng)更新，這就要求業(yè)務(wù)系統(tǒng)在更新業(yè)務(wù)數(shù)據(jù)時(shí)，手工更新時(shí)間戳字段。 同觸發(fā)器方式一樣，時(shí)間戳方式的性能也比較好，數(shù)據(jù)抽取相對清楚簡單，但對業(yè)務(wù)系統(tǒng)也有很大的傾入性(加入額外的時(shí)間戳字段)，特別是對不支持時(shí)間戳的自動(dòng)更新的數(shù)據(jù)庫，還要求業(yè)務(wù)系統(tǒng)進(jìn)行額外的更新時(shí)間戳操作。另

56、外，無法捕獲對時(shí)間戳以前數(shù)據(jù)的delete和update操作,在數(shù)據(jù)準(zhǔn)確性上受到了一定的限制。 附加:數(shù)據(jù)源表HDS.SYC_TSL00_DAYHIS(日結(jié)數(shù)據(jù)表)表結(jié)構(gòu)。loaddate:加載日期 。交易日期V_MONTH 參考:HDS 4 TABLE.xls c.全表比對(不談) d.日志對比(不談)第46頁/共56頁四、什么是ETL(ETL中的關(guān)鍵技術(shù))第47頁/共56頁四、什么是ETL(ETL中的關(guān)鍵技術(shù))DB2DB2DB2DB2表名表名表中文名稱表中文名稱 序號序號字段名稱字段名稱數(shù)據(jù)類型數(shù)據(jù)類型業(yè)務(wù)含義業(yè)務(wù)含義映射規(guī)則映射規(guī)則目標(biāo)表名目標(biāo)表名表中文名稱表中文名稱 序號序號字段名字段

57、名數(shù)據(jù)類型數(shù)據(jù)類型業(yè)務(wù)含義業(yè)務(wù)含義HDS.SYC_TSL00_DAYHISHDS.SYC_TSL00_DAYHIS 日結(jié)數(shù)據(jù)表1COMPCDCHARACTER(4) 有限公司編號間接HDS.TSL00_DAYHIHDS.TSL00_DAYHIS S日結(jié)數(shù)據(jù)表1SERIALINTEGER NOT NULL 序列號HDS.SYC_TSL00_DAYHIS 日結(jié)數(shù)據(jù)表2V_MONTHDATE NOT NULL 交易日期HDS.TSL00_DAYHIS日結(jié)數(shù)據(jù)表2IMPORTDATE DATE 導(dǎo)入日期HDS.SYC_TSL00_DAYHIS 日結(jié)數(shù)據(jù)表3STORECDCHARACTER(8)客戶編號

58、HDS.TSL00_DAYHIS日結(jié)數(shù)據(jù)表3ORIGINCHARACTER(4) 有限公司編號HDS.SYC_TSL00_DAYHIS 日結(jié)數(shù)據(jù)表4ITEMCDCHARACTER(8)卷煙編號HDS.TSL00_DAYHIS日結(jié)數(shù)據(jù)表4CUSTCDCHARACTER(8) NOT NULL 門店編號HDS.SYC_TSL00_DAYHIS 日結(jié)數(shù)據(jù)表5INITQTYDECIMAL(12 3) 期初數(shù)量(包)HDS.TSL00_DAYHIS日結(jié)數(shù)據(jù)表5CIGCDCHARACTER(6) NOT NULL 卷煙編號HDS.SYC_TSL00_DAYHIS 日結(jié)數(shù)據(jù)表6PCQTYDECIMAL(12

59、 3) 收購數(shù)量(包)HDS.TSL00_DAYHIS日結(jié)數(shù)據(jù)表6INVDATEDATE NOT NULL 業(yè)務(wù)日期HDS.SYC_TSL00_DAYHIS 日結(jié)數(shù)據(jù)表7SALQTYDECIMAL(12 3) 零售數(shù)量(包)HDS.TSL00_DAYHIS日結(jié)數(shù)據(jù)表7INITQTYDECIMAL(15 4) 期初數(shù)量(條)HDS.SYC_TSL00_DAYHIS 日結(jié)數(shù)據(jù)表8OVQTYDECIMAL(12 3) 損益數(shù)量(包)HDS.TSL00_DAYHIS日結(jié)數(shù)據(jù)表8PCQTYDECIMAL(15 4) 收購數(shù)量(條)HDS.SYC_TSL00_DAYHIS 日結(jié)數(shù)據(jù)表9INVQTYDECI

60、MAL(12 3) 結(jié)余數(shù)量(包)HDS.TSL00_DAYHIS日結(jié)數(shù)據(jù)表9SALQTYDECIMAL(15 4) 零售數(shù)量(條)HDS.SYC_TSL00_DAYHIS 日結(jié)數(shù)據(jù)表10CHECKQTYDECIMAL(12 3) 校驗(yàn)數(shù)量HDS.TSL00_DAYHIS日結(jié)數(shù)據(jù)表10OVQTYDECIMAL(15 4) 損益數(shù)量(條)HDS.SYC_TSL00_DAYHIS 日結(jié)數(shù)據(jù)表11ACCEPTTIME DATE 接收日期HDS.TSL00_DAYHIS日結(jié)數(shù)據(jù)表11INVQTYDECIMAL(15 4) 結(jié)余數(shù)量(條)HDS.SYC_TSL00_DAYHIS 日結(jié)數(shù)據(jù)表12TRANS