0

I have a fact table with Store Account data on orders. Every Order currently - 1 per line. I have been informed that the FACT table should now only show the Latest Order Number for Reporting. However they want an option to display every order associated with an account as well .

If I change the granularity of the FACT table, to now house the latest order. I could use a modified Order Dimension to give me the secondary list of orders by adding Account Nos in, so it can join on order number to give info on latest order and join on Account to give all the order numbers .

However I feel there may be a better approach I may be overlooking ? Any ideas .

James Khan
  • 773
  • 2
  • 18
  • 46
  • 1) have a dimension that is `is_latest_order_number`, then each time you add a record check if there is a record to update to No, and insert the new record with Yes. 2) Have two fact tables, one with just the latest orders, and one with all historic orders. – MatBailie Mar 22 '21 at 15:38
  • Without knowing more, however, I'm just grasping at straws for you. For example, do you ever need to know the other facts about the old order numbers, or just that those order numbers exist. There's simply not enough information here to make a properly informed judgement. – MatBailie Mar 22 '21 at 15:39
  • Also consider a snowflake schema, which is essentially a star schema with more than one fact table; one for accounts and one for orders. – MatBailie Mar 22 '21 at 15:43
  • Keep *coll* and change nothing! Calculate the *last order* on the fly in the report. – Marmite Bomber Mar 22 '21 at 15:50
  • Too many calculations in the FACT table on new line level to keep old orders. Having a second FACT table an interesting option – James Khan Mar 22 '21 at 17:39
  • Literally just need a list of orders for each account kept somewhere . Junk dimesnion ? – James Khan Mar 22 '21 at 17:40
  • @MatBailie - Please pop your comments into the answer and I will tick it and give you points ? – James Khan Mar 23 '21 at 10:04

0 Answers0