histogram in data warehouse

I got a ton of samples in the following format:

 <timestamp> <dim1> <dim2> <dim3> 324
 <timestamp> <dim1> <dim2> <dim3> 3565
 <timestamp> <dim1> <dim2> <dim3> 122
 <timestamp> <dim1> <dim2> <dim3> 2333

324, 3565, ... are the measurements, and I would like to store these in a data warehouse.

The measurement will be rounded to the nearest hundred (300, 3600, 100开发者_运维问答, 2300).

How can I store this and quickly be able to create a histogram of the facts?

One (bad) way is to have a column in the fact table for each "bucket" (ie 100, 200, 300, ...), and then use SUM() on each column to build up the histogram. The drawbacks are obvious: the fact table will be huge and non-flexible (limited range of samples)

Anyone got any clever ideas?

Well, you have to discretize somehow. One way -- providing that your bucket ranges do not change frequently -- is to add a table with ranges (dimBucket) and assign a bucket key to each value during the ETL. If value ranges ever change, the whole fact table has to be re-calculated to assign new BucketKeys. So, something like this -- note that ValueRange is a string like '100-199', '200-299', ...

SELECT
  ValueRange
, sum(SaleAmount)  AS SaleValue
, count(1)         AS Transactions
from factSale  AS f
JOIN dimBucket AS b ON b.BucketKey = f.BucketKey
GROUP BY ValueRange;

I find it hard to work with generic <dim>, <dim>, <dim> so I have used my own example.

The other way is not to think about it on the DW level, but to use an analytic package. Almost every analytic/data-mining package offers several types of discretization. Try Weka, it's open source and quite good; R is very popular too.