Business Objects : Universe Design Scenario - Objects using Context

In last 3 posts, we have seen how to use Derived Table, Analytical Functions at Universe Object at Universe Level and Merged Dimension at Report Level.

Below are the links for the same

Derived Table -- http://gyaankatta.blogspot.in/2013/10/business-objects-universe-design.html
Analytical Function -- http://gyaankatta.blogspot.in/2013/10/business-objects-universe-design_13.html
Merge Dimension -- http://gyaankatta.blogspot.in/2013/10/business-objects-universe-design_6236.html

In this post, we will try to resolve same problem but this time using the Context at Universe level.

Before actually creating a new Universe, we will first analyze the scenario on the basis of which we will be defining the context.

We have Employees and Departments table [ we do not have Dimension and Fact tables separate] which are having pure 3NF relationship. We are going to use Dimension Modeling Concept on top of this 3NF layer.

Logically we have 2 separate paths
1. To get the Employee information along with Salary as a Fact
2. To get Departments information along with Salary as a Fact

Our logical model to implement this solutions is as follows..

At Universe Level, we will create 2 alias of Employee table for each fact table as described in above image.

We will define 2 contacts as follows
1. Employee Context -- We will have all Employee information from this context along with Employee Salary as a measure attribute. We will have join between Emp Dim, Emp Fact and Dep Dim
2. Department Context -- We will have all Departments information from this context along with Department wise Salary as a measure attribute. We will have a join between Dep Dim and Dep FACT table.

How will you create an Alias ?

You just have to select the table of which you want to create an alias.

Click on Insert Menu where you will get an option to create an Alias of a selected table.

Create 2 Aliases of Employees table with name as Emp FACT and Dep FACT respectively.




Final join between imported Tables and Aliases will be as below

Next task is to define Context for Employee and Departments information separately.

How will you create a Context ?

At Insert menu, you will have an option to create Context at Universe.

Once you click on the Context option, new window will appear, which will prompt you to give Context Name and select the joins which will participate in that context.

For our purpose, we will use below joins for 2 context.

1. Employee Context --

a. Join between Employees and Departments
b. Join between Employees and Employee Fact

2. Department Context --

a. Join between Departments and Department Fact






Once you define the context, next job is to create classes and objects.

As we have defined 2 context for Employee and Departments separately, we will create objects accordingly.

1. Employees class will have all Employee related Dimensions e.g Employee Id, First Name, Last Name etc.
2. Departments class will have Department related Dimensions e.g Department Id, Department_Name
3. Measure class will have 2 measure objects coming from 2 separate context.
   a. Salary_Per_Employee -- will come from Emp FACT table
   b. Salary_Per_Department -- will come from Dep FACT table.

Finally our Universe will appear something as below

We have done with Universe creation, next and final part is of creating a Report.

As soon as you pull the objects which are in 2 different context -- at Query Panel, BO Tool will create 2 separate queries, one from each context.

As you can see in a image, BO has created 2 separate queries.

1. Using Department Context, which will give Department Name and Salary_Per_Department
2. Using Employees Context, which will give Employee Information along with Salary_Per_Employee

 Ultimately tool will join results of these queries internally on the basis of common columns, in this case it is Department_ID

Note -- If you define the context, BO Tool will create 2 separate queries for you by its own and merge results of those on the basis common dimension internally. However, when you define 2 separate queries at BO by your own, you have to define / join result sets of those by defining merged dimension which we had seen earlier.