Call for tender, Response Writing ("Tender Response")

During this internship, I had the opportunity to participate in writing a response to a tender from ESCP (“École Supérieure de Commerce de Paris”). ESCP is a prestigious French business school with various campuses and connections in several countries across Europe, giving it significant international influence. It is therefore crucial for the school to have regular and substantial follow-up with this data (applicants, students, professors) through reports or dashboards to assist the school’s administrative staff. ESCP is thus looking for a team capable of setting up a data retrieval infrastructure, performing manipulations with this data, and creating dynamic and relevant reports based on this data.

Source: ESCP

Team Context

The team consisted of a consultant and interns, working together to meet the tender requirements and provide a complete and optimized solution.

Project Objective

The objective is to create a case similar to that of ESCP to demonstrate the exploitation of databases and graphics in a real-world situation. The chosen example is that of a fictional association with donors, donations, donation pledges, and advertising campaigns to create a scenario similar to the ESCP case. In this context, we generate consistent data to have relevant dashboards and reports; the creation of an infrastructure connecting and manipulating databases; the creation of reports and dashboards with these databases.

Conditions and Tools

The required conditions include the use of Jaspersoft and the implementation of a star schema database (a database modeling approach that facilitates complex queries and analyses by optimizing the data structure for reads, with a central fact table containing information associated with dimension tables providing context).

Source: Jaspersoft

To transform a relational database into a star schema model, we used the Talend suite. Talend is a data integration tool suite that allows for building robust and efficient data pipelines. We use it for ETL (“Extract, Transform, Load”) tasks to transform the database into a star schema for the Data Warehouse (a centralized repository that stores data from multiple sources, facilitating analysis and information generation).

Source: Talend

We feed the PostgreSQL database with previously generated data, via CSV files, and process them to fit a star schema. We will use the result to create reports with Jaspersoft. Jaspersoft offers several solutions: a cloud offer for creating interactive dashboards (“Jaspersoft Cloud”), another offer for hosting a server in the cloud or on a local server (“Jaspersoft Server”), and a software offer for creating reports (“Jaspersoft Studio”).

Personal Contribution

My personal contribution initially involved data generation and exploring the various Jaspersoft solutions. For data generation, I first conducted preliminary research to determine the different criteria that could impact donations and the various hypotheses needed to generate this data. The goal was to have interesting data to model with dashboards and reports, seeking to contrast and highlight exploitable information from the data.