We have a transaction table. Not only does sales team want to analyze by CustomerId or Productid sold, but also by sales amount sold or quantities. Kimball states, a fact table here can also be a dimension. In that case, could I leave by transaction table as a large dimension table? Just want to understand Kimballs article below.
It seems that
amount sold and
quantities sold directly relate to a transaction and I would think those items should be in a transaction fact table and not on a dimension.
If quantities are especially interesting (e.g., enough to have a hierarchy), one might create a dimension for it, in addition to the
Quantity field in the fact table. Such an
OrderSize dimension could have fields like
QuantityBand with values like "1 to 10" and "11 to 50", and
Can a Transaction Table also be a Dimension?
The answer is yes, it can. Should it? Probably no, but dependent columns are not unheard of. Just know that you are wasting tons of space with repeating columns. Also, the term is Dimensions vs Fact tables. Dimension tables describe the data while Fact tables describe what they are doing.