Database retrieval and reporting system
Systems, devices, computer-implemented methods, and tangible non-transitory computer readable media for implementing an organizational management platform that manages organizational data for an organization. The system can receive, from a user device, a user request for a report. Additionally, the system can generate a logic plan based on the user request, the logic plan determining a plurality of models. Moreover, the system can generate a first query being expressed in a custom query language. Furthermore, the system can combine the first query with a second query to generate a unified query. Subsequently, the system can translate the unified query into a final query, wherein the final query is expressed in a data access language. The system can execute the final query to retrieve data from a database, where a report is generated based on the retrieved data.
1. A computer system that implements an organizational management platform that manages organizational data for an organization, the computer system comprising:
one or more processors; and
a plurality of databases that collectively store the organizational data associated with the organization, wherein the organizational data comprises an object graph data structure comprising a plurality of employee data objects that respectively correspond to a plurality of employees of the organization; and
one or more non-transitory computer-readable media that collectively store instructions that, when executed by the one or more processors, cause the computer system to perform operations, the operations comprising:
receiving, from a user device, a user request for a report;
generating a logic plan based on the user request, the logic plan determining the plurality of databases to obtain data for the report;
generating, based on the logic plan, a first query being expressed in a custom query language, the first query being for a join operation;
combining the first query with a second query to generate a unified query, the second query being for a filter operation, wherein the unified query is being expressed in the custom query language;
aggregating the unified query from a first database of the plurality of databases with a new query from a second database of the plurality of databases to generate an aggregated query;
generating, based on the unified query and the aggregated query, a final query, wherein the final query is expressed in a data access language; and
executing the final query to retrieve data from one or more databases in the plurality of databases, wherein the report is generated based on the data retrieved from the one or more databases.
2. The computer system of claim 1 , the operations further comprising:
retrieving data from a first database in the plurality of databases based on the user request; and
retrieving data from a second database in the plurality of databases based on the new query.
3. The computer system of claim 2 , the operations further comprising:
generating permission filters for the first database in the plurality of databases based on user privileges of the user device.
4. The computing system of claim 1 , wherein the aggregated query performs an aggregation on one-to-many joined datasets.
5. The computing system of claim 4 , wherein the aggregation is performed using a first hashing algorithm.
6. The computing system of claim 5 , the operations further comprising:
determining a minimal hash collision probability for each hash algorithm in a plurality of hash algorithm;
selecting the first hashing algorithm from the plurality of hash algorithm, the first hash algorithm having a minimal hash collision probability.
7. The computer system of claim 1 , the operations further comprising:
serializing the unified query to generate a serialized query, wherein the final query is further generated based on the serialized query.
8. The computer system of claim 1 , wherein the data access language is Structured query language (SQL).
9. The computer system of claim 1 , wherein the logic plan is associated with a plurality of models, the operations further comprising:
loading a query generator for the plurality of models.
10. The computer system of claim 1 , wherein the report is a job report based on the organizational data of the organization.
11. The computer system of claim 1 , the operations further comprising:
determining that the final query is associated with data in a first database of the plurality of database;
retrieving the data in the first database based on the determination; and
generating the report based on the retrieved data.
12. A computer-implemented method, comprising:
receiving, from a user device, a user request for a report;
generating a logic plan based on the user request, the logic plan determining a plurality of databases to obtain data for the report;
generating, based on the logic plan, a first query being expressed in a custom query language, the first query being for a join operation;
combining the first query with a second query to generate a unified query, the second query being for a filter operation, wherein the unified query is being expressed in the custom query language;
aggregating the unified query from a first database of the plurality of databases with a new query from a second database of the plurality of databases to generate an aggregated query;
generating, based on the unified query and the aggregated query, a final query, wherein the final query is expressed in a data access language; and
executing the final query to retrieve data from a database, wherein the report is generated based on the data retrieved from the database.
13. The method of claim 12 , wherein the aggregated query performs an aggregation on one-to-many joined datasets.
14. The method of claim 12 , the method further comprising:
serializing the unified query to generate a serialized query, wherein the final query is further generated based on the serialized query.
15. The method of claim 12 , wherein the data access language is Structured query language (SQL).
16. The method of claim 12 , the method further comprising:
loading data from a first database in the plurality of database based on the user request; and
loading data from a second database in the plurality of databases based on the user request.
17. The method of claim 16 , the method further comprising:
generating permission filters for the first database in the plurality of databases based on user privileges of the user device.
18. One or more tangible non-transitory computer-readable media storing computer-readable instructions that, when executed by one or more processors, cause the one or more processors to:
receive, from a user device, a user request for a report;
generate a logic plan based on the user request, the logic plan determining a plurality of model;
generate a first query being expressed in a custom query language, the first query being for a join operation;
combine the first query with a second query to generate a unified query, the second query being for a filter operation, wherein the unified query is being expressed in the custom query language;
aggregating the unified query from a first database of the plurality of databases with a new query from a second database of the plurality of databases to generate an aggregated query;
translate the aggregated query into a final query, wherein the final query is expressed in a data access language; and
execute the final query to retrieve data from a database, wherein the report is generated based on the data retrieved from the database.