If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Display Data from 2 Fact Tables

Started by rpvs, 11 Jun 2010 02:48:42 PM

Previous topic - Next topic

rpvs

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???

blom0344

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..

James_Bonnell

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.