Business Objects : Universe Design Scenario - Derived Table

Today we are going to see a simple Scenario using Employees and Departments table in HR schema [you will get this schema created automatically once you install ORCL or XE default databases from oracle].


Requirement -- We have 2 normalized tables Employees and Departments. Structure of tables are as below

We need a single report which will have columns as
1. Employee_ID,
2. First_Name,
3. Last_Name,
4. Email,
5. Phone_Number,
6. Salary,
7. Department_Name,
8. Salary_Per_Department,
9. % of Salary [Salary_per_Employee / Salary_Per_Department]




Universe Design -- Columns 1 to 6 are direct columns, coming from Employees table, even Department Name you can get directly from joining Departments table to Employees table; However, pain area is to get the value for Salary_Per_Department.


Before actually creating a universe, we will try to derive this report using SQL Query..


SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
EMAIL,
PHONE_NUMBER,
SALARY,
DEP.DEPARTMENT_ID,
DEPARTMENT_NAME
FROM EMPLOYEES EMP LEFT JOIN DEPARTMENTS DEP
ON EMP.department_ID = dep.department_id;


As said earlier, first 7 columns will come directly from Source table; Now, to derive salary_per_department we will use Analytical Function, so the ultimate query will be like below.


SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
EMAIL,
PHONE_NUMBER,
SALARY,
DEP.DEPARTMENT_ID,
DEPARTMENT_NAME,
CASE WHEN DEP.DEPARTMENT_ID IS NULL THEN 0 ELSE SUM(SALARY) OVER(PARTITION BY DEP.DEPARTMENT_ID) END AS SALARY_PER_DEPARTMENT
FROM EMPLOYEES EMP LEFT JOIN DEPARTMENTS DEP
ON EMP.department_ID = dep.department_id;


As we are ready with our report query, we will use the same at the time of Universe Design using it as a Derived Table.


How will you create a universe ?

If you have installed BO 3.0, then at Program File you will have a menu called "Universe Designer" [In case of BO 4.0 it is termed as Information Design Tool ]
After you login --

1. At top-left corner, you will have a menu to create a new universe. Once you click on it, new window will appear.
2. You need to give Universe Name which you are going to create.
3. Create a new Connection if you do not have it already.
4. Click OK.




Next step is to import the tables to the universe which you have created.

You will get options to import tables [using the connection, which you have specified at the time of universe creation]

Here, instead of importing a new table to your universe, we will create a Derived Table.

As query written above is sufficient for our reporting requirement, we will use the same query while creating a derived table.

How will you create classes and objects?

You need to create respective classes and objects for your report generation. You will create these at Universe itself.

Just drag your derived table to left window panel and automatically new class [name same as your derived table] with objects [all columns of your derived table] gets created.

All objects by default gets created as a Dimension, you can change their property to Measure or Detail as required. In this case we will change the property of Salary and 'Salary Per Department' to measure.

As shown in an image, you can change the property of a specific object as Dimension, Measure or Details depending on the requirement.


Now you have your universe with respective objects ready for the reporting.









How will you use universe for reporting ?

Normally you can use BO Infoview, BO Rich Client, BO Full Client for reporting purpose. For the sack of this reporting explanation I am going to use BO Rich Client.

At program files, you will have a installed menu called "Web Intelligence Rich Client".

Once you log into it, new window will appear which will prompt you [first option] to choose a universe for your reporting. Select the universe which you have just created.

Once you import a universe into Rich Client, new query panel will appear as above to create a new Report Query.

You can see same objects at left side panel which you have created at Universe. You need to drag your objects - as per your reporting requirement - to Results Objects panel and filters if any at Query Filters panel.

As per requirement, we have selected respective objects at Result Objects and Query Filters Panel and click on Run Query.

A report will get created with all records having department name as "Finance". Now the next task is to add a new column % of Salary [Salary_per_Employee / Salary_Per_Department] at Report level.

Right click on last column of a report and a popup will appear to add a new column; select "Insert column to the right" in this case

Once new column gets added, click "Ctr + Enter" -- new window will appear wherein you can enter a required formula i.e. Salary / Salary_Per_Department * 100

 
As shown in above image, write a formula in Formula Editor and click OK. 

All corresponding values will appear as per the formula specified in a new derived column.
You can find sample universe and report explained in above example at below URL.

Note -- We have not added original Employees and Departments table at Universe just for sack of this example. By not adding these tables to universe, we have actually restricted the report functionality which is now limited only to the objects available in Derived Table. As a general practice, I have seen people do not use \ or try to avoid the use of Derived Table in universe.