经常看到一部分朋友在网上询问交叉表(行列置换)解决方案,一般习惯利用动态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中做多维数据分析。