3

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?

Josh Gallagher
  • 5,211
  • 2
  • 33
  • 60
Lefty
  • 426
  • 3
  • 10
  • Without code, it is usually hard to guess what the exact problem is, and guessing for answers is usually the only thing anybody can do. If you provide a piece of code of your previous attempts, it will be a lot easier to understand what the exact problem you're having is. That piece of code can even be code that doesn't work at all, because it would still give a better idea of what you are trying to do exactly. – Joeytje50 Jan 27 '14 at 16:00
  • 2
    ... what code? it's a ssas question about setting up a relationship between fact and dim tables. I'm talking tables and structure not 'code'. – Lefty Jan 28 '14 at 09:33

2 Answers2

0

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.

FrankPl
  • 13,205
  • 2
  • 14
  • 40
0

You should use the Order ID to lookup the Order Date and put an Order Date dimension key in the Sales Transaction fact table. Since there may be multiple transactions per order, the other way around probably just doesn't make sense. If it is 1:1 you could do the reverse, but it would mean updating order facts once the transaction occurs which could be a load-time complexity and performance hit. Make sure you really NEED order by Transaction Date.

Richard M
  • 1