Multiple contexts issue

[Former Member]

Hi,

I am using Bo XI R3.1. I've 3 classes in universe people Details, Project status Report, project Details. Also based on this classes 3 contexts has been created which are project_status, people and project and joins which belong to these classes has been added in three of this contexts. Now if objects has been taken from different classes (which are based on different contexts) in webi rich client report it is showing as synchronization with two select statements. The display of data is in two tables but there some empty columns. There are no measure objects used in universe. I've gone through the queries based on this issue in this forum but didn't understand.

The option multiple sql statement for each context is also checked under parameters.

I would like to know

If the data coming is correct or not

If we can display all the columns in one table (single query instead of two queries)

Please advise the solution in detail.

Thanks in advance.

[Former Member]

You need to look at how your contexts are built. If there are no "circular" references, such as where two tables are linked together and then each of them has a link to third table, you don't really need contexts.

If you do have this situation, you'll need to either expand your contexts to include the tables from all of the classes, but avoid circular references or you'll need to redesign the universe to use aliases instead of multiple links to a single table. Which route you take will depend on what your data looks like.

Examples:

Context

Loan -> Loan_History -> History_Session
    |                    ^
    V                    |
Tax_Line -> Tax_Line_History 

In this case, there's never a situation where we will be showing both loan and tax line history in the same "table" on a report - they don't make sense when combined. So, we created two contexts - one for Loan History that also included the Tax_Line table and one for Tax Line History that also included the Loan table. Sometimes users have to select the context they need for the report, but we trained the users and wrote documentation that explains what that means.

Alias

Employee -> Department
      |      ^
      V      |
       Manager

In this case there are times when we want to see both the employee's department and the manager's department in the same table. So we aliased the Department table so that department information could be queried for both types of people. (Actually, Manager is an alias for the Employee table, but we aliased because we needed to relate two different instances of the same table, not because we needed to resolve a circular reference.)

-Dell

[Former Member]

Dell, don't forget you don't have to have loops to require contexts. They can also be used to solve fan traps, and I can have a fan trap with only two tables and only one join. On the other hand, if the original poster has no measures, then there are no fan traps.

With at least three tables and two joins I can have a chasm trap. Chasm traps do not show up as a loop either, but require contexts (and also separate report blocks) to report accurately.

To the original poster: without seeing the table structure and cardinality settings it's hard to say whether you need contexts or not.

[Former Member]

Hi,

Thanks for your replies. As i didn't see the way to attach the pdf format of unv copy if attachments been supported in this site i am providing the following information but not sure to how much extent if this helps in providing the advice. Before that i would like to mention i've tried removing the contexts and didnt see any loops in universe.

26 Classes

373 Objects

19 Tables

9 Aliases

21 Joins

3 Contexts

0 Hierarchies

0 Conditions

Below are the three main classes

People Details

Project status Report

Project Details.

The requirement is such that there is an object called country existing in People Details class (covered by context people) and it is required to be used along with project_status which is covered with context project_status

Context List:

people

Project_status

Project

Join 0: SKILLMATRIXDB.dbo.PnL.Number= SKILLMATRIXDB.dbo.People.PnL

Join1: SKILLMATRIXDB.dbo.Country.ID=SKILLMATRIXDB.dbo.PnL.LegalEntityID

Join2: SKILLMATRIXDB.dbo.BusinessDivisions.ID=SKILLMATRIXDB.dbo.PnL.BusinessDivision

Join3: SKILLMATRIXDB.dbo.BusinessUnits.ID=SKILLMATRIXDB.dbo.PnL.BusinessUnit

Join4: SKILLMATRIXDB.dbo.Projects.ID=SKILLMATRIXDB.dbo.Allocations.ProjectID

Join5: SKILLMATRIXDB.dbo.Programs.LinkedProjectAndProgramIDs=SKILLMATRIXDB.dbo.Projects.Number

Join6: SKILLMATRIXDB.dbo.Projects.ID=SKILLMATRIXDB.dbo.StatusReport.ProjectID

Join7: SKILLMATRIXDB.dbo.Projects.Number=SKILLMATRIXDB.dbo.ProjectDeliverables.ProjectNumber

Join8: SKILLMATRIXDB.dbo.ProjectMilestones.ProjectNumber=SKILLMATRIXDB.dbo.Projects.Number

Join9: SKILLMATRIXDB.dbo.StatusReport.UpdatedBy=@Variable('BOUSER') or

SKILLMATRIXDB.dbo.StatusReport.UpdatedBy=( select SKILLMATRIXDB.d

bo.XrefUserMappings.PeopleXPID fr

om SKILLMATRIXDB.dbo.XrefUserMap

pings where SKILLMATRIXDB.dbo.Xr

efUserMappings.LoginID=@Variable(

'BOUSER'))

Join10: SKILLMATRIXDB.dbo.Projects.Manager=SKILLMATRIXDB_dbo_People2.XPID

Join11: SKILLMATRIXDB_dbo_People22.XPID=SKILLMATRIXDB.dbo.Issues.RaisedBy

Join12: SKILLMATRIXDB_dbo_PnL2.Number=SKILLMATRIXDB_dbo_Projects2.PnL

Join13: SKILLMATRIXDB_dbo_Projects2.Number=SKILLMATRIXDB_dbo_ProjectMilestones2.ProjectNumber

Join14: SKILLMATRIXDB_dbo_Projects2.Number=SKILLMATRIXDB_dbo_ProjectDeliverables2.ProjectNumber

Join15: SKILLMATRIXDB_dbo_Projects2.Number=SKILLMATRIXDB_dbo_Risks2.ProjectNumber

Join16: SKILLMATRIXDB_dbo_Projects2.Number=SKILLMATRIXDB_dbo_Issues2.ProjectNumber

Join17: SKILLMATRIXDB.dbo.PnL.SubBusinessUnit=SKILLMATRIXDB.dbo.SubBusinessUnits.ID

Join18: SKILLMATRIXDB.dbo.People.Manager=SKILLMATRIXDB_dbo_People21.XPID

Join19: SKILLMATRIXDB.dbo.Projects.ID=SKILLMATRIXDB.dbo.Risks.ID

Join20: SKILLMATRIXDB.dbo.Projects.Number=SKILLMATRIXDB.dbo.Issues.IssueNumber

Any input is highly appreciated.

Thanks again..

[Former Member]

Here's what I would try...

1. Click on the "Check Integrity" button or go to the Tools menu and select "Check Integrity".

2. Uncheck everything except "Check for Loops" and "Check for Context" and then click on OK.

If the results are empty, then you have no loops that are "fixed" by contexts. In this case you can remove the contexts entirely which should solve your problem. If, however, it shows that there are loops that are resolved by contexts, you'll probably want to see if you can alias the table that is causing the loop so that you no longer need the context.

-Dell

[Former Member]

Thanks for the replies.

I've removed the context and ran the check integrity by checking the options check for loops and check for context and it is showing as OK

This indicates there are no presence of loops in universe.

Also i would like to know there is no cardinality defined in universe does that mean there is no need to check for chasm traps. There would be no possibility for fantraps as there are no measure objects.

Edited by: Eswar Rao on Jun 29, 2010 5:40 AM