大型数据仓库最佳实践

SQLServer2005大型数据仓库系统设计经验分享聂发如技术方案专家微软(中国)有限公司安排SQLServer2005数据仓库增强关系型数据仓库数据仓库设计数据装载、删除与备份

硬件与存储SQLServer集成服务(IntegrationServices)SQL

Server分析服务(AnalysisServices)SQLServer2005数据仓库增强SQLServer关系型引擎表分区与索引分区在线索引维护Piecemeal备份&恢复TSQL分析语句--Pivot,Unpivot,RankNUMA支持X64支持共享、可扩展数据库--SharedScalableDatabase快照隔离级别SQLServer报表服务企业报表对RDBMS与AS多维数据源的支持最终用户查询工具–报表生成器SQLServer2005数据仓库增强SQLServer集成服务高效的内存级的ETL处理专为数据仓库服务的转换组件缓慢变化维--SlowlyChangingDimensions主键检索--KeyLookup丰富的日志与容错处理能力SQLServer分析服务支持星型，甚至多-多等非传统数据架构完成支持维度模型--UDM丰富、灵活、可脚本化cube处理；DistinctCount的提升典型架构：源多维立方体

多维分区往往与事实表分区向对应

…维表事实表分区ODS或中间数据库数据源…多维分区关系型数据仓库SSISor

BulkInsert

SSIS事实表装载SSIS维表装载维度多维立方体处理

安排SQLServer2005数据仓库增强关系型数据仓库数据仓库设计数据装载、删除与备份

硬件与存储SQLServer集成服务(IntegrationServices)SQL

Server分析服务(AnalysisServices)事实表和维表事实表PeriodDateKeyWeekNumberYearCustomerCustomerKeyCustomerIDCustomerNameEnterpriseNameSICCodeAddressCityStateZipProductProductKeyProductIDProductNameCategorySalesDateKeyCustomerKeyProductKeyQtySoldDollars维表典型的星型架构‘StarSchema’查询示例:

Selectsum(Dollars)

fromSalesS

joinProductP

onP.Product_Key=S.Product_KeyjoinCustomerC

onC.Customer_Key=S.Customer_Key

joinPeriodPeonPe.Date_Key=S.Date_Key

where

Product_Name=‘SQLServer2005’

andEnterprise_Name=‘MSFT’

andWeek_Number=20

andYear=2006事实表物理设计在日期字段上建立ClusteredIndex支持对某一时间段的快速顺序扫描在每个foreignkey上建立日期字段上建立NonclusteredIndexes(foreignkey,DateKey)用于支持针对某一dimension的选择性查询避免PK与FK约束加快数据加载事实表物理设计CreateclusteredindexSalesCIDate

onSales(DateKey)CreatenonclusteredindexSalesNCICustomer

onSales(CustomerKey,DateKey)CreatenonclusteredindexSalesNCIProduct

onSales(ProductKey,DateKey)PeriodDateKeyWeekNumberYearCustomerCustomerKeyCustomerIDCustomerNameEnterpriseNameSICCodeAddressCityStateZipProductProductKeyProductIDProductNameSalesDateKeyCustomerKeyProductKeyQtySoldDollars事实表维表SalesNCIProductSalesCIDate事实表索引SalesNCICustomer对大型（大于50~100GB）事实表及其索引分区按日期字段分区可管理性滑动窗口可以简化历史数据清理查询性能包含时间条件的查询只需要访问所涉及的分区使用有意义的integer类型的日期key(20060101)易于编写适用于分区过滤的查询事实表物理设计--2事实表物理设计--2总是使用对齐的分区索引总是保持在首尾有空的分区使增加与删除分区更高效分离与合并--（SplitandMerge）PeriodDateKeyWeekNumberYearCustomerCustomerKeyCustomerIDCustomerNameEnterpriseNameSICCodeAddressCityStateZipProductProductKeyProductIDProductNameCategorySalesDateKeyCustomerKeyProductKeyQtySoldDollars事实表维表2006-01Sales2006-02Sales2006-03Sales2006-04Sales2006-05SalesPartition事实表分区分区设计注意事项精心选择分区粒度通常为月，季度或年清除数据时通常一次清除整个分区一次性加载整个分区非常快所以以日为单位分区很有吸引力但是，分区个数有限制（1000）分区粒度影响查询并行性只涉及一个分区的查询可以并行执行涉及多个分区的查询并行执行行为与一般查询不同每一分区一个并发线程直至MaxDegreeofParallelism如果需要查询并行执行，避免大多查询只涉及2、3个分区的设计维表物理设计使用integer代理键对日期使用有意义的integer（如：yyyymmdd）对LookupKey建立索引INCLUDE代理键

可以加快数据加载时的lookup对DimensionKey建立PKindex对其他经常被查询的列建立Nonclusteredindexes维表通常避免使用分区维表物理设计PeriodDateKeyWeekNumberYearCustomerCustomerKeyCustomerIDCustomerNameEnterpriseNameSICCodeAddressCityStateZipProductProductKeyProductIDProductNameSalesDateKeyCustomerKeyProductKeyQtySoldDollars事实表维表CustNCICustomerNameCustNCIID维表索引CustPKCICustomerKeyCustNCIEnterpriseNameAlterTableCustomeraddconstraintCustPKCICustomerKey primarykeyclustered(CustomerKey)…CreatenonclusteredindexCustNCID

onCustomer(CustomerID)INCLUDE(CustomerKey)CreatenonclusteredindexCustNCICustomerName

onCustomer(CustomerName)CreatenonclusteredindexCustNCIEnterpriseName

onCustomer(EnterpriseName)安排SQLServer2005数据仓库增强关系型数据仓库数据仓库设计数据装载、删除与备份

硬件与存储SQLServer集成服务(IntegrationServices)SQL

Server分析服务(AnalysisServices)滑动窗口回顾保持一个时间段内的事实数据在线加载最新的数据，卸载最老的数据两种加载新数据的方式一次性加载整个新分区增量加载最新分区总为两端各保留一个空分区加载最新数据Partition#123452005-02-012005-03-012005-04-012005-05-01

1/2005&

Earlier2/2005Data3/2005Data4/2005Data5/2005&

Later[EMPTY][EMPTY]在与目标分区所在的filegroup上创建中间表(5/2005)Split

最新的分区批量加载（Bulkload）并索引中间表Switch

数据到次新分区[EMPTY]2005-06-015/2005Data6/2005&

Later65/2005中间表卸载过期数据Partition#2005-03-012005-04-012005-05-013/2005Data4/2005Data在目标分区同一filegroup上创建用于卸载的表Switch

数据到表中Merge

第一个和第二个分区存档或Truncate表[EMPTY]2005-06-015/2005Data6/2005&

Later2/2005卸载表[EMPTY]123452/2005&

Earlier2/2005Data123452005-02-01

1/2005&

Earlier[EMPTY]6避免Split/Merge非空分区效率很低额外的日志只Split/Merge空分区即使需要临时用SWITCH清空分区加载历史数据使用Simple或BulkLoggedrecoverymodel创建没有索引的分区表创建为每个分区创建没有索引的表并发加载每个分区对应的表SWITCH所有表到对应的分区创建ClusteredIndex创建NonclusteredIndexes删除数据尽可能使用SWITCH分区从未分区的表中删除大量行避免deletefrom…where…带来大量锁和日志INSERT需要的行到新表通常更快安排SQLServer2005数据仓库增强关系型数据仓库数据仓库设计数据装载、删除与备份

硬件与存储SQLServer集成服务(IntegrationServices)SQL

Server分析服务(AnalysisServices)64位与存储策略64位平台非常适合大型数据仓库内存CPU

throughput大数据量与多并发用户的支持存储物理分离日志与数据存储使用RAID10存储日志，Raid10或5存储数据通常将数据分散在尽可能多的spindles上基于存储共享的可伸缩设计SAN上的只读版本的数据库可以被多台服务器共享多个服务器负载均衡部分SANs可创建共享的只读快照Copy

onwrite新数据被加载后刷新快照SAN报表服务器只读数据库(SAN快照)更新服务器读、写数据库安排SQLServer2005数据仓库增强关系型数据仓库数据仓库设计数据装载、删除与备份

硬件与存储SQLServer集成服务(IntegrationServices)SQL

Server分析服务(AnalysisServices)ETL服务包–在哪儿运行?SSISETL服务包是在显示调试与执行界面，或是调用dtexec.exe的计算机上运行的；采用SQLAgent调度执行时，

调度任务的服务器将运行服务包；注意:服务包运行于ServiceAccount的安全级别；如果你本机的SQL

ManagementStudio连接到SSIS服务器,在SQL

Management

Studio中选中服务包,并执行‘Run’:

服务包将运行在你的客户机上!避免内存问题基于内存的管道设计避免了各步骤之间数据持久化带来的不必要的IO部分pipeline转换组件可能使用大量内存Sorts考虑使用数据库排序Aggregations（大量分组时）考虑使用数据库聚合LookupTransform（fullycached）使用SQLStatement返回需要的行或使用CachedMemoryRestriction选项或在RDBMS中查找这些转换组件可能耗尽32位或64位服务器系统内存SSIS服务包作为任务协调器数据流管道任务在数据库之外执行;有些用户喜欢将ETL业务逻辑嵌入在存储过程中,借助一系列中间表来处理数据

这种模式有时也称为“ELT”SSIS服务包可通过“执行SQL任务

”组件来协调ELT操作控制流协调并行任务与依赖关系；记录错误日志或非正常结果，并采取补救措施；但请记住–管道任务处理效率更高,由于省却了多次数据存取的IO操作。数据仓库专用管道任务：缓慢变化维向导好处

简化数据仓库管理；基于源数据系统的列数据变化，轻松指定何时需更新或添加维表记录；可标示维表数据是否为当前数据或已过期，并跟踪数据的有效时间范围；注意用于执行单次数据的查找、更新与插入最好不要用在大量数据行一次性插入场景不能扩展处理1百万行数据的插入超大数据量的变化维考虑采用数据库操作来处理，最好不用管道任务从源数据中捕获数据变化，将其放到SQLServer中间表里基于自然键，建立中间表与维度表外连接,并通过比较新值与旧值，将“更新”与“插入”区分开；使用基于Set的SQL语句，插入与更新整个数据变化；详见ProjectREAL白皮书中的方法

/technet/prodtechnol/sql/2005/realetldp.mspx

数据仓库专用管道任务：查找转换基于维表的当前行，查找事实表的代理键；注意:对大维表的全缓存，可能会耗尽系统内存；使用SQL查询语句，指定要查找表的字段与代理键，不要直接用表降低内存的网络消耗；

缓存查找仅执行EQUAL关联对于更复杂的关联条件，请使用限制缓存并修改SQL查询语句/article.php/ssis-lookup-with-range不缓存或内存受限的缓存，可以对每一行执行数据库查找但对大量输入数据的扩展能力不佳配置高效的查找使用查询语句，指定所需的最少字段；避免直接使用表或视图作为查找参照源；其它SSIS考虑大型数据仓库中，应考虑将SSIS运行在独立的物理服务器上消除对CPU与内存的竞争使用避免使用SQLServerDestination目标连接只有当SSIS服务包与SQLServer在同一服务器上才有效；

限制了服务包的可移植性；不支持“提交大小”(commitsize)的设置加载分区表与索引时效率低下应适用OLEDBDestination目标连接尽量避免在SSIS中使用事务若必须使用事务，在任务属性中将隔离级别设为ReadCommitted缺省为Serializable避免在管道中使用AnalysisServicesDestination采用ASProcessingTask；安排SQLServer2005数据仓库增强关系型数据仓库数据仓库设计数据装载、删除与备份

硬件与存储SQLServer集成服务(IntegrationServices)SQL

Server分析服务(AnalysisServices)高效AnalysisServices架构复杂不利于性能：

使用传统的星型架构关系型数据仓库作为数据源

避免采用高度规范的ER架构模型

示例:比较AdventureWorks与AdventureWorksDW的不同使用MOLAP存储模式维度采用整型键值避免父子维度不支持聚合避免超大数据的多对多(many-to-many)维度如果维度成员达到百万，极大地降低处理与查询效率；在多对多维中，其属性不支持聚合；数据源:采用高性能的提供器若为SQL

Server数据源，应采用 “NativeOLEDB/SQLNativeClient”

该OLEDB驱动器在连接字符串中为“SQLNCLI”；不要使用.NET提供器：“SqlClientDataProvider”避免“All-In-One”立方体AS2000

AS2005,AS对象粒度有所改变AS2000立方体

AS2005度量组AS2000数据库

AS2005立方体UDM容许在单个立方体中有多个度量组然而–将大量度量组置于单立方体中并非好设计更高的查询与计算开销可考虑，使用多个立方体，每立方体中少量度量组若需展示来自多个立方体的数据，可连接度量组实现如同AS2000中的虚拟立方体应考虑，仅将需经常同时查询并共享一组维度的度量组，置于同一立方体中维度通常基于星型架构的单个维度表，或雪花架构的多个关联维度表；

包含多种类型对象用户层次：

提供下钻浏览路径与多聚合层次，并能在查询中实现数据切片与旋转透视；属性：

附加的字段，可用于切片与旋转透视客户维示例属性自然层次报表层次维表属性属性与属性层次会增添立方体的空间，处理与索引开销仅包含报表与分析所需存取的字段不要自动地将维度表的所有属性包含进去；缺省时，所有字段增添为属性.

仅将在分析与旋转透视时需经常用到的属性的‘属性层次’设为“Enable”如:电话号码并不是个有用的属性层次缺省时，所有属性的“属性层次”已被设为“Enable”.不需要的属性与属性层次，会显著增加立方体的大小与处理时间；字段与属性没被用作属性的字段层次被禁的属性属性2类用户层次：报表层次与自然层次报表层次与自然层次报表层次：浏览路径或通常用作展现路径如: 性别

职业

收入状况暗示多对多关系各种组合,而不是上卷自然层次：浏览路径，同时也是聚合的基础如: 姓名

邮政编码

城市

省

国家暗示1对多

关系在层次的每一层上设置“属性关系”，即可创建自然层次大维度中,自然层次对查询性能提升非常重要;缺省时，AS仅考虑在自然层次中创建数据聚合；自然层次需在层次中设置属性关系立方体建立向导中，属性关系并未缺省创建；在维度设计器中，属性关系缺省不可见；属性关系显示单击此处属性关系缺省时，所有属性关系都基于维度主键；在相应的层次上设置属性之间的关系，构造出自然层次；确保这些关系在层次上是上下连接的；CountryCityZipStateNameCustomerIDGenderAge缺省:正确:CountryCityZipStateNameCustomerIDGenderAge属性关系界面显示缺省:正确:属性关系类型灵活关系–缺省时容许维度行灵活变化，维度成员移动到层次中的另一位置也无须重新处理立方体如：

员工

部门适合“Type-1”的缓慢变化维，或维度数据源直接来自OLTP系统

固定关系适合关系不会的维度，一旦变化，需重新处理维度如：

邮政编码

城市适合于“Type2”缓慢变化维–维数据变化作为一条新记录尽可能采用固定关系灵活关系会迫使AS一旦检测到维值变化，就会删除并重建所有分区的索引与聚合；任何属性关系都是一成员属性度量组分区