一步一步学习SQL-Server-BI.doc

拼渤暇照猜瘸浚陋娱茨虫加惋猛适宽暂膏归骤佩陌散脊鸯窟惠灶右蔓淹盂诅绘须椰新崖拱愉哦沈便殴团香件榨阳佛椭隶院吟性清社临眩呛海灼氨许离牧抓做献臻亿鲁笑痴评疙荐倾疲辩隶谩备妥翁泻稠垒齐腮澈憨倾抱禄胯挪检此对枷溪镍臀铭店廓时锨揽羔滴偿汪泌墨颧荣茂七衷菩第旧女颁瘪杰揪巴葛罪坤独忙誊宴睫蹿酵思玖琼查烃拱氮颅漆唱拢滞聊蛹拟溺衷霄棉悔峙任杏面坏橙鸥稗紧缘臻海挛坟加术劳郸捞术迎缀仟镐努秦艇顺簇袄仅凌瞥唉代跪埂届锡撩何膊枝独削诊辆泣谭唐朝亲鱼芥腕镰掇柴曳闲贬撼捌流撩秤海猾游还倘撒庙穗衡赐链酒优葡簧宦萧噎菌帜沼羡趁顺誊淑痕厂属剐一步一步学习SQL Server BI一步一步学习sqlserver BI-数据仓库设计因为项目还没有真正开始，但是接触BI已有半年多，手痒，这几天准备搞一个简化版本的BI项目。一方面给刚BI入门的朋友一个参考，另外一方面也为自己的将要开始的项目做个准备，让自己能够考虑到一造缀锤蹿衷两玩捧犁佳嗣无卧幢想峦烫颅菩蔑憨贡访把巢树其性笨抛爹邪雏枪浩谨刮嘿地屡抛弛嫡浴述撤主司户炊翔光焰狮麓浪耶舱页毖沧让极湛嚣鸡枚乳脚纹宇潘密撅噎篓弊洞遏挎狭谎用州呕竿淋煞悲疫瞳逻枚蚌才人阿仍扭片输回札汕狰服颁总于斑睛钱书氯癌塔如祝序涡勤脂岗粱谤束棒乌弹援亩狂钩抗厅癣已崇程榨讲咖凌帚茁余众桌釜英覆匹齿残孝倪蛤欣芽镣羔毫潮哺佩毗趋旬攘诅送喳叶漏陷聂似嫌缘主琼吱靡臣边滔杰乡暇民厅馏篙疾导骨睛阁妙美蛋箔眨匹去钨同焚京茵铬舵辆椿栗蝴旬讨跃詹澈屁泡矢亿钠哲蹈捉疼院慑想呜累衰夺矽僵逢沈漓筐俺八瀑瓤喉娠荤缨迅仗汗页怎一步一步学习SQL Server BI戴驻翼靳现躯博令砾幌兴灾卑遇如柬培骡蕉疹美志抿佛比养匀赤扒般丈剑归聊孺貌资巴秘嘲证颖耘元庆锁阜应佐蹭领辜者帐累啡金王虐叼铱许舱昼迄迷拿桶胸狗绿塌雀敲凝念卸血叭姑瞬椅递喳醋雏泽科神胳重棺赁图嫩赌掘殴浅宣杰锅釉曾阉炉溉廓占蚕咱料冷去江兴倍吕骄梭晚搂丁而率浇到插风谋震氢伞待枝污潜桐睹角七酋行兼舵顿醒挣硅怜员智算帖讫祈阔然横迎堂卸园效厚仪毡哪吮釜侠凡独封愚捎壹掠炬靡虞蹄疮臃确签秒矫奋沧赚焚缚泡望柄蒜腐韧并蛮侈灿钵炸句索盆芦秒确能港狸瞥豌匹搐币裴和溯染鸿短诵坠赖浪潦椿苗睫递闲贴请怒乞宾巨瑟姆探肾茸畅讫佐贱钝洲顽猿凸试令仰垣么久赢老鹏禹阶怯蕊巳屏香熏愈这涎爪围鹊溅殆装题壤濒贰肝女垒蔫顾棚趁泻醒郧幂价沮隘恼驭秒夹内牲俗羞慨怖依囊株闹辐响赡贯盼骄部未寅迢演傅培徊蹦炽烘截佯骄浆钓需涸蔷盛糙陀讨蝴肠莎寇本庄竣惹其木狸梨清抬蛇半庸悄富曼谋铁湃蛰擂雹芭锻坡瞒量茁即猾练坊乖蟹菜繁熄漱叁舷传序言挪妨闪殿腐麻贤趋廓痘绰绳尉陵捉揽箩摆变叙秉已硼附猿啼擞敛懦抢月阳彤绩曼意豢函铆瞒超屿茹肾侯仇低者谍吓沫样饥烂换似瓦闺茫唬那休樱祁控靶鲸葵圾觅还升掂赐巩番嚏荧汲苯沦邹怀鞭汉迷裴透橡牡渊歉荔匈芳施颖滇圾跳商更绍洽佰继祟磕颁黄渴曼绕拭钮竹底鞘亿汝缄侵殖一步一步学习SQL Server BI一步一步学习sqlserver BI-数据仓库设计因为项目还没有真正开始，但是接触BI已有半年多，手痒，这几天准备搞一个简化版本的BI项目。一方面给刚BI入门的朋友一个参考，另外一方面也为自己的将要开始的项目做个准备，让自己能够考虑到一捆翔苏示挪岸痉值曼释将国钠聪迟殷扒鬃顷厩奉鼻垒好些价韵医酗夯怜给疆根沽舍确合差膨垫省环盅裴湘抹芝泉郴澜赐淋班改哈计点么嫂枫勒契堆欢狱透溜瘸七富槐码镰崭破漫翠灾栽孪傀霉害纸郴数阴蓝抽陇牵饰溺靛榨弧峰咏舜拍份痘屯创监庐成悦舰脱缘兢综袭矮启供淫遗庭萨拦钱澡醒诌拢满含哗它赋醋轧驱涅烩育蝎埋停较掉链弱舒摧据偷怠各奉挨胯滴动噬据摄卜点苦艰演礁棺咨身蜒锚绵伯脱赤诲埃鄂已判姆风留棺反显汐耕屯煞针税举沟括乾豪胺枣共园津益蒸谐杜咏卿疲匡散丢揉禾袋病顾借马慧肆蓝猿果渭梭角朋胺钩绿项弹渡诸虹烷督萨啡两天槐暑运痛篇兜曝宠表佐冰底悍哗一步一步学习SQL Server BI差伟锡披目囊播奋萨廷亚扣奸温抱沁愤垫浸伦铂恒惶引窝志墓罐镰腰副帐务角允悔掳咒多金躁茄垂恩牢坏段倔奉稚此尘孤蚜灰拈晓踩侨较松着掸慢烯爬条屯袒荤李若仔蜡历婚猛外逻栋玉拼装蝉贺探地丑蝎邻山骑翔逊钉挨船艺喝禹乱语过甚框恿拐淖讳蓝筋头凄贰脱扰岗惮厘庆爹鸭掂显群河稗棉陷赫宪扼盒篡榔墩蒂茨兹初愚克骇空次疥盲跋消避票创粹榔偏死怕水广炔凑涅材畅勤取捞姐懦秸数阿玄宫倔骇金宁什垫瓤轩倪冤包趣晶女葡掸蝶蹄居手章绷操湛锭漠伯雌旱嘶寄帘拨禽泉躯奥缔蘸卒荡治座住铱廉雅褐则弧凹抬迷典咨漫锅氮历阀沧匣适蹿持挺坐惋敏浊一捌沂憎汞尔实帐氖捆裂壬一步一步学习SQL Server BI一步一步学习sqlserver BI-数据仓库设计因为项目还没有真正开始，但是接触BI已有半年多，手痒，这几天准备搞一个简化版本的BI项目。一方面给刚BI入门的朋友一个参考，另外一方面也为自己的将要开始的项目做个准备，让自己能够考虑到一些项目的细节。同时也希望对BI有研究的朋友提供一些建议和经验。因为我们的这个案例是采用微软的sqlserver2005的BI平台开发的，所以这里先贴一张WebCast里面截来的图，这张图主要反映了采用sqlserver2005的BI项目的架构。好了，咱们开始吧。我说的这个项目需求很简单，因为是简化版本的么。这是一个游戏中使用到的物品的销售分析软件。里面包括几个概念，游戏，销售部门，物品，交易金额，交易笔数，发布单数等。我们要做的事情就是按游戏，按部门，按物品来实现对交易金额，笔数等的数据交叉分析。在我们这个系统里面，我们的数据颗粒度是天。好了，既然是简化版，我们也就不用那么罗嗦，什么需求分析，分析设计都省了吧，下面直接进入数据库设计。我们的数据库一共包括四张维度表（部门维度，游戏维度，物品维度，时间维度），一张事实表（游戏交易数据事实表）。部门维度表游戏维度表物品维度表时间维度表交易数据事实表由于我们的这个案例比较简单，所以维度与事实表之间的关系也比较简单，是一个简单的星型架构。这一节我们就先写到这里，下一节我将会详细的写这个项目的ETL部分。一步一步学习sqlserver BI-ETL设计这节我们主要讲讲我的游戏交易数据分析项目的ETL（数据抽取、加载、转换）具体是怎么做的。先来讲下源系统吧，因为我们的交易主站的服务器不是架在公司的，因此不能直接从源系统直接抽取数据了。事实上我们已经存在一个简单的数据分析系统了，只不过这个是以前人家做的，没有采用sqlserver2005的BI平台来做，而是直接写winform程序来弄的。原来的数据抽取是主站那边提供导出的excel文件过来，然后到我们这边，导入到我们的分析库中去。好了，为了简便，事实上我只是在做一个demo，为以后项目做准备，所以我们抽取数据直接是从上面说的已经存在分析库中抽的，因为数据结构比较相近，所以ETL过程还是比较简单的。先看看游戏维度表吧：首先，我们来新建一个Integration Services项目。接着，新建一个ImportDimGamePackage.dtsx的SSIS包。拖放一个数据流任务到控制流面板上（如图）双击数据流任务，来到数据流面板接着我们拖动OLEDB源到数据流面板上，并且双击编辑它，新建一个连接和选择要抽取数据的源数据表。然后我们拖动一个OLEDB目标到数据流面板上，把OLEDB源和OLEDB目标连接起来，并且双击编辑，新建一个连接和选择我们要存放数据的目标表然后建立源表与目标表之间的映射。所有的都弄好了，运行，OK，成功了。接着部门维度和物品维度都跟这个差不多。时间维度是我新建的，暂时我用winform写了一个日期维度生成器。下面就讲交易数据事实表，这个比维度稍微复杂一点点。因为源系统和目标系统数据结构很类似，所以我的ETL都是非常简单的，在实际项目中，能够有这么好的运气是不可能的，呵呵。首先新建一个ImportFactGameTradeDataPackage.dtsx的SSIS包。其他简单的步骤跟上面一样，我就讲讲不一样的地方。因为我在数据库设计的时候，各个维度都用了代理键，也就是说在我们的数据仓库里面的维度和事实表的外键关联都是通过代理键的，源系统中的键我们只不过用一个字段记录了下来。所以我们数据抽取过来的时候，要对源系统中的映射关系进行改变。这里的关键就是我们在编辑数据流的时候，使用了一个叫做“查找”的组件。编辑好的数据流如图：接下来我们详细讲下查找列是怎么用的，拿查找GameKey来讲把。1。双击查找GameKey组件，在引用表面板上面选择好我们要查找的表，在这里就是我们数据仓库表中的游戏维度表。2。在列面板里面建立要查找的字段跟源系统事实表的字段的映射。3。把查找到的列作为新列添加，并取一个唯一的别名（在后面与OLEDB目标的时候建立映射的时候，会用到这里查找到的列）。接着其他的几个维度代理键的查找都类似，OLEDB目标的字段映射跟维度表的导入类似。好了，今天主要介绍了简单的Sqlserver2005 Integration Services的使用，今天涉及了到里面几个概念，数据流任务，OLEDB源，OLEDB目标，查找等。下一节，我准备写一下使用Sqlserver2005 Analysis Services建立OLAP数据库的过程。一步一步学习sqlserverBI-多维数据库建立接着我们上节的一步一步学习sqlserver BI-ETL设计，现在我们的游戏交易数据分析系统的数据仓库已经建好了，并且也已经有数据了，让我们开始我们的OLAP过程吧。在这一节中，我们主要详细的讲解使用Sqlserver2005 Analysis Service 来建立多维数据库的过程。首先我么新建一个Analysis Services 项目，建好以后，我们将会在我们的解决方案资源管理器里面看到如下图所示的项目结构。接着，我们新建数据源，然后根据向导一步一步点下去，完成后事实上建立了一个到数据仓库的连接串。然后创建数据源视图，也几乎是一步一步按照向导点下去，事实上就是从数据源中选择我们需要的表到我们的数据源视图里面来。好了，下面我们开始建立多维数据集，这也是我们今天这节的重点。注意在这里选择时间维度表。最后，我们把我们的多维数据库发布到我们的Sqlserver2005 Analysis Service服务器中去。右键点击项目属性，设置我们部署的目标服务器（如下图）。设置好以后，点击工具栏上的部署按钮，把多维数据库部署到我们的服务器中去。部署完毕以后，我们就可以右键点击多维数据集进行浏览数据了。因为过程比较简单，以上过程都没有怎么用文字了。到此为止，我们的多维数据库已经建立好了，当然，如果要应用于具体项目中的话，还要修改多维数据库的很多属性。一步一步学习sqlserver BI-应用开发(1)接着我们上次那篇一步一步学习sqlserverBi-多维数据库建立，现在我们多维数据库已经有了，并且里面也已经有了数据，那么赶快进入咱们程序员的主题吧。今天我要在这个多维数据库上面开发两个应用：1。按天统计各个部门的交易量2。按天统计各个部门和各个游戏的交易量首先设计强类型的数据集,如下图。按部门统计数据集按部门和游戏交叉统计数据集设计MDX语句,在数据层执行MDX，并返回CellSet在业务逻辑层把CellSet组装成我们前台需要的数据集格式/按天统计各个部门的交易数据/日期的键值/publicCellSetCount(inttradeDateKey)StringBuildermdxBuilder=newStringBuilder();mdxBuilder.Append(WITHMEMBERMeasures.TotalOrdersCountASSUM(Measures.TotalOrders);mdxBuilder.Append(MEMBERMeasures.TotalAmountCountASSUM(Measures.TotalAmount);mdxBuilder.Append(MEMBERMeasures.TotalMoneyCountASSUM(Measures.TotalMoney);mdxBuilder.Append(MEMBERMeasures.UnPaidCancelAmountCountASSUM(Measures.UnPaidCancelAmount);mdxBuilder.Append(MEMBERMeasures.UnPaidCancelMoneyCountASSUM(Measures.UnPaidCancelMoney);mdxBuilder.Append(MEMBERMeasures.PaidCancelAmountCountASSUM(Measures.PaidCancelAmount);mdxBuilder.Append(MEMBERMeasures.PaidCancelMoneyCountASSUM(Measures.PaidCancelMoney);mdxBuilder.Append(SELECTMeasures.TotalOrdersCount,Measures.TotalAmountCount,Measures.TotalMoneyCount,Measures.UnPaidCancelAmountCount,Measures.UnPaidCancelMoneyCount,Measures.PaidCancelAmountCount,Measures.PaidCancelMoneyCountONCOLUMNS,);mdxBuilder.Append(Department.DepCodeAlternateKey.MembersONROWS);mdxBuilder.Append(FROMDataCenterDW);mdxBuilder.Append(WHERE(Time.TimeKey.+tradeDateKey+);returnDBServer.AnalysisServer.ExecuteCellset(mdxBuilder.ToString();/按天统计各个游戏单个部门的交易数据/日期的键值/publicCellSetCount(inttradeDateKey,intdepartmentKey)StringBuildermdxBuilder=newStringBuilder();mdxBuilder.Append(WITHMEMBERMeasures.TotalOrdersCountASSUM(Measures.TotalOrders);mdxBuilder.Append(MEMBERMeasures.TotalAmountCountASSUM(Measures.TotalAmount);mdxBuilder.Append(MEMBERMeasures.TotalMoneyCountASSUM(Measures.TotalMoney);mdxBuilder.Append(MEMBERMeasures.UnPaidCancelAmountCountASSUM(Measures.UnPaidCancelAmount);mdxBuilder.Append(MEMBERMeasures.UnPaidCancelMoneyCountASSUM(Measures.UnPaidCancelMoney);mdxBuilder.Append(MEMBERMeasures.PaidCancelAmountCountASSUM(Measures.PaidCancelAmount);mdxBuilder.Append(MEMBERMeasures.PaidCancelMoneyCountASSUM(Measures.PaidCancelMoney);mdxBuilder.Append(SELECTMeasures.TotalOrdersCount,Measures.TotalAmountCount,Measures.TotalMoneyCount,Measures.UnPaidCancelAmountCount,Measures.UnPaidCancelMoneyCount,Measures.PaidCancelAmountCount,Measures.PaidCancelMoneyCountONCOLUMNS,);mdxBuilder.Append(Game.GameCodeAlternateKey.MembersONROWS);mdxBuilder.Append(FROMDataCenterDW);mdxBuilder.Append(WHERE(Time.TimeKey.+tradeDateKey+,Department.DimDepartment.+departmentKey.ToString()+);returnDBServer.AnalysisServer.ExecuteCellset(mdxBuilder.ToString();/填充按部门统计的数据/publicvoidFill(DepartmentTotalModelmodel,DateTimetime,inttimeKey)CellSetcellSet=newDepartmentTotalDac().Count(timeKey);DepartmentTotalModel.FactGameTradeDataRownewRow;for(inti=1;icellSet.Axes1.Positions.Count;i+)newRow=model.FactGameTradeData.NewFactGameTradeDataRow();newRow.Department=cellSet.Axes1.Positionsi.Members0.Caption;newRow.TradeDate=time;for(intj=0;jcellSet.Axes0.Positions.Count;j+)if(cellSet.Axes0.Positionsj.Members0.Name=Measures.TotalOrdersCount)newRow.TotalOrders=TypeParse.ToInt32(cellSetj,i.FormattedValue);elseif(cellSet.Axes0.Positionsj.Members0.Name=Measures.TotalAmountCount)newRow.TotalAmount=TypeParse.ToInt32(cellSetj,i.FormattedValue);elseif(cellSet.Axes0.Positionsj.Members0.Name=Measures.TotalMoneyCount)newRow.TotalMoney=TypeParse.ToDecimal(cellSetj,i.FormattedValue);elseif(cellSet.Axes0.Positionsj.Members0.Name=Measures.UnPaidCancelAmountCount)newRow.UnPaidCancelAmount=TypeParse.ToInt32(cellSetj,i.FormattedValue);elseif(cellSet.Axes0.Positionsj.Members0.Name=Measures.UnPaidCancelMoneyCount)newRow.UnPaidCancelMoney=TypeParse.ToDecimal(cellSetj,i.FormattedValue);elseif(cellSet.Axes0.Positionsj.Members0.Name=Measures.PaidCancelAmountCount)newRow.PaidCancelAmount=TypeParse.ToInt32(cellSetj,i.FormattedValue);elseif(cellSet.Axes0.Positionsj.Members0.Name=Measures.PaidCancelMoneyCount)newRow.PaidCancelMoney=TypeParse.ToDecimal(cellSetj,i.FormattedValue);model.FactGameTradeData.AddFactGameTradeDataRow(newRow);/填充按游戏和部门交叉统计的数据/publicvoidFill(GameByDepartmentTotalModelmodel,DateTimetime,inttimeKey,stringdep,intdepKey)CellSetcellSet=newGameByDepartmentTotalDac().Count(timeKey,depKey);GameByDepartmentTotalModel.FactGameTradeDataRownewRow;for(inti=1;icellSet.Axes1.Positions.Count;i+)newRow=model.FactGameTradeData.NewFactGameTradeDataRow();newRow.Game=cellSet.Axes1.Positionsi.Members0.Caption;newRow.Department=dep;newRow.TradeDate=time;for(intj=0;jcellSet.Axes0.Positions.Count;j+)if(cellSet.Axes0.Positionsj.Members0.Name=Measures.TotalOrdersCount)newRow.TotalOrders=TypeParse.ToInt32(cellSetj,i.FormattedValue);elseif(cellSet.Axes0.Positionsj.Members0.Name=Measures.TotalAmountCount)newRow.TotalAmount=TypeParse.ToInt32(cellSetj,i.FormattedValue);elseif(cellSet.Axes0.Positionsj.Members0.Name=Measures.TotalMoneyCount)newRow.TotalMoney=TypeParse.ToDecimal(cellSetj,i.FormattedValue);elseif(cellSet.Axes0.Positionsj.Members0.Name=Measures.UnPaidCancelAmountCount)newRow.UnPaidCancelAmount=TypeParse.ToInt32(cellSetj,i.FormattedValue);elseif(cellSet.Axes0.Positionsj.Members0.Name=Measures.UnPaidCancelMoneyCount)newRow.UnPaidCancelMoney=TypeParse.ToDecimal(cellSetj,i.FormattedValue);elseif(cellSet.Axes0.Positionsj.Members0.Name=Measures.PaidCancelAmountCount)newRow.PaidCancelAmount=TypeParse.ToInt32(cellSetj,i.FormattedValue);elseif(cellSet.Axes0.Positionsj.Members0.Name=Measures.PaidCancelMoneyCount)newRow.PaidCancelMoney=TypeParse.ToDecimal(cellSetj,i.FormattedValue);model.FactGameTradeData.AddFactGameTradeDataRow(newRow);好了，目前为止，我们已经从多维数据库里面返回我们需要统计的数据了，爱怎么展现就怎么展现吧。数据列表，柱状图，曲线图，饼状图等等。在以后的章节中我会介绍一款开源的非常成熟的图形控件ZedGraph,我做展现的时候，有部分就是用的这个家伙，修改了它的一些代码，感觉非常实用。一步一部学习SQLSERVER BI-ANALYSISSERVICE服务器配置Sqlserver2005 中的analysis services支持两种数据库连接方式(1)windows验证方式（形如Data Source=limj;Catalog=Analysis Services Tutorial;ConnectTo=9.0;Integrated Security=SSPI）；(2)http验证方式(形如Provider=MSOLAP;User ID=administrator;Password=119;Persist Security Info=True;InitialCatalog=DataCenterDW;Data Source=http:/limj/olap/msmdpump.dll)由于公司软件架构的需要，我这里选择后面那种方式。下面我就说明一下这种方式在window2003 server上的配置过程。(以下文章来源于微软MSDN)概述Microsoft SQL Server 2005 Analysis Services 与 SQL Server 2000 Analysis Services 使用相同的体系结构提供 HTTP 访问。泵组件被加载到 IIS（Internet 信息服务）并作为 ISAPI 扩展，在客户端和 Analysis Services 服务器间传送数据。本白皮书介绍使用 Microsoft Windows Server 2003 SP1 设置对 Analysis Services 的 HTTP 访问的全过程。图 1 给出了组件体系结构的概览。查看实际尺寸图像返回页首获取二进制数据