Adding Calculated Measures to a fact

[pcs]

Hello All,

What are the best practices to consider when contemplating adding calculated facts to a fact table.

I work for a retail grocer and we have a sales transaction line item fact table capturing price and discount (and other measures) at point of sale.

Are their strong opinions about adding one or more calculated measures to this table? there is a request to add a column that shows the result of price minus discount (net sales amount) This measure fits the grain... but seems extraneous.

There are mitigating circumstances in that different groups might want to see this value differently. (customer service may have a different definition for net sales than finance) - perhaps we need to add multiple columns to cover this...

thank you for your thoughts on this...

[hang]

Given your business circumstance, I would leave them to the semantic layer instead of physical columns in the fact table. It's about the business clarity and convenience rather than performance. Implementing some simple calculation logic in reporting layer instead of ETL process makes those measures more transparent and flexible. On the other hand, if there is a significant performance benefit, or the logics are too complex for user to understand, then using ETL to populate the data in the fact table makes sense.

[pcs]

Thank you Hang. I appreciate the guidance

[Mike Honey]

Hi pcs,

I tend to use the ETL layer to add those calculations to the Fact Table, as far as possible (if the calc requires aggregation then this is usually not appropriate).

My main reason is to try to encourage the business to standardize their definitions and terminology. From an enterprise perspective, should you encourage two different business units to use your warehouse to produce two different results for "net sales"? If you provide a net sales measure on the fact, it can only calculated one way.

From a technical perspective, this also simplifies the report/cube authoring - there is no ambiguity about calculation and report outputs are consistent.

Good luck!
Mike

[pcs]

Thank you for the feedback and advice. I think getting all of the business groups to agree on the calculations for these basic measures is definitely the correct approach. thank you for reinforcing this thought. I struggle with "can" we do something vs "should" we do it. While we can let every business group have their own net sales measure, we probably should not. Thank you again - I will pursue a single definition of this requested measure.

[ngalemmo]

Both Mike and Hang make good points, but I have concerns about physically storing a large number of calculated measures in the fact table. Doing so can lead to bloated tables and poor performance if the number of calculations gets out of hand. I would only create such a column if the calculation involves other data in other tables (currency conversions, for example). For the simple ones based on measures in the fact row itself, I would use the semantic layer in a BI tool, or a view. Using a view gives you the advantage of accessing those calculations at the database level allowing them to function as if they were physically in the table without actually storing them.

[pcs]

thank you for the clarification and reinforcement. I'll use this

create such a column if the calculation involves other data in other tables (currency conversions, for example)

as a rule of thumb, but keep in mind the advice from Hang and Mike as well.

[ngalemmo]

Yep. There really isn't a right or wrong approach here, the appropriate choice is purely situational.

[hang]

thank you for the clarification and reinforcement. I'll use this

create such a column if the calculation involves other data in other tables (currency conversions, for example)

as a rule of thumb, but keep in mind the advice from Hang and Mike as well.

Agreed, but may I add another condition, 'or if the calculation involves other fact row in the same table'.

[Vishy]

Interrow calculations are always complex and tough to implement at report level, you need to put your mind to work again if you find that intrarow calcuations will have to be done.

That is also one of the reason why we use accumlated fact and not transactino fact in some cases.

[Sponsored content]