SQLServer 数据仓库最佳实践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）,CREATETABLEdbo.DimProduct(ProductKeyintIDENTITY(1,1)NOTNULL,BKProductSKUnvarchar(25)NOTNULLDEFAULTNZZ-000-ZZ,ProductNamenvarchar(50)NOTNULLDEFAULTNProductunknownornotprovided,ProductSubCategorynvarchar(50)NOTNULLDEFAULTNProductSubcategoryunknownornotprovided,ProductCategorynvarchar(50)NOTNULLDEFAULTNProductCategoryunknownornotprovided,CONSTRAINTPK_dbo.DimProductPRIMARYKEYCLUSTERED(ProductKeyASC)ONDimFileGroupWITH(DATA_COMPRESSION=PAGE);-onlyifthisisaverybigdimension-为表描述创建扩展属性execsys.sp_addextendedpropertyname=NTableDescription,value=NInformationaboutproducts,level0type=NSCHEMA,level0name=dbo,level1type=NTABLE,level1name=DimProduct;GO;-创建用户访问视图CREATEVIEWProductASSELECTProductKey,BKProductSKU,ProductName,ProductSubCategory,ProductCategoryFROMDimProduct;GO;,.,42,事实表-数据类型/约束,数据类型约束尽量避免主键和外键，加快数据加载完整性靠ETL来保障,.,43,事实表-分区,对大事实表进行分区:通常是日期键好处:通过分区表并行扫描提高查询性能提高CUBE处理速度快速加载和删除改进索引管理性增强备份和还原的灵活性使用分区对其的索引视图:索引视图和表分区对区实现过程创建文件组和文件创建分区函数创建分区方案创建分区表对于已经存在的事实表可以通过重建聚集索引来分区,Pre-Jan,JanJun,JulDec,.,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/2008COMMIT最后也可以将行标记为Deleted，然后再非重要时间删除这些行。,.,65,九、手动管理统计值,在加载新数据后手动更新统计值，通过脚本更新统计值如果在周期性加载数据后会自动更新统计值，可以关闭表上的自动更新统计值功能。这些对经常读取最新数据的查询非常有用（优化器知道数据的最新分布信息）在小的维度表上增量加载数据后更新统计值也对性能有帮助，可以在更新维度表上的统计值是使用FULLSCAN选项来提供更准确的查询计划。,.,66,十、考虑高效的备份策略,备份很大的数据仓库非常耗时例如，备份一个2TB的数据库到SAN上的10块RAID-5的磁盘需要花费2小时（275MB/秒）使用SAN的快照备份技术是很好的选项减少备份的数据量历史数据分区所在的文件组可以标记为“只读”。当文件组变成只读后一次性备份文件组对读写文件组进行常规的备份计划注意只读文件组不可以进