CODEWITHSUNDEEP
database-designdata-warehouse
Andi
Andi

Reputation: 3

DWH fact table design (calculated measure)

I have a measure called Sales which is calculated by

Is it a good choice to have a fact table with three columns (UnadjustedSalesValue, AdjX, AdjY)?

I think another way could be to have an Account dimension or separate fact tables for each ratio.

Upvotes: 0

Views: 1369

Answers (2)

Pondlife
Pondlife

Reputation: 16240

Assuming that the 3 values are at the same level of granularity, I would go for 4 measure columns on the same fact table: UnadjustedSales, AdjustmentX, AdjustmentY and AdjustedSales (or whatever names make sense). Whether or not you physically implement AdjustedSales as a calculated measure or as a discrete value depends on your data and toolset.

The reason is that assuming that your "adjustments" are things like sales tax, shipping costs or customer discounts, then report users are very likely to be interested in them individually as well as in the final AdjustedSales value. If they haven't already expressed an interest, they almost certainly will, especially if customer discounts are involved (i.e. who is getting how much discount?).

Having said that, it isn't clear what sort of adjustments you're referring to so I'm not sure how important this point is for you. But as a general rule, data warehouses aim to provide as much information at the lowest level of granularity possible, so I think the principle is sound.

Upvotes: 1

MatthewMartin
MatthewMartin

Reputation: 33143

Unless there is something different about the dimensions (i.e. these metric are only available on a national level, these are only available on a state level) I'd put these all into the same fact table.

This would be true for schema design in a non-snowflake pattern world. Think carefully about why you might want to create a 1:1 table where the relationship is obligatory (i.e. there always is 1 row in the one table and exactly one row in the other). Usually these patterns show up when there is a peculiar technical reason, such as wanting to store a large column on a different disk.

Upvotes: 0

Related Questions