In this project I was part of a team of 2 engineers in charge of creating a Business Intelligence system for a software development client in Medellín, Colombia.
The main idea of the project was to use Power BI to generate interactive reports for the client so it could take better decisions on how its product was working.
The main problem is that the information was scattered in several places:
It was decided to create a new PostgreSQL database where all the information would be unified, organized and cleaned so Power BI could connect and query for records
I was in charge of creating this new PostgreSQL database, and unifying all the information there.
As I said before, the solution was to unify all the information on a single database (a PostgreSQL database) and have all the information stored there.
And to pull all the information into a single database, 2 Lambda functions where created:
There where several challenges aside of being a new development platform for me.
AWS Lambda is designed (at the time of this writing) to be used and developed using their online tool. This makes the version control much harder and also makes the overall development slower.
In this way I had all required elements to develop locally and with version control.
Maybe the hardest problem to solve, was that the files in AWS S3 came to fast. This would cause timeouts in the Lambda functions since Postgres didn't permit more that 100 of simultaneous connections.
The solution for this problem was using Connection Pooling in the PostgreSQL Node Driver. Unfortunately this required a complete rewrite of the lambda function since the way to create a connection is somewhat different than using a client connection directly.
This was an small problem at the beginning of the project where all the permissions weren't available to create triggers for the new Lambda Functions.
With the help of the client, we had to try several ACL combinations in AWS IAM to achieve the final solution.
Only 2 deliverables where given in this project: