SQLServer數(shù)據(jù)倉(cāng)庫(kù)最佳實(shí)踐

1、SQL Server 數(shù)據(jù)倉(cāng)庫(kù)最佳實(shí)踐Level 300課程內(nèi)容數(shù)據(jù)倉(cāng)庫(kù)設(shè)計(jì)與最佳實(shí)踐設(shè)計(jì)最佳實(shí)踐案例2數(shù)據(jù)倉(cāng)庫(kù)基本概念數(shù)據(jù) 和 信息 的區(qū)別數(shù)據(jù) 是由可觀察和可記錄的事實(shí)組成，通常存在于OLTP系統(tǒng)中。數(shù)據(jù)只有被加工處理為 信息 后，才有意義。信息 是經(jīng)過(guò)加工處理并對(duì)人類(lèi)客觀行為產(chǎn)生影響的數(shù)據(jù)表現(xiàn)形式。 3Kimball 數(shù)據(jù)倉(cāng)庫(kù)建設(shè)4一、定義業(yè)務(wù)需求了解業(yè)務(wù)訪談業(yè)務(wù)人員和IT記錄匯報(bào)從業(yè)務(wù)需求中提煉業(yè)務(wù)流程建立初始總線矩陣5說(shuō)明提煉業(yè)務(wù)流程初始化數(shù)據(jù)倉(cāng)庫(kù)總線矩陣6二、維度建模7四步建模過(guò)程識(shí)別業(yè)務(wù)流程確定粒度選擇維度選擇事實(shí)8Adventure Works Cycles公司企業(yè)數(shù)據(jù)倉(cāng)庫(kù)總線

2、矩陣-全公司9定義業(yè)務(wù)流程優(yōu)先級(jí)業(yè)務(wù)流程數(shù)據(jù)可行性低，業(yè)務(wù)價(jià)值/影響低10確定粒度維度中粒度表示法業(yè)務(wù)流程的粒度及基礎(chǔ)度量11業(yè)務(wù)角色矩陣定義角色，明晰每個(gè)角色需要看的業(yè)務(wù)流程數(shù)據(jù)進(jìn)一步可進(jìn)行數(shù)據(jù)安全性的設(shè)計(jì)12定義維度屬性 / 數(shù)據(jù)質(zhì)量分析定義維度屬性源系統(tǒng)數(shù)據(jù)質(zhì)量分析13為每個(gè)業(yè)務(wù)流程定義詳細(xì)維度模型Sales OrderItem QuantityUnit CostTotal CostUnit PriceSales AmountShipping CostTime(Order Date and Ship Date)SalespersonCustomerProductCalendar Year

3、 Month DateFiscal Year Fiscal Quarter Month DateRegion Country TerritoryManager NameNameCountry State or Province CityAgeMarital StatusGenderCategory Subcategory Product NameColorSize14根據(jù)維度模型詳細(xì)定義進(jìn)行數(shù)據(jù)倉(cāng)庫(kù)邏輯設(shè)計(jì) 維度表 事實(shí)表 星型架構(gòu)15雪花型架構(gòu)以下情況考慮:在多個(gè)維度之間共享的子維度存在層次結(jié)構(gòu)，并且維度表包含變化頻繁的小的數(shù)據(jù)子集多個(gè)不同粒度的事實(shí)表引用到維度層次結(jié)構(gòu)的不同層級(jí)DimSal

4、esPersonSalesPersonKeySalesPersonNameStoreKeyDimProductProductKeyProductNameProductLineKeySupplierKeyDimCustomerCustomerKeyCustomerNameGeographyKeyFactOrdersCustomerKeySalesPersonKeyProductKeyShippingAgentKeyTimeKeyOrderNoLineItemNoQuantityRevenueCostProfitDimDateDateKeyYearQuarterMonthDayDimShippin

5、gAgentShippingAgentKeyShippingAgentNameDimProductLineProductLineKeyProductLineNameDimGeographyGeographyKeyCityRegionDimSupplierSupplierKeySupplierNameDimStoreStoreKeyStoreNameGeographyKey16維度表邏輯設(shè)計(jì)-鍵代理建業(yè)務(wù)鍵17屬性和層次結(jié)構(gòu)層次結(jié)構(gòu)切片鉆取明細(xì)18Unknown 和 None識(shí)別 NULL 值的含義Unknown 還是 None?不要假設(shè) NULL 等價(jià)使用 ISNULL( )源維度表19設(shè)計(jì)緩

6、慢漸變維度類(lèi)型1類(lèi)型 2類(lèi)型 320時(shí)間維度表代理鍵粒度范圍屬性和層次結(jié)構(gòu)多日歷未知值ETL 還是預(yù)先填充？21自關(guān)聯(lián)的維度表 Kim Abercrombie Kamil Amireh Jeff Hay Cesar Garcia備注：如果層次固定，還是建議重構(gòu)成固定層次的平面表，然后創(chuàng)建層次結(jié)構(gòu)，父子層次結(jié)構(gòu)如果中間層級(jí)過(guò)多，性能不好。22 垃圾（Junk）維度將不屬于已有維度的低基數(shù)屬性合并到一起避免創(chuàng)建很多小的維度表23事實(shí)表設(shè)計(jì)-列維度鍵度量值退化維度24度量類(lèi)型累加半累加不可累加25事實(shí)表的三種類(lèi)型26示例事務(wù)型事實(shí)表周期性快照事實(shí)表累計(jì)快照事實(shí)表27價(jià)值鏈跨業(yè)務(wù)流程共享維度28設(shè)計(jì)之

7、其他考慮-Factless 事實(shí)表如何獲取正在參加促銷(xiāo)活動(dòng)但是沒(méi)有銷(xiāo)售出去的產(chǎn)品信息？29設(shè)計(jì)之其他考慮-維度和事實(shí)之間多對(duì)多多個(gè)維度值指派到一個(gè)事實(shí)交易如果需要為每一個(gè)銷(xiāo)售代表分配銷(xiāo)售，可以在中間表添加權(quán)重30設(shè)計(jì)之其他考慮-維度和維度之間多對(duì)多例如：一個(gè)客戶(hù)有一個(gè)或多個(gè)Account，一個(gè)Account對(duì)應(yīng)一個(gè)或多個(gè)客戶(hù)31三、技術(shù)架構(gòu)設(shè)計(jì)32微軟DW / BI 系統(tǒng)體系結(jié)構(gòu)33服務(wù)器架構(gòu)（1）34服務(wù)器架構(gòu)（2）35開(kāi)發(fā)團(tuán)隊(duì)配置36四、數(shù)據(jù)倉(cāng)庫(kù)物理設(shè)計(jì)37物理數(shù)據(jù)分布跨物理設(shè)備分布數(shù)據(jù):使用文件組和RAID存儲(chǔ)（推薦RAID10）數(shù)據(jù)文件和日志文件分開(kāi)存儲(chǔ)工作區(qū)對(duì)象單獨(dú)存儲(chǔ)：例如，臨時(shí)的

8、數(shù)據(jù)表預(yù)先分類(lèi)空間禁用自動(dòng)增長(zhǎng)為所有文件分配一樣的尺寸38維度表-索引代理鍵創(chuàng)建聚集索引業(yè)務(wù)鍵創(chuàng)建非聚集索引Include（代理鍵），加速Lookup效率經(jīng)常查詢(xún)的屬性創(chuàng)建非聚集索引對(duì)于非常大的包含緩變屬性的維度創(chuàng)建四列索引：索引鍵（業(yè)務(wù)鍵，開(kāi)始日期），包含（結(jié)束時(shí)間，代理鍵）這樣可以加速ETL處理過(guò)程39維度表-視圖通過(guò)視圖封裝例如在雪花型架構(gòu)中，通過(guò)創(chuàng)建視圖可以把多個(gè)維度表連接起來(lái)可以將視圖定義成索引視圖，從而將數(shù)據(jù)物理化40維度表物理創(chuàng)建腳本（示例 All in One）CREATE TABLE dbo.DimProduct(ProductKey int IDENTITY(1,1) NO

9、T NULL,BKProductSKU nvarchar (25) NOT NULL DEFAULT NZZ-000-ZZ,ProductName nvarchar(50) NOT NULLDEFAULT NProduct unknown or not provided,ProductSubCategory nvarchar(50) NOT NULLDEFAULT NProduct Subcategory unknown or not provided,ProductCategory nvarchar(50) NOT NULLDEFAULT NProduct Category unknown

10、or not provided,CONSTRAINT PK_dbo.DimProduct PRIMARY KEYCLUSTERED (ProductKeyASC) ON DimFileGroupWITH ( DATA_COMPRESSION = PAGE ); - only if this is a very bigdimension- 為表描述創(chuàng)建擴(kuò)展屬性exec sys.sp_addextendedproperty name=NTable Description,value=NInformation about products, level0type=NSCHEMA,level0name

11、=dbo, level1type=NTABLE, level1name=DimProduct;GO;- 創(chuàng)建用戶(hù)訪問(wèn)視圖CREATE VIEW Product AS SELECT ProductKey, BKProductSKU,ProductName, ProductSubCategory, ProductCategoryFROM DimProduct;GO;41事實(shí)表-數(shù)據(jù)類(lèi)型 / 約束數(shù)據(jù)類(lèi)型約束盡量避免主鍵和外鍵，加快數(shù)據(jù)加載完整性靠ETL來(lái)保障事實(shí)列類(lèi)型最快的 SQL Server 數(shù)據(jù)類(lèi)型代理鍵tinyint、smallint、int、bigint日期鍵采用 yyyyMMdd 格式

12、的 int整數(shù)度量值tinyint、smallint、int、bigint 數(shù)值度量值smallmoney、money、real、float（請(qǐng)注意，與 money 和 float 類(lèi)型相比，decimal 和 vardecimal 要求更強(qiáng)大的 CPU 處理能力）非重復(fù)計(jì)數(shù)列tinyint、smallint、int、bigint （如果您的計(jì)數(shù)列是 char，則考慮執(zhí)行哈希計(jì)算或用代理鍵進(jìn)行替換）42事實(shí)表-分區(qū)對(duì)大事實(shí)表進(jìn)行分區(qū):通常是日期鍵好處:通過(guò)分區(qū)表并行掃描提高查詢(xún)性能提高CUBE處理速度快速加載和刪除改進(jìn)索引管理性增強(qiáng)備份和還原的靈活性使用分區(qū)對(duì)其的索引視圖:索引視圖和表分區(qū)對(duì)區(qū)

13、實(shí)現(xiàn)過(guò)程創(chuàng)建文件組和文件創(chuàng)建分區(qū)函數(shù)創(chuàng)建分區(qū)方案創(chuàng)建分區(qū)表對(duì)于已經(jīng)存在的事實(shí)表可以通過(guò)重建聚集索引來(lái)分區(qū)Pre-JanJanJunJulDec43分區(qū)數(shù)據(jù)分布與操作示例44滑動(dòng)窗口保持一個(gè)時(shí)間段內(nèi)的事實(shí)數(shù)據(jù)在線加載最新的數(shù)據(jù)，卸載最老的數(shù)據(jù)兩種加載新數(shù)據(jù)的方式一次性加載整個(gè)新分區(qū)增量加載最新分區(qū)總為兩端各保留一個(gè)空分區(qū)45加載最新數(shù)據(jù)在與目標(biāo)分區(qū)所在的文件組上創(chuàng)建中間表 (5/2008)拆分 最新的分區(qū)批量加載（Bulk Insert 或者bcp）并索引中間表交換 數(shù)據(jù)到次新分區(qū)46卸載過(guò)期數(shù)據(jù)在目標(biāo)分區(qū)同一filegroup上創(chuàng)建用于卸載的表交換 數(shù)據(jù)到表中合并 第一個(gè)和第二個(gè)分區(qū)存檔或清空

14、表47避免 拆分 / 合并 非空分區(qū)效率很低額外的日志只Split / Merge 空分區(qū)即使需要臨時(shí)用 SWITCH 清空分區(qū)48加載歷史數(shù)據(jù)使用 簡(jiǎn)單或 大容量日志恢復(fù)模式創(chuàng)建沒(méi)有索引的分區(qū)表為每個(gè)分區(qū)創(chuàng)建沒(méi)有索引的表并發(fā)加載每個(gè)分區(qū)對(duì)應(yīng)的表交換所有表到對(duì)應(yīng)的分區(qū)創(chuàng)建聚集索引創(chuàng)建非聚集索引49刪除數(shù)據(jù)盡可能使用交換分區(qū)從未分區(qū)的表中刪除大量行避免 delete fromwhere帶來(lái)大量鎖和日志INSERT需要的行到新表通常更快50演示演示1 1：對(duì)事實(shí)表分區(qū)：對(duì)事實(shí)表分區(qū)演示演示2 2：加載新數(shù)據(jù)加載新數(shù)據(jù)演示演示3 3：歸檔舊數(shù)據(jù)：歸檔舊數(shù)據(jù)演示演示4 4：實(shí)現(xiàn)滑動(dòng)窗口：實(shí)現(xiàn)滑動(dòng)窗口5

15、1事實(shí)表-索引索引建議為日期列創(chuàng)建聚集索引，如果有多個(gè)日期列，則選擇其中一個(gè)（如OrderDate）支持某一時(shí)間段的快速順序掃描如果需要分區(qū)，先考慮分區(qū)字段為每一個(gè)外鍵列創(chuàng)建非聚集索引Index（外鍵，日期），用于針對(duì)某一維度的選擇性查詢(xún)除非關(guān)聯(lián)的維度基數(shù)很低列存儲(chǔ)索引（SQL Server 2012以后的版本）檢查缺失的索引sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups and sys.dm_db_missing_index_details檢查索引索引sys.dm_db_index_physical_st

16、ats 的 avg_fragmentation_in_percent ，不應(yīng)該大于25%52事實(shí)表-數(shù)據(jù)壓縮減少物理空間需求可以改進(jìn)IO綁定查詢(xún)的性能CPU綁定的查詢(xún)額外20-30%的負(fù)載兩種壓縮方式:行壓縮：將定長(zhǎng)字段存儲(chǔ)成變長(zhǎng)字段頁(yè)壓縮：在一個(gè)頁(yè)上存儲(chǔ)冗余數(shù)據(jù)的一個(gè)實(shí)例可以在以下對(duì)象上啟用:表索引分區(qū)Pre-Jan(頁(yè)壓縮)JanJun(行壓縮)JulDec(不壓縮)例如: 一月份之前很少訪問(wèn) 一月到六月中等頻率訪問(wèn) 七月到十二月經(jīng)常頻繁訪問(wèn)53事實(shí)表-數(shù)據(jù)壓縮示例AdventureworksDW數(shù)據(jù)倉(cāng)庫(kù)中的FactInternetSales事實(shí)表5455課程內(nèi)容數(shù)據(jù)倉(cāng)庫(kù)設(shè)計(jì)與最佳實(shí)踐設(shè)計(jì)

17、最佳實(shí)踐案例ETL設(shè)計(jì)與最佳實(shí)踐設(shè)計(jì)最佳實(shí)踐案例CUBE設(shè)計(jì)與最佳實(shí)踐設(shè)計(jì)最佳實(shí)踐案例56 一、考慮對(duì)大事實(shí)表分區(qū)大事實(shí)表：50100GB及以上查詢(xún)被限制在一個(gè)分區(qū)內(nèi)快速完成通常在日期鍵對(duì)事實(shí)表分區(qū)啟用滑動(dòng)窗口57二、在事實(shí)表的日期字段創(chuàng)建聚集索引可以高效的CUBE處理（CUBE處理的時(shí)候可以并行處理多個(gè)分區(qū)）以及檢索歷史數(shù)據(jù)切片如果在批窗口加載數(shù)據(jù)，可以在創(chuàng)建或者重建事實(shí)表的聚集索引的時(shí)候使用ALLOW_ROW_LOCKS = OFF 和 ALLOW_PAGE_LOCKS = OFF。這可以加速查詢(xún)時(shí)表掃描操作并可以幫助在大量數(shù)據(jù)更新的時(shí)候避免過(guò)度的鎖活動(dòng)。為每一個(gè)外鍵建立非聚集索引，這有助

18、于基于選擇的維度謂詞進(jìn)行精確的查詢(xún)來(lái)獲取數(shù)據(jù)使用文件組用于管理目的，例如備份/還原，部分?jǐn)?shù)據(jù)庫(kù)可用性等。58三、小心的選擇分區(qū)粒度大多數(shù)客戶(hù)使用月，季度或者年。為了有效刪除，必須一次刪除一個(gè)分區(qū)。一次加載一個(gè)完成的分區(qū)非?？鞂?duì)于每日的數(shù)據(jù)加載進(jìn)行按日的分區(qū)是一個(gè)有吸引力的選擇SQL Server 2008 SP1只支持1000個(gè)分區(qū)，SP2則可以支持15000個(gè)分區(qū)。分區(qū)粒度影響查詢(xún)的并行性并行線程（注意MAXDOP設(shè)置）是分布式的掃描分區(qū)，并且即時(shí)多個(gè)分區(qū)需要掃描的時(shí)候，每個(gè)分區(qū)都可以使用多個(gè)線程。如果經(jīng)常執(zhí)行的查詢(xún)只訪問(wèn)23個(gè)分區(qū)，不建議進(jìn)行分區(qū)設(shè)計(jì)（如果需要MAXDOP并行，假設(shè)MAXD

19、OP=4或更高）59四、正確的設(shè)計(jì)維度表為所有維度的代理鍵使用整型數(shù)據(jù)類(lèi)型，越小越好，這樣可以使得事實(shí)表比較窄。使用有意義的整數(shù)型日期鍵（例如： 20060215，根據(jù)日期生成）不要使用代理鍵很容易在寫(xiě)查詢(xún)時(shí)使用該字段作為條件為每一個(gè)維度表在代理鍵上創(chuàng)建聚集索引，在業(yè)務(wù)鍵創(chuàng)建非聚集索引（可能包含行開(kāi)始時(shí)間字段）來(lái)支持加載時(shí)查找代理鍵。在經(jīng)常搜索的字段上創(chuàng)建非聚集索引不要分區(qū)維度表不要在事實(shí)表和維度表之間強(qiáng)制外鍵約束，這樣可以運(yùn)行快速數(shù)據(jù)加載，一定需要的話(huà)可以創(chuàng)建 使用 NOCHECK 的外鍵，在SSIS中使用查找轉(zhuǎn)化來(lái)強(qiáng)制參照完成性，或者在數(shù)據(jù)源執(zhí)行數(shù)據(jù)完整性檢查。60五、撰寫(xiě)有效的查詢(xún)?nèi)绾慰?/p>

20、以的，直接使用事實(shí)表上的分區(qū)鍵（日期維度鍵）作為查詢(xún)謂詞這樣可以只查詢(xún)數(shù)據(jù)所在的分區(qū)。61六、使用滑動(dòng)窗口技術(shù)維護(hù)數(shù)據(jù)為事實(shí)表的在線訪問(wèn)維護(hù)一個(gè)滑動(dòng)的時(shí)間窗口。加載新數(shù)據(jù)，卸載就數(shù)據(jù)。在分區(qū)范圍的兩端總是保留一個(gè)空分區(qū)（加載新數(shù)據(jù)前通過(guò)拆分分區(qū)生成，卸載舊數(shù)據(jù)后通過(guò)分區(qū)合并生成），這樣不會(huì)導(dǎo)致數(shù)據(jù)移動(dòng)。一定不要拆分或者合并已有數(shù)據(jù)的分區(qū)，這樣非常低效率，并且會(huì)導(dǎo)致最多4倍的日志生成，同時(shí)會(huì)導(dǎo)致大量的鎖。在要加載數(shù)據(jù)的分區(qū)所在的文件組中創(chuàng)建中間臨時(shí)表。在要卸載數(shù)據(jù)的分區(qū)所在的文件組中創(chuàng)建中間臨時(shí)表。一次加載整個(gè)分區(qū)是非常快的，但是僅可能在分區(qū)大小和數(shù)據(jù)加載的頻率是一樣的（例如：每天一個(gè)分區(qū)，數(shù)據(jù)

21、加載的頻率也是每天一次）。如果分區(qū)大小和數(shù)據(jù)加載頻率不一致，增量加載最新的分區(qū)?？偸且淮涡遁d一個(gè)分區(qū)62七、高效的加載初始化數(shù)據(jù)在初始化加載時(shí)使用簡(jiǎn)單或者大容量日志記錄恢復(fù)模式。創(chuàng)建帶聚集索引的分區(qū)事實(shí)表為每個(gè)分區(qū)創(chuàng)建沒(méi)有索引的中間臨時(shí)表，和用于填充每個(gè)分區(qū)的源數(shù)據(jù)文件。并行填充所有中間臨時(shí)表使用多個(gè)BULK INSERT，BCP或者SSIS任務(wù)如果沒(méi)有IO瓶頸的話(huà)，創(chuàng)建盡可能多個(gè)加載腳本來(lái)并行執(zhí)行，如果IO有限，減少并行執(zhí)行的腳本數(shù)量。加載時(shí)使用大小為 0 的CommitSize。加載時(shí)使用 大小為0 BatchSize使用TABLOCK如果源數(shù)據(jù)文件在同一服務(wù)器上，使用BULK INSERT，如果來(lái)自遠(yuǎn)程服務(wù)器使用bcp或者SSIS在每一個(gè)中間臨時(shí)表創(chuàng)建聚集索引，然后創(chuàng)建CHECK約束。將所有分區(qū)交換到分區(qū)表在分區(qū)建立非聚集索引63八、高效刪除舊數(shù)據(jù)如果可能的話(huà)使用分區(qū)交換從未分區(qū)的索引的標(biāo)