SQL--Server-数据仓库最佳实践.ppt
《SQL--Server-数据仓库最佳实践.ppt》由会员分享,可在线阅读,更多相关《SQL--Server-数据仓库最佳实践.ppt(69页珍藏版)》请在咨信网上搜索。
1、SQLServer数据仓库最佳实践Level300.课程内容数据仓库设计与最佳实践设计最佳实践案例2.数据仓库基本概念数据和信息的区别数据是由可观察和可记录的事实组成,通常存在于OLTP系统中。数据只有被加工处理为信息后,才有意义。信息是经过加工处理并对人类客观行为产生影响的数据表现形式。3.Kimball数据仓库建设4.一、定义业务需求5.说明提炼业务流程初始化数据仓库总线矩阵6.二、维度建模7.四步建模过程8.AdventureWorksCycles公司企业数据仓库总线矩阵-全公司9.定义业务流程优先级业务流程数据可行性低,业务价值/影响低10.确定粒度维度中粒度表示法业务流程的粒度及基础
2、度量11.业务角色矩阵定义角色,明晰每个角色需要看的业务流程数据进一步可进行数据安全性的设计12.定义维度属性/数据质量分析定义维度属性源系统数据质量分析13.为每个业务流程定义详细维度模型Sales OrderItem QuantityUnit CostTotal CostUnit PriceSales AmountShipping CostTime(Order Date and Ship Date)SalespersonCustomerProductCalendar Year Month DateFiscal Year Fiscal Quarter Month DateRegion Cou
3、ntry TerritoryManager NameNameCountry State or Province CityAgeMarital StatusGenderCategory Subcategory Product NameColorSize14.根据维度模型详细定义进行数据仓库逻辑设计维度表事实表星型架构15.雪花型架构以下情况考虑:在多个维度之间共享的子维度存在层次结构,并且维度表包含变化频繁的小的数据子集多个不同粒度的事实表引用到维度层次结构的不同层级DimSalesPersonSalesPersonKeySalesPersonNameStoreKeyDimProductProd
4、uctKeyProductNameProductLineKeySupplierKeyDimCustomerCustomerKeyCustomerNameGeographyKeyFactOrdersCustomerKeySalesPersonKeyProductKeyShippingAgentKeyTimeKeyOrderNoLineItemNoQuantityRevenueCostProfitDimDateDateKeyYearQuarterMonthDayDimShippingAgentShippingAgentKeyShippingAgentNameDimProductLineProduc
5、tLineKeyProductLineNameDimGeographyGeographyKeyCityRegionDimSupplierSupplierKeySupplierNameDimStoreStoreKeyStoreNameGeographyKey16.维度表逻辑设计-键代理建业务键17.属性和层次结构层次结构切片钻取明细18.Unknown和None识别 NULL 值的含义Unknown 还是 None?不要假设 NULL 等价使用 ISNULL()源维度表19.设计缓慢渐变维度类型1类型 2类型 320.时间维度表代理键粒度范围属性和层次结构多日历未知值ETL 还是预先填充?21.
6、自关联的维度表Kim AbercrombieKamil AmirehJeff HayCesar Garcia备注:如果层次固定,还是建议重构成固定层次的平面表,然后创建层次结构,父子层次结构如果中间层级过多,性能不好。22.垃圾(Junk)维度将不属于已有维度的低基数属性合并到一起避免创建很多小的维度表23.事实表设计-列维度键度量值退化维度24.度量类型累加半累加不可累加25.事实表的三种类型26.示例事务型事实表周期性快照事实表累计快照事实表27.价值链跨业务流程共享维度28.设计之其他考虑-Factless事实表如何获取正在参加促销活动但是没有销售出去的产品信息?29.设计之其他考虑-维
7、度和事实之间多对多多个维度值指派到一个事实交易如果需要为每一个销售代表分配销售,可以在中间表添加权重30.设计之其他考虑-维度和维度之间多对多例如:一个客户有一个或多个Account,一个Account对应一个或多个客户31.三、技术架构设计32.微软DW/BI系统体系结构33.服务器架构(1)34.服务器架构(2)35.开发团队配置36.四、数据仓库物理设计37.物理数据分布跨物理设备分布数据:使用文件组和RAID存储(推荐RAID10)数据文件和日志文件分开存储工作区对象单独存储:例如,临时的数据表预先分类空间禁用自动增长为所有文件分配一样的尺寸38.维度表-索引代理键创建聚集索引业务键创
8、建非聚集索引Include(代理键),加速Lookup效率经常查询的属性创建非聚集索引对于非常大的包含缓变属性的维度创建四列索引:索引键(业务键,开始日期),包含(结束时间,代理键)这样可以加速ETL处理过程39.维度表-视图通过视图封装例如在雪花型架构中,通过创建视图可以把多个维度表连接起来可以将视图定义成索引视图,从而将数据物理化演示:索引视图演示:索引视图40.维度表物理创建脚本(示例AllinOne)CREATE TABLE dbo.DimProduct(ProductKey int IDENTITY(1,1)NOT NULL,BKProductSKU nvarchar(25)NOT
9、NULL DEFAULT NZZ-000-ZZ,ProductName nvarchar(50)NOT NULLDEFAULT NProduct unknown or not provided,ProductSubCategory nvarchar(50)NOT NULLDEFAULT NProduct Subcategory unknown or not provided,ProductCategory nvarchar(50)NOT NULLDEFAULT NProduct Category unknown or not provided,CONSTRAINT PK_dbo.DimProd
10、uct PRIMARY KEYCLUSTERED(ProductKeyASC)ON DimFileGroupWITH(DATA_COMPRESSION=PAGE);-only if this is a very bigdimension-为表描述创建扩展属性exec sys.sp_addextendedproperty name=NTable Description,value=NInformation about products,level0type=NSCHEMA,level0name=dbo,level1type=NTABLE,level1name=DimProduct;GO;-创建用
11、户访问视图CREATE VIEW Product AS SELECT ProductKey,BKProductSKU,ProductName,ProductSubCategory,ProductCategoryFROM DimProduct;GO;41.事实表-数据类型/约束数据类型约束尽量避免主键和外键,加快数据加载完整性靠ETL来保障事实列类型最快的SQLServer数据类型代理键tinyint、smallint、int、bigint日期键采用yyyyMMdd格式的int整数度量值tinyint、smallint、int、bigint数值度量值smallmoney、money、real、f
12、loat(请注意,与money和float类型相比,decimal和vardecimal要求更强大的CPU处理能力)非重复计数列tinyint、smallint、int、bigint(如果您的计数列是char,则考虑执行哈希计算或用代理键进行替换)42.事实表-分区对大事实表进行分区:通常是日期键好处:通过分区表并行扫描提高查询性能提高CUBE处理速度快速加载和删除改进索引管理性增强备份和还原的灵活性使用分区对其的索引视图:索引视图和表分区对区实现过程创建文件组和文件创建分区函数创建分区方案创建分区表对于已经存在的事实表可以通过重建聚集索引来分区Pre-JanJanJunJulDec43.分区
13、数据分布与操作示例44.滑动窗口保持一个时间段内的事实数据在线加载最新的数据,卸载最老的数据两种加载新数据的方式一次性加载整个新分区增量加载最新分区总为两端各保留一个空分区45.加载最新数据在与目标分区所在的文件组上创建中间表(5/2008)拆分 最新的分区批量加载(Bulk Insert 或者bcp)并索引中间表交换 数据到次新分区Partition#Partition#Partition#Partition#1 1 1 12 2 2 23 3 3 34 4 4 45 5 5 52008-02-012008-02-012008-03-012008-03-012008-04-012008-04
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL Server 数据仓库 最佳 实践
1、咨信平台为文档C2C交易模式,即用户上传的文档直接被用户下载,收益归上传人(含作者)所有;本站仅是提供信息存储空间和展示预览,仅对用户上传内容的表现方式做保护处理,对上载内容不做任何修改或编辑。所展示的作品文档包括内容和图片全部来源于网络用户和作者上传投稿,我们不确定上传用户享有完全著作权,根据《信息网络传播权保护条例》,如果侵犯了您的版权、权益或隐私,请联系我们,核实后会尽快下架及时删除,并可随时和客服了解处理情况,尊重保护知识产权我们共同努力。
2、文档的总页数、文档格式和文档大小以系统显示为准(内容中显示的页数不一定正确),网站客服只以系统显示的页数、文件格式、文档大小作为仲裁依据,平台无法对文档的真实性、完整性、权威性、准确性、专业性及其观点立场做任何保证或承诺,下载前须认真查看,确认无误后再购买,务必慎重购买;若有违法违纪将进行移交司法处理,若涉侵权平台将进行基本处罚并下架。
3、本站所有内容均由用户上传,付费前请自行鉴别,如您付费,意味着您已接受本站规则且自行承担风险,本站不进行额外附加服务,虚拟产品一经售出概不退款(未进行购买下载可退充值款),文档一经付费(服务费)、不意味着购买了该文档的版权,仅供个人/单位学习、研究之用,不得用于商业用途,未经授权,严禁复制、发行、汇编、翻译或者网络传播等,侵权必究。
4、如你看到网页展示的文档有www.zixin.com.cn水印,是因预览和防盗链等技术需要对页面进行转换压缩成图而已,我们并不对上传的文档进行任何编辑或修改,文档下载后都不会有水印标识(原文档上传前个别存留的除外),下载后原文更清晰;试题试卷类文档,如果标题没有明确说明有答案则都视为没有答案,请知晓;PPT和DOC文档可被视为“模板”,允许上传人保留章节、目录结构的情况下删减部份的内容;PDF文档不管是原文档转换或图片扫描而得,本站不作要求视为允许,下载前自行私信或留言给上传者【精****】。
5、本文档所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用;网站提供的党政主题相关内容(国旗、国徽、党徽--等)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。
6、文档遇到问题,请及时私信或留言给本站上传会员【精****】,需本站解决可联系【 微信客服】、【 QQ客服】,若有其他问题请点击或扫码反馈【 服务填表】;文档侵犯商业秘密、侵犯著作权、侵犯人身权等,请点击“【 版权申诉】”(推荐),意见反馈和侵权处理邮箱:1219186828@qq.com;也可以拔打客服电话:4008-655-100;投诉/维权电话:4009-655-100。