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

SQLServer数据仓库最佳实践 Level300 2 课程内容 数据仓库设计与最佳实践设计最佳实践案例 3 数据仓库基本概念 数据和信息的区别数据是由可观察和可记录的事实组成 通常存在于OLTP系统中 数据只有被加工处理为信息后 才有意义 信息是经过加工处理并对人类客观行为产生影响的数据表现形式 4 Kimball数据仓库建设 5 一 定义业务需求 6 说明 提炼业务流程 初始化数据仓库总线矩阵 7 二 维度建模 8 四步建模过程 9 AdventureWorksCycles公司企业数据仓库总线矩阵 全公司 10 定义业务流程优先级业务流程 数据可行性低 业务价值 影响低 11 确定粒度 维度中粒度表示法 业务流程的粒度及基础度量 12 业务角色矩阵 定义角色 明晰每个角色需要看的业务流程数据进一步可进行数据安全性的设计 13 定义维度属性 数据质量分析 定义维度属性 源系统数据质量分析 14 为每个业务流程定义详细维度模型 SalesOrderItemQuantityUnitCostTotalCostUnitPriceSalesAmountShippingCost Time OrderDateandShipDate Salesperson Customer Product CalendarYearMonthDateFiscalYearFiscalQuarterMonthDate RegionCountryTerritoryManagerNameName CountryStateorProvinceCityAgeMaritalStatusGender CategorySubcategoryProductNameColorSize 15 根据维度模型详细定义进行数据仓库逻辑设计 维度表事实表星型架构 16 雪花型架构 以下情况考虑 在多个维度之间共享的子维度存在层次结构 并且维度表包含变化频繁的小的数据子集多个不同粒度的事实表引用到维度层次结构的不同层级 DimSalesPersonSalesPersonKeySalesPersonNameStoreKey DimProductProductKeyProductNameProductLineKeySupplierKey DimCustomerCustomerKeyCustomerNameGeographyKey FactOrdersCustomerKeySalesPersonKeyProductKeyShippingAgentKeyTimeKeyOrderNoLineItemNoQuantityRevenueCostProfit DimDateDateKeyYearQuarterMonthDay DimShippingAgentShippingAgentKeyShippingAgentName DimProductLineProductLineKeyProductLineName DimGeographyGeographyKeyCityRegion DimSupplierSupplierKeySupplierName DimStoreStoreKeyStoreNameGeographyKey 17 维度表逻辑设计 键 代理建 业务键 18 属性和层次结构 层次结构 切片 钻取明细 19 Unknown和None 识别NULL值的含义Unknown还是None 不要假设NULL等价使用ISNULL 源 维度表 20 设计缓慢渐变维度 类型1 类型2 类型3 21 时间维度表 代理键粒度范围属性和层次结构多日历未知值 ETL还是预先填充 22 自关联的维度表 KimAbercrombieKamilAmirehJeffHayCesarGarcia 备注 如果层次固定 还是建议重构成固定层次的平面表 然后创建层次结构 父子层次结构如果中间层级过多 性能不好 23 垃圾 Junk 维度 将不属于已有维度的低基数属性合并到一起避免创建很多小的维度表 24 事实表设计 列 维度键度量值退化维度 25 度量类型 累加半累加不可累加 26 事实表的三种类型 27 示例 事务型事实表周期性快照事实表累计快照事实表 28 价值链 跨业务流程共享维度 29 设计之其他考虑 Factless事实表 如何获取正在参加促销活动但是没有销售出去的产品信息 30 设计之其他考虑 维度和事实之间多对多 多个维度值指派到一个事实交易 如果需要为每一个销售代表分配销售 可以在中间表添加权重 31 设计之其他考虑 维度和维度之间多对多 例如 一个客户有一个或多个Account 一个Account对应一个或多个客户 32 三 技术架构设计 33 微软DW BI系统体系结构 34 服务器架构 1 35 服务器架构 2 36 开发团队配置 37 四 数据仓库物理设计 38 物理数据分布 跨物理设备分布数据 使用文件组和RAID存储 推荐RAID10 数据文件和日志文件分开存储工作区对象单独存储 例如 临时的数据表预先分类空间禁用自动增长为所有文件分配一样的尺寸 39 维度表 索引 代理键创建聚集索引业务键创建非聚集索引Include 代理键 加速Lookup效率经常查询的属性创建非聚集索引对于非常大的包含缓变属性的维度创建四列索引 索引键 业务键 开始日期 包含 结束时间 代理键 这样可以加速ETL处理过程 40 维度表 视图 通过视图封装例如在雪花型架构中 通过创建视图可以把多个维度表连接起来可以将视图定义成索引视图 从而将数据物理化 演示 索引视图 41 维度表物理创建脚本 示例AllinOne CREATETABLE dbo DimProduct ProductKey int IDENTITY 1 1 NOTNULL BKProductSKU nvarchar 25 NOTNULLDEFAULTN ZZ 000 ZZ ProductName nvarchar 50 NOTNULLDEFAULTN Productunknownornotprovided ProductSubCategory nvarchar 50 NOTNULLDEFAULTN ProductSubcategoryunknownornotprovided ProductCategory nvarchar 50 NOTNULLDEFAULTN ProductCategoryunknownornotprovided CONSTRAINT PK dbo DimProduct PRIMARYKEYCLUSTERED ProductKey ASC ON Dim WITH DATA COMPRESSION PAGE onlyifthisisaverybigdimension 为表描述创建扩展属性execsys sp addextendedproperty name N TableDescription value N Informationaboutproducts level0type N SCHEMA level0name dbo level1type N TABLE level1name DimProduct GO 创建用户访问视图CREATEVIEW Product ASSELECT ProductKey BKProductSKU ProductName ProductSubCategory ProductCategory FROM DimProduct GO 42 事实表 数据类型 约束 数据类型约束尽量避免主键和外键 加快数据加载完整性靠ETL来保障 43 事实表 分区 对大事实表进行分区 通常是日期键好处 通过分区表并行扫描提高查询性能提高CUBE处理速度快速加载和删除改进索引管理性增强备份和还原的灵活性使用分区对其的索引视图 索引视图和表分区对区实现过程创建文件组和文件创建分区函数创建分区方案创建分区表对于已经存在的事实表可以通过重建聚集索引来分区 Pre Jan Jan Jun Jul Dec 44 分区数据分布与操作示例 45 滑动窗口 保持一个时间段内的事实数据在线加载最新的数据 卸载最老的数据两种加载新数据的方式一次性加载整个新分区增量加载最新分区总为两端各保留一个空分区 46 加载最新数据 在与目标分区所在的文件组上创建中间表 5 2008 拆分最新的分区批量加载 BulkInsert或者bcp 并索引中间表交换数据到次新分区 Partition 1 2 3 4 5 2008 02 01 2008 03 01 2008 04 01 2008 05 01 1 2008 Earlier 2 2008Data 3 2008Data 4 2008Data 5 2008 Later EMPTY EMPTY EMPTY 2008 06 01 5 2008Data 6 2008 Later 6 47 卸载过期数据 在目标分区同一上创建用于卸载的表交换数据到表中合并第一个和第二个分区存档或清空表 Partition 2008 03 01 2008 04 01 2008 05 01 3 2008Data 4 2008Data EMPTY 2008 06 01 5 2008Data 6 2008 Later 2 2008卸载表 EMPTY 48 避免拆分 合并非空分区 效率很低额外的日志只Split Merge空分区即使需要临时用SWITCH清空分区 49 加载历史数据 50 删除数据 尽可能使用交换分区从未分区的表中删除大量行避免deletefrom where 带来大量锁和日志INSERT需要的行到新表通常更快 51 演示1 对事实表分区演示2 加载新数据演示3 归档旧数据演示4 实现滑动窗口 52 事实表 索引 索引建议为日期列创建聚集索引 如果有多个日期列 则选择其中一个 如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 53 事实表 数据压缩 减少物理空间需求可以改进IO绑定查询的性能CPU绑定的查询额外20 30 的负载两种压缩方式 行压缩 将定长字段存储成变长字段页压缩 在一个页上存储冗余数据的一个实例可以在以下对象上启用 表索引分区 Pre Jan 页压缩 Jan Jun 行压缩 Jul Dec 不压缩 例如 一月份之前很少访问一月到六月中等频率访问七月到十二月经常频繁访问 54 事实表 数据压缩示例 AdventureworksDW数据仓库中的FactInternetSales事实表 55 演示 压缩事实表 56 课程内容 数据仓库设计与最佳实践设计最佳实践案例ETL设计与最佳实践设计最佳实践案例CUBE设计与最佳实践设计最佳实践案例 57 一 考虑对大事实表分区 大事实表 50 100GB及以上查询被限制在一个分区内快速完成通常在日期键对事实表分区启用滑动窗口 58 二 在事实表的日期字段创建聚集索引 可以高效的CUBE处理 CUBE处理的时候可以并行处理多个分区 以及检索历史数据切片如果在批窗口加载数据 可以在创建或者重建事实表的聚集索引的时候使用ALLOW ROW LOCKS OFF和ALLOW PAGE LOCKS OFF 这可以加速查询时表扫描操作并可以帮助在大量数据更新的时候避免过度的锁活动 为每一个外键建立非聚集索引 这有助于基于选择的维度谓词进行精确的查询来获取数据使用文件组用于管理目的 例如备份 还原 部分数据库可用性等 59 三 小心的选择分区粒度 大多数客户使用月 季度或者年 为了有效删除 必须一次删除一个分区 一次加载一个完成的分区非常快对于每日的数据加载进行按日的分区是一个有吸引力的选择SQLServer2008SP1只支持1000个分区 SP2则可以支持15000个分区 分区粒度影响查询的并行性并行线程 注意MAXDOP设置 是分布式的扫描分区 并且即时多个分区需要扫描的时候 每个分区都可以使用多个线程 如果经常执行的查询只访问2 3个分区 不建议进行分区设计 如果需要MAXDOP并行 假设MAXDOP 4或更高 60 四 正确的设计维度表 为所有维度的代理键使用整型数据类型 越小越好 这样可以使得事实表比较窄 使用有意义的整数型日期键 例如 20060215 根据日期生成 不要使用代理键很容易在写查询时使用该字段作为条件为每一个维度表在代理键上创建聚集索引 在业务键创建非聚集索引 可能包含行开始时间字段 来支持加载时查找代理键 在经常搜索的字段上创建非聚集索引不要分区维度表不要在事实表和维度表之间强制外键约束 这样可以运行快速数据加载 一定需要的话可以创建使用NOCHECK的外键 在SSIS中使用查找转化来强制参照完成性 或者在数据源执行数据完整性检查 61 五 撰写有效的查询 如何可以的 直接使用事实表上的分区键 日期维度键 作为查询谓词这样可以只查询数据所在的分区 62 六 使用滑动窗口技术维护数据 为事实表的在线访问维护一个滑动的时间窗口 加载新数据 卸载就数据 在分区范围的两端总是保留一个空分区 加载新数据前通过拆分分区生成 卸载旧数据后通过分区合并生成 这样不会导致数据移动 一定不要拆分或者合并已有数据的分区 这样非常低效率 并且会导致最多4倍的日志生成 同时会导致大量的锁 在要加载数据的分区所在的文件组中创建中间临时表 在要卸载数据的分区所在的文件组中创建中间临时表 一次加载整个分区是非常快的 但是仅可能在分区大小和数据加载的频率是一样的 例如 每天一个分区 数据加载的频率也是每天一次 如果分区大小和数据加载频率不一致 增量加载最新的分区 总是一次卸载一个分区 63 七 高效的加载初始化数据 在初始化加载时使用简单或者大容量日志记录恢复模式 创建带聚集索引的分区事实表为每个分区创建没有索引的中间临时表 和用于填充每个分区的源数据文件 并行填充所有中间临时表使用多个BULKINSERT BCP或者SSIS任务如果没有IO瓶颈的话 创建尽可能多个加载脚本来并行执行 如果IO有限 减少并行执行的脚本数量 加载时使用大小为0的CommitSize 加载时使用大小为0BatchSize使用TABLOCK如果源数据文件在同一服务器上 使用BULKINSERT 如果来自远程服务器使用bcp或者SSIS在每一个中间临时表创建聚集索引 然后创建CHECK约束 将所有分区交换到分区表在分区建立非聚集索引 64 八 高效删除旧数据 如果可能的话使用分区交换从未分区的索引的标删除上百万行数据不要使用DELETEFROM WHERE 大量的锁和日志记录如果取消删除操作将长时间回滚建议在未索引的表中插入新记录在表上创