Choosing Filters When You Define Context ..

This question came to my mind, when I came across one data issue in prod environment. Our BO Tool was generating 3 queries - I am assuming there were different context defined at universe - and filter which user had choose at Query level was actually getting applied only for one Query. User was expecting to filter some records, however - as filter was getting applied to only one query - at report level user was getting all records with null values.

Lets re-create the same scenario as below

In our earlier post http://gyaankatta.blogspot.in/2013/10/business-objects-universe-design_9960.html we had seen how you can create a Context at universe.

We will use same example now.

We have defined 2 context in our earlier post
1. To get Salary for each Employee, so joining Employee Fact and Emp and Dep dimentions.
2. To get Salary for each Departments, so joining Departments Fact and Dep Dimension.

At universe level, your joins will be as below

As you can see, we have 2 Contexts defined and Respective joins for it.

Report Requirement
We have to generate report, which will give Department_Name, Employee_Name, Salary_Per_Employee and Salary_Per_Department having Salary_Per_Department less than 15000.

You will choose your objects at BO Query Panel as below

You have pulled Salary_Per_Department condition in your BO Query. If you refresh the report, you will expect that your report should give you all Departments and Employees having salary less than 15000.

However, your reports will give all records and no record will get filtered out.

Why is so ?
If we verify 2 queries generated by BO, filter conditions which we have specified will get applied only to one Query as below

 This is a query one, generated by context Department and Employee_Fact. This one do not have filter condition which user is expecting to add.

This is a Query2, from Context Department and Department_Fact and this is having filter condition which user has specified.









BO tool will do FULL OUTER JOIN of the results generated from above 2 queries.

Second query will filter all departments having salary less than 15000, however first query won't do that. So, at final results, user can see all departments.

Is this because user added Fact as a Filter Condition?
No, its not because of that. Lets add one more table - Location - at Universe using same structure.

As you see here, we have added LOCATIONS table and all columns from this table we have exposed to Universe as Dimensions.

Save the universe, and now generate same query again, with filter condition as City = 'Bombay' as below

In earlier example, we have added filter which was a Fact, however this time its a pure Dimension. Same as earlier, BO Tool will generate 3 queries depending upon the context.

Query 1 got generated using the first context of Employee_FACT and Employee_DIM.

 Query 2 got generated using context Departments_FACT and Departments

Query 3 got generated using context Departments_FACT and Departments












As you can see, Dimension filter which we have added at BO Query got applied only for one query out of three. So at ultimate report, you will get all records but not specific to City as Bombay.

This situation will occur only when  you give your universe for Ad-Hoc reporting. In case of Canned Report you do not have to worry.

Remedy / Workaround --
Even I am not sure the workaround of it. however hoping to get something related to it.

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.