Multiple fact tables and a having filter clause

Hello everyone,

I manage an ad hoc reporting environment on a Oracle 10g server in which in many instances users need to report against multiple fact tables (different grain, sharing dimensions). The client tool (Business Objects) builds the tool based on what the user selects (no free hand SQL). A query is generated for each fact table by BO. If a user happens to add a filter based on the results (having clause filter) there is a mismatch in the results across the queries.


Example:
query 1
select dim1,dim2,dim3,sum(ft1_qty),sum(ft1_amt) from fact_table1 group by dim1,dim2,dim3 having sum(ft1_qty)>0
query 2
select dim1,dim2,dim3,dim4,sum(ft2_qty),sum(ft2_amt) from fact_table2 group by dim1,dim2,dim3,dim4

The report ends up bringing back results from both queries and show blanks for rows that are missing from the filtered query (query 1). The desired result would be for the database to only return those records where there is a match between the 2 queries.

Any suggestions on the best way to handle this scenario would be greatly appreciated,

Benny

BO does a full outer join on multipass queries. One way to get rid of the blanks is to filter them out at the report level. They'll still be in the report cube though, so if you're dealing with a lot of records it could cause your report to be more bloated than necessary. It also potentially requires some education for your users (depending on their skill level).

There might be some other ways I could think of if I put my mind to it. That would be my first solution though. If that's unacceptable maybe we could revisit the issue. Hope this helps.

You need to define contexts for the multiple facts tables and common joins. Once that is done in the universe your problem should be solved.

First, thanks for the help. It is greatly appreciated.

Report filtering would work but like you mentioned, it would make the report much bigger than necessary due to the unfiltered query and it requires the user to know that filtering is necessary, which I am trying to avoid. This is an ad hoc reporting environment where essentially the user is creating the report.

In response to the second suggestion I do have contexts defined which allows BO to break up the queries based on the data selected. However, creating the context does not prevent this issue from happening. The data from the seperate queries is still returned independently.

It could very well be possible that there is no way for the front end tool (in this case BO) to return the results I want. In my company it is very common to use multiple fact tables on one report and this is my BI guru's major complaint. Perhaps a future release of the software will implement it.

Thanks again,

Benny

I don't see contexts being the problem here. Each fact table needs its own context to eliminate loops. BO will automatically do multipass SQL on both fact tables (as it should) and union the results together based on the contexts. The full outer join means that nulls from both sides are returned and shared fields create an intersect between both queries.

I also don't see a problem using multiple fact tables on the same report. You can even do it with data of multiple grains but that takes a report designer with a bit of experience. Sometimes the data lives in different fact tables but the business needs to see it combined.

So it occurred to me after I gave it some thought; have you tried using a combined query? In the query panel build a new query and then add a combined query and change it to "Intersect". You'll need the same dimensions in both queries but you can bring facts in from separate fact tables. That should give you only dimension matches from both sides.

Let me know how that goes.

My situation touches on similar issues, therefore I think I can pursue my questions here rather than starting a new thread.

I am on the Oracle DW (and DB) side. Reporting is done using Business Objects. The BO universe and reports are setup by another group. Dealing with join issue like yours and other issues that that can be solved by writing queries with outer joins in Oracle. Seeing the BO tool set I think that equivalent somewhat complex queries can be produced in BO. However, I am asked to add materialized views in Oracle. I think that the warehouse with the star schemas and additional reference tables should be there without adding materialized views to return record sets for BO. I guess it is a fine line, but I am not sure where it should be?

"I think that the warehouse with the star schemas and additional reference tables should be there without adding materialized views to return record sets for BO"...

Don't think about it that way. It has nothing to do with BO really. If you have frequent analysis that regularly requires joins across fact tables, creating an aggregate is a normal course of evolution in a data warehouse. The trick is to create aggregates that make sense and are useful to many.