商业智能需要站在全局角度考虑问题

首先看一下下面这个表样.这个表样是润乾的一个示例表样,接着介绍birt 是如何做的
[img]http://jjjava.iteye.com/upload/picture/pic/9994/c5064865-dcf6-393e-
b40e-8a64b1ee2dae.jpg[/img]
这个表样算是报表中最常见的一种需求了，把不同粒度的数据聚合到同一粒度上.

我们用automobile 表示汽车表, 用houseproperty 表示房产表, 用estate表示土地表,用others 表示其他表.他们都有customerid 来表示客户名称.customers 表有customerid 和customername

1. 用视图来拼数据

   对于  automobile 聚合使用一个autoview , sql 如下

   select sum(automobile price* quantity) autototal, customerid  from automobile group by customerid

  对于 houseproperty 使用如下view

   select sum( house price * quantity ) housetotal, customerid  from houseproperty group by customerid

  对于 土地和其他也是差不多类型的sql



  得到了上面这四个视图之后, 就把customers 表根据customerid 和其他四个视图join 起来.

  select customername ,  autototal  , housetotal , estatetotal , othertotal

   from  customers c , automobile a , houseproperty h , estate e , others o

   where 四个视图和customers的连接条件



  由于这个表样并不涉及到以 汽车，房产，土地， 其他表中的任何一个客户为主, 所以暂时也不考虑左右连接的问题.



2. 使用Birt 的Joined Data Set

   Birt 提供的Joined Data Set 跟数据库里面的视图是差不多的概念，使不过一个是在数据库内部join 数据，一个是使用Java 循环的方式join 数据.

   跟上面创建视图一样，创建四个DataSet , 输出两个字段: customerid 和 total.

   然后创建一个Joint Data Set , 把customers 表和 autototal DataSet 按照customerid join起来.这时得到四个字段 customers.customername , customers.customerid  ,  auto DataSet.customerid ，auto DataSet.autotal . 最后我们把这个Joint DataSet 命名为jdataSet1 (代表的是autoDataSet 和 customers 产生的Joint DataSet ).

  然后继续创建另一个Joint Data Set 把customers 的customerid 和前一步创建的jDataSet1 按照customerid join 起来得到一个第二个Joint DataSet1 ,我们命名为jDataSet2 .

  以此类推，最后我们输出5个字段: customers.name   ,   auto.total ,  house.total  , estate.total , others.total , 实际输出可能在customers.customerid 上重复了4次.



上面两种方式报表的做法主要有两点差别:

1. 性能

    如果本身数据库单独执行一个视图的sql 结果比较小，而本身的automobile 表数据量比较大（也就是说一个customer对非常多的automobile 记录) , 那么使用数据库视图就会比较慢. 因为数据库在尝试得到4个视图的结果进行了一些不必要的按照automobiel表的customerid和customers的customerid进行的比较操作( 如果有索引肯定是对索引进行了多次不必要的读) ,这时候使用Java的这种循环方式会比较快一点 . 如果本身结构集比较大，那么还是用数据库本身的排序和join 比较快, 这个道理比较类似于数据库中如果结构集大于5% 就不要走索引,直接进行全表扫描.只不过这是一个java 版的.

2. 左右连接的问题

   如果这个表的数据不是以customers里面的数据为主，而是以四个视图其中的一个为主.这时候要情况可能会比较复杂一些.由于我不太清楚birt 里面joint data set 计算的方式是怎样的，所以也不太好评论.不过如果不使用joint data set , 就是使用一般的编程的方式.那么按照下面这个思路会比较好:

a , b , c , d ,e  为customers , automobiles view  (后面简写avlist 表示这个集), housetotal view , 里面的指针.  四个view 都已经排好了序.group by 是默认排序的. outlist 表示最后需要的输出 , row表示最后输出的一条数据

for ( 按照主数据循环)  {

    row.customerid = customers.customerid;

    if( customers.customerid == avlist[b].customerid) {

           outlist[x].autotaol = avlist[b].autotal ;

           b++

     }

    四个view 同一个判断方式, 但是每个view 的指针指的是不一样的.

    最后:   outlistSet . add (outlist[x])

}



如果数据源本身的数据量也很大，需要输出的数据也很多，则上面两种方式都会有性能问题，这时可以考虑商业智能里面的万金油方式: ETL



3. ETL

    从上面这个表样看来，可能会有两个隐含条件.一个是聚合的数据粒度在时间上是月，另一个在销售地点粒度上是：支行086001-301 , 所以用ETL一开始就聚合数据可能会更好的解决性能问题.另外一个是如果用户想要排序和过滤数据的话用一个单独的ETL聚合表也比拼数据的方式好的多.



这篇文章的目的并不只是想解释一个做表的问题，而是说明商业智能的一些问题不光需要站在一个独立的角度思考，有些问题本身可能随着环境的一些变化而采取对应的措施，比如上面为了解决排序，过滤，左右连接，性能问题都需要从不同的角度来考虑问题.有时候可能一个做表的问题不光只是要限定在sql 或某个报表软件的本身，有时候某个ETL问题可能不关只是ETL就能解决的,随着情况的复杂可能需要从整个商业智能的全局来进行考虑，商业智能越来越倾向与多个解决方法的融合.

本文原文在 http://www.gemini5201314.net

如果你对ETL中性能优化问题有兴趣的话，也可以查看另一篇关于在ETL中性能优化要站在全局的角度，而不是只在数据库角度的文章.