This project aims to transform an existing database to support analytical processing (OLAP) for a data-warehouse that holds a huge amount of data and streams more data on daily basis. The analytical tasks are mostly statistical such as mean, variance, normalization, standardization, max, min, median. The challenge here is that the nested queries and the big data make the response time too slow and consumes lots of computational power that takes the server down.
The requirement here is :
1. Propose a solution to make these reports more efficient in a way that allows the site to load in less than a second
2. evaluating the performance of current queries
3. Install and configure the necessary needed software to the server (if any)
4. Migrate the database from the current DBMS (MySQL) to the new DBMS
5. Migrate the views and nested queries in the current database to be efficiently implemented in the new database
6. Review each SQL statements to and propose how it can be optimized to run in the most efficient way
7. Create and optimize materialized views + triggers as needed
8. optimize the performance of the migrated database
A new database to be used by the website with response time less than 3 seconds. I will just change the connection string in my site to connect to the new database and it should load the same data from the same views as fast as it can. The new database should be able to handle huge amounts of data (more than 30 million records)
Obviously, MySQL does not support nested select statements, so many queries are just created to be used by the end queries for business. The business queries used by the site are listed at the end of the attached document. These queries must be created by the same names in the new database. The other internal queries used implicitly by the main queries can be traced through the code or listed from MySQL
Before awarding the project, I will have a chat with each applicant to discuss the proposed solutions, the data warehousing experience and query optimization method.