Calculations Defined by Business

Hi All -

I need to have a table in the database which contains calculations (defined by the business). The table should contain the new metric name and the calculation which consists of columns in table(s). For example, Metric: Profit; Calculation: Revenue - Cost. Revenue and Cost are columns in Table A. I need a way to combine Table A with the Calculations table so that I can pull the metric Profit by a specific date or whatever. I know this can be done in the metadata layer of a BI Reporting tool BUT these are business rules that need to be built in so that the users can't alter the calculations OR so the Report developer doesn't have to recreate/update the calculations over in other models.

I'm at a loss as to how to accomplish this...please help me.

Running out of ideas

Hi kclark,

I've seen the approach you describe attempted many times - it never seems to be successful. I recommend you stick to the tried and tested Kimball methodology and materialise the result of your calculations as extra columns. This will meet your requirements as per your last two sentences.

The usual counter-argument is that "the calculations might change" - IMO this is a sop to avoid more rigorous business analysis and requirements specification. Any decent ETL process will support managed changes to calculation definitions.

Some would do this in a View but I prefer to actually add columns in the fact table - your ETL process will have more flexibility than a view definition and you have control over the grain.

Good luck!
Mike

Thanks Mike,

Let's say...we complicate things. What if the calculation involves one field from one fact table and a second field from another fact table? Would this then mean a separate view instead of the extra columns?

Krystal

I know this can be done in the metadata layer of a BI Reporting tool

That is often where it is done. It depends a lot on the specific tool you use. There are a lot of BI tools that allow you to protect metadata from unauthorized changes.

kclark wrote:Thanks Mike,

Let's say...we complicate things. What if the calculation involves one field from one fact table and a second field from another fact table? Would this then mean a separate view instead of the extra columns?

Krystal

There are basic query patterns that deal with this. A lot of BI tools implement that pattern and will successfully integrate measures from different fact tables.

It becomes a matter of finding the right tool and adjusting the model (create aggregates, summarizations) as business needs or performance demands warrant it.

Thanks ngalemmo,

I am totally with you. I am architecting the datawarehouse for the "modeler" to use in the BI Reporting Tool, Spotfire. Not my choice of tools. :-)

The "modeler" states that because of the complexity of the aggregations/summarizations, it would greatly impact efficiency in the tool and to repeat that complexity for separate models. In that aspect, I agree. I just hate to ETL something like that when it can be so frequently changed. So I feel a view or...would a physical table be more appropriate?

This is very helpful.

Hi Krystal

I'd prefer a physical table in that scenario. It might need to be a new Fact table with a more summarized grain.

Good luck!
Mike