SQLServer数据仓库最佳实践.

SQLServer数据仓库最佳实践Level300课程内容数据仓库设计与最佳实践设计最佳实践案例2数据仓库基本概念数据和信息的区别数据是由可观察和可记录的事实组成，通常存在于OLTP系统中。数据只有被加工处理为信息后，才有意义。信息是经过加工处理并对人类客观行为产生影响的数据表现形式。3Kimball数据仓库建设4一、定义业务需求了解业务访谈业务人员和IT记录汇报从业务需求中提炼业务流程建立初始总线矩阵5说明提炼业务流程初始化数据仓库总线矩阵6二、维度建模7四步建模过程识别业务流程确定粒度选择维度选择事实8AdventureWorksCycles公司企业数据仓库总线矩阵--全公司9定义业务流程优先级业务流程数据可行性低，业务价值/影响低10确定粒度维度中粒度表示法业务流程的粒度及基础度量11业务角色矩阵•定义角色，明晰每个角色需要看的业务流程数据•进一步可进行数据安全性的设计12定义维度属性/数据质量分析定义维度属性源系统数据质量分析13为每个业务流程定义详细维度模型SalesOrderItemQuantityUnitCostTotalCostUnitPriceSalesAmountShippingCostTime(OrderDateandShipDate)SalespersonCustomerProductCalendarYearMonthDateFiscalYearFiscalQuarterMonthDateRegionCountryTerritoryManagerNameNameCountryStateorProvinceCityAgeMaritalStatusGenderCategorySubcategoryProductNameColorSize14根据维度模型详细定义进行数据仓库逻辑设计•维度表•事实表•星型架构15雪花型架构以下情况考虑:在多个维度之间共享的子维度存在层次结构，并且维度表包含变化频繁的小的数据子集多个不同粒度的事实表引用到维度层次结构的不同层级DimSalesPersonSalesPersonKeySalesPersonNameStoreKeyDimProductProductKeyProductNameProductLineKeySupplierKeyDimCustomerCustomerKeyCustomerNameGeographyKeyFactOrdersCustomerKeySalesPersonKeyProductKeyShippingAgentKeyTimeKeyOrderNoLineItemNoQuantityRevenueCostProfitDimDateDateKeyYearQuarterMonthDayDimShippingAgentShippingAgentKeyShippingAgentNameDimProductLineProductLineKeyProductLineNameDimGeographyGeographyKeyCityRegionDimSupplierSupplierKeySupplierNameDimStoreStoreKeyStoreNameGeographyKey16维度表逻辑设计-键代理建业务键17属性和层次结构层次结构切片钻取明细18Unknown和None•识别NULL值的含义•Unknown还是None?•不要假设NULL等价•使用ISNULL()源维度表19设计缓慢渐变维度类型1类型2类型320时间维度表•代理键•粒度•范围•属性和层次结构•多日历•未知值ETL还是预先填充？21自关联的维度表•KimAbercrombie•KamilAmireh•JeffHay•CesarGarcia备注：如果层次固定，还是建议重构成固定层次的平面表，然后创建层次结构，父子层次结构如果中间层级过多，性能不好。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）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[DimFileGroup]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;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-JanJan–JunJul–Dec43分区数据分布与操作示例44滑动窗口保持一个时间段内的事实数据在线加载最新的数据，卸载最老的数据两种加载新数据的方式一次性加载整个新分区增量加载最新分区总为两端各保留一个空分区45加载最新数据在与目标分区所在的文件组上创建中间表(5/2008)拆分最新的分区批量加载（BulkInsert或者bcp）并索引中间表交换数据到次新分区Partition#123452008-02-012008-03-012008-04-012008-05-011/2008&Earlier2/2008Data3/2008Data4/2008Data5/2008&Later[EMPTY][EMPTY][EMPTY]2008-06-015/2008Data6/2008&Later65/2008中间表46卸载过期数据在目标分区同一filegroup上创建用于卸载的表交换数据到表中合并第一个和第二个分区存档或清空表Partition#2008-03-012008-04-012008-05-013/2008Data4/2008Data[EMPTY]2008-06-015/2008Data6/2008&Later2/2008卸载表[EMPTY]123452/2008&Earlier2/2008Data123452008-02-011/2008&Earlier[EMPTY]647避免拆分/合并非空分区效率很低额外的日志只Split/Merge空分区即使需要临时用SWITCH清空分区48加载历史数据使用简单或大容量日志恢复模式创建没有索引的分区表为每个分区创建没有索引的表并发加载每个分区对应的表交换所有表到对应的分区创建聚集索引创建非聚集索引49删除数据尽可能使用交换分区从未分区的表中删除大量行避免deletefrom…where…带来大量锁和日志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(页压缩)Jan–Jun(行压缩)Jul–De