Analytics Software

Hello guys

Here is an interesting situation and I don't think there is a good way to handle it in OBIEE, but I'd like to get some inputs from you guys

In our environment, it is very common that the users don't know their data nor do they really own it. Therefore, when they run a report, more often than none, they don't have any expectation on what they are getting. They will wait until the report comes back with what it is, and make decisions based on the report.

We have a couple of subject areas or places where the fact tables are extended out to join to other fact tables from different business world, such as sales fact to order holds; sales fact to invoice etc. In these cases, not all the sales fact records have matching order or invoice transactions. Therefore, the inner join will only return the datas that are in all 3 business worlds.

What the business wants is to have all the sales order number returns back even if they are not put on hold or invoiced. This can be achieved by outer joins. However I don't want to outer join the fact and all 50 dimension tables (God know when they will pull in new dimensional columns). My understanding of the BI in relational database is that, if there is no item number xxxbbb in the report but it's in Item table, that means xxxbbb is not in the fact table.

So what is the best way to handle situations like that, when they wonder "then why can't you bring back all the item numbers even if they are null? Just like my dear whatever 50-year-old reporting system is doing?"

Any thoughts and inputs will be greatly appreciated, share your experience..