From: johansson.andersson@telia.com   
      
   One idea.it might be possible to have a date for each row about the sales.   
   So in this example the icecream Päronsplit have changed price 2015-05-23   
   from 7 to 10   
   So here you could drill down to see the sales for week. You could also add   
   one more column   
   where you indicate that date X we had a big advertisement and you might want   
   to see the effect on sales   
   for this drive. You would hope that when we have a big advertisement we want   
   to sale more ice cream.   
      
   Sales table   
    Date                        Ice cream            City               Price   
   Quantity   
    2015-02-01             Päronsplitt        Stockhomn       7              3   
    2015-02-02             Top hat            Göteborg          5   
   7   
    2015-02-03             Ice pig             Malmö             11   
   0   
    2015-02-04             Piggelin           Sundsvall          7   
   3   
    2015-02-05             Sandwith           Luleå              9   
   1   
   . . .   
    2015-05-23             Päronsplitt        Stockholm       10            3   
      
      
   //Tony   
      
      
   "Tony Johansson"  skrev i meddelandet   
   news:mbr037$noc$1@dont-email.me...   
   >I mean to put the price as a column in the sales tables doesn't solve the   
   >problem because you would overwrite the previous price.   
   >   
   > //Tony   
   >   
   > "Erland Sommarskog"  skrev i meddelandet   
   > news:XnsA442CB2B72BBEYazorman@127.0.0.1...   
   >> Tony Johansson (johansson.andersson@telia.com) writes:   
   >>> Assume I have a table called Sales in the dataware house where we have   
   >>> the   
   >>> four columns Month, Ice cream, City and quantity.   
   >>> We have three dimensions where we have Month, Ice cream and City.   
   >>> It could look like this   
   >>> Sales table   
   >>> Month        Ice cream        City                Quantity   
   >>> January      Päronsplitt        Stockhomn       3   
   >>> January      Top hat            Göteborg           7   
   >>> January       Smugler           Malmö              13   
   >>>   
   >>>   
   >>> Now to my question. It is important to keep historic data in the   
   >>> dataware   
   >>> house.   
   >>> So assume that I want to keep history of the price that have been   
   >>> existing   
   >>> on different ice cream how   
   >>> would this be structured in the sales table.   
   >>   
   >> I don't do data warehouses, but my gut reaction would be to have a table   
   >> with three columns ProductID, Date and Price.   
   >>   
   >> Then there is a choice whether to have a row only for the dates when the   
   >> price changes or have a price for every day. The former saves space, but   
   >> it   
   >> makes the table more difficult to query. The latter takes up space, and   
   >> you need a mechanism to copy prices from day to day.   
   >>   
   >> In the system I work with, we use the model with one row per day, no   
   >> matter whether the price change or not, but there are two remarks:   
   >>   
   >> 1) We made this design with SQL 6.5 where writing a query to get the   
   >> price per a certain date was difficult and not always performant.   
   >> With SQL 2005 and CROSS APPLY, this is a lot easier.   
   >>   
   >> 2) We hold prices for financial instruments and FX rates, which typically   
   >> change from day to day anyway, whereas the price for many retail   
   >> products like ice cream is fairly static.   
   >>   
   >>   
   >> --   
   >> Erland Sommarskog, Stockholm, esquel@sommarskog.se   
   >   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)