Adding Calculated Measures to a fact

View previous topic View next topic Go down

Adding Calculated Measures to a fact

Post  pcs on Tue Feb 07, 2012 4:32 pm

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...





pcs

Posts : 20
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Adding Calculated Measures to a fact

Post  hang on Tue Feb 07, 2012 8:22 pm

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 : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Adding Calculated Measures to a fact

Post  pcs on Wed Feb 08, 2012 7:16 am

Thank you Hang. I appreciate the guidance

pcs

Posts : 20
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Adding Calculated Measures to a fact

Post  Mike Honey on Mon Feb 13, 2012 9:06 pm

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
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Adding Calculated Measures to a fact

Post  pcs on Tue Feb 14, 2012 10:38 am

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 : 2009-02-03

View user profile

Back to top Go down

Re: Adding Calculated Measures to a fact

Post  ngalemmo on Tue Feb 14, 2012 7:05 pm

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.

avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Adding Calculated Measures to a fact

Post  pcs on Wed Feb 15, 2012 2:24 pm

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.

pcs

Posts : 20
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Adding Calculated Measures to a fact

Post  ngalemmo on Wed Feb 15, 2012 5:28 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Adding Calculated Measures to a fact

Post  hang on Wed Feb 15, 2012 7:26 pm

pcs wrote: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'.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Adding Calculated Measures to a fact

Post  Vishy on Fri Mar 09, 2012 5:06 am

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.

Vishy

Posts : 75
Join date : 2012-02-28
Location : India

View user profile

Back to top Go down

Re: Adding Calculated Measures to a fact

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum