Oracle biee (Business Intelligence) 11g 11.1.1.6.0 Learning (4) Create multidimensional Drilling Analysis

In the previous section, we created a salary Classification summary based on department numbers. A concept is introduced here:Dimension

For professional explanations, Baidu is the only option. From the perspective of data usage, the dimension can be simply understood as "a basis for data classification and summarization ".

By "department number (deptno)", "deptno" is a dimension. By "job) "sorts and summarizes the" salary ", and" job "is another dimension;

There can also be a hierarchical relationship between dimensions. For example, we first summarize the dimensions based on "Jobs", and then each employee in the same job position is summarized by "department. In this scenario, deptno is considered to be a subdimension of a job.

First, summarize data by job, for the same job, and then perform more detailed data summary by deptno. This layer-by-layer in-depth data analysis process has a terminology in the Bi layer:Drilling(Drill ).

The following describes the scenario (that is, sort and summarize Sal layers by job-> deptno) to see how to create a dimension:

I. Create a DB table structure

Through the previous study, we should have a general impression that we should create a small table to correspond to each type of data classification (for example, when summarizing by Department number, the dept Department table is considered as a dimension table)

Now we need to analyze the data through two hierarchical dimensions: job and deptno. Therefore, we need to have two tables.

Before you start, insert a question: Generally, analytics on the Data consumes dB performance (especially complex analysis), so the actual Bi analysis usually requires data in advance.ExtractionTo another table (or even another database dedicated to Bi analysis), this analysis will not affect normal production systems.
I don't want to discuss data extraction here (as a matter of fact, I haven't learned this yet ~ _~), To reflect this idea, we have created several new tables prefixed with "Bi _" as the Bi analysis data source.

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

In this way, two small tables bi_dim_job and bi_dim_dept are created to correspond to the two dimensions of job and dept. However, the table usually has a primary key:

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);

After adding the primary key, let's take a look at the master table EMP. Since job and deptno are all prepared to use separate small table records, these fields are no longer needed in the master table, we just want to analyze Sal, so we also process the EMP main table as follows:

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);

In this way, a new (fact) master table is obtained as the fact table data source of Bi.

Oh, let's take a closer look: There is no ename (employee name) in this table. Simply consider empno as a dimension and separate it:

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);

As you can see, some people may have discovered a major problem: the main table bi_fact_emp does not seem to have any data associations with these small tables?

It seems that there is one more table to describe the relationship between empno and job and 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);

Well, the world is peaceful. the main table bi_fact_emp is associated with the slave table metadata through empno, while the slave table is further associated with bi_dim_emp, bi_dim_job, and bi_dim_dept through empno, and then through empno.

Looking at the preceding SQL script, it is easy to get dizzy. To intuitively describe the relationship between tables, here is a diagram:

Ii. Create an RPD model and dimension

2.1 first use the "Bi management" tool to open the current RPD online and clear all the original objects. Then, based on the previous knowledge, add all the newly created "Bi _" headers to the table. After completing the operations, it looks like the following:

Note that the two yellow fields in the figure are sal_sum and empno_count. This is the two new aggregation fields I have manually added. sal_sum is the sum aggregation of Sal, and empno_count is the count aggregation of empno, see:

2.2 right-click Scott and choose create Object> logical dimension> hierarchical dimension.

On the displayed page, enter the name bi_emp_dims.

Right-click bi_emp_dims and choose create Object> logical level.

Enter empno (that is, create the bottom dimension)

Drag the empno under bi_dim_drill_emp to the created dimension empno, for example:

After completion, it is similar:

Right-click the empno dimension and choose create Object> parent level.

Name it deptno

After that, we can see that deptno and empno have a parent-child relationship.

Similarly, drag deptno in bi_dim_drill_emp to the created dimension deptno, for example:

In the deptno dimension, continue to create the parent dimension job

Similarly, drag the job on bi_dim_drill_emp to the newly created dimension job. The final dimension level is as follows:

2.3 create dimension keywords: Right-click on the job and choose create logic-level keywords (for example)

Save the default value. If no modification is made, click "OK"

Similarly, on the deptno and empno subdimensions, you can also create keywords and click Save. The final result is as follows:

Note: After saving successfully, a small icon similar to "hand * Gun" will appear before the fields in each dimension.

2.4 drag the entire dimension bi_emp_dims to bi_fact_emp in the [representation] layer, for example:

If you click Save, the following error will pop up:

Solution: Right-click the "-" attribute on "empno", for example)

Click +

On the displayed page, expand the bi_dim_drill_emp table under Scott and double-click empno (that is, set the empno field of bi_dim_drill_emp to the display column of the dimension empno)

Similarly, set deptno of bi_dim_drill_emp to the display column of the dimension deptno, set the job of bi_dim_drill_emp to the display column of the dimension job, and click Save again.

The model and dimension have finally been completed, and 80% has been successful. the victory of the revolution is in sight!

Iii. Create Drilling Analysis

Log on to the management menu and click "reload files and metadata" to make the modified RPD take effect.

Create an analysis. From the topic area on the left, you can see the various models you just created. We select the job, sal_sum, and empno_count columns, for example:

Switch to the result tag. For example, note that the title of the job column and the column data are displayed in the form of a link. To facilitate future modification, save the file name scott_emp_drill, click the small icon (for example) in the toolbar to preview the effect in the dashboard.

This is the rendering effect in the dashboard. You can click it on the job title.

Here, biee will automatically drill down to the next dimension (deptno) based on the level of the dimension, such:

In the deptno dimension, there is also the bottom-level dimension empno. You can continue to drill down, for example, (note that the values of the sal_sum and empno_count columns are automatically updated after each drill)

Of course, dimensions are more than this method. You can also directly put the entire dimension into the analysis results. For example, if you double-click bi_emp_dims to add the entire dimension to the selected column

For the sake of appearance, let's turn the column name into Chinese.

Change the column title to "salary", and handle other columns similarly.

It seems much more comfortable now

You can click the plus sign (+) in front of the group data to expand it. In this way, drill the data in a tree-like way.

Finally, add a dynamic chart to make the analysis result look more fashion. But before doing this, we have to deal with the details first. "Number of people (empno_count)" is "single digit" (<10), while "salary Summary (sal_sum) "is of the" thousands "level. If two bar columns are displayed in the same graph, the height of the" Number of people "bar chart is almost close to 0, which cannot be seen at all, therefore, it is necessary to enlarge the value of "Number of people" to keep it close to sal_sum (at least the same order of magnitude)

After increasing the number of students to 1000, the results seem satisfactory.

The following figure shows how many charts are dynamically changed during drilling:

When drilling down to the deptno dimension

When you drill down to the bottom-layer empno

I finally finished writing the graph for the last day. I hope it will be helpful to my friends who are learning Oracle biee.