Oracle BIEE (Business Intelligence) 11g 11.1.1.6.0 学习(4)创建多维钻取分析

  在上一节时,我们创建了一个基于部门号的工资分类汇总。 这里就引出了一个概念:维度

  专业的解释大家自行百度,这里就不班门弄斧了。从数据的使用角度看,维度可以简单的理解成“数据分类汇总的一种依据”。

  按“部门号(DEPTNO)”对“工资(SAL)”进行分类汇总,“DEPTNO"就是一种维度;按“工作岗位(JOB)”对“工资”进行分类汇总,“JOB”就是另一种维度;

  维度之间也可以有层次关系,比如:我们先按“工作岗位” 汇总,然后每个相同工作岗位的员工,再按“部门”汇总。这种场景下:DeptNo就认为是Job的子级维度。

  先按Job汇总数据,相同Job的,再按DeptNo做更详细的数据汇总,这种逐层深入的数据分析过程,在BI层里有一个专业术语:钻取(Drill).

  下面就刚才的场景(即:按JOB -> DEPTNO来层层对SAL进行分类汇总),来看看维度如何创建:

一、db表结构的创建

  通过上回的学习,大家应该有一个大概印象,每种数据分类的依据,我们应该创建一张小表来与之对应(比如按部门号汇总时,DEPT部门表就认为是一张维度表)

  现在我们要通过JOB、DEPTNO 这二个有层级的维度对数据进行分析,所以:得有二张表。

  动手之前,插点题外话:通常对数据进行分析比较消耗db性能(特别是复杂的分析),所以一般真正的BI分析,都会事先把数据抽取到另一张表(甚至另一个专用于BI分析的库),这样分析时,不至于影响正常的生产系统使用。
这里我不想对数据抽取展开讨论(事实上,这一块我还没学会~_~),但是为了体现这个思想,我们创建几张以“BI_”为前缀的新表来作为BI分析的数据源。

create table BI_DIM_JOB as  select  distinct(job) from emp;
create table BI_DIM_DEPT as select deptno,dname from dept;

  这样就创建了二张小表BI_DIM_JOB、BI_DIM_DEPT以对应JOB、DEPT二个维度,但通常表还得有主键:

alter table BI_DIM_JOB
  add constraint PK_BI_DIM_JOB primary key (JOB);
alter table BI_DIM_DEPT
  add constraint PK_BI_DIM_DEPT_DEPTNO primary key (DEPTNO);

  加上主键后,我们再来看下主表EMP,既然JOB、DEPTNO都准备用单独的小表记录,主体表中就不再需要这些字段了,我们只是想对SAL进行分析,所以我们把EMP主表也做下处理:

create table BI_FACT_EMP as select empno,sal from emp;
alter table BI_FACT_EMP
  add constraint PK_BI_FACT_EMP primary key (EMPNO);

  这样就得到了一张新的(事实)主表,做为BI的事实表数据源。

  哦,仔细看了一下:这张表里没有ENAME(员工姓名),干脆,把EMPNO也当成一个维度,独立出来吧:

create table BI_DIM_EMP as select empno,ename from emp;
alter table BI_DIM_EMP
  add constraint PK_BI_DIM_EMP_EMPNO primary key (EMPNO);

  看到这里,可能已经有人发现了一个重大的问题:主表BI_FACT_EMP与这些小表之间好象没有任何数据上的关联关系?

  看来,还少一张用于描述EMPNO与JOB、DEPTNO之间关系的表:

create table BI_DIM_DRILL_EMP as select job,deptno,empno from emp group by job,deptno,empno order by job,deptno,empno;

alter table BI_DIM_DRILL_EMP
  add constraint PK_DIM_DRILL_EMP_EMPNO primary key (EMPNO);

alter table BI_DIM_DRILL_EMP
  add constraint FK_DIM_DRILL_EMP_REF_JOB foreign key (JOB)
  references bi_dim_job (JOB);

alter table BI_DIM_DRILL_EMP
  add constraint FK_DIM_DRILL_EMP_REF_DEPTNO foreign key (DEPTNO)
  references bi_dim_dept (DEPTNO);

alter table BI_DIM_DRILL_EMP
  add constraint FK_DIM_DRILL_EMP_REF_EMPNO foreign key (EMPNO)
  references bi_dim_emp (EMPNO);

alter table BI_FACT_EMP
  add constraint FK_BI_FACT_EMP_REF_EMPNO foreign key (EMPNO)
  references bi_dim_drill_emp (EMPNO);

  好了,天下太平,主表BI_FACT_EMP 与 从表 BI_DIM_DRILL_EMP 通过EMPNO关联,而 BI_DIM_DRILL_EMP 再进一步通过EMPNO、JOB、DEPTNO分别与BI_DIM_EMP、BI_DIM_JOB、BI_DIM_DEPT关联,完全符合数据库范式。

  光看上面的sql脚本,比较容易晕,为了更直观的说明各表的关系,这里给一张关系图:

二、创建RPD 模型及维度

2.1、 先用“BI管理”工具,联机打开当前RPD,并把原来的对象全清空掉,再按前几次学到的知识,把刚刚新创建的"BI_"打头的表,全添加进来,弄好后,大概是下面这个样子

注意图中二个黄色的字段:SAL_SUM、EMPNO_COUNT,这是我手动新添加的二个聚合字段,SAL_SUM是对SAL的SUM聚合,而EMPNO_COUNT是对EMPNO的COUNT聚合,参见下图:

 2.2、 在scott上右击->新建对象-》逻辑维-》具有基于级别的层次的维

在弹出的界面中,输入名称:BI_EMP_DIMs

然后在BI_EMP_DIMs上右击-》新建对象->逻辑级别

输入EMPNO(即创建最底层的维度)

 然后把BI_DIM_DRILL_EMP下的EMPNO拖动到刚创建的维度EMPNO下,如下图:

完成后,类似下图:

在EMPNO维度上右击-》新建对象-》父级别

 命名为DEPTNO 

完成后,可以看到DEPTNO与EMPNO已经呈现出父子层次关系

同样,把BI_DIM_DRILL_EMP中的DEPTNO拖动刚创建的维度DEPTNO上,如下图:

在DEPTNO维度上,再继续创建父级维度JOB

同样把BI_DIM_DRILL_EMP上的JOB拖动到新创建的维度JOB上,最终的维度层次如下图:

2.3、创建维度关键字:在JOB上右击->新建逻辑级别关键字(如下图)

保存默认,不作任何修改,直接点击“确定”

类似的,在DEPTNO,EMPNO二个子级维度上,也创建关键字,然后点击保存,最终的样子如下图:

注:保存成功后,每个维度下的字段前,会有一个类似“手*枪”的小图标

2.4、把整个维度BI_EMP_DIMs拖动到【表示】层中的BI_FACT_EMP上,如下图:

如果这时点击保存,会弹出如下错误:

解决方法:在EMPNO上右击-》属性(如下图)

点击+号

在弹出的界面中,展开SCOTT下的BI_DIM_DRILL_EMP表,双击EMPNO(即设置BI_DIM_DRILL_EMP的EMPNO字段为维度EMPNO的显示列)

类似的,把BI_DIM_DRILL_EMP的DEPTNO设置成维度DEPTNO的显示列,把BI_DIM_DRILL_EMP的JOB设置成维度JOB的显示列,再次点击保存,应该就能成功了。

模型和维度终于弄好了,已经成功80%,革命胜利在望!

三、创建钻取分析

登录,切换到管理菜单,点击“重新加载文件和元数据”,以便让刚才修改过的RPD生效

创建一个分析,从左侧的主题区域里,可以看到刚才创建的各种模型,我们选JOB和SAL_SUM、EMPNO_COUNT这三列,如下图:

切换到结果标签,如下图,注意JOB列标题及该列数据,都以A链接的形式呈现,为了方便以后修改,这里我们先保存一下,文件命名为SCOTT_EMP_DRILL,同时点击工具栏的小图标(如下图),预览下在仪表盘中的效果

这是仪表盘中的呈现效果,可以在JOB标题上点击一下

这里,BIEE会根据维度的层次关系,自动“下钻”到下一个维度(即DEPTNO),如下图:

DEPTNO维度下,还有最底层维度EMPNO,还可以继续下钻,如下图(注意一下每次钻取后,SAL_SUM、EMPNO_COUNT这二列的值会自动更新)

当然,维度不止这一种玩法,还可以直接把整个维度放到分析结果中,如下图,如果双击BI_EMP_DIMs把整个维度加入所选列

为了美观,我们把列名“汉化”一下

把列标题改成“工资”,其它几列也类似处理

现在看起来舒服多了

可以点击分组数据前的+号展开,这样就以另一种类似树状的方式进行钻取了

最后,为了让分析结果看起来更fashion一点,给它加个动态图表。不过做这个之前,有一个细节得先处理一下,“人数(EMPNO_COUNT)”是“个位数”级别的(<10),而“工资汇总(SAL_SUM)”是“千位数”级别的,如果二个条形柱显示在同一张图中,"人数"的柱状图高度几乎接近于0,根本看不出来,所以有必要把“人数”的数值放大一些,以保持跟SAL_SUM接近(至少同一个数量级)

把人数放大1000位以后,结果看上去还比较满意

下面是钻取过程中,图表动态变化的几张截图:

下钻到DEPTNO维度时的截图

下钻到最底层EMPNO时的截图

截了近一天的图,总算写完了,希望对于正在学习oracle BIEE的朋友们有所帮助

posted @ 2012-11-30 19:53  菩提树下的杨过  阅读(8579)  评论(21编辑  收藏  举报