SQL Server 数据仓库最佳实践

《SQL Server 数据仓库最佳实践》由会员分享，可在线阅读，更多相关《SQL Server 数据仓库最佳实践（69页珍藏版）》请在金锄头文库上搜索。

1、 SQLServer数据仓库最佳实践 Level300 2 课程内容 数据仓库设计与最佳实践设计最佳实践案例 3 数据仓库基本概念 数据和信息的区别数据是由可观察和可记录的事实组成 通常存在于OLTP系统中 数据只有被加工处理为信息后 才有意义 信息是经过加工处理并对人类客观行为产生影响的数据表现形式 4 Kimball数据仓库建设 5 一 定义业务需求 6 说明 提炼业务流程 初始化数据仓库总线矩阵 7 二 维度建模 8 四步建模过程 9 AdventureWorksCycles公司企业数据仓库总线矩阵 全公司 10 定义业务流程优先级业务流程 数据可行性低 业务价值 影响低 11 确定粒度

2、 维度中粒度表示法 业务流程的粒度及基础度量 12 业务角色矩阵 定义角色 明晰每个角色需要看的业务流程数据进一步可进行数据安全性的设计 13 定义维度属性 数据质量分析 定义维度属性 源系统数据质量分析 14 为每个业务流程定义详细维度模型 SalesOrderItemQuantityUnitCostTotalCostUnitPriceSalesAmountShippingCost Time OrderDateandShipDate Salesperson Customer Product CalendarYearMonthDateFiscalYearFiscalQuarterMonthDa

3、te RegionCountryTerritoryManagerNameName CountryStateorProvinceCityAgeMaritalStatusGender CategorySubcategoryProductNameColorSize 15 根据维度模型详细定义进行数据仓库逻辑设计 维度表事实表星型架构 16 雪花型架构 以下情况考虑 在多个维度之间共享的子维度存在层次结构 并且维度表包含变化频繁的小的数据子集多个不同粒度的事实表引用到维度层次结构的不同层级 DimSalesPersonSalesPersonKeySalesPersonNameStoreKey DimP

4、roductProductKeyProductNameProductLineKeySupplierKey DimCustomerCustomerKeyCustomerNameGeographyKey FactOrdersCustomerKeySalesPersonKeyProductKeyShippingAgentKeyTimeKeyOrderNoLineItemNoQuantityRevenueCostProfit DimDateDateKeyYearQuarterMonthDay DimShippingAgentShippingAgentKeyShippingAgentName DimPr

5、oductLineProductLineKeyProductLineName DimGeographyGeographyKeyCityRegion DimSupplierSupplierKeySupplierName DimStoreStoreKeyStoreNameGeographyKey 17 维度表逻辑设计 键 代理建 业务键 18 属性和层次结构 层次结构 切片 钻取明细 19 Unknown和None 识别NULL值的含义Unknown还是None 不要假设NULL等价使用ISNULL 源 维度表 20 设计缓慢渐变维度 类型1 类型2 类型3 21 时间维度表 代理键粒度范围属性和

6、层次结构多日历未知值 ETL还是预先填充 22 自关联的维度表 KimAbercrombieKamilAmirehJeffHayCesarGarcia 备注 如果层次固定 还是建议重构成固定层次的平面表 然后创建层次结构 父子层次结构如果中间层级过多 性能不好 23 垃圾 Junk 维度 将不属于已有维度的低基数属性合并到一起避免创建很多小的维度表 24 事实表设计 列 维度键度量值退化维度 25 度量类型 累加半累加不可累加 26 事实表的三种类型 27 示例 事务型事实表周期性快照事实表累计快照事实表 28 价值链 跨业务流程共享维度 29 设计之其他考虑 Factless事实表 如何获取

7、正在参加促销活动但是没有销售出去的产品信息 30 设计之其他考虑 维度和事实之间多对多 多个维度值指派到一个事实交易 如果需要为每一个销售代表分配销售 可以在中间表添加权重 31 设计之其他考虑 维度和维度之间多对多 例如 一个客户有一个或多个Account 一个Account对应一个或多个客户 32 三 技术架构设计 33 微软DW BI系统体系结构 34 服务器架构 1 35 服务器架构 2 36 开发团队配置 37 四 数据仓库物理设计 38 物理数据分布 跨物理设备分布数据 使用文件组和RAID存储 推荐RAID10 数据文件和日志文件分开存储工作区对象单独存储 例如 临时的数据表预先

8、分类空间禁用自动增长为所有文件分配一样的尺寸 39 维度表 索引 代理键创建聚集索引业务键创建非聚集索引Include 代理键 加速Lookup效率经常查询的属性创建非聚集索引对于非常大的包含缓变属性的维度创建四列索引 索引键 业务键 开始日期 包含 结束时间 代理键 这样可以加速ETL处理过程 40 维度表 视图 通过视图封装例如在雪花型架构中 通过创建视图可以把多个维度表连接起来可以将视图定义成索引视图 从而将数据物理化 演示 索引视图 41 维度表物理创建脚本 示例AllinOne CREATETABLE dbo DimProduct ProductKey int IDENTITY 1

9、1 NOTNULL BKProductSKU nvarchar 25 NOTNULLDEFAULTN ZZ 000 ZZ ProductName nvarchar 50 NOTNULLDEFAULTN Productunknownornotprovided ProductSubCategory nvarchar 50 NOTNULLDEFAULTN ProductSubcategoryunknownornotprovided ProductCategory nvarchar 50 NOTNULLDEFAULTN ProductCategoryunknownornotprovided CONST

10、RAINT PK dbo DimProduct PRIMARYKEYCLUSTERED ProductKey ASC ON DimFileGroup WITH DATA COMPRESSION PAGE onlyifthisisaverybigdimension 为表描述创建扩展属性execsys sp addextendedproperty name N TableDescription value N Informationaboutproducts level0type N SCHEMA level0name dbo level1type N TABLE level1name DimPr

11、oduct GO 创建用户访问视图CREATEVIEW Product ASSELECT ProductKey BKProductSKU ProductName ProductSubCategory ProductCategory FROM DimProduct GO 42 事实表 数据类型 约束 数据类型约束尽量避免主键和外键 加快数据加载完整性靠ETL来保障 43 事实表 分区 对大事实表进行分区 通常是日期键好处 通过分区表并行扫描提高查询性能提高CUBE处理速度快速加载和删除改进索引管理性增强备份和还原的灵活性使用分区对其的索引视图 索引视图和表分区对区实现过程创建文件组和文件创建分区

12、函数创建分区方案创建分区表对于已经存在的事实表可以通过重建聚集索引来分区 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

13、 2008 Earlier 2 2008Data 3 2008Data 4 2008Data 5 2008 Later EMPTY EMPTY EMPTY 2008 06 01 5 2008Data 6 2008 Later 6 47 卸载过期数据 在目标分区同一filegroup上创建用于卸载的表交换数据到表中合并第一个和第二个分区存档或清空表 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

14、避免拆分 合并非空分区 效率很低额外的日志只Split Merge空分区即使需要临时用SWITCH清空分区 49 加载历史数据 50 删除数据 尽可能使用交换分区从未分区的表中删除大量行避免deletefrom where 带来大量锁和日志INSERT需要的行到新表通常更快 51 演示1 对事实表分区演示2 加载新数据演示3 归档旧数据演示4 实现滑动窗口 52 事实表 索引 索引建议为日期列创建聚集索引 如果有多个日期列 则选择其中一个 如OrderDate 支持某一时间段的快速顺序扫描如果需要分区 先考虑分区字段为每一个外键列创建非聚集索引Index 外键 日期 用于针对某一维度的选择性查

15、询除非关联的维度基数很低列存储索引 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 的负载两种压缩方式 行压缩 将定长字段存储成变长字段页压缩 在一个

16、页上存储冗余数据的一个实例可以在以下对象上启用 表索引分区 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 这可以加速查询时表扫描操作并可以帮助在大量数据更新的时候避免过度的锁活动 为每一个外键建立非聚集索引 这有助于基于选择的维度