BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

variable joins


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
Listserv Archives
Archive Owner
Archive Owner



Joined: 25 Jun 2002

Posts: 24831



PostPosted: Tue Sep 22, 1998 2:40 pm 
Post subject: variable joins



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.


_________________
AOL Listserv Archive Post
This message was originally posted on the BUSOB-L AOL Listserv. It was archived to this forum to maintain a historical listing of issues and solutions from 1998 to August 2002. More Details
Back to top
Listserv Archives
Archive Owner
Archive Owner



Joined: 25 Jun 2002

Posts: 24831



PostPosted: Wed Sep 23, 1998 11:01 am 
Post subject: variable joins



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
* (281) 514-9445
* Erich.Hurst@Compaq.com




_________________
AOL Listserv Archive Post
This message was originally posted on the BUSOB-L AOL Listserv. It was archived to this forum to maintain a historical listing of issues and solutions from 1998 to August 2002. More Details
Back to top
Listserv Archives
Archive Owner
Archive Owner



Joined: 25 Jun 2002

Posts: 24831



PostPosted: Thu Sep 24, 1998 4:19 am 
Post subject: variable joins



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
* (281) 514-9445
* Erich.Hurst@Compaq.com




_________________
AOL Listserv Archive Post
This message was originally posted on the BUSOB-L AOL Listserv. It was archived to this forum to maintain a historical listing of issues and solutions from 1998 to August 2002. More Details
Back to top
Listserv Archives
Archive Owner
Archive Owner



Joined: 25 Jun 2002

Posts: 24831



PostPosted: Thu Sep 24, 1998 9:01 am 
Post subject: variable joins



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" .. :)

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


_________________
AOL Listserv Archive Post
This message was originally posted on the BUSOB-L AOL Listserv. It was archived to this forum to maintain a historical listing of issues and solutions from 1998 to August 2002. More Details
Back to top
Listserv Archives
Archive Owner
Archive Owner



Joined: 25 Jun 2002

Posts: 24831



PostPosted: Thu Sep 24, 1998 4:04 pm 
Post subject: variable joins



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

_________________
AOL Listserv Archive Post
This message was originally posted on the BUSOB-L AOL Listserv. It was archived to this forum to maintain a historical listing of issues and solutions from 1998 to August 2002. More Details
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0286 seconds using 17 queries. (SQL 0.0031 Parse 0.0009 Other 0.0247)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo