I work for a retail company and we are in the process of building a new data warehouse. One element we need to store is physical stock data as to how many units of any sku are available at a certain location at a certain time. the variables we want to look at are, therefore: skus, sites, day, hour and quantity.
The problem is that there are approximately: 100k skus and 200 stores. If I store data for every hour I would get 100k x 200 x 24 = 480,000,000 lines a day. I understand that not every sku has a quantity > 0 at every site. Let's assume sparsity is as high as 80% and when the quantity is 0 we don't store it so we remain with 96,000,000 rows a day. Moreover, even though we have websites and sales won't stop at night we may assume that night hours will have very little changes in quantities and that not every sku is sold every hour. Let's assume that on average a sku is sold only on 3 different hours a day. This brings us down to 12,000,000.
This is 2.5% of what we initially looked at, however, it is still a considerable amount. 12,000,000 rows every day is going to occupy a lot of space after a few months. A partial solution to the problem is what Kimball suggests in The Data Warehouse Toolkit which is to store data for each hour for the last month or so and then only daily for the last n months and then remove older records. Considering that we are already assuming that on average we sell a sku only on 3 different hours per day switching to daily data from hourly data will reduce only by two thirds. Let's assume I want to save stock data for the last six month. One month at hourly level and five months at daily level (let's also assume 30 days months to ease the math) this is going to be: (12,000,000 X 30) + (4,000,000 X 150) = 960,000,000 rows.
I really can't think of a better way to do this, had anyone had to work with physical stock data before? Any useful lesson you learned? Is there a more efficient way to store this data?