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%，革命胜利在望！

三、创建钻取分析

创建一个分析，从左侧的主题区域里，可以看到刚才创建的各种模型，我们选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接近（至少同一个数量级）