Direct joins being ignored

[Former Member]

Hello

I am analyzing Interactive Analysis for a client who is considering purchasing it for their university if it can do what they are looking for. I have read all the help documentation, watched all the videos, and have set up a universe with their tables. It seems as tho direct joins are being ignored, and indirect joins that go through every other table is used instead.

For example, I have two tables I want to make a report from. They are joined on two fields (I tried joining each field separately and also tried together as one join). One of the tables also has a join to another table I am not querying. Instead of the IA using the direct joins to the two tables I am using in the report, it uses the join to the table I am not using and goes through 10 joins to get back to the second table. The query cannot complete because it gets lost in the crazy join mess.

How do I make it so it uses direct joins between the two tables?

[Former Member · Accepted solution]

Hi ,

if that is the case just go for Context

Thanks

[Former Member]

Please can you post the SQL generated by the query.

[amitrathi239]

Hi,

Can you explain more in details your problem.

as i understand by your query in your universe you have set the join between tables like a,b,c,d.now you are dragging object from a and d table so all tables are coming in the sql. and in your universe you want to set direct join between a and d table so if you selecting objects only from a and d table only these tables come in the sql.

This you can achieve by creating shortcut join between these two tables.

Select two tables and right click and go to Join.select the fields and checked the check box "shortcut join".

Thanks,

Amit

[Former Member]

It is more like i am joining a to b, b to c, c to d, d to e, a to c and a to e. If I want a report with A and E, it should not join ALL abcde when there is a direct join specified.

When I use shortcut joins (a very simple example) it generates syntax like:

(Note: Finances has Principal ID and Business ID, and Principals has Buisness ID)

SELECT

finances.Transaction_Date,

finances.Order_Amount,

principals.Username,

principals.First_Name,

principals.Last_Name,

businesses.Business_Name,

businesses.Business_Description

FROM

finances,

principals,

businesses

WHERE

finances.Transaction_Date BETWEEN {d '2011-07-01 00:00:00'} AND {d '2011-07-26 00:00:00'}

As you can see, my "expression" (finances.Principal_ID = principals.Principal_ID, etc) is not put into the WHERE clause, and no joining happens in the FROM, so this query is incorrect and crashes Interactive Analysis.

Any help would be greatly appreciated!!!

[Former Member]

Please can you post the exact SQL generated by the query without using shortcut joins?

Have you done an integrity check on the universe yet?

Have you set all your cardinalities?