0

Working on my first data warehouse project and learning online on the way using tutorials, articles, youtube videos etc.

I'm using data meant to mimic an OLTP database for a dvd rental company, and I'm at the stage of implementing my dimension and fact tables.

The fact table I'm currently building out is an inventory table - I want it to be a snapshot every month of a dvd's stock, including how many dvd were lost that month, and some other aggregates.

One thing I can't wrap my head around though is how to implement a date dimension - currently none of my other attributes capture a timestamp that can facilitate this.

and tutorials online don't really go into the details of how to build a snapshot fact table so that i can define the grain as 'week', 'month' etc.

I don't understand how my fact table can make a reference to a date dimension if my other dimensions don't contain a date attribute?

I'm attaching a photo of my model below in case it helps paint a better picture.enter image description here

Jean-Paul Azzopardi
  • 401
  • 1
  • 2
  • 10
  • I assume by “date dimension” you mean a dimension with one record for each day? If so, why do you want to build one? Your ERD looks fine as it is - your fact table is at the grain of month so you use a month “date” dimension – NickW Jul 16 '23 at 22:15
  • yes apologies, i actually created a new dim month table to stay at the month grain - I guess my question is how do I load data into the fact table so that each record in the fact table corresponds to the correct dimensions? – Jean-Paul Azzopardi Jul 16 '23 at 22:47
  • 1
    In which case, you’d probably be better off asking that as a new question. Show your source data, the result you want to achieve and the solution that you’ve managed to write on your own - and then ask a specific question about an issue you are facing – NickW Jul 16 '23 at 23:13
  • 1
    Does this answer your question? [Building a Snapshot Fact Table](https://stackoverflow.com/questions/76700842/building-a-snapshot-fact-table) – NickW Jul 17 '23 at 11:55

0 Answers0