none
Confusion regarding TIME dimension table in Sales data model

    Question

  • Please check out the sales data model mentioned on this link :  

    http://learndatamodeling.com/blog/category/data-modeling/data-modeling-types/

    I have below queries :

    1. Why DATETIMESTAMP field is mentioned in all tables?.. Isn't it wrong?  It should only be mentioned in SALES table . Is it really needed in PRODUCT table or ORGANIZATION table ?

    2.  Relationship between SALES table and TIME table is shown incorrectly according to me.  It should be ONE (TIME) is to MANY(SALES) . I have seen both types of relationships in different models given on different websites. In some model, it is ONE (TIME) is to MANY(SALES)  and in other models it is MANY(TIME) is to ONE(SALES) .

    Please help . Thank you.

    Thursday, May 24, 2018 12:47 PM

All replies

  • 1. Why DATETIMESTAMP field is mentioned in all tables?.. Isn't it wrong? 


    No, that's correct, that are SCD2 = "Slowly changing dimensions" type 2, see https://en.wikipedia.org/wiki/Slowly_changing_dimension

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, May 24, 2018 1:09 PM
    Moderator
  • Thank you Olaph but my 2nd question is still unanswered.

    Also, regarding datetimestamp field I checked the link you have mentioned. So, datetimestamp in say supplier table suggests till what date or time the supplier was exists . Correct me if I am wrong. I think this would help us in queries like - ' When did last order was placed from particular supplier ?'

    Friday, May 25, 2018 10:16 AM
  • Thank you Olaph but my 2nd question is still unanswered.

    Also, regarding datetimestamp field I checked the link you have mentioned. So, datetimestamp in say supplier table suggests till what date or time the supplier was exists . Correct me if I am wrong. I think this would help us in queries like - ' When did last order was placed from particular supplier ?'

    yes

    It determines the point in time from which a particular entry is valid

    You can either use single date column or two columns (start and End). In latter case it represents date range within which the entry is valid. FOr the latest entry you give a very large max date value for End column or can even be left NULL

    The entries in the fact table will have corresponding surrogate key to link to the current dimension entry valid for the period based on the transaction date

    Now for your second question, where does it say many TIME to single SALES? it just shows the direction from TIME to SALES fact which is how it is in the star schema. The surrogate Key of the Time would be referred as FK inside SALES fact.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, May 25, 2018 11:42 AM
  • I think this would help us in queries like - ' When did last order was placed from particular supplier ?'

    Not with timestamp in Dimension, they have a different Intention.

    Let's say you have a turn over report by customer state. A customer moves from state A to state B; shall his turn over move to in your Report?

    When you have in dimension customer to Information "lived from 2010-2012 ins state A, from 2013 in state B" then you can report turn over by state where it was really done, not where your customer currently lives.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, May 25, 2018 11:58 AM
    Moderator
  • You can either use single date column or two columns (start and End). In latter case it represents date range within which the entry is valid. FOr the latest entry you give a very large max date value for End column or can even be left NULL


    Thanks.. This is really helpful and lesser confusing.

    Looking at the arrow directions I got confused. Reading your reply I got to know those do not indicate relationship like one to many or vice a versa.

    Monday, May 28, 2018 9:02 AM
  • I think this would help us in queries like - ' When did last order was placed from particular supplier ?'

    Not with timestamp in Dimension, they have a different Intention.

    Let's say you have a turn over report by customer state. A customer moves from state A to state B; shall his turn over move to in your Report?

    When you have in dimension customer to Information "lived from 2010-2012 ins state A, from 2013 in state B" then you can report turn over by state where it was really done, not where your customer currently lives.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    We can achieve this by having FROM TIME and TILL TIME  in CUSTOMER table . Then why do we need "Time" related information in "LOCATION" table ?

    "From Time" and "TILL TIME" in customer table indicates duration of customer in particular state. Say, PIN code is primary key in location table, then we are having PIN code in customer table. from this information we can easily find out the output you expect. sorry if I am confusing you. :(

    Monday, May 28, 2018 9:17 AM