MEL Science reports and analytics
Project Description
Implemented financial report for 2018 year.
There were requirements to investigate in complicated company database structure. Then create complicated SQL query for extracting sales data for all year. There was also task to do additional data processing, that was problematic to do on SQL query level. Final report should be provided for accounting department in xlsx format for future processing in Excel.
While I was working on that report, I have to solve additional problems:
- There were no direct access to database, then all SQL queries were created and executed in Metabase environment
- Database structure was changed, then there were different tables structure and logic for data for different months
- Sometimes database structure was very unclear, so I had a lot of discussions with developers
- Some data was missing in main database. It was stored on separate excel files, google docs or even on paper
- Final SQL query could be executed for month, but not for all year because of performance issues, database size and other limitations
- Excel export didn't work because of large amount of data
- There were payments in different currencies in database
As a result, I implemented complicated SQL query, divided to logical blocks with variables and WITH operator. I had to use WHEN/ELSE for handling different database structure. In some cases I hand to use pivot and crosstab for data processing. Some extra data was added manually using temporary table.
Final SQL report was quite complicated and heavy. It took about 10 seconds for 1 month interval. And it was impossible to run query for all year. There also were requirements for future data processing. Then I decided to export data by months into csv files, process them and build general report with Python script. I also used that python script for processing data, that was missing in main database. For example, some data was stored in excel files and google docs.
Another requirement was to convert payments from different currencies to one specific currency. Conversion should be done useng currency rate for specific date. Then I implemented parser for receiving currency rates from official sources(EUR, RUB, GBP, CAD, MXN).
When I generated final yearly report, it was used for passing audit check. Then it was used for new sales data analyse.
Besides that, I created other analytics reports, that were not so complicated. I used Metabase environment for data visualisation and user interface.
Technologies: Python, Excel, PostgreSQL, Metabase