Simple Calculations

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?

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.

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?

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.

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.