The Système électronique d'appel d'offres (SEAO) provides public procurement data in structured formats such as JSON and XML. These files contain detailed information about public tenders, contracts, suppliers, and procurement activities. In order to perform analysis, generate insights, and ensure efficient data management, this information must be integrated into a structured database. This has been done by GRIDD lab at École de technologie supérieure ÉTS.

You can have access to the designed public Power BI dashboard by clicking on the link in the section below, or send a request to the provided emails to have a direct access to the database.

To access the structured database, and have more information, please contact:

Ali.Motamedi@etsmtl.ca
gabriel.jobidon@etsmtl.ca

A Power BI dashboard, based on the structured database, is accessible by the link below:

https://app.powerbi.com/view?r=eyJrIjoiMjQ2MDYxYzUtMmVlNi00N2JmLWEwMjQtNmU3NTQ1NzgxOWE0IiwidCI6IjY3YTFhZDVjLTQ2YzAtNGQ5My05ZWMwLTAyOGM2NDhjYTdmMSJ9

a. Public tender

Public procurement refers to the process by which governments and public organizations (such as municipalities, schools, or hospitals) buy goods, services, or construction work from private companies. For example, this can include building roads, supplying equipment, or providing IT services.

The goal of public procurement is to ensure that public money is spent efficiently, fairly, and transparently. To achieve this, clear rules and procedures are followed so that companies have equal access to opportunities and the best offer (in terms of price and quality) is selected.

In practice, public procurement involves several key elements: publishing opportunities (often on platforms like SEAO), defining clear requirements, receiving and evaluating bids from companies, awarding contracts, and monitoring their execution. Different methods can be used, such as open tenders, invited tenders, or direct contracts, depending on the situation.

b. Public Procurement Methods

1. Public Tender (Open)

A public tender is open to all companies that meet the requirements and must be published on SEAO. It ensures a transparent and fair process while helping achieve the best value for money.

2. Invited Tender (Limited)

An invited tender involves a small number of selected companies and does not need to be published on SEAO. It allows for a faster process while still maintaining competition, usually by including at least one SME.

3. Direct Contract (Sole Source)

A direct contract is awarded without a formal tendering process. It is used for smaller amounts or specific cases, but it must be justified and documented to ensure fairness and prevent favoritism.

c. Description of SEAO Data Sources (XML and JSON)

According to the Treasury Board Secretariat Québec publishes on the electronic tendering system (SEAO) a wide range of open data related to public procurement from several governmental, municipal, educational, and healthcare organizations. These data include, in particular, published tenders, contracts, committed amounts, and associated expenditures.

Initially, public contract data were provided in XML format and were mainly used between 2009 and May 2024. This format includes three types of files:

There is also a revision version (e.g., Avis Revisions_20180101_20180131.xml). Each of these XML files follows a standard structure, with fields such as the SEAO number, organization name, contract type, nature of work or services, awarded supplier, committed amounts, dates, as well as geographic metadata such as city, province, and postal code.

Since March 2021, SEAO has gradually introduced a JSON format inspired by the Open Contracting Data Standard (OCDS), which is more structured and aligned with modern data exchange standards. The JSON format became exclusive as of May 2024, fully replacing the XML format. Although it provides similar information, this format offers better standardization, making it easier to integrate into databases and analysis tools such as Power BI.

d. Relational Database Modeling

To exploit public contract data from SEAO, two distinct schemas were designed: one for XML files and another, more complete and final one, for JSON files.

It should be noted that in the XML-based model, all types of contracts published on SEAO were integrated into the database (all files exist in SEAO). However, the JSON relational model was designed exclusively using construction contracts (G12, C02, G31, S8, S5, G19, S19, G6, C01, IMM1, G25, C03, S3), This selection allowed the analysis to focus on the targeted construction sector.

e. Initial Model Based on XML Files

The first schema is based from the XML files (notices, contracts, expenditures, including revisions). This model, is based on a simple architecture that links contract information with their notices, additional expenditures, and awarded suppliers.

Each table also includes a history table for past records. (the full schemas is in the XML schema PDF file)

f. Unified Model Based on JSON Format

The other relational schema, this time inspired by the standardized structure of the JSON format compliant with the Open Contracting Data Standard (OCDS).