variable joins

building semantic layer
1

hi,

just wondering if anyone out there has some experience with variable joins in BO universes – since I noticed that the ‘edit join’ function also allows @functions I thought that perhaps there might be a way to define joins that depend on objects selected as query result objects.

here’s the simplified situation:

  • assume a universe with 2 identical tables (in
    terms of fields), these are the only tables in the universe. let’s say one table contains figures for a particular year, whereas the other one contains the figures of the previous year. - data needs to be retrieved from both tables, but
    we obviously don’t want the user to have to apply conditions once for each table: since the fields are identical, we can impose conditions on the first table, and link the table to the other one. obviously there’s no problem there.
  • HOWEVER, not all joins can be active at all times -
    only the fields selected in the query result panel ought to be joined

any suggestions ?
thanks,
mirko.

Listserv Archives (BOB member since 2002-06-25)

2

Mirko,

My suggestion would be to look into “shortcut joins”. Consider the following scenario:

  • You have two tables in your database, a “master” and a “detail” table, let’s call them MAST and DET. They are joined through an arbitrary primary key field, say, ID.

  • You have a “dimension” table in your database, let’s call it DIM. This table can be joined to MAST and DET through field F1, but obviously you don’t need to join it to both when you have a join to MAST and DET.

In other words, if you build a query on DIM and MAST, you want the WHERE clause to look something like this:

WHERE DIM.F1 = MAST.F1 (1)

If you build a query on DIM and DET, you want the WHERE clause to look something like this:

WHERE DIM.F1 = DET.F1 (2)

And if you build a query on MAST and DET alone, you want the WHERE clause to look something like this:

WHERE MAST.ID = DET.ID (3)

The problem is, if you create all three joins, when you build a query on all three tables your WHERE clause would end up looking like:

WHERE DIM.F1 = MAST.F1 (1)
AND DIM.F1 = DET.F1 (2)
AND MAST.ID = DET.ID (3)

When all three tables are included you don’t need join (2) because you already have joins (1) and (3). You could just delete join (2), but if you did, BO would automatically include MAST in any queries you build on DIM + DET – and that is hardly ever desirable. So, the question is, is there a way to make join (2) variable.

With shortcut joins you can accomplish this. If you make join (2) a shortcut join, when you build a query on only DIM + DET, BO will only use join (2); but when you build a query on DIM + DET + MAST, BO will ignore join (2).

I hope I’ve done a good enough job of explaining shortcut joins.

Erich Hurst
Compaq Computer Corporation

Listserv Archives (BOB member since 2002-06-25)

3

Erich,

Thanks for the tip, but I’m afraid it doesn’t really answer my question:

shortcut joins do indeed allow joins to be used in function of the TABLES involved in the query, but what we’re looking for is a way of defining joins in function of the FIELDS involved in the query.

Perhaps I should clarify the example I mentioned in my initial message: initially I only mentioned 2 tables, without ‘lookup’ table (I assume this is the Dimension table you refer to in your message) – however, without a third ‘Dimension’ table it doesn’t seem possible at all to make the same selection on both tables without having to link 2 seperate queries. Thus, let’s assume that there is a Dimension table, but which does not include ALL possible combinations of the various Dimensions

  • 3 tables, BUDGET and ACTUAL and LOOKUP, which all have the
    following fields:

  • Name

  • Company

  • Product

  • FY

  • Quarter

  • Price

  • if I want to retrieve budgeted and actual revenue for a
    particular Product during a certain Quarter, I would want to use the following joins

LOOKUP.FY=BUDGET.FY
LOOKUP.FY=ACTUAL.FY
LOOKUP.Quarter=BUDGET.Quarter
LOOKUP.Quarter=ACTUALS.Quarter
LOOKUP.Product=BUDGET.Product
LOOKUP.Product=ACTUAL.Product

  • HOWEVER, to retrieve budgeted and actual revenue for a
    particular Quarter, I only want to use the joins

LOOKUP.FY=BUDGET.FY
LOOKUP.FY=ACTUAL.FY
LOOKUP.Quarter=BUDGET.Quarter
LOOKUP.Quarter=ACTUALS.Quarter

as mentioned above, the lookup table does not include all possible combinations of the various dimensions: hence, if I would use the joins on Product as well, I’m bound to exclude some records by performing this query (namely, those records in BUDGET or ACTUAL which have a value in the Product field which does not exist in combination with the given FY and Quarter in the LOOKUP table)

It seems to me that the only solution will consist of creating a LOOKUP including all possible combinations of Dimension values…

mirko.

“Hurst, Erich” Erich.Hurst@COMPAQ.COM on 23-09-98 18:01:05

Please respond to Business Objects Query Tool BUSOB-L@LISTSERV.AOL.COM

Sent by: “Hurst, Erich” Erich.Hurst@COMPAQ.COM

cc: (bcc: Mirko Vonck/Cortex/Antwerp/BE/SGS)

Mirko,

My suggestion would be to look into “shortcut joins”. Consider the following scenario:

  • You have two tables in your database, a “master” and a “detail” table, let’s call them MAST and DET. They are joined through an arbitrary primary key field, say, ID.

  • You have a “dimension” table in your database, let’s call it DIM. This table can be joined to MAST and DET through field F1, but obviously you don’t need to join it to both when you have a join to MAST and DET.

In other words, if you build a query on DIM and MAST, you want the WHERE clause to look something like this:

WHERE DIM.F1 = MAST.F1 (1)

If you build a query on DIM and DET, you want the WHERE clause to look something like this:

WHERE DIM.F1 = DET.F1 (2)

And if you build a query on MAST and DET alone, you want the WHERE clause to
look something like this:

WHERE MAST.ID = DET.ID (3)

The problem is, if you create all three joins, when you build a query on all
three tables your WHERE clause would end up looking like:

WHERE DIM.F1 = MAST.F1 (1)
AND DIM.F1 = DET.F1 (2)
AND MAST.ID = DET.ID (3)

When all three tables are included you don’t need join (2) because you already have joins (1) and (3). You could just delete join (2), but if you did, BO would automatically include MAST in any queries you build on DIM + DET – and that is hardly ever desirable. So, the question is, is there a way to make join (2) variable.

With shortcut joins you can accomplish this. If you make join (2) a shortcut join, when you build a query on only DIM + DET, BO will only use join (2); but when you build a query on DIM + DET + MAST, BO will ignore join (2).

I hope I’ve done a good enough job of explaining shortcut joins.

Erich Hurst
Compaq Computer Corporation

Listserv Archives (BOB member since 2002-06-25)

4

Mirko,

some suggestions, but not complete solutions:

  1. You could try to define the join as @Script(‘GetMyOwnJoinCondition’) … Never tried this, but would surely question the need to use metadata (universe) at all. You would be defining your own “metadata” as bo script file.

  2. Try to work out with n(n-1) views and do the join path selection with @Aggregate_Aware(). I seriously doubt this approach.

  3. You could try to define the join conditions in the WHERE part of of an object.
    So, for the Quarter object, you would define the SELECT clause as you like, and the WHERE clause as:
    LOOKUP.Quarter=BUDGET.Quarter AND
    LOOKUP.Quarter=ACTUALS.Quarter

And for Product, you would define the WHERE as:
LOOKUP.Product=BUDGET.Product AND
LOOKUP.Product=ACTUAL.Product

etc…

Now, if I choose Quarter object, the WHERE clause will be used, creating an equivalent of a JOIN.

Disclaimer:
Never done this, just an idea. For example, I don’t know if BO will whine about cartesian products if no actual join is defined in the universe, but “object-aware joins” are used. Also, I don’t know what will be inserted into the FROM part of the SQL. But this can surely be circumvented by using Tables… -button in Object definition.

Please let me know if you have any success with approach #3. I’d be very interested to hear about the results…

-Harri

Ps. If approach#3, is feasible and never used before, I’d like to copyright the term “Object-Aware Joins” … :slight_smile:

Pps. By the way, are outer joins out of question?

Listserv Archives (BOB member since 2002-06-25)

5

Mirko,

I guess I just don’t understand WHY you want to have “variable joins”. I though your first example was an example where shortcut joins would be helpful. Your second example, in my opinion, has more serious problems, due to the way relational tables work:

Suppose these tables had the following records:

LOOKUP

Listserv Archives (BOB member since 2002-06-25)