i Have a Sales Fact And an Invoice Fact. When an order line item from Sales fact gets shipped and invoiced it disappers from Sales fact and will be available in invoice fact. I want to report on all the line items for an order some of which are in Sales Fact and some of which are in invoice fact (Sales Order Number being common) between them. I tried join query which created query3 ( query1 - sales fact , query2 - invoice fact) and i pulled data items from query2 in query3 so that i can display in the report, All i am seeing in invoice fact and nothing from sales fact.
Can somebody please suggest me an option of getting this done???
Multifact may be very messy in your case. My suggestion (as quite frequently) is to approach the sales and invoice fact by 2 queries and instead of joining try using a union. This way you are guaranteed to see orders that only exist as orders (no invoice yet), lines spread over both facts and orders already removed from sales and all lines transferred to invoice.
By using a union you will never miss out on data. You group on the sales order number from the resultset and you have a rather simple grouped list as a result..
Also, you might look at how your date dimesion(s) are defined.
For instance, we had a date dimension that (via views) went against several different kinds of dates - but some were shared and some were not. It didn't actually end up working correctly until I added another view to a "budget date" so that Budget.Budget Date was actually using a different "date" than Expense.Budget Date. I was getting some very strange results until I did that.
Mind you, I don't know if that's your problem but it's one of the things I would think about.