Business Objects and joins across two (large) fact tables

In a business objects universe, I have several fact tables which are related by various dimensions. If I want to select facts from two of the fact tables, business objects will generate two separate queries, run the SQL and join the two results sets in the reporting tool. There are two issues I have around this:

1) This works fine for small datasets, but becomes unwieldly when users need to aggregate data from the lowest level tables. We have some summary tables but don't cover off everything - how do others deal with this situation. If I was solving this in SQL, I would write the query as two nested selected and push the processing down to the server (which would then return the summarised results set) but Business Objects does not have this option.

2) How do people handle this situation when the join between the two fact tables wants to be on a degenerate dimension - for example. as a retail business we have invoices, and customers may return goods for which they will receive a credit. So occasionally a report may be required that gives invoices and any corresponding credits. To enable this join I would have to link the fact tables which is not correct and creates all sorts of other issues with the contexts. For the moment, I have Invoice Number as a dimension which isn't right, but resolves the problem. I am re-engineering parts of the data warehouse to reflect changing reporting requirements and data structures, and wold be interested to hear how others have resolved this issue.
Thanks.

On #1... That's just the way BOBJ works. It correctly does two summarized queries, but the fact it receives both result sets and merges in the client it the way they set things up. It's a little odd they don't do subqueries (and push the subset join to the DB), because, if you define a derived table, it is embedded as a subquery in the SQL it generates. It is possible there may be a setup option to change this behavior, but I am not aware of one. Submit a request to them to provide that facility...maybe they will put it in a future release.

On #2 I don't understand your distinction between degenerate dimensions and "making invoice number a dimension". Do you mean from a BOBJ sense? Invoice number should be an attribute/dimension object in a Universe...

Sorry for not making my self clearer. We've actually had a similar discussion before on another thread. By making the invoice number into a dimension, I actually mean having a dimension table that contains invoice number and little else. This then provides the connectivity between two fact tables and can enable some joins to be performed within the database rather than back in BusObj. It's a breakage of the dimensional model really, but provides a structure in BusObj to enable the joins.

I am questioning the technique to see if someone else has a better way of doing it. I can expose the invoice number from the fact tables as dimensions within business objects, but if you try to specify credits.invoice number = invoices.invoice number is the condition panel it will give back an 'incompatible objects' error message as you are trying to join across two contexts.

Yeah, you are right.

Creating a Invoice table is not the way to go...

I dealt with this a long time ago and don't remember how...

Things you can try:

1. Define the relationship between degenerate values in the Universe and add the relationship to one (or maybe both) contexts. And set the relationship cardinality to 1-1 so BOBJ doesn't choke...

2. Don't change the context and include the invoice number in both queries... maybe BOBJ will match them up on the merge.

Philip,

I'm curious if you came to a solution to this problem? I'm facing almost the exact same problem.

Here's the real crux of my problem: I'm dealing with shipped cases and the line items representing items inside the cases. There are measures (physical dimensions) of the case which are associated only with the case (header). Other measures, such as shipping cost, are easily allocated to the line items by a formula using weight and volume. However, the outside case physical dimension measurements cannot be allocated in this manner.

My initial thought was to create separate fact tables for the header and detail to house the header-only measures. This is the model mentioned earlier in this thread. This seemed like it would be a perpetual problem in our Cognos framework and query design.

I think I now have a solution, but I want to see if others have successfully used this approach and if I'm missing anything.

1. Add the Carton H/W/L measures to the detail fact table.
   
2. Insert a row for every carton into the detail table. However, this would not be a summary row. This would use the "line number" of 0. All line-detail specific dimension keys would be set to the value for "Unknown". All of the line-detail measure values would be set to 0. Carton-level measures and dimensions would be set/attached the exact same way that they would be for a carton fact table.
   
3. All of the carton-level measure values would be set to 0 on the other lines.
   

Advantages:

• Cardinality issues with queries auto-generated from our Cognos framework model essentially disappear.
  

Drawbacks:

• All the carton-line-detail rows would have added several bytes for the 0 value carton-level measures. This use of data storage space will be minimized by using DB2's Value compression feature.
  
• All the carton-level lines would have added several bytes for the "unused" dimensions and the 0-value line measures. Most of our unknown SK's are 0, so we will turn on compression for these measures and dimensions as well.