基于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开发报表），进而抽取数据存到临时表，也算是一种数据的物化吧.
====================================================================================
页面设计要求：
表格联动
图标联动
图表详细
指标自定义公式
最新文章
- windows命令
- C++ 编写DLL文件给易语言调用
- ArcEngine编写WebService
- Linq之求和，平均值，最大值，最小值
- Hibernate关联关系之——单向n-1
- .NET3.5中的高性能 Socket API
- object- c 字符串操作
- MMDrawerController 使用遇到的问题及定制
- 扩展BaseAdapter实现不存储列表项的ListView
- python 对Excel表格的读取
- pandas 常用技巧总结
- leetcode笔记--水箱问题
- Spring MVC 之 ContentNegotiatingViewResolver
- 【转】 js数组 Array 交集 并集 差集 去重
- MySQL入门详解（三）---mysql如何进行主从配置
- DM6467开发领航-开发坏境安装
- layui图片显示
- C语言 · 十六进制转八进制
- jvm学习笔记（一）
- Atitit.c# .net 3.5 4.0 4.5 5.0 6.0各个版本新特性战略规划总结