0

I am currently designing a dimensional model where there are dimensions like

orders, product, shipment, returns, items.

My goal is to calculate metrics at day level and populate the fact table.

The metrics are count of orders per day, total gross sales per day, total net sales per day.

I have designed the dimensions and the problem I am facing is how to design my fact table. As I only need these three metrics, I want my fact table to have the below four fields in my fact table to have a better performance.

But thing is I am worried about is will it be okay if I leave the dimensions isolated without connecting to the fact table?

Please kindly advise me on this. Any help would be appreciated. Thank you.

Marcus D
  • 1,074
  • 1
  • 12
  • 27
Teja
  • 13,214
  • 36
  • 93
  • 155
  • I'm not sure I understand. What will the dimensions do, if they don't connect to the fact table(s)? If you could add your table design, and perhaps a few sample records, it will help us answer your question. – David Rushton Apr 07 '17 at 07:59
  • 1
    This is not a fact table, it is just a (simple) report. This is the result to having queried your fact table without filter on product, shipment, return item, and choosing the time on date dimension. Not connecting the dimension would mean that you are only able to do THIS report, and it would be a very poor dwh indeed. – momobo Apr 07 '17 at 09:18
  • If you mean 'do I need there to be a foreign key relationship defined' then no, you don't, although many still choose to. – Rich Apr 08 '17 at 22:45
  • You don't need foreign keys in a data warehouse. Referential Integrity can be handled by your ETL. This also allows hot-swappable dimensions – Neil McGuigan Apr 12 '17 at 18:37
  • I think @Teja is wondering how do I report a new customer who has ordered nothing (and thus has no record of orders in the Fact table), but exists in the Dimension table?? Is that so? – Marcus D Apr 20 '17 at 12:31

1 Answers1

0

If you only want to calculate metrics like - count of orders per day,total gross sales per day, total net sales per day. You can directly do it from the transactional table.

If you are doing dimensional modelling your fact table should have granular details, in your case, it should order line item. You will have to link your dimensions to the fact so that you can perform aggregations at different level and Slicing/Dicing on already aggregated data to show details.

Now, if you are doing all above and still want to show metrics at daily level for count of orders and sum of gross/net sales then you can create an aggregate table for just this purpose. Aggregate table only if you want it to be pre-calculated, otherwise you can perform aggregation on Fact itself and get the result. This is in general, your actual use case scenario might help to explain further.

Siddharth Shukla
  • 76
  • 6