Multiple Contexts on same set of tables

building semantic layer
1

Hello :slight_smile:

I have a collection of tables in my universe with two different possible ways of joining them to each other. For simplification purpose I shall explain the scenario using three tables- Sales, Products, Licenses
The two different set of joins between these tables and thus the two contexts as shown below:

Context_1: sales -> Join_1 -> products -> Join_2 -> licenses
Context_2: Licenses -> Join_3 -> products -> Join_4 -> Sales

whenever I use objects from any two or all three of these tables, as two different paths exist between them, I expect to be prompted for selection of the context.
However, BO includes all available joins between the tables and generates the SQL.

meaning:
If I select Sales_Document and Product_Description from Sales and Products respectively, instead of prompting for selecting Context_1 (hence join_1) or Context_2 (hence join_4) it generates a query as:

SELECT Sales_Document, Product_Description
FROM Sales *OUTER JOIN Products on (Join_1 and Join_2)

*OUTER it changes INNER JOIN to RIGHT/LEFT/FULL OUTER JOIN as appropriate based on the new relations (combination of all possible joins) between the tables

I tried different combinations of -
a. multiple SQL for different context
b. allow selection from differnt context
c. COMPARE_CONTEXTS_WITH_JOINS
However, it makes absolutely no difference to the BO behaviour.
BO Version: XI R3.1
DBMS: MySQL

I came across a couple of discussions on this forum which touched upon a similar issue “all self-restricting joins on a table being included in the query even if they belong to different context”.
https://bobj-board.org/t/161336
http://forumtopics.org/busobj/viewtopic.php?p=15610

However, the discussion moved towards alternatives and workarounds for the self-restricting joins. It didn’t elaborate on the reason for two joins from different context being included in the same query.

Could anyone please throw some light on it?

Thanks!!
Best Regards,
Rig

rig (BOB member since 2010-10-22)

2

Hi,

What’s the business meaning of 2 different join paths between the 3 tables? Is it possible (considering that Sales is a fact table) that either Products table or the Licenses table plays different role/purpose in the 2 paths? If so then such table is a candidate for an alias.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

Hi Marek,

The purpose of this universe is not for numeric analysis. It has no measures. It is used for listing different relations in the data.

Context_1: Sales → Product → Licenses
Helps to project all Sales to a customer and actually cut licenses (if any) against them

here, join between Product → Licenses is 1-> 0,M(licenses being optional)

  • Customer may cut more licenses than actually allowed
  • Customer may renew license
  • customer may not cut licenses at all

Context_2: Licenses → Product → Sales
Helps to project all Licenses cut by customer and sales details associated with them

Together they would help to identify cross-licensing, re-selling and other malpractices like

  • product sold to customer A but License Cut By Customer B
  • same license used by more than one customer
  • doubtful renewals
    -etc.

Hope the explanation is convincing enough to justify the use of two separate paths/contexts…( for simplicity I have picked just three tables out of many that together help to provide the necessary information. Hence I may not have been able to justify thoroughly the use of two different contexts)

rig (BOB member since 2010-10-22)

4

Hi Marek,

Thanks very much for your suggestion!

All the tables in the universe can be traversed through two different logical paths. I have many more than these three tables. Hence, creating an alias for each one and then replicating the classes and objects would be tedious and probably hard for maintenance and updates.
Moreover, as explained in the earlier post, sales table doesn’t necessarily play the role of a fact table in this case. So including sales all tables may need aliases.

Best Regards,
Rig

PS:sorry for posting separate responses. Didn’t mean to bump the post!

rig (BOB member since 2010-10-22)

5

Rig,

Maybe this will work:
Create 2 aliasses on the products table: products_1 and products_2.
Now you create the following joins and cardinalities:

sales - products_1 (1:n)
licenses - products_1 (1:n)
sales - products_2 (1:n)
licenses - products_2 (1:n)
products - products_1 (1:n)
products - products_2 (1:n)

Remove the 2 existing contexts. They didn’t work :reallymad:
Detect contexts, accept only these 2: products_1 and products_2
I love ‘Detect Context’ :mrgreen:

Now you have 2 different paths.
You can keep using the existing objects.
There are no objects needed on the tables products_1 and products_2 (maybe for selecting the correct context).

No aliasses needed for every table, just 2 aliasses on tables to add a path/context. :blue:
No replication of classes and objects needed. :blue:
Maintenance is easier with the funcion ‘Detect Context’
Remember that the suggested contextnames are based on the name of the (in this case factless) facttables: products_1 and products_2.
You are free to give the aliasses names that are meaningful.

Hope this helps,
Johan

JdVries :netherlands: (BOB member since 2006-02-06)