Simple Calculations

View previous topic View next topic Go down

Simple Calculations

Post  daredevil on Tue Aug 17, 2010 8:43 am

Where should I place simple calculations such as A/B where A and B are fields in the database. Should the calculation exist in the database as a separate column or should it be a calculation in the report. What are the pros and cons of both approaches? What are the variables that could help us decide?

daredevil

Posts : 9
Join date : 2010-08-05

View user profile

Back to top Go down

Re: Simple Calculations

Post  ngalemmo on Tue Aug 17, 2010 11:40 am

Generally such calculations are defined in the BI metalayer that supports reporting. Also, many databases (SQL Server and Oracle 11 for example) support the definition of virtual columns in a table (much as you would in a view) by specifying a column expression that does the calculation.

They are almost never manifest as a value in a table, primarily due to the fact such calculations are often the product of business rules which are subject to change.

If you are not using a BI tool, and virtual columns are not an option, such calculations are usually defined in views.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Simple Calculations

Post  daredevil on Tue Aug 17, 2010 2:04 pm

Thanks...I do have a metalayer....I wanted to put it into the database so that the processing is all on the database side. I agree that its a simple calculation and won't hurt the reports if its in the metalayer, but, i couldn't think of a reason why it would hurt if its in the database.

What if we look and the business rule seems like it wont change such as Profit=Selling Price-Cost Price?

daredevil

Posts : 9
Join date : 2010-08-05

View user profile

Back to top Go down

Re: Simple Calculations

Post  ngalemmo on Tue Aug 17, 2010 3:17 pm

It doesn't 'hurt', it is simply more difficult to change if the value is materialized as a column. And, ANY calculation, no matter how simple it may be, is subject to change. Besides, almost all the BI tools push the calculation down to the database anyway.

Where you run into problems is if you take this to extremes, where your fact table has more derived columns than real ones. The width of the row has a direct impact on query performance. The slowest operation in any query is reading disk... the less you have to read, the better performance will be.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Simple Calculations

Post  Guest on Wed Aug 18, 2010 7:02 am

I would add in the Profit calculation example you gave above as an extra column. Anything which involves division I would consider a bit more carefully, simply because you may be creating semi-additive measures, which can confuse the end user. An example is a percentage, that is valid for that particular row, but you cannot then sum the percentages up to obtain an average percentage for a set of customers. If your metadata layer is business objects, you could use aggregate navigation to get around this.

My rationale for adding certain calculated columns to the database is that it provides for more efficient query access - the database engine is not having to perform the calculation for each row processed. It becomes even more inefficient if the calculation also ends up in a 'having...' clause.

You know best which business rules are likely to change - the profit example is one example of a benchmark measure that is very unlikely to change. Others we have include such items as sales value less sales tax and sales values less discounts.

Guest
Guest


Back to top Go down

Re: Simple Calculations

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