OLAP 称为在线分析,其实就是报表系统,和BI系统. BI系统是套产品在这里不谈. 分析和报表其实都是用存储过程开发出来的,一个是在线提供给用户使用,另一个是离线提供给同事使用的.

 在线分析目前来看应用不广,所涉及到的数据量相对比较小,只是用户量比较大

1 用户只关心自己的. 比如购买次数,购买总额,等用户所关心的数据

2 产品关联,比如说购买该产品的用户还购买了其他什么产品!

3 产品火红度;

而报表涉及到所有的数据,包含历性数据. 每个部门有不同的报表要求,每个同事,每个部门领导都会提些自己关心的报表.

ORACLE 数据库 是从交易型数据库发展过来的,处理分析型数据时候总有点力不从心!

1 开始安装数据库时候选择OLAP 它会自动调整下必要的参数

2 设置64-128KB的数据块 而不是默认的8KB

3 分层设计, 因为报表众多,如果直接从原始表获取必然造成性能大阻塞. 因此要把基础的,共同的做成数据表,其他报表直接从这些基数表里获取数据. 这样就极大减少了数量.

 a 抽取源表层  b  基础表层 C 共同层 D 部门层

如何分? 哪些数据做在哪里,是需要多业务了解和熟悉,对公司和各个部门的报表了解,方能有大概的想法,  这些不一定一开始就能搞定的,需要不断地优化中.因为短时间内无法对业务的彻底熟悉.

4  任务调度:

 采用储存过程和软件包来做每个报表,每个表的数据产生. 那么这些任务之间必然产生了依赖.

 采用ORACLE 本身的JOB来调度,采用存储过程里面包含存储过程,也就是说JOB调度启动存储过程,启动存储过程把相关的存储过程包含在一起.

该方法不太灵活,扩展性比较差,维护比较难!

应该采用crontab 方式的调度. 比如说写个轮休的JOB 该JOB每隔5-10分钟运行一次. 该JOB只调用一个存储过程. 存储过程启动任务,任务是软件包或者是存储过程.

该存储过程 读取任务信息表, 任务依赖表,何时启动该任务, 并监督任务运行状况和报警.

5 软件包里 一般包含 a 抽取存储过程; b 清单存储过程;c 日数据存储过程; d 周数据存储过程; e 月存储过程;f 移动到结果表的存储过程;g 回滚的存储过程;h清理过期数据的过程

a 抽取存储过程 把源表的数据抽取到临时表中,这里指任务所需数据的表; 这里的临时表是物理的 以_TMP命名的.

之所以采用临时表法,因为ORACLE 对表连接成本很高, 尤其是多表的LEFT JOIN +LEFT JOIN . 采用临时表可以把必要的字段,必要的行形成较小的数据块.

b 清单存储过程

清单的意思是 这部分数据要临时存上1-3个月,主要的是去重的要求, 求一个月的人数不能从每天的人数SUM过来. 以_LST命名 这个清单要做成分区表 月,日或者小时的分区.

C 日数据过程 是从清单里获取数据进行统计,当然如果没有清单直接从抽取的临时表中获得

D 周过程, 周这个时间很麻烦的事情 尤其涉及到跨年的周. 如果不去重可以直接从日数据中提取

E 月过程 同上.

F 过程: 是避免结果表的更新影响到领导的查询, 所以先把所有的数据整合在一个临时汇总表中,再移动到结果表

G过程:是个重要的过程,它主要功能是实现回滚UNDO操作,因为依靠ORACLE自身的UNDO机制是很慢的.

   处理月报表每天都累加一次的情况,或者是清单过于庞大,保留一个月太多了,或者说扫描一个月的数据太久了.那么采取每天跑一次,每天加一次.

 类似是 update table set value=value+new_value;

这样的场景,如果运算过程中发生了故障,就会发生前后数据不一致,只更新了30%的数据就故障了. 所以更新前,把新的值存储在回滚表中.每次运行前调用回滚过程,检查回滚标志

如果非正常结束,那么提取相应的数据 对数据进行 UPDATE TABLE SET VALUE=VALUE-NEW_VALUE 操作;

H 清理过程: 这里主要是清理暂时保留一段时间的清单表.

 每个过程运行前 都要做 TRUNCATE TABLE XXXXX_TMP 的清空表的操作. 如果涉及到清单和目的表,那么要DELETE TABLE  WHERE YYYY= XXXX  因为避免得到重复的数据.

6 游标批处理

 因为数据量很大成百上千万行, 不可能一次性地提交上去. 比如  insert into table_name  (xx,yyy,zz,hhh,) select xx,yy,zz,hh from table_tmp left jion table_tmp2; 会很慢滴

采用游标和批提取方式

cursor  cur_day_result is  --计算月登录人数和次数  

         select      provcode from table_b group by 1;

  type type_provcode        is table of oss_openplat_truslogin_day_lst.provcode%type index by binary_integer;

  l_ary_provcode            type_provcode;

begin

    open cur_day_result;
    loop
      fetch cur_day_result bulk collect into

        l_ary_provcode 

    limit g_batch_size_n;   --- 这里可以控制提取行数

      forall i in 1..l_ary_provcode.count
          insert into login_day_lst
          ( provcode)

          values(l_ary_provcode )

       commit;   -- 这里把一部分数据提交到数据上

 end loop

7 复杂的要求:

经常有 连续三个月的购买用户人数, 日增加额和增加率, 当天与上个月当天的比 即同比; 月累加值.

采用MERG INTO和 UPDATE 的方式会比较慢. 直接采用INSERT 和DELETE

比如 日期, 分类1,分类2,分类3,统计值,统计值月累加;

通过 日数据过程和月数据过程 分别生成了数据

日期, 分类1,分类2,分类3,统计值;

日期, 分类1,分类2,分类3,统计值月累加;

分别insert into 到 汇总表 (日期, 分类1,分类2,分类3,统计值,统计值月累加)

insert into 汇总表 (日期, 分类1,分类2,分类3,统计值,统计值月累加)  select 日期, 分类1,分类2,分类3,统计值,0 from table_day_tmp;

把不属自己的字段值0

最后 汇总表在移动结果表时

select  日期, 分类1,分类2,分类3, sum(统计值),sum(统计值月累加) from 汇总表