一步一步学习SQL-Server-BI

一步一步学习SQLServerBI一步一步学习sqlserverBI--数据仓库设计因为项目还没有真正开始，但是接触BI已有半年多，手痒，这几天准备搞一个简化版本的BI项目。一方面给刚BI入门的朋友一个参考，另外一方面也为自己的将要开始的项目做个准备，让自己能够考虑到一些项目的细节。同时也希望对BI有研究的朋友提供一些建议和经验。因为我们的这个案例是采用微软的sqlserver2005的BI平台开发的，所以这里先贴一张WebCast里面截来的图，这张图主要反映了采用sqlserver2005的BI项目的架构。

好了，咱们开始吧。我说的这个项目需求很简单，因为是简化版本的么。这是一个游戏中使用到的物品的销售分析软件。里面包括几个概念，游戏，销售部门，物品，交易金额，交易笔数，发布单数等。我们要做的事情就是按游戏，按部门，按物品来实现对交易金额，笔数等的数据交叉分析。在我们这个系统里面，我们的数据颗粒度是天。好了，既然是简化版，我们也就不用那么罗嗦，什么需求分析，分析设计都省了吧，下面直接进入数据库设计。我们的数据库一共包括四张维度表（部门维度，游戏维度，物品维度，时间维度），一张事实表（游戏交易数据事实表）。

部门维度表

游戏维度表

物品维度表

时间维度表

交易数据事实表

由于我们的这个案例比较简单，所以维度与事实表之间的关系也比较简单，是一个简单的星型架构。

这一节我们就先写到这里，下一节我将会详细的写这个项目的ETL部分。一步一步学习sqlserverBI--ETL设计这节我们主要讲讲我的游戏交易数据分析项目的ETL（数据抽取、加载、转换）具体是怎么做的。

先来讲下源系统吧，因为我们的交易主站的服务器不是架在公司的，因此不能直接从源系统直接抽取数据了。事实上我们已经存在一个简单的数据分析系统了，只不过这个是以前人家做的，没有采用sqlserver2005的BI平台来做，而是直接写winform程序来弄的。原来的数据抽取是主站那边提供导出的excel文件过来，然后到我们这边，导入到我们的分析库中去。

好了，为了简便，事实上我只是在做一个demo，为以后项目做准备，所以我们抽取数据直接是从上面说的已经存在分析库中抽的，因为数据结构比较相近，所以ETL过程还是比较简单的。先看看游戏维度表吧：

首先，我们来新建一个IntegrationServices项目。

接着，新建一个ImportDimGamePackage.dtsx的SSIS包。

拖放一个数据流任务到控制流面板上（如图）

双击数据流任务，来到数据流面板

接着我们拖动OLEDB源到数据流面板上，并且双击编辑它，新建一个连接和选择要抽取数据的源数据表。

然后我们拖动一个OLEDB目标到数据流面板上，把OLEDB源和OLEDB目标连接起来，并且双击编辑，新建一个连接和选择我们要存放数据的目标表

然后建立源表与目标表之间的映射。

所有的都弄好了，运行，OK，成功了。

接着部门维度和物品维度都跟这个差不多。时间维度是我新建的，暂时我用winform写了一个日期维度生成器。下面就讲交易数据事实表，这个比维度稍微复杂一点点。因为源系统和目标系统数据结构很类似，所以我的ETL都是非常简单的，在实际项目中，能够有这么好的运气是不可能的，呵呵。首先新建一个ImportFactGameTradeDataPackage.dtsx的SSIS包。

其他简单的步骤跟上面一样，我就讲讲不一样的地方。因为我在数据库设计的时候，各个维度都用了代理键，也就是说在我们的数据仓库里面的维度和事实表的外键关联都是通过代理键的，源系统中的键我们只不过用一个字段记录了下来。所以我们数据抽取过来的时候，要对源系统中的映射关系进行改变。

这里的关键就是我们在编辑数据流的时候，使用了一个叫做“查找”的组件。编辑好的数据流如图：

接下来我们详细讲下查找列是怎么用的，拿查找GameKey来讲把。

1。双击查找GameKey组件，在引用表面板上面选择好我们要查找的表，在这里就是我们数据仓库表中的游戏维度表。

2。在列面板里面建立要查找的字段跟源系统事实表的字段的映射。

3。把查找到的列作为新列添加，并取一个唯一的别名（在后面与OLEDB目标的时候建立映射的时候，会用到这里查找到的列）。接着其他的几个维度代理键的查找都类似，OLEDB目标的字段映射跟维度表的导入类似。好了，今天主要介绍了简单的Sqlserver2005IntegrationServices的使用，今天涉及了到里面几个概念，数据流任务，OLEDB源，OLEDB目标，查找等。下一节，我准备写一下使用Sqlserver2005AnalysisServices建立OLAP数据库的过程。一步一步学习sqlserverBI--多维数据库建立接着我们上节的《一步一步学习sqlserverBI--ETL设计》，现在我们的游戏交易数据分析系统的数据仓库已经建好了，并且也已经有数据了，让我们开始我们的OLAP过程吧。在这一节中，我们主要详细的讲解使用Sqlserver2005AnalysisService来建立多维数据库的过程。

首先我么新建一个AnalysisServices项目，建好以后，我们将会在我们的解决方案资源管理器里面看到如下图所示的项目结构。

接着，我们新建数据源，然后根据向导一步一步点下去，完成后事实上建立了一个到数据仓库的连接串。

然后创建数据源视图，也几乎是一步一步按照向导点下去，事实上就是从数据源中选择我们需要的表到我们的数据源视图里面来。

好了，下面我们开始建立多维数据集，这也是我们今天这节的重点。

注意在这里选择时间维度表。

最后，我们把我们的多维数据库发布到我们的Sqlserver2005AnalysisService服务器中去。

右键点击项目属性，设置我们部署的目标服务器（如下图）。设置好以后，点击工具栏上的部署按钮，把多维数据库部署到我们的服务器中去。

部署完毕以后，我们就可以右键点击多维数据集进行浏览数据了。

因为过程比较简单，以上过程都没有怎么用文字了。到此为止，我们的多维数据库已经建立好了，当然，如果要应用于具体项目中的话，还要修改多维数据库的很多属性。一步一步学习sqlserverBI--应用开发(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([Measures].[Total

Orders]

)'

");

mdxBuilder.Append("

MEMBER

[Measures].[Total

Amount

Count]

AS

'SUM([Measures].[Total

Amount])'");

mdxBuilder.Append("

MEMBER

[Measures].[Total

Money

Count]

AS

'SUM([Measures].[Total

Money])'");

mdxBuilder.Append("

MEMBER

[Measures].[Un

Paid

Cancel

Amount

Count]

AS

'SUM([Measures].[Un

Paid

Cancel

Amount])'");

mdxBuilder.Append("

MEMBER

[Measures].[Un

Paid

Cancel

Money

Count]

AS

'SUM([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].[Total

Amount

Count],

[Measures].[Total

Money

Count],

[Measures].[Un

Paid

Cancel

Amount

Count],

[Measures].[Un

Paid

Cancel

Money

Count],

[Measures].[Paid

Cancel

Amount

Count],

[Measures].[Paid

Cancel

Money

Count]}

ON

COLUMNS,");

mdxBuilder.Append("

{[Department].[Dep

Code

Alternate

Key].Members}

ON

ROWS");

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([Measures].[Total

Orders]

)'

");

mdxBuilder.Append("

MEMBER

[Measures].[Total

Amount

Count]

AS

'SUM([Measures].[Total

Amount])'");

mdxBuilder.Append("

MEMBER

[Measures].[Total

Money

Count]

AS

'SUM([Measures].[Total

Money])'");

mdxBuilder.Append("

MEMBER

[Measures].[Un

Paid

Cancel

Amount

Count]

AS

'SUM([Measures].[Un

Paid

Cancel

Amount])'");

mdxBuilder.Append("

MEMBER

[Measures].[Un

Paid

Cancel

Money

Count]

AS

'SUM([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].[Total

Amount

Count],

[Measures].[Total

Money

Count],

[Measures].[Un

Paid

Cancel

Amount

Count],

[Measures].[Un

Paid

Cancel

Money

Count],

[Measures].[Paid

Cancel

Amount

Count],

[Measures].[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

+

"],[Department].[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

Orders

Count]")

{

newRow.TotalOrders

=

TypeParse.ToInt32(cellSet[j,

i].FormattedValue);

}

else

if

(cellSet.Axes[0].Positions[j].Members[0].Name

==

"[Measures].[Total

Amount

Count]")

{

newRow.TotalAmount

=

TypeParse.ToInt32(cellSet[j,

i].FormattedValue);

}

else

if

(cellSet.Axes[0].Positions[j].Members[0].Name

==

"[Measures].[Total

Money

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].FormattedValue);

}

else

if

(cellSet.Axes[0].Positions[j].Members[0].Name

==

"[Measures].[Un

Paid

Cancel

Money

Count]")

{

newRow.UnPaidCancelMoney

=

TypeParse.ToDecimal(cellSet[j,

i].FormattedValue);

}

else

if

(cellSet.Axes[0].Positions[j].Members[0].Name

==

"[Measures].[Paid

Cancel

Amount

Count]")

{

newRow.PaidCancelAmount

=

TypeParse.ToInt32(cellSet[j,

i].FormattedValue);

}

else

if

(cellSet.Axes[0].Positions[j].Members[0].Name

==

"[Measures].[Paid

Cancel

Money

Count]")

{

newRow.PaidCancelMoney

=

TypeParse.ToDecimal(cellSet[j,

i].FormattedValue);

}

}

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,string

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

Amount

Count]")

{

newRow.TotalAmount

=

TypeParse.ToInt32(cellSet[j,

i].FormattedValue);

}

else

if

(cellSet.Axes[0].Positions[j].Members[0].Name

==

"[Measures].[Total

Money

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].FormattedValue);

}

else

if

(cellSet.Axes[0].Positions[j].Members[0].Name

==

"[Measures].[Un

Paid

Cancel

Money

Count]")

{

newRow.UnPaidCancelMoney

=

TypeParse.ToDecimal(cellSet[j,

i].FormattedValue);

}

else

if

(cellSet.Axes[0].Positions[j].Members[0].Name

==

"[Measures].[Paid

Cancel

Amount

Count]")

{

newRow.PaidCancelAmount

=

TypeParse.ToInt32(cellSet[j,

i].FormattedValue);

}

else

if

(cellSet.Axes[0].Positions[j].Members[0].Name

==

"[Measures].[Paid

Cancel

Money

Count]")

{

newRow.PaidCancelMoney

=

TypeParse.ToDecimal(cellSet[j,

i].FormattedValue);

}

}

model.FactGameTradeData.AddFactGameTradeDataRow(newRow);

}

}

好了，目前为止，我们已经从多维数据库里面返回我们需要统计的数据了，爱怎么展现就怎么展现吧。数据列表，柱状图，曲线图，饼状图等等。在以后的章节中我会介绍一款开源的非常成熟的图形控件ZedGraph,我做展现的时候，有部分就是用的这个家伙，修改了它的一些代码，感觉非常实用。一步一部学习SQLSERVERBI--ANALYSISSERVICE服务器配置Sqlserver2005中的analysisservices支持两种数据库连接方式

(1)windows验证方式（形如"DataSource=limj;Catalog=AnalysisServicesTutorial;ConnectTo=9.0;IntegratedSecurity=SSPI"）；

(2)http验证方式(形如"Provider=MSOLAP;UserID=administrator;Password=119;PersistSecurityInfo=True;Initial

Catalog=DataCenterDW;DataSource=http://limj/olap/msmdpump.dll")

由于公司软件架构的需要，我这里选择后面那种方式。下面我就说明一下这种方式在window2003server上的配置过程。(以下文章来源于微软MSDN)

概述Microsoft®SQLServer™2005AnalysisServices与SQLServer2000AnalysisServices使用相同的体系结构提供HTTP访问。泵组件被加载到IIS（Internet信息服务）并作为ISAPI扩展，在客户端和AnalysisServices服务器间传送数据。本白皮书介绍使用MicrosoftWindowsServer™2003SP1设置对AnalysisServices的HTTP访问的全过程。图1给出了组件体系结构的概览。

查看实际尺寸图像返回页首获取二进制数据获取二进制数据：将%Installationfolder%\OLAP\bin\isapi目录的内容复制到要用作IIS中虚拟目录基础的文件夹。本例中，我们将把C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\OLAP\bin\isapi文件夹中的所有文件复制到C:\inetpub\wwwroot\olap目录中。注意：

为使用全部安全设置，请确保作为虚拟目录基础的文件夹位于格式化为NTFS文件系统的驱动器上，这一点非常重要。由于IIS的限制，指向目录的路径不能包含空格。如果要在不同于AnalysisServices服务器的其他服务器上运行HTTP泵，请确保同时为AnalysisRedistributable数据包安装OLEDB。返回页首创建应用程序库创建应用程序库：1.要打开“计算机管理”控制台，请依次打开“控制面板”、“管理工具”及“计算机管理”。（或者，右键单击“我的电脑”图标，然后在快捷菜单上选择管理。）2.在“计算机管理”控制台中，依次展开“服务和应用程序”节点和“Internet信息服务”节点。如果无法找到“Internet信息服务”节点，可能是因为您的计算机上尚未安装IIS。如需安装，请在“控制面板”中打开“添加或删除程序”。选择添加／删除Windows组件。然后将IIS添加到Windows中。3.右键单击“应用程序池”打开快捷菜单，选择“新建”，然后再选择应用程序池。4.指定应用程序池的名称。本例中，我们将它命名为OLAP。（参见图2。）图2

查看实际尺寸图像返回页首创建虚拟目录创建虚拟目录：1.要打开“计算机管理”控制台，请依次打开“控制面板”、“管理工具”及“计算机管理”。（或者，右键单击“我的电脑”图标，然后在快捷菜单上选择管理。）2.在“计算机管理”控制台中，依次展开“服务和应用程序”节点和“Internet信息服务”节点。如果无法找到“Internet信息服务”节点，可能是因为您的计算机上尚未安装IIS。如需安装，请在“控制面板”中打开“添加或删除程序”。选择添加／删除Windows组件。然后将IIS添加到Windows中。3.右键单击“网站”打开快捷菜单，选择新建，然后选择虚拟目录。4.指定虚拟目录的名称。本例中，我们将它命名为OLAP。“内容目录”应指向您刚才创建的文件夹。本例中指向C:\inetpub\wwroot\olap。5.请确保在访问权限下只选中第二个复选框运行脚本（如ASP）。（参见图3。）图3

查看实际尺寸图像返回页首设置虚拟目录属性设置虚拟目录属性：1.右键单击虚拟目录节点，然后从菜单中选择属性。您会看到如图4所示的屏幕。图4

查看实际尺寸图像需要修改的属性已用红线圈出。不需修改但对安全和性能至关重要的属性用绿线圈出。2.选择您刚才创建的应用程序池。3.单击配置按钮，您会看到如图5所示的屏幕。图5

查看实际尺寸图像4.单击添加按钮。5.在可执行选项中，输入msmdpump.dll的完整路径名。在本例中为：C:\wwroot\olap\msmdpump.dll6.在扩展文本框中输入

.dll。您在屏幕上将看到如图6所示的对话框。图6

查看实际尺寸图像7.单击确定按钮，接受设置。返回页首选择安全设置选择安全设置：1.选择目录安全性选项卡，然后在身份验证和访问控制上单击编辑。您将看到如图

7所示的对话框。图7

查看实际尺寸图像2.您会看到三个选项。选择其中一个。本部分对这些选项进行简要说明，指出每个选项的优点、缺点和安全性问题。匿名访问选择此模式时，泵(msmdpump.dll)运行时会带有凭据；本例中这些凭据是IUSR_MACHINENAME用户的凭据。因此，每个与AnalysisServices的连接都是以IUSR_MACHINENAME用户的身份打开的。选择此模式时，用户是连接到IIS还是连接到AnalysisServices