Adding Calculated Measures to a fact
5 posters
Page 1 of 1
Adding Calculated Measures to a fact
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...
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...
pcs Posts : 20
Join date : 20090203
Re: Adding Calculated Measures to a fact
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.
hang Posts : 528
Join date : 20100507
Location : Brisbane, Australia
Re: Adding Calculated Measures to a fact
Thank you Hang. I appreciate the guidance
pcs Posts : 20
Join date : 20090203
Re: Adding Calculated Measures to a fact
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
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
Re: Adding Calculated Measures to a fact
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.
pcs Posts : 20
Join date : 20090203
Re: Adding Calculated Measures to a fact
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.
Re: Adding Calculated Measures to a fact
thank you for the clarification and reinforcement. I'll use this
as a rule of thumb, but keep in mind the advice from Hang and Mike as well.create such a column if the calculation involves other data in other tables (currency conversions, for example)
pcs Posts : 20
Join date : 20090203
Re: Adding Calculated Measures to a fact
Yep. There really isn't a right or wrong approach here, the appropriate choice is purely situational.
Re: Adding Calculated Measures to a fact
Agreed, but may I add another condition, 'or if the calculation involves other fact row in the same table'.pcs wrote:thank you for the clarification and reinforcement. I'll use thisas a rule of thumb, but keep in mind the advice from Hang and Mike as well.create such a column if the calculation involves other data in other tables (currency conversions, for example)
hang Posts : 528
Join date : 20100507
Location : Brisbane, Australia
Re: Adding Calculated Measures to a fact
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.
That is also one of the reason why we use accumlated fact and not transactino fact in some cases.
Vishy Posts : 75
Join date : 20120228
Location : India
Similar topics
» Multiple fact tables or Calculated Measure
» Mathematical expression for calculated measures like quantity/quality
» Calculated dimension attributes based on fact
» Multiple Fact Table / Calculated Measure
» Calculated measure value in aggregate fact table
» Mathematical expression for calculated measures like quantity/quality
» Calculated dimension attributes based on fact
» Multiple Fact Table / Calculated Measure
» Calculated measure value in aggregate fact table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum

