SQL--Server-数据仓库最佳实践.ppt

SQLServer数据仓库最佳实践Level300.课程内容数据仓库设计与最佳实践设计最佳实践案例2.数据仓库基本概念数据和信息的区别数据是由可观察和可记录的事实组成，通常存在于OLTP系统中。数据只有被加工处理为信息后，才有意义。信息是经过加工处理并对人类客观行为产生影响的数据表现形式。3.Kimball数据仓库建设4.一、定义业务需求5.说明提炼业务流程初始化数据仓库总线矩阵6.二、维度建模7.四步建模过程8.AdventureWorksCycles公司企业数据仓库总线矩阵-全公司9.定义业务流程优先级业务流程数据可行性低，业务价值/影响低10.确定粒度维度中粒度表示法业务流程的粒度及基础度量11.业务角色矩阵定义角色，明晰每个角色需要看的业务流程数据进一步可进行数据安全性的设计12.定义维度属性/数据质量分析定义维度属性源系统数据质量分析13.为每个业务流程定义详细维度模型Sales OrderItem QuantityUnit CostTotal CostUnit PriceSales AmountShipping CostTime(Order Date and Ship Date)SalespersonCustomerProductCalendar Year Month DateFiscal Year Fiscal Quarter Month DateRegion Country TerritoryManager NameNameCountry State or Province CityAgeMarital StatusGenderCategory Subcategory Product NameColorSize14.根据维度模型详细定义进行数据仓库逻辑设计维度表事实表星型架构15.雪花型架构以下情况考虑:在多个维度之间共享的子维度存在层次结构，并且维度表包含变化频繁的小的数据子集多个不同粒度的事实表引用到维度层次结构的不同层级DimSalesPersonSalesPersonKeySalesPersonNameStoreKeyDimProductProductKeyProductNameProductLineKeySupplierKeyDimCustomerCustomerKeyCustomerNameGeographyKeyFactOrdersCustomerKeySalesPersonKeyProductKeyShippingAgentKeyTimeKeyOrderNoLineItemNoQuantityRevenueCostProfitDimDateDateKeyYearQuarterMonthDayDimShippingAgentShippingAgentKeyShippingAgentNameDimProductLineProductLineKeyProductLineNameDimGeographyGeographyKeyCityRegionDimSupplierSupplierKeySupplierNameDimStoreStoreKeyStoreNameGeographyKey16.维度表逻辑设计-键代理建业务键17.属性和层次结构层次结构切片钻取明细18.Unknown和None识别 NULL 值的含义Unknown 还是 None?不要假设 NULL 等价使用 ISNULL()源维度表19.设计缓慢渐变维度类型1类型 2类型 320.时间维度表代理键粒度范围属性和层次结构多日历未知值ETL 还是预先填充？21.自关联的维度表Kim AbercrombieKamil AmirehJeff HayCesar Garcia备注：如果层次固定，还是建议重构成固定层次的平面表，然后创建层次结构，父子层次结构如果中间层级过多，性能不好。22.垃圾（Junk）维度将不属于已有维度的低基数属性合并到一起避免创建很多小的维度表23.事实表设计-列维度键度量值退化维度24.度量类型累加半累加不可累加25.事实表的三种类型26.示例事务型事实表周期性快照事实表累计快照事实表27.价值链跨业务流程共享维度28.设计之其他考虑-Factless事实表如何获取正在参加促销活动但是没有销售出去的产品信息？29.设计之其他考虑-维度和事实之间多对多多个维度值指派到一个事实交易如果需要为每一个销售代表分配销售，可以在中间表添加权重30.设计之其他考虑-维度和维度之间多对多例如：一个客户有一个或多个Account，一个Account对应一个或多个客户31.三、技术架构设计32.微软DW/BI系统体系结构33.服务器架构（1）34.服务器架构（2）35.开发团队配置36.四、数据仓库物理设计37.物理数据分布跨物理设备分布数据:使用文件组和RAID存储（推荐RAID10）数据文件和日志文件分开存储工作区对象单独存储：例如，临时的数据表预先分类空间禁用自动增长为所有文件分配一样的尺寸38.维度表-索引代理键创建聚集索引业务键创建非聚集索引Include（代理键），加速Lookup效率经常查询的属性创建非聚集索引对于非常大的包含缓变属性的维度创建四列索引：索引键（业务键，开始日期），包含（结束时间，代理键）这样可以加速ETL处理过程39.维度表-视图通过视图封装例如在雪花型架构中，通过创建视图可以把多个维度表连接起来可以将视图定义成索引视图，从而将数据物理化演示：索引视图演示：索引视图40.维度表物理创建脚本（示例AllinOne）CREATE TABLE dbo.DimProduct(ProductKey int IDENTITY(1,1)NOT 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 or not provided,CONSTRAINT PK_dbo.DimProduct PRIMARY KEYCLUSTERED(ProductKeyASC)ON DimFileGroupWITH(DATA_COMPRESSION=PAGE);-only if this is a very bigdimension-为表描述创建扩展属性exec sys.sp_addextendedproperty name=NTable Description,value=NInformation about products,level0type=NSCHEMA,level0name=dbo,level1type=NTABLE,level1name=DimProduct;GO;-创建用户访问视图CREATE VIEW Product AS SELECT ProductKey,BKProductSKU,ProductName,ProductSubCategory,ProductCategoryFROM DimProduct;GO;41.事实表-数据类型/约束数据类型约束尽量避免主键和外键，加快数据加载完整性靠ETL来保障事实列类型最快的SQLServer数据类型代理键tinyint、smallint、int、bigint日期键采用yyyyMMdd格式的int整数度量值tinyint、smallint、int、bigint数值度量值smallmoney、money、real、float（请注意，与money和float类型相比，decimal和vardecimal要求更强大的CPU处理能力）非重复计数列tinyint、smallint、int、bigint（如果您的计数列是char，则考虑执行哈希计算或用代理键进行替换）42.事实表-分区对大事实表进行分区:通常是日期键好处:通过分区表并行扫描提高查询性能提高CUBE处理速度快速加载和删除改进索引管理性增强备份和还原的灵活性使用分区对其的索引视图:索引视图和表分区对区实现过程创建文件组和文件创建分区函数创建分区方案创建分区表对于已经存在的事实表可以通过重建聚集索引来分区Pre-JanJanJunJulDec43.分区数据分布与操作示例44.滑动窗口保持一个时间段内的事实数据在线加载最新的数据，卸载最老的数据两种加载新数据的方式一次性加载整个新分区增量加载最新分区总为两端各保留一个空分区45.加载最新数据在与目标分区所在的文件组上创建中间表(5/2008)拆分 最新的分区批量加载（Bulk Insert 或者bcp）并索引中间表交换 数据到次新分区Partition#Partition#Partition#Partition#1 1 1 12 2 2 23 3 3 34 4 4 45 5 5 52008-02-012008-02-012008-03-012008-03-012008-04-012008-04-012008-05-012008-05-011/2008&1/2008&1/2008&1/2008&EarlierEarlierEarlierEarlier2/2008 Data2/2008 Data2/2008 Data2/2008 Data3/2008 Data3/2008 Data3/2008 Data3/2008 Data4/2008 Data4/2008 Data4/2008 Data4/2008 Data5/2008&5/2008&5/2008&5/2008&LaterLaterLaterLaterEMPTYEMPTYEMPTYEMPTYEMPTYEMPTYEMPTYEMPTYEMPTYEMPTYEMPTYEMPTY2008-06-012008-06-015/2008 Data5/2008 Data5/2008 Data5/2008 Data6/2008&6/2008&6/2008&6/2008&LaterLaterLaterLater6 6 6 65/2008 5/2008 中间表中间表中间表中间表46.卸载过期数据在目标分区同一filegroup上创建用于卸载的表交换 数据到表中合并 第一个和第二个分区存档或清空表Partition#Partition#Partition#Partition#2008-03-012008-03-012008-04-012008-04-012008-05-012008-05-013/2008 Data3/2008 Data3/2008 Data3/2008 Data4/2008 Data4/2008 Data4/2008 Data4/2008 DataEMPTYEMPTYEMPTYEMPTY2008-06-012008-06-015/2008 Data5/2008 Data5/2008 Data5/2008 Data6/2008&6/2008&6/2008&6/2008&LaterLaterLaterLater2/2008 2/2008 卸载表卸载表卸载表卸载表EMPTYEMPTYEMPTYEMPTY1 1 1 12 2 2 23 3 3 34 4 4 45 5 5 52/2008&2/2008&2/2008&2/2008&EarlierEarlierEarlierEarlier2/2008 Data2/2008 Data2/2008 Data2/2008 Data1 1 1 12 2 2 23 3 3 34 4 4 45 5 5 52008-02-012008-02-011/2008&1/2008&1/2008&1/2008&EarlierEarlierEarlierEarlierEMPTYEMPTYEMPTYEMPTY6 6 6 647.避免 拆分/合并 非空分区效率很低额外的日志只Split/Merge 空分区即使需要临时用 SWITCH 清空分区48.加载历史数据49.删除数据尽可能使用交换分区从未分区的表中删除大量行避免 delete fromwhere带来大量锁和日志INSERT需要的行到新表通常更快50.演示演示1：对事实表分区：对事实表分区演示演示2：加载新数据：加载新数据演示演示3：归档旧数据：归档旧数据演示演示4：实现滑动窗口：实现滑动窗口51.事实表-索引索引建议为日期列创建聚集索引，如果有多个日期列，则选择其中一个（如OrderDate）支持某一时间段的快速顺序扫描如果需要分区，先考虑分区字段为每一个外键列创建非聚集索引Index（外键，日期），用于针对某一维度的选择性查询除非关联的维度基数很低列存储索引（SQLServer2012以后的版本）检查缺失的索引sys.dm_db_missing_index_group_stats,sys.dm_db_missing_index_groupsandsys.dm_db_missing_index_details检查索引索引sys.dm_db_index_physical_stats的avg_fragmentation_in_percent，不应该大于25%52.事实表-数据压缩减少物理空间需求可以改进IO绑定查询的性能CPU绑定的查询额外20-30%的负载两种压缩方式:行压缩：将定长字段存储成变长字段页压缩：在一个页上存储冗余数据的一个实例可以在以下对象上启用:表索引分区Pre-Jan(页压缩)JanJun(行压缩)JulDec(不压缩)例如:一月份之前很少访问一月到六月中等频率访问七月到十二月经常频繁访问53.事实表-数据压缩示例AdventureworksDW数据仓库中的FactInternetSales事实表54.演示：压缩事实表演示：压缩事实表55.课程内容数据仓库设计与最佳实践设计最佳实践案例ETL设计与最佳实践设计最佳实践案例CUBE设计与最佳实践设计最佳实践案例56.一、考虑对大事实表分区一、考虑对大事实表分区大事实表：50100GB及以上查询被限制在一个分区内快速完成通常在日期键对事实表分区启用滑动窗口57.二、在事实表的日期字段创建聚集索引在事实表的日期字段创建聚集索引可以高效的CUBE处理（CUBE处理的时候可以并行处理多个分区）以及检索历史数据切片如果在批窗口加载数据，可以在创建或者重建事实表的聚集索引的时候使用ALLOW_ROW_LOCKS=OFF和ALLOW_PAGE_LOCKS=OFF。这可以加速查询时表扫描操作并可以帮助在大量数据更新的时候避免过度的锁活动。为每一个外键建立非聚集索引，这有助于基于选择的维度谓词进行精确的查询来获取数据使用文件组用于管理目的，例如备份/还原，部分数据库可用性等。58.三、小心的选择分区粒度三、小心的选择分区粒度大多数客户使用月，季度或者年。为了有效删除，必须一次删除一个分区。一次加载一个完成的分区非常快对于每日的数据加载进行按日的分区是一个有吸引力的选择SQLServer2008SP1只支持1000个分区，SP2则可以支持15000个分区。分区粒度影响查询的并行性并行线程（注意MAXDOP设置）是分布式的扫描分区，并且即时多个分区需要扫描的时候，每个分区都可以使用多个线程。如果经常执行的查询只访问23个分区，不建议进行分区设计（如果需要MAXDOP并行，假设MAXDOP=4或更高）59.四、正确的设计维度表正确的设计维度表为所有维度的代理键使用整型数据类型，越小越好，这样可以使得事实表比较窄。使用有意义的整数型日期键（例如：20060215，根据日期生成）不要使用代理键很容易在写查询时使用该字段作为条件为每一个维度表在代理键上创建聚集索引，在业务键创建非聚集索引（可能包含行开始时间字段）来支持加载时查找代理键。在经常搜索的字段上创建非聚集索引不要分区维度表不要在事实表和维度表之间强制外键约束，这样可以运行快速数据加载，一定需要的话可以创建使用NOCHECK的外键，在SSIS中使用查找转化来强制参照完成性，或者在数据源执行数据完整性检查。60.五、撰写有效的查询如何可以的，直接使用事实表上的分区键（日期维度键）作为查询谓词这样可以只查询数据所在的分区。61.六、使用滑动窗口技术维护数据使用滑动窗口技术维护数据为事实表的在线访问维护一个滑动的时间窗口。加载新数据，卸载就数据。在分区范围的两端总是保留一个空分区（加载新数据前通过拆分分区生成，卸载旧数据后通过分区合并生成），这样不会导致数据移动。一定不要拆分或者合并已有数据的分区，这样非常低效率，并且会导致最多4倍的日志生成，同时会导致大量的锁。在要加载数据的分区所在的文件组中创建中间临时表。在要卸载数据的分区所在的文件组中创建中间临时表。一次加载整个分区是非常快的，但是仅可能在分区大小和数据加载的频率是一样的（例如：每天一个分区，数据加载的频率也是每天一次）。如果分区大小和数据加载频率不一致，增量加载最新的分区。总是一次卸载一个分区62.七、高效的加载初始化数据在初始化加载时使用简单或者大容量日志记录恢复模式。创建带聚集索引的分区事实表为每个分区创建没有索引的中间临时表，和用于填充每个分区的源数据文件。并行填充所有中间临时表使用多个BULKINSERT，BCP或者SSIS任务如果没有IO瓶颈的话，创建尽可能多个加载脚本来并行执行，如果IO有限，减少并行执行的脚本数量。加载时使用大小为0的CommitSize。加载时使用大小为0BatchSize使用TABLOCK如果源数据文件在同一服务器上，使用BULKINSERT，如果来自远程服务器使用bcp或者SSIS在每一个中间临时表创建聚集索引，然后创建CHECK约束。将所有分区交换到分区表在分区建立非聚集索引63.八、高效删除旧数据如果可能的话使用分区交换从未分区的索引的标删除上百万行数据不要使用DELETEFROM.WHERE.大量的锁和日志记录如果取消删除操作将长时间回滚建议在未索引的表中插入新记录在表上创建索引然后将表重命名来替代原来的表Createindex(es)onthetable另外还有一个方法，还可以执行少量的数据删除循环DELETETOP(1000).;COMMIT最后也可以将行标记为Deleted，然后再非重要时间删除这些行。64.九、手动管理统计值在加载新数据后手动更新统计值，通过脚本更新统计值如果在周期性加载数据后会自动更新统计值，可以关闭表上的自动更新统计值功能。这些对经常读取最新数据的查询非常有用（优化器知道数据的最新分布信息）在小的维度表上增量加载数据后更新统计值也对性能有帮助，可以在更新维度表上的统计值是使用FULLSCAN选项来提供更准确的查询计划。65.十、考虑高效的备份策略考虑高效的备份策略备份很大的数据仓库非常耗时例如，备份一个2TB的数据库到SAN上的10块RAID-5的磁盘需要花费2小时（275MB/秒）使用SAN的快照备份技术是很好的选项减少备份的数据量历史数据分区所在的文件组可以标记为“只读”。当文件组变成只读后一次性备份文件组对读写文件组进行常规的备份计划注意只读文件组不可以进行并行恢复。66.课程内容数据仓库设计与最佳实践设计最佳实践案例ETL设计与最佳实践设计最佳实践案例CUBE设计与最佳实践设计最佳实践案例67.AdventureworksCycle公司数据仓库案例SQL自带的数据仓库实例销售主题分析财务主题分析15 min讲解，讲解，10 min 提问提问68.Yum（百胜餐饮）数据仓库案例SweetSpot报表系统，5000用户营运主题人事主题20 min讲解，讲解，10 min 提问提问69.