经常看到一部分朋友在网上询问交叉表(行列置换)解决方案,一般习惯利用动态SQL或者是Case When 技巧处理,比较复杂的行列置换,用的动态SQL也比较复杂难于理解. 然而多维数据集能非常简单的处理复杂的交叉表和统计数据. 以下举一个项目管理销售的例子,来看用多维数据处理交叉表。 具备以下条件: A、数据仓库(维度表信息、事实表信息) B、数据库关系图 C、OLAP模型设计 D、利用AnalyZer工具做多维数据展现。 1、数据仓库建立和普通数据库差不多,以下以“ProjectManageDW"命名建立数据仓库,相关表结构如下: 其中维度包括:城市、时间、天气、项目。指标包括:产品的销售价格。(维度和主题也是业务系统的分析主题)
--
维度表(城市信息)
CREATE
TABLE
DimCity(CityId
INT
, CityName
VARCHAR
(
200
))
GO
INSERT
INTO
DimCity(CityID,CityName)
SELECT
1
,
'
北京
'
UNION
ALL
SELECT
2
,
'
上海
'
UNION
ALL
SELECT
3
,
'
鞍山
'
UNION
ALL
SELECT
4
,
'
常德
'
UNION
ALL
SELECT
5
,
'
武汉
'
UNION
ALL
SELECT
6
,
'
重庆
'
UNION
ALL
SELECT
7
,
'
天津
'
UNION
ALL
select
8
,
'
蚌埠
'
UNION
ALL
select
9
,
'
深圳
'
UNION
ALL
select
10
,
'
武昌
'
UNION
ALL
SELECT
11
,
'
长沙
'
GO
--
维度表(项目信息)
CREATE
TABLE
DimProject(ProjectID
int
,ProjectName
varchar
(
20
))
GO
INSERT
INTO
DimProject(ProjectID,ProjectName)
SELECT
1
,
'
防汛抗旱指挥系统
'
UNION
ALL
SELECT
2
,
'
水质分析评价系统
'
UNION
ALL
SELECT
3
,
'
股票预测系统
'
UNION
ALL
SELECT
4
,
'
二维码集成系统
'
UNION
ALL
SELECT
5
,
'
雨水情GIS查询系统
'
UNION
ALL
SELECT
6
,
'
BI多维数据分析系统
'
UNION
ALL
SELECT
7
,
'
办公OA系统
'
UNION
ALL
SELECT
8
,
'
公安部网站
'
UNION
ALL
SELECT
9
,
'
爬虫软件
'
UNION
ALL
SELECT
10
,
'
鞍山诚信评价系统
'
GO
--
维度表(销售时间)
CREATE
TABLE
DimTime(TimeID
INT
,Date
DATETIME
)
GO
INSERT
INTO
DimTime(TimeID,Date)
select
1
,
'
2003-07-07 00:00:00.000
'
union
all
select
2
,
'
2003-10-20 00:00:00.000
'
union
all
select
3
,
'
2003-12-04 00:00:00.000
'
union
all
select
4
,
'
2004-01-12 00:00:00.000
'
union
all
select
5
,
'
2004-03-10 00:00:00.000
'
union
all
select
6
,
'
2004-06-12 00:00:00.000
'
union
all
select
7
,
'
2004-09-10 00:00:00.000
'
union
all
select
8
,
'
2004-11-02 00:00:00.000
'
union
all
select
9
,
'
2005-06-13 00:00:00.000
'
union
all
select
10
,
'
2005-12-05 00:00:00.000
'
union
all
select
11
,
'
2006-09-02 00:00:00.000
'
union
all
select
12
,
'
2007-01-14 00:00:00.000
'
union
all
select
13
,
'
2007-12-03 00:00:00.000
'
union
all
select
14
,
'
2008-03-21 00:00:00.000
'
GO
--
维度表(天气信息)
CREATE
TABLE
DimWeather(WeatherID
int
, WeatherDetail
VARCHAR
(
200
))
GO
INSERT
INTO
DimWeather(WeatherID,WeatherDetail)
SELECT
1
,
'
晴
'
UNION
ALL
SELECT
2
,
'
阴天
'
UNION
ALL
SELECT
3
,
'
大风
'
UNION
ALL
SELECT
4
,
'
小雪
'
UNION
ALL
SELECT
5
,
'
沙尘暴
'
UNION
ALL
SELECT
6
,
'
多云转晴
'
UNION
ALL
SELECT
7
,
'
晴
'
UNION
ALL
SELECT
8
,
'
晴转多云
'
UNION
ALL
SELECT
9
,
'
雾有风
'
UNION
ALL
SELECT
10
,
'
大雪转雨加雪
'
UNION
ALL
SELECT
11
,
'
多云转阴
'
UNION
ALL
SELECT
12
,
'
多云
'
UNION
ALL
SELECT
13
,
'
雾转多云
'
UNION
ALL
SELECT
14
,
'
多云风力三四级
'
--
事实表(销售事实数据)
CREATE
TABLE
FacSaleSoft(SaleID
int
,CityID
Int
,ProjectID
INT
,TimeID
INT
,WeatherID
INT
,SalePrice
float
)
GO
INSERT
INTO
FacSaleSoft (SaleID,CityID,ProjectID,TimeID,WeatherID,SalePrice)
SELECT
1
,
1
,
1
,
1
,
1
,
200000.00
UNION
ALL
SELECT
2
,
1
,
3
,
11
,
2
,
300000.00
UNION
ALL
SELECT
3
,
1
,
4
,
13
,
4
,
20000.00
UNION
ALL
SELECT
4
,
1
,
6
,
9
,
3
,
15000.00
UNION
ALL
SELECT
5
,
1
,
8
,
6
,
8
,
30000.00
UNION
ALL
SELECT
6
,
1
,
9
,
12
,
9
,
5000.00
UNION
ALL
SELECT
7
,
2
,
2
,
10
,
5
,
300000.00
UNION
ALL
SELECT
8
,
3
,
10
,
11
,
14
,
150000.00
UNION
ALL
SELECT
9
,
4
,
10
,
8
,
13
,
50000.00
UNION
ALL
SELECT
10
,
5
,
5
,
3
,
4
,
200000.00
UNION
ALL
SELECT
11
,
6
,
5
,
6
,
7
,
100000.00
UNION
ALL
SELECT
12
,
7
,
2
,
2
,
6
,
80000.00
UNION
ALL
SELECT
13
,
8
,
2
,
4
,
10
,
200000.00
2、数据库关系图建立 数据库关系图主要是指维度数据和指标数据之间的关系,由于建维度和指标关系时,必须提供表的主键,所以此时,把表的主键补充完整(表的主键建立比较简单,此时略) 如下图: 从此关系模式中,很容易看它的采用的是数据仓库中的“雪花模型”。
3、关于OLAP设计。 a、用VS.NET 建立一个Analysis Services 项目。命名为“ProjectManageOlap“ b、配置数据源。成功后,如下图: c、配置数据源视图,一般都选择默认的事实表、维度表即可。(注意:如果分析的业务系统比较复杂,可以在关系图组织中,根据销售主题分出多个关系图。) 如下图: d、建分析的维度、分析指标(度量值组)。注意:可以更改维度用法来改变维度和度量值组的关系、可以根据MDX计算方法,计算比较复杂的报表。这些用法在以后文章中介绍。 此时OLAP设计基本上完成。
3、AnalyZer多维数据展现。 a、按照城市名称、项目名称,统计产品销售价格报表如下: b、项目名称、天气情况,统计产品销售价格情况如下: c、按照城市、销售时间、销售产品,各产品销售价格统计如下图:
说明,AnalyZer工具提供报表计算、各种统计图、行列比计算、维度排序,等非常多的功能支持,有兴趣的朋友可以了解该工具。
总结: SQL Server 2005 中多维数据分析可以根据不同的主题,分析不同的数据结果。在做数据仓库项目中,首先必须分析需求设计的主题(包括维度、分析指标),其次就可以在OLAP中做多维数据分析。