Skip to content
All projects

Business Intelligence

Ecuador’s exports through a Business Intelligence lens

A BI solution to explore Ecuador’s exports from 2018 to 2023 by product, destination and period — from data dictionary and model to DAX measures and dashboard design.

Programme
Business Analytics · Master’s in Business Intelligence and Data Science, UEES
Format
Academic group project · team of five
Period
2024 · data from 2018–2023
  • Power BI
  • DAX
Illustration Abstract illustration inspired by an exports dashboard. It does not reproduce the project’s original data or charts.

Overview

The project takes export data published by the Central Bank of Ecuador — products, transaction types (traditional and non-traditional), destination countries, FOB value and net weight — and turns it into a Business Intelligence solution that lets different users explore trends, patterns and opportunities in the country’s exports.

The work covers the full path from raw data to decision support: organising the data, documenting it in a data dictionary, designing a relational model, defining DAX measures and choosing the right visual for each business question.

The business problem

Export data is detailed but hard to read in raw form. Investors, government bodies, chambers of commerce, suppliers, ports and shipping companies all need different answers from the same figures: which markets matter most, which products generate the most value and how stable exports are over time.

Objectives

  1. 01

    Define the ETL process that provides the input for the BI system.

  2. 02

    Apply a user-experience process so the final product is useful for its intended users.

  3. 03

    Build a Power BI dashboard that surfaces relevant information and insights.

Business questions

  1. Q1 Which countries receive the most exports?
  2. Q2 Which products generate the highest export value?
  3. Q3 Ecuador is seen as an oil country — is oil also where most export value comes from?
  4. Q4 What were the highest and lowest points between 2018 and 2023?
  5. Q5 Is there a month that consistently generates more exports?
  6. Q6 Are export levels stable across years, or do they fluctuate?

Methodology

  1. 01

    Data & ETL

    Export records from the Central Bank of Ecuador, organised into three tables: product sub-headings, monthly exports by product and country, and destination countries.

  2. 02

    Data dictionary & model

    Each field documented with type, keys and description; tables related through the sub-heading code and the ISO-3 country code.

  3. 03

    User research & ideation

    Potential users identified, business questions defined and filters proposed by time (year, month), geography (continent) and economic sector (oil / non-oil).

  4. 04

    Measures & visual design

    DAX measures for FOB value and tonnes, and one visual per question: treemap for destinations, bars for products, a donut for oil vs non-oil, lines for trends and grouped bars for seasonality.

EXP_SUBPARTIDAS

Dimension · product

  • Subpartida PK
  • Producto_Principal
  • Tipo
  • Subtipo
  • Tipo_Transaccion
  • Descripcion_Subpartida

EXP_PROD_PAIS_SUBPARTIDA

Fact · monthly exports

  • Año
  • Mes
  • Subpartida FK
  • ISO-3 FK
  • Unidades
  • FOB_USD
  • Peso_Neto

PAÍSES

Dimension · destination country

  • ISO-3 PK
  • Pais
From the original project Data model as described in the project’s data dictionary (original table and field names, in Spanish).
DAX Measures table 
Total FOB =
    SUM ( EXPORTACIONES[FOB_USD] )

Total TON =
    CALCULATE ( SUM ( EXPORTACIONES[Peso_Neto] ) )

Toneladas NP =
    CALCULATE ( [Toneladas], SUBPARTIDAS[Tipo] = "NO PETROLERO" )

TOT FOB PETROLERO =
    CALCULATE ( [TOT FOB USD], SUBPARTIDAS[Tipo] = "PETROLERO" )
From the original project DAX measures as they appear in the project documentation.

Key findings

  • Crude oil is the single largest export product, yet only about one third of export value comes from oil products — challenging the idea of Ecuador as a mainly oil-exporting economy.
  • Shrimp and bananas stand out as major non-oil exports, a sign of diversification.
  • Exports are highly concentrated in a few markets, with the United States and China as the main destinations.
  • 2022 was the strongest year of the period; April 2020 shows a sharp drop linked to the pandemic. Data for 2023 was only available up to July.

Tools used

  • Power BI
  • DAX

What this project demonstrates

  1. 1

    Starting from business questions — and from who is asking them — before choosing any chart.

  2. 2

    Documenting data properly: a data dictionary and a clear model make an analysis easier to trust and maintain.

  3. 3

    Reading results critically, for example flagging that the 2023 data was incomplete before drawing conclusions.

About authorship

Completed by a team of five students as part of the Business Analytics course. This page describes the team’s work as a whole and does not attribute individual tasks.