IDA: Business Intelligence for Ceará's Agrarian Management
Demand and Action Indicators Dashboard for the State Agrarian Development Secretariat
Web system built for SDA Ceará to register, track and cross-reference demand and action indicators for the Secretariat's programs, covering all 184 municipalities of the state, with a Power BI report embedded on the home screen to present already treated and analyzed data.
IDA is the Business Intelligence dashboard the State Agrarian Development Secretariat of Ceará uses to register, cross-reference and visualize demand and strategic action indicators across the state's 184 municipalities, replacing scattered spreadsheets with a single, real-time view.
Context and Scope
IDA (Demand and Action Indicators) is the system used by the State Agrarian Development Secretariat of Ceará (SDA CE) to register and track demands and strategic actions across the Secretariat's programs — cisterns, benefits, farmers served, partner entities, among other indicators — cross-referencing data by municipality, year and action type across all 184 municipalities of the state.
System modules
- Registration — indicators, entities and metadata for the Secretariat's actions
- Movement — logging and updating the execution of each action
- Reports — analytical extraction for accountability
- Administration — access profiles and permissions for Secretariat users
Registration module: beneficiary lookup with search, sorting and configurable columns
Movement module: demand lookup — 18,403 records in the system's history
The "My Demands" grid, in the Movement module, gives a sense of IDA's real data volume: over 18,000 demands recorded since 2015, each with a number, year, municipality, indicator and unit, filterable and searchable in real time.
Each of these demands starts from a structured action-tracking form, with data about the responsible party (CPF/CNPJ), the request and its linked project, plus a general data section that requires full geolocation — state, municipality, district, neighborhood/locality, latitude, longitude and an image — along with classification by indicator type, specific indicator, unit of measure and target audience. It's this level of granularity at data entry that backs the cross-references and aggregations later shown in Power BI.
Movement module: action-tracking form, with geolocation and indicator classification
Business Intelligence Dashboard with Power BI
The entire system — registration, movement, reports and administration — was built from scratch in Laravel, with PostgreSQL as the source of truth. Power BI comes in at one specific, deliberate point in the flow: once the data is treated and analyzed through SQL queries against the database, a Power BI report is published and embedded directly on the system's home screen, acting as the executive visualization layer on top of data already processed by the application.
Interactive map
Choropleth view of Ceará's 184 municipalities, with filtering and per-region highlighting.
Analytical charts
Quantity by municipality, by indicator type and by year, on a single screen.
Total cards
Aggregate indicators highlighted at the top of the dashboard for immediate reading.
Beyond the consolidated home view, the Power BI report itself opens a full historical series: each indicator has its own year-by-year evolution, from 2015 to 2023, making it possible to compare trends between programs and spot drops or spikes in execution over time without leaving the system.
Power BI report embedded on the home screen: side-by-side indicator comparison by year (2015–2023)
Some figures shown in this case study's images were hidden due to the confidentiality of the Secretariat's data.
Architecture and Stack
The backend was built in PHP with Laravel, handling the business rules for registering, tracking and aggregating indicators, with PostgreSQL as the database and direct SQL queries for the treatment and analysis that feed Power BI. The application runs containerized with Docker, standardizing the environment between development and production and simplifying deployment of new versions.
Domain in Laravel
The rules for registering, tracking and aggregating demands and actions live in the Laravel backend, isolated from the visualization layer.
Transactional vs analytical
The SQL treatment and aggregation queries are separated from transactional rules, so analysis doesn't weigh on daily operation.
Power BI as visualization
Power BI consumes the already-treated data and delivers the executive dashboards — the presentation layer doesn't recompute business rules.
Real-time indicators
Every update to a demand reflects in the corresponding indicator, giving the Secretariat tracking without waiting for manual consolidation.
Consistent relational model
PostgreSQL models demands, strategic actions and movements with referential integrity, a reliable base for aggregation.
Standardized environments
Docker isolating dev and production, version-controlled via Git — the same infrastructure pattern as SDA's other systems.
High-level architecture: a Laravel backend over PostgreSQL, with a SQL analytics layer that treats and aggregates the data and feeds the Power BI dashboards, all containerized.
The split between transactional operation and analytical layer is what lets the dashboard respond in real time without stalling data entry: rules stay in Laravel, heavy aggregation goes to SQL and Power BI only presents — a decision that underpins the 25% reduction in demand response time.
Project Impact
IDA now covers all 184 municipalities of Ceará and sustains a history of 18,403 demands recorded since 2015, tracking over 100 new demands per month with 99.5% uptime. Centralizing indicators into a single dashboard cut demand response time by around 25%, replacing manual spreadsheet cross-referencing with direct queries in the system.
Few systems give a developer the chance to work with public data covering an entire state. Building IDA while I was at the State Agrarian Development Secretariat of Ceará meant working with a dataset that's genuinely valuable for public administration — geolocated, historical, and spanning all 184 municipalities of Ceará.
Key Challenges Faced
IDA needed to turn scattered spreadsheets and demand/action records into a single, reliable dashboard covering all 184 municipalities of Ceará — simple enough for managers to make decisions without relying on manual exports.
Consolidating heterogeneous indicators (beneficiaries, amounts, cisterns, entities) into a single data model
Cross-referencing data by municipality, year and indicator type with acceptable performance on aggregate queries
Keeping data registration and movement auditable without compromising real-time reads on the BI dashboard
Structural Technical Indicators
- 184/184 Ceará municipalities with registered indicators
- 500+ active users in the system
- 18,403 demands recorded in the system's history, since 2015
- 100+ demands tracked per month
- 99.5% uptime in production
- -25% in demand response time after switching to the BI dashboard
- Containerized environment with Docker for reproducible builds