Force a join the universe

building semantic layer
1

I have a universe with 3 tables say x,y and Z.
Is there a way if i select objects from x and y i can force to include the join X–Z without selecting objects from Z?

x–>y
X–>Z

I know we can always create a derived table with tables X and Z and then join that to Y but is there a way in universe we can force a join without actually using an object form that tabe?

Thanks,
–H

hourigan (BOB member since 2007-04-06)

2

You can open objects that are based on table X and Y and force them to also include table Z. By doing that, the join will always be included. The main issue with this strategy is that if you ever touch the object, you have to remember to reset the table association.

Something that looks weird but works without the issue listed above is to create a where clause in the object that looks like this:

SELECT: X.COL_A
WHERE: Z.PRIMARY_KEY_COL IS NOT NULL

In this case the object is based on table X, but it references Z in the where clause. The primary key should never be null, of course, so this where clause will never cause rows to fall out. By having the reference to table Z in the WHERE clause it’s visible, and more robust (less likely to accidentally be removed). It does, however, have the overhead of having to run the where clause…

There is nothing magic about the IS NOT NULL logic. You could just as easily do something like Z.PRIMARY_KEY = Z.PRIMARY KEY which is another expression that will always be true.

Finally, if you have all of your objects from table X and Y in the same class, you can consider creating a class restriction that forces table Z to always be included. This avoids the issue of having to touch each and every object, but they all have to live in the same class.

Dave Rathbun :us: (BOB member since 2002-06-06)

3

hourigan, Dave,
Forgive my ignorance but what exactly is the point of forcing the join to table Z without selecting any data element?
On the surface all I can think of is to generate a cartisian product, but that to me doesn’t make sense as you would inflate the end result by the join.

Thanks for the insight!

amosgreg (BOB member since 2012-01-27)

4

Say table X is a list of countries, table Z may be a list of focus countries that your users want to report on and that they maintain via a screen (table X is part of the main warehouse). So you would force the join to restrict the queries based on the inner join, but all the country details are already in X.

5

Got it.
Thanks Mark!

amosgreg (BOB member since 2012-01-27)