Calculations Defined by Business

View previous topic View next topic Go down

Calculations Defined by Business

Post  kclark on Wed Sep 19, 2012 6:30 pm

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



kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Calculations Defined by Business

Post  Mike Honey on Wed Sep 19, 2012 9:26 pm

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
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: Calculations Defined by Business

Post  kclark on Wed Sep 19, 2012 10:28 pm

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

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Calculations Defined by Business

Post  ngalemmo on Wed Sep 19, 2012 10:36 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Calculations Defined by Business

Post  ngalemmo on Wed Sep 19, 2012 10:41 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Calculations Defined by Business

Post  kclark on Wed Sep 19, 2012 11:11 pm

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.

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Calculations Defined by Business

Post  Mike Honey on Tue Sep 25, 2012 3:29 am

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
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: Calculations Defined by Business

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