0

We are currently developing a web application for financial analysis using PHP (Zend Framework 2) and Doctrine as ORM-Tool (MySQL database). The complex financial calculations are done on the server-side, since the client has to be simple as possible. The data should be available in nearly "real-time", since the user is entering the data live. All user data will be transmitted to the server immediately.

For the financial calculations, we have to join multiple tables with custom filtering and data aggregation. The business logic and the calculations are rather complex.

So, we are concerned about mixing the business logic and rules in our SQL statements (encapsulation principle). Even using an ORM tool, some statements will be in native SQL and not easily understandable/modifiable.

We consider using an ETL or a BI software for the data processing. But most ETL tools and BI software are written in Java and their integration with PHP seems to be rather cumbersome. We, a team of 5 PHP developers, have little knowledge in ETL design and processing. The most crucial drawback we see in ETL is the processing time, latency and the up-to-dateness of the data.

Is the use of an ETL tool / Business Intelligence software recommended in our situation? Or should we stick to the complex Doctrine/SQL statements and tangle our business logic with SQL?

Many thanks for any insights or recommendations.

catilgan
  • 23
  • 3

1 Answers1

1

I'm not sure if this question is asking for an opinion. This answer is intended to give you things to think about in making the choice.

The choice between "external" ETL or "internal" ETL depends on several factors:

  • The skill set of the developers.
  • The requirements for the ETL.
  • Specific requirements for data transformations.
  • Specific requirements for database performance.

In many circumstances, you can accomplish the same goals in the database or using external tools. External tools have the advantage that they do not encumber the database server -- or at least you can control it much more easily. Also, external tools are designed for the purpose of moving data and processing data, so they often offer better connectivity, performance, and error reporting.

That said, if your skill set is centered on SQL, then loading data into staging tables and doing the work in the database is also viable. I often find that I prefer to do such processing in the database, but it depends on the requirements of the project.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for your opinion. Indeed, our skill set for external ETL is not very high. We tend to do the transformations using SQL and internal transformation tables. Even that means, we have to do all necessary transformations manually, which can be laborious and error-prone. But we see the advantage of external ETLs in higher level of data modelling as well, which is more comprehensible and better visualised. – catilgan May 27 '15 at 09:51