基于RDBMS的BI设计

================================

都说BI，什么OLAP，什么ROLAP,MOLAP，但是如何基于RDBMS实现，基本都不怎么说。

怎么做的：

　　1.通过多维分析模型，存储元数据

　　2.动态建立主题临时表

　　3.依据多维模型的元数据，组装SQL，创建临时表，插入数据到临时表

================================

程序使用SQL都是针对Select，Update，Delete

但是很少用create table，drop table，其实在数据库分析中，就是这种场景

数据库原始视图在开发中的使用....,场景也有，比如查看表已经表的主外键

================================

生成的临时表结构--本质还是对这个临时表的理解个人感觉

DIM_*:这些就是维度

DIM_*_DATA:就是具体的度量值或者指标

这个表是根据用户的多维分析的模型和用户自定义的表格有关，他会自动生成。

查询语句：

SELECT DIM_27_CODE AS DIM_27_CODE,
MAX(DIM_27_DISPLAY) AS DIM_27_DISPLAY,
DIM_29_CODE AS DIM_29_CODE,
MAX(DIM_29_DISPLAY) AS DIM_29_DISPLAY,
DIM_DATE_CODE AS DIM_DATE_LAST_CODE,
SUM(DIM_95_DATA) AS DIM_95_DATA
FROM vhbiyf.rep_temp_report_47
WHERE DIM_DATE_CODE IN ('201409')
AND DIM_DATE_LEVEL = 'YEAR_MONTH'
AND DIM_21_CODE IN ('1000')
AND DIM_27_CODE IN ('1', '2', '3', '1', '2', '3')
GROUP BY
DIM_27_CODE,
DIM_29_CODE,
DIM_DATE_CODE
ORDER BY
DIM_27_CODE ASC,
DIM_29_CODE ASC

 

页面请求：

创建临时表的SQL：

CREATE TABLE temp_DIM_95
(
DIM_95_fint_year VARCHAR(4000),
DIM_95_fint_month VARCHAR(4000),
DIM_95_a_id VARCHAR(4000),
DIM_95_p_id VARCHAR(4000),
DIM_95_comp_code VARCHAR(4000),
DIM_95_num NUMBER(18, 6)
);
CREATE TABLE temp_DIM_27
(
DIM_95_fint_year VARCHAR(4000),
DIM_95_fint_month VARCHAR(4000),
DIM_95_a_id VARCHAR(4000),
DIM_95_p_id VARCHAR(4000),
DIM_95_comp_code VARCHAR(4000),
DIM_27_name VARCHAR(4000),
DIM_27_id VARCHAR(4000),
DIM_95_num NUMBER(18, 6)
);
CREATE TABLE temp_DIM_29
(
DIM_95_fint_year VARCHAR(4000),
DIM_95_fint_month VARCHAR(4000),
DIM_95_a_id VARCHAR(4000),
DIM_95_p_id VARCHAR(4000),
DIM_95_comp_code VARCHAR(4000),
DIM_27_name VARCHAR(4000),
DIM_27_id VARCHAR(4000),
DIM_29_name VARCHAR(4000),
DIM_29_id VARCHAR(4000),
DIM_95_num NUMBER(18, 6)
);
CREATE TABLE temp_DIM_21
(
DIM_95_fint_year VARCHAR(4000),
DIM_95_fint_month VARCHAR(4000),
DIM_95_a_id VARCHAR(4000),
DIM_95_p_id VARCHAR(4000),
DIM_95_comp_code VARCHAR(4000),
DIM_27_name VARCHAR(4000),
DIM_27_id VARCHAR(4000),
DIM_29_name VARCHAR(4000),
DIM_29_id VARCHAR(4000),
DIM_21_comp_name VARCHAR(4000),
DIM_21_comp_code VARCHAR(4000),
DIM_95_num NUMBER(18, 6)
);
INSERT INTO temp_DIM_95
SELECT DIM_95.fint_year AS DIM_95_fint_year,
DIM_95.fint_month AS DIM_95_fint_month,
DIM_95.a_id AS DIM_95_a_id,
DIM_95.p_id AS DIM_95_p_id,
DIM_95.comp_code AS DIM_95_comp_code,
SUM(DIM_95.num) AS DIM_95_num
FROM (
SELECT YYB_ORDER.*
FROM YYB_ORDER YYB_ORDER
) DIM_95
WHERE DIM_95.fint_year = '2014'
AND DIM_95.fint_month = '09'
GROUP BY
DIM_95.fint_year,
DIM_95.fint_month,
DIM_95.a_id,
DIM_95.p_id,
DIM_95.comp_code;
INSERT INTO temp_DIM_27
SELECT DIM_95.DIM_95_fint_year AS DIM_95_fint_year,
DIM_95.DIM_95_fint_month AS DIM_95_fint_month,
DIM_95.DIM_95_a_id AS DIM_95_a_id,
DIM_95.DIM_95_p_id AS DIM_95_p_id,
DIM_95.DIM_95_comp_code AS DIM_95_comp_code,
DIM_27.name AS DIM_27_name,
DIM_27.id AS DIM_27_id,
SUM(DIM_95.DIM_95_num) AS DIM_95_num
FROM temp_DIM_95 DIM_95
INNER JOIN (
SELECT YYB_AREA.*
FROM YYB_AREA YYB_AREA
) DIM_27
ON (1 = 1 AND DIM_27.id = DIM_95.DIM_95_a_id)
GROUP BY
DIM_95.DIM_95_fint_year,
DIM_95.DIM_95_fint_month,
DIM_95.DIM_95_a_id,
DIM_95.DIM_95_p_id,
DIM_95.DIM_95_comp_code,
DIM_27.name,
DIM_27.id;
INSERT INTO temp_DIM_29
SELECT DIM_27.DIM_95_fint_year AS DIM_95_fint_year,
DIM_27.DIM_95_fint_month AS DIM_95_fint_month,
DIM_27.DIM_95_a_id AS DIM_95_a_id,
DIM_27.DIM_95_p_id AS DIM_95_p_id,
DIM_27.DIM_95_comp_code AS DIM_95_comp_code,
DIM_27.DIM_27_name AS DIM_27_name,
DIM_27.DIM_27_id AS DIM_27_id,
DIM_29.name AS DIM_29_name,
DIM_29.id AS DIM_29_id,
SUM(DIM_27.DIM_95_num) AS DIM_95_num
FROM temp_DIM_27 DIM_27
INNER JOIN (
SELECT YYB_PRO.*
FROM YYB_PRO YYB_PRO
) DIM_29
ON (1 = 1 AND DIM_29.id = DIM_27.DIM_95_p_id)
GROUP BY
DIM_27.DIM_95_fint_year,
DIM_27.DIM_95_fint_month,
DIM_27.DIM_95_a_id,
DIM_27.DIM_95_p_id,
DIM_27.DIM_95_comp_code,
DIM_27.DIM_27_name,
DIM_27.DIM_27_id,
DIM_29.name,
DIM_29.id;
INSERT INTO temp_DIM_21
SELECT DIM_29.DIM_95_fint_year AS DIM_95_fint_year,
DIM_29.DIM_95_fint_month AS DIM_95_fint_month,
DIM_29.DIM_95_a_id AS DIM_95_a_id,
DIM_29.DIM_95_p_id AS DIM_95_p_id,
DIM_29.DIM_95_comp_code AS DIM_95_comp_code,
DIM_29.DIM_27_name AS DIM_27_name,
DIM_29.DIM_27_id AS DIM_27_id,
DIM_29.DIM_29_name AS DIM_29_name,
DIM_29.DIM_29_id AS DIM_29_id,
DIM_21.comp_name AS DIM_21_comp_name,
DIM_21.comp_code AS DIM_21_comp_code,
SUM(DIM_29.DIM_95_num) AS DIM_95_num
FROM temp_DIM_29 DIM_29
INNER JOIN (
SELECT SYS_COMPANY.*
FROM SYS_COMPANY SYS_COMPANY
) DIM_21
ON (1 = 1 AND DIM_21.comp_code = DIM_29.DIM_95_comp_code)
WHERE DIM_21.comp_code IN ('1000')
GROUP BY
DIM_29.DIM_95_fint_year,
DIM_29.DIM_95_fint_month,
DIM_29.DIM_95_a_id,
DIM_29.DIM_95_p_id,
DIM_29.DIM_95_comp_code,
DIM_29.DIM_27_name,
DIM_29.DIM_27_id,
DIM_29.DIM_29_name,
DIM_29.DIM_29_id,
DIM_21.comp_name,
DIM_21.comp_code;
SELECT DIM_21.DIM_27_id AS DIM_27_CODE,
MAX(DIM_21.DIM_27_name) AS DIM_27_DISPLAY,
DIM_21.DIM_29_id AS DIM_29_CODE,
MAX(DIM_21.DIM_29_name) AS DIM_29_DISPLAY,
'201409' AS DIM_DATE_CODE,
'YEAR_MONTH' AS DIM_DATE_LEVEL,
DIM_21.DIM_21_comp_code AS DIM_21_CODE,
MAX(DIM_21.DIM_21_comp_name) AS DIM_21_DISPLAY,
SUM(DIM_21.DIM_95_num) AS DIM_95_DATA
FROM temp_DIM_21 DIM_21
GROUP BY
DIM_21.DIM_27_id,
DIM_21.DIM_29_id,
DIM_21.DIM_21_comp_code
ORDER BY
DIM_21.DIM_27_id ASC,
DIM_21.DIM_29_id ASC,
DIM_21.DIM_21_comp_code ASC;DROP TABLE temp_DIM_95;DROP TABLE temp_DIM_27;
DROP TABLE temp_DIM_29;DROP TABLE temp_DIM_21;

 

 

====================================================================================

--创建表缓存汇总的数据
CREATE TABLE temp_4690DIM_2417
(
DIM_2417_fint_year VARCHAR(4000),--年度
DIM_2417_fint_month VARCHAR(4000),--月份
DIM_2417_profit_state VARCHAR(4000),--收益===
DIM_2417_comp_code VARCHAR(4000),--单位
DIM_2417_disease_code_vh VARCHAR(4000) --病种编码
) ;

CREATE TABLE temp_4690DIM_2408
(
DIM_2417_fint_year VARCHAR(4000),
DIM_2417_fint_month VARCHAR(4000),
DIM_2417_profit_state VARCHAR(4000),
DIM_2417_comp_code VARCHAR(4000),
DIM_2408_name VARCHAR(4000),--收益名称
DIM_2408_code VARCHAR(4000),--收益名称
DIM_2417_disease_code_vh VARCHAR(4000)
) ;

CREATE TABLE temp_4690DIM_21
(
DIM_2417_fint_year VARCHAR(4000),
DIM_2417_fint_month VARCHAR(4000),
DIM_2417_profit_state VARCHAR(4000),
DIM_2417_comp_code VARCHAR(4000),
DIM_2408_name VARCHAR(4000),
DIM_2408_code VARCHAR(4000),
DIM_21_comp_name VARCHAR(4000),--单位名称
DIM_21_comp_code VARCHAR(4000),--单位编码
DIM_2417_disease_code_vh VARCHAR(4000)
) ;

INSERT INTO temp_4690DIM_2417
SELECT
DIM_2417.fint_year AS DIM_2417_fint_year,--年度
DIM_2417.fint_month AS DIM_2417_fint_month,--月份
DIM_2417.profit_state AS DIM_2417_profit_state,--盈亏
DIM_2417.comp_code AS DIM_2417_comp_code,--单位
DIM_2417.disease_code AS DIM_2417_disease_code_vh --病例编码
FROM
(
SELECT
VIEW_DISE_QYBZSY.*
FROM
VIEW_DISE_QYBZSY VIEW_DISE_QYBZSY --原表
) DIM_2417
WHERE
DIM_2417.fint_year = '2013' --缓存期间唯一的单位
AND DIM_2417.fint_month = '12'
GROUP BY
DIM_2417.fint_year,--年度
DIM_2417.fint_month,--月份
DIM_2417.profit_state,--盈亏
DIM_2417.comp_code,--单位
DIM_2417.disease_code;--病种编码

INSERT INTO temp_4690DIM_2408
SELECT
DIM_2417.DIM_2417_fint_year AS DIM_2417_fint_year,
DIM_2417.DIM_2417_fint_month AS DIM_2417_fint_month,
DIM_2417.DIM_2417_profit_state AS DIM_2417_profit_state,
DIM_2417.DIM_2417_comp_code AS DIM_2417_comp_code,
DIM_2408.name AS DIM_2408_name,
DIM_2408.code AS DIM_2408_code,
DIM_2417.DIM_2417_disease_code_vh AS DIM_2417_disease_code_vh
FROM
temp_4690DIM_2417 DIM_2417
INNER JOIN (
SELECT
VIEW_DISE_PROFIT_TYPE.*
FROM
VIEW_DISE_PROFIT_TYPE VIEW_DISE_PROFIT_TYPE --盈亏类型表
) DIM_2408
ON (1 = 1 AND DIM_2408.code = DIM_2417.DIM_2417_profit_state) --维度之间的关联关系
GROUP BY
DIM_2417.DIM_2417_fint_year,
DIM_2417.DIM_2417_fint_month,
DIM_2417.DIM_2417_profit_state,
DIM_2417.DIM_2417_comp_code,
DIM_2408.name,
DIM_2408.code,
DIM_2417.DIM_2417_disease_code_vh;

INSERT INTO temp_4690DIM_21
SELECT
DIM_2408.DIM_2417_fint_year AS DIM_2417_fint_year,
DIM_2408.DIM_2417_fint_month AS DIM_2417_fint_month,
DIM_2408.DIM_2417_profit_state AS DIM_2417_profit_state,
DIM_2408.DIM_2417_comp_code AS DIM_2417_comp_code,
DIM_2408.DIM_2408_name AS DIM_2408_name,
DIM_2408.DIM_2408_code AS DIM_2408_code,
DIM_21.comp_name AS DIM_21_comp_name,
DIM_21.comp_code AS DIM_21_comp_code,
DIM_2408.DIM_2417_disease_code_vh AS DIM_2417_disease_code_vh
FROM
temp_4690DIM_2408 DIM_2408
INNER JOIN (
SELECT
SYS_COMPANY.*
FROM
SYS_COMPANY SYS_COMPANY --关联单位
) DIM_21
ON (1 = 1 AND DIM_21.comp_code = DIM_2408.DIM_2417_comp_code) --维度之间的关联关系
WHERE
DIM_21.comp_code IN ('01') --单位编码
GROUP BY
DIM_2408.DIM_2417_fint_year,
DIM_2408.DIM_2417_fint_month,
DIM_2408.DIM_2417_profit_state,
DIM_2408.DIM_2417_comp_code,
DIM_2408.DIM_2408_name,
DIM_2408.DIM_2408_code,
DIM_21.comp_name,
DIM_21.comp_code,
DIM_2408.DIM_2417_disease_code_vh;

---此处得到最终的结果之后最后一次按照数据进行汇总
SELECT
DIM_21.DIM_2408_code AS DIM_2408_CODE,--单位编码
MAX(DIM_21.DIM_2408_name) AS DIM_2408_DISPLAY,--名称
'201312' AS DIM_DATE_CODE,--自动加年度
'YEAR_MONTH' AS DIM_DATE_LEVEL,--期间类型
DIM_21.DIM_21_comp_code AS DIM_21_CODE,--单位编码 --关联维度
MAX(DIM_21.DIM_21_comp_name) AS DIM_21_DISPLAY,--单位名称
COUNT(DISTINCT DIM_21.DIM_2417_disease_code_vh) AS DIM_2417_DATA --数据汇总
FROM
temp_4690DIM_21 DIM_21
GROUP BY
DIM_21.DIM_2408_code,
DIM_21.DIM_21_comp_code
ORDER BY
DIM_21.DIM_2408_code ASC,
DIM_21.DIM_21_comp_code ASC;

DROP TABLE temp_4690DIM_2417;DROP TABLE temp_4690DIM_2408;DROP TABLE --删除表
temp_4690DIM_21;

--自动生成的查询语句（以后查询仅仅通过这个语句进行查询）
SELECT DIM_DATE_CODE as DIM_DATE_CODE,--时间
MAX(DIM_DATE_LEVEL) as DIM_DATE_LEVEL,--期间类型
DIM_2408_CODE as DIM_2408_CODE,--单位
MAX(DIM_2408_DISPLAY) as DIM_2408_DISPLAY,
DIM_DATE_CODE as DIM_DATE_LAST_CODE,--时间维度
SUM(DISTINCT DIM_2417_DATA) as DIM_2417_DATA --汇总数据
FROM bi.rep_temp_report_3221
WHERE DIM_DATE_CODE IN ('201312', '201212') --页面时间过滤
AND DIM_DATE_LEVEL = 'YEAR_MONTH'--过滤
AND DIM_21_CODE IN ('01')--过滤
GROUP BY DIM_DATE_CODE, DIM_2408_CODE, DIM_DATE_CODE
ORDER BY DIM_DATE_CODE ASC, DIM_2408_CODE ASC

 

 

====================================================================================

BI的需求说白了，就是可以按需要出一些报表（客户处存在专门的业务分析人员），这就是BI的需求。

但是对于我们开发来说如何实现？？？？？？

理论上，现在都存在一些多维分析模型，这是一种理论化的解决方案，在开发中，编码实现对多维模型进行分析进而形成元数据metedata，最为关键的。其次就是数据的存储。

上面的SQL本质就是根据多维模型的元数据进而形成SQL（以前我们面向SQL开发报表），进而抽取数据存到临时表，也算是一种数据的物化吧.

====================================================================================

页面设计要求：

表格联动

图标联动

图表详细

指标自定义公式