Model a relationship between two fact tables
I have a Sales fact table, an Orders fact table (both line level detail), and two date roleplaying dimensions (from the Date dimension) for Order Date and Transaction Date.
I'm trying to get to a point where you can view sales measures by order date and order measures by transaction date.
The Sales table has the key for the related Order line if the sale was from an order and null if it was a non-order sale. The Order table doesn't have any links to the related transaction.
I've been trying to wrap my head around how to model a relationship based on the link between the two fact tables and the only method I can get to work would be to create a dimension based on the Orders table which contains only the key, then use many-to-many relationships... which somehow seems completely wrong, but I'm not sure what would be the "right" approach to this situation.
If at all possible I'd like the non-order sales to show as "unknown" order dates when viewing Sales Measures by Order date, so you can see the complete picture rather than just sales from orders. Using the above approach this isn't happening.
Any suggestions about what needs to be changed to get this to work?
Answer
You were on the right track. I would create a view in the relational database or a named query in the DSV containing as the single column the distinct non-null order IDs, maybe call it "DimOrderId". Then build a dimension from it, setting the "Null processing" property (you have to click the "plus" two times for the "Key Columns" property of the attribute in BIDS to access this property) to "UnknownMember".
And then use this dimension for the many-to-many relationship.