operational complex logic requirements

View previous topic View next topic Go down

operational complex logic requirements

Post  guyka on Sun Mar 03, 2013 3:14 pm

Hi,
in one of our BI projects there are extensive complex requirements for heavy operational calculations and algorithem as part of the ETL process.
the project is based on manufacturing system which developed in-house and has no real reporting functionality as part of it.
the ETL tool we are using is informatica and reporting tool is QlickView.
My question is - what will be general recommendation for implementing complex logic and algorithem. my major concern is that by writing such complex SQL scripts and functions the project will be expose to failures and problematic future maintanance.

guyka

Posts : 4
Join date : 2010-01-10
Age : 48
Location : Tel Aviv

View user profile

Back to top Go down

Re: operational complex logic requirements

Post  ngalemmo on Mon Mar 04, 2013 5:16 pm

I feel your pain. The best advice I can give you is make the risks clear, and document things as best you can.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: operational complex logic requirements

Post  guyka on Tue Mar 05, 2013 8:29 am

Thanks.

guyka

Posts : 4
Join date : 2010-01-10
Age : 48
Location : Tel Aviv

View user profile

Back to top Go down

Re: operational complex logic requirements

Post  marcin.wizgird on Tue May 07, 2013 3:56 am

You may be tempted to build some custom, SQL-based measure calculation engine. Personally, I would dimish the SQL usaage in the ETL implementation to the absolute minimum. You can consider architecting the metadata-driven BI solution. Informatica offers you some capabilities for designing flexible and reusable component such as mapplets or Visio Mapping Architect.
In case you decide to implement custom measure calculation engine, then ensure that this will be standard, reusable building block for all applications on your data warehouse platform.
avatar
marcin.wizgird

Posts : 4
Join date : 2013-05-07

View user profile

Back to top Go down

Re: operational complex logic requirements

Post  BoxesAndLines on Tue May 07, 2013 11:08 am

I would try to put it in the ETL. Any heavy lifting done by the BI tool just ends up slowing down the reports. Plus you do it every time a report is executed.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: operational complex logic requirements

Post  Mike Honey on Tue May 07, 2013 7:52 pm

I agree with BoxesAndLines - the ETL layer is the best place for this. As well as SQL, you can use the transformation functinality of your ETL tool. It's also well worthwhile to materialize the calculation results as columns in tables. This speeds query performance and helps testing and review.

Trying to do this in the BI tool results in a "black box" which hinders testing. In real world scenarios you often find the calculation requirements are impossible or very difficult to achieve in BI tools alone (sorry vendors).
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: operational complex logic requirements

Post  LAndrews on Thu May 09, 2013 2:56 pm

Having been through a similar scenario, I agree with all the comments thus far.

Keep a few things in mind when designing your ETL solution.

1. The calculations will change over time. Try and minimize the effort required to accomodate the changes.
2. The more complex the calculation, the greater chance of errors or perceived errors. Your solution needs to be transparent and easy to debug. As MikeH suggests, keeping the intermediate results stored in tables will greatly aid in any analysis.
3. Keep well defined metadata about the calculations. Most of the consumers of the data may not be intimate with the calculations, so you need to provide them the calculations in a clear format. (e.g. you can't send end users a SQL script )

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: operational complex logic requirements

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