Dave’s Adventures in Business Intelligence

I submit the answer is that nothing went wrong. When I set up an outer join, I told my universe that some customers did not have orders, and that was okay. When my user set up their query, they said they wanted to restrict their report to show customers and order totals but only for orders that fell between a certain date range. Where does a customer with no orders go in this case? Or in other words, what can I tell from an empty amount on this report? It doesn’t tell me if the customer has zero orders, or if they have zero orders within the date range. Those are two possible cases. In order to avoid the ambiguity, the outer join is turned off and my new customer is missing.

Altering the Outer Join Behavior

In the scenario I have outlined so far, I don’t think I would change anything. In my opinion, the answer provided is the correct answer. But there could be cases where I want to allow conditions on an outer join without breaking the outer join behavior. Fortunately it is possible to do that. What I have to do is move the condition (query filter) to the FROM clause instead of applying it to the WHERE clause.

Here is the SQL code that generated the report shown above.

SELECT
  SSG.CUSTOMER.CUSTID,
  SSG.CUSTOMER.NAME,
  SSG.CUSTOMER.STATE,
  sum(SSG.ORD.TOTAL)
FROM
  SSG.ORD RIGHT OUTER JOIN SSG.CUSTOMER ON (SSG.CUSTOMER.CUSTID=SSG.ORD.CUSTID)
WHERE
  SSG.ORD.ORDERDATE  BETWEEN  '01-01-1987 00:00:00'  AND  '31-03-1987 00:00:00'
GROUP BY
  SSG.CUSTOMER.CUSTID,
  SSG.CUSTOMER.NAME,
  SSG.CUSTOMER.STATE

To fix this, I need to go back to my universe and fine tune the join settings for my outer join. I showed this same screen earlier but this time I am going to click the Advanced button so I can fine-tune my join behavior.

Clicking that button gets me to this screen.

Of the options shown, I can be selective or all-inclusive. If I want every single object from the impacted table to appear in the FROM clause for a condition, there is an easy setting to do that. In this example I am going to specify that only the Order Date object goes into the FROM clause.

The “Fix” In Action

After making these changes and exporting my universe, I can open my Web Intelligence report and see what happens. Here is what the report looks like.

And here is the SQL code.

SELECT
  SSG.CUSTOMER.CUSTID,
  SSG.CUSTOMER.NAME,
  SSG.CUSTOMER.STATE,
  sum(SSG.ORD.TOTAL)
FROM
  SSG.ORD RIGHT OUTER JOIN SSG.CUSTOMER ON (SSG.CUSTOMER.CUSTID=SSG.ORD.CUSTID  AND
  SSG.ORD.ORDERDATE  BETWEEN  '01-01-1987 00:00:00'  AND  '31-03-1987 00:00:00')
GROUP BY
  SSG.CUSTOMER.CUSTID,
  SSG.CUSTOMER.NAME,
  SSG.CUSTOMER.STATE

Is this better or worse?

Remember that earlier I talked about how an outer join condition can make a report ambiguous. I remember (from looking at my full data set earlier) that there is only one customer in my database that has no orders. Yet in the results shown above there are two customers showing with no orders. One of them has no orders. The other has no orders within the time frame specified in the query filter.

Before moving the order date condition into the FROM clause here are the steps my database was using to create my report dataset:

1. Get a list of orders
2. Join the customers to the orders; include all customers even if they do not have a matching order
3. Apply the WHERE clause to restrict the results to those orders in the date range

Since the WHERE clause happens after the join has been done, it essentially invalidates the outer join process. Customers with no orders have a NULL order date, and NULL orders do not exist between any date range. Therefore the rows are dropped.

Here is the modified process with the order date moved into the from clause:

1. Get a list of orders, limited to those orders within the date range (the date condition is in the FROM clause now)
2. Get a list of customers, and join that list to the list of orders returned in the first step. Customers do not have to match up with an order

In the modified version, the order date filter is still used to reduce the list of order rows, but it happens before the join to the customer list. That means every customer still shows up. It’s all in the sequence of operations, and that sequencing is controlled by whether conditions appear in the FROM or WHERE clause.

Which version is correct?

Conclusion

Ultimately it is up to the business user community and the universe designer to figure out which answer is the right answer. I would submit that removing the outer join in this case is the best solution, rather than trying to tweak how it behaves. If I am asking for oder-related information, to me it stands to reason that I only want to see customers with orders. I would rather do that than try to explain all of the different possible answers that users could get with an outer join.

If I want to get a list of customers that have no orders, there are more effective ways to do that than use an outer join. That may be another blog post. But if I need to include an outer join in my universe, at least it’s nice to know I have options as to how it behaves. In this example I only tweaked a single object but as I mentioned I can extend the behavior to an entire table by selecting a different option. The “Advanced” button has been available since version 6 (if my memory is correct) and certainly appears in all versions of XI.

Addendum

Like many posts, this one was inspired by a question posted on BOB. The poster tried to use the Advanced tab, and was told that the join was “too complex” because it was a two part key. In my simple example I only had one-part keys for my joins. The easy way to work around this is to create two simple joins between the tables instead of a single complex join. By doing that the Advanced button works as I described in this post. Just make sure both simple joins are in the same context(s) if the universe includes that feature.