From: esquel@sommarskog.se   
      
   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)