一步一步学习ETL

一步一步学习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组装成我们前台需要的数据集格式/// <summary>/// 按天统计各个部门的交易数据/// </summary>/// <param name="tradeDateKey">日期的键值</param>/// <returns></returns>public CellSet Count(int tradeDateKey){StringBuilder mdxBuilder = new StringBuilder();mdxBuilder.Append("WITH MEMBER [Measures].[Total Orders Count] AS 'SUM([M easures].[Total Orders] )' ");mdxBuilder.Append(" MEMBER [Measures].[Total Amount Count] AS 'SUM([Measur es].[Total Amount])'");mdxBuilder.Append(" MEMBER [Measures].[Total Money Count] AS 'SUM([Measures].[Total Money])'");mdxBuilder.Append(" MEMBER [Measures].[Un Paid Cancel Amount Count] AS 'SU M([Measures].[Un Paid Cancel Amount])'");mdxBuilder.Append(" MEMBER [Measures].[Un Paid Cancel Money Count] AS 'SU M([Measures].[Un Paid Cancel Money])'");mdxBuilder.Append(" MEMBER [Measures].[Paid Cancel Amount Count] AS 'SUM ([Measures].[Paid Cancel Amount])'");mdxBuilder.Append(" MEMBER [Measures].[Paid Cancel Money Count] AS'SUM([M easures].[Paid Cancel Money])'");mdxBuilder.Append(" SELECT { [Measures].[Total Orders Count], [Measures].[Tota l Amount Count], [Measures].[Total Money Count], [Measures].[Un Paid Cancel Amount Coun t], [Measures].[Un Paid Cancel Money Count], [Measures].[Paid Cancel Amount Count], [Mea sures].[Paid Cancel Money Count]} ON COLUMNS,");mdxBuilder.Append(" {[Department].[Dep Code Alternate Key].Members} ON ROW S");mdxBuilder.Append(" FROM [Data Center DW]");mdxBuilder.Append(" WHERE ([Time].[TimeKey].["+tradeDateKey+"])");return DBServer.AnalysisServer.ExecuteCellset(mdxBuilder.ToString());}/// <summary>/// 按天统计各个游戏单个部门的交易数据/// </summary>/// <param name="tradeDateKey">日期的键值</param>/// <returns></returns>public CellSet Count(int tradeDateKey,int departmentKey){StringBuilder mdxBuilder = new StringBuilder();mdxBuilder.Append("WITH MEMBER [Measures].[Total Orders Count] AS 'SUM([M easures].[Total Orders] )' ");mdxBuilder.Append(" MEMBER [Measures].[Total Amount Count] AS 'SUM([Measur es].[Total Amount])'");mdxBuilder.Append(" MEMBER [Measures].[Total Money Count] AS 'SUM([Measur es].[Total Money])'");mdxBuilder.Append(" MEMBER [Measures].[Un Paid Cancel Amount Count] AS 'SU M([Measures].[Un Paid Cancel Amount])'");mdxBuilder.Append(" MEMBER [Measures].[Un Paid Cancel Money Count] AS 'SU M([Measures].[Un Paid Cancel Money])'");mdxBuilder.Append(" MEMBER [Measures].[Paid Cancel Amount Count] AS 'SUM ([Measures].[Paid Cancel Amount])'");mdxBuilder.Append(" MEMBER [Measures].[Paid Cancel Money Count] AS'SUM([Measures].[Paid Cancel Money])'");mdxBuilder.Append(" SELECT { [Measures].[Total Orders Count], [Measures].[Tota l Amount Count], [Measures].[Total Money Count], [Measures].[Un Paid Cancel Amount Coun t], [Measures].[Un Paid Cancel Money Count], [Measures].[Paid Cancel Amount Count], [Mea sures].[Paid Cancel Money Count]} ON COLUMNS,");mdxBuilder.Append(" {[Game].[Game Code Alternate Key].Members} ON ROWS");mdxBuilder.Append(" FROM [Data Center DW]");mdxBuilder.Append(" WHERE ([Time].[TimeKey].[" + tradeDateKey + "],[Departmen t].[Dim Department].["+departmentKey.ToString()+"])");return DBServer.AnalysisServer.ExecuteCellset(mdxBuilder.ToString());}/// <summary>/// 填充按部门统计的数据/// </summary>/// <param name="model"></param>/// <param name="timeKey"></param>public void Fill(DepartmentTotalModel model, DateTime time, int timeKey){CellSet cellSet = new DepartmentTotalDac().Count(timeKey);DepartmentTotalModel.FactGameTradeDataRow newRow;for (int i = 1; i < cellSet.Axes[1].Positions.Count; i++){newRow = model.FactGameTradeData.NewFactGameTradeDataRow();newRow.Department = cellSet.Axes[1].Positions[i].Members[0].Caption;newRow.TradeDate = time;for (int j = 0; j < cellSet.Axes[0].Positions.Count; j++){if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Total Order s Count]"){newRow.TotalOrders = TypeParse.ToInt32(cellSet[j, i].FormattedValue);}else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Total Am ount Count]"){newRow.TotalAmount = TypeParse.ToInt32(cellSet[j, i].FormattedValue);}else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Total Mo ney Count]"){newRow.TotalMoney = TypeParse.ToDecimal(cellSet[j, i].FormattedValue);}else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Un Paid Cancel Amount Count]"){newRow.UnPaidCancelAmount = TypeParse.ToInt32(cellSet[j, i].FormattedV alue);}else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Un Paid Cancel Money Count]"){newRow.UnPaidCancelMoney = TypeParse.ToDecimal(cellSet[j, i].Formatte dValue);}else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Paid Can cel Amount Count]"){newRow.PaidCancelAmount = TypeParse.ToInt32(cellSet[j, i].FormattedVal ue);}else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Paid Can cel Money Count]"){newRow.PaidCancelMoney = TypeParse.ToDecimal(cellSet[j, i].FormattedV alue);}}model.FactGameTradeData.AddFactGameTradeDataRow(newRow);}}/// <summary>/// 填充按游戏和部门交叉统计的数据/// </summary>/// <param name="model"></param>/// <param name="timeKey"></param>/// <param name="depKey"></param>public void Fill(GameByDepartmentTotalModel model,DateTime time,int timeKey,strin g dep,int depKey){CellSet cellSet = new GameByDepartmentTotalDac().Count(timeKey, depKey);GameByDepartmentTotalModel.FactGameTradeDataRow newRow;for (int i = 1; i < cellSet.Axes[1].Positions.Count; i++){newRow = model.FactGameTradeData.NewFactGameTradeDataRow();newRow.Game = cellSet.Axes[1].Positions[i].Members[0].Caption;newRow.Department = dep;newRow.TradeDate = time;for (int j = 0; j < cellSet.Axes[0].Positions.Count; j++){if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Total Orders Count]"){newRow.TotalOrders = TypeParse.ToInt32(cellSet[j, i].FormattedValue);}else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Total Am ount Count]"){newRow.TotalAmount = TypeParse.ToInt32(cellSet[j, i].FormattedValue);}else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Total Mo ney Count]"){newRow.TotalMoney = TypeParse.ToDecimal(cellSet[j, i].FormattedValue);}else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Un Paid Cancel Amount Count]"){newRow.UnPaidCancelAmount = TypeParse.ToInt32(cellSet[j, i].FormattedV alue);}else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Un Paid Cancel Money Count]"){newRow.UnPaidCancelMoney = TypeParse.ToDecimal(cellSet[j, i].Formatte dValue);}else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Paid Can cel Amount Count]"){newRow.PaidCancelAmount = TypeParse.ToInt32(cellSet[j, i].FormattedVal ue);}else if (cellSet.Axes[0].Positions[j].Members[0].Name == "[Measures].[Paid Can cel Money Count]"){newRow.PaidCancelMoney = TypeParse.ToDecimal(cellSet[j, i].FormattedV alue);}}model.FactGameTradeData.AddFactGameTradeDataRow(newRow);}}好了，目前为止，我们已经从多维数据库里面返回我们需要统计的数据了，爱怎么展现就怎么展现吧。