Jorge Riquelme

Class failure rates were tracked institutionally, but reported as aggregate figures — masking the underlying factors driving student underperformance. The question was not just how many students were failing, but which conditions were most strongly associated with failure.

Using Python, I cross-referenced Cognos reports to build a multi-dimensional dataset spanning three indicator groups: Demographic Insights, Educational Engagement, and Background and Support. The analysis combined descriptive statistics, clustering, correlation analysis, and linear regression — with a deliberate bias reduction strategy that avoided individual identifiers and program-specific data.

The chart shows average failed classes per metric across three indicator groups — offering a balanced view among indicators, metrics, and submetrics.

In this recreation using synthetic data, the following patterns can be observed:

• Trend: Steady increase from Residency (2.39) to Funding Code (3.37) across all three groups.
• Demographics: Age Range (2.52) emerges as the most significant demographic factor.
• Engagement: Shaped primarily by Semester (2.74) and Previous Standing (2.89).
• Background & Support: Funding Code (3.37) and Catchment Area (2.95) show the highest average failure rates.

Figure 1: Average Class Failure Rates per Metric

Financial circumstances, represented by Funding Code, emerged as the strongest predictor of class failure — stronger than gender, age, or residency status. This challenges assumptions that demographic identity is the primary risk factor, and points toward financial support systems as a higher-leverage intervention point.

This analytical framework applies directly to healthcare (readmission risk), HR (employee turnover), and credit risk modeling — any domain where disaggregated risk analysis can identify systemic factors behind individual outcomes.

Wide-format datasets are the standard output of legacy administrative systems — efficient for storage and transactional reporting, but structurally limited for dynamic BI analysis. This case documents the audit and transformation of a retention dataset typical of Ontario post-secondary institutions: 985 rows and 33 columns, where semester-retention values are embedded directly in column names rather than stored as rows.

Wide-format structure where each semester and student type occupies a separate column — 18 retention columns embedded in column names, making dynamic slicing in Power BI impractical:

985 rows x 33 columns — 18 of which are semester-retention values embedded in column names (SEM1-SEM6 x Total / DOM / INT)

Using Python, the dataset was unpivoted from wide to long format — extracting the semester number from column names into a dedicated field. The result reduces 33 columns to 6, making the data natively compatible with Power BI slicers and DAX measures. Demographic dimensions are added separately via foreign key joins:

Long format — one row per program, campus, and semester. What previously required 18 static measures becomes a single Students field.

A foreign key was constructed to enable joins with dimension tables — adding demographic, program, and enrollment context without embedding it in the base retention structure. This separation of concerns keeps the retention layer clean and scalable as additional data sources are connected.

Institutional enrollment data is typically aggregated at the program-cohort level, not at the individual student level. To support cohort tracking across semesters, the dataset was restructured using enrollment data as the foundation — making it possible to distinguish organic retention from other enrollment movements that aggregate reporting does not capture by default. Cohort-level program tracking is developed further in Case 2.

A long-format structure ready to calculate institutional and program-level retention rates dynamically — with a foundation that scales to include additional dimensions as data sources are connected via foreign keys. What previously required 18 static measures becomes a single analytical layer adaptable to any reporting need.

Institutional retention is often reported as an aggregate figure, assuming a linear progression where "Semester 1" represents the same starting point for all records. This audit identified a critical Attribution Bias: the mixing of true freshmen with internal transfer students within program-level cohorts.

Comparison of data integrity status across analysis levels. The current model assumes a linear path that does not exist for 15-20% of the population:

The lack of data atomicity triggers a domino effect across institutional KPIs, leading to skewed strategic decisions:

• Retention Inflation: Transfers (higher success probability) artificially boost averages, masking the real dropout rate of vulnerable freshmen.
• Time-to-Degree Distortion: Programs appear more efficient by "importing" graduates who completed 50% of their credits in other departments.
• Resource Misallocation: Budget for first-year support is under-prioritized because aggregated data suggests a healthy performance.

I proposed a transition toward Data Atomicity to restore system integrity. This framework enables dynamic segmentation without losing historical context:

The "Cohort Contradiction" is a structural data flaw applicable to any sector where aggregated reporting masks the origin or lifecycle of the subject.

This case is the direct implementation of the Cohort Attribution Audit. The audit identified the structural flaw — misattributed cohort labels masking 15–20% of enrollment events. This case documents how that finding was resolved in practice: a flag pipeline built on the raw enrollment file that reconstructs every student trajectory without modifying the source data.

Institutional enrollment data arrives as a flat transaction file. Each row represents a student-program-semester record, but nothing in the raw data distinguishes a student who dropped out from one who transferred programs, completed their credential, or simply repeated a semester. Aggregate retention formulas — built on Sem N − Sem N+1 subtractions — collapse these distinct events into a single number, hiding the destination.

Rather than filtering or removing records, the transformation was applied directly on top of the raw enrollment file. Every transaction was preserved and labeled through a flag architecture — eight boolean columns that reconstruct the full student lifecycle without altering the audit trail.

Eight boolean columns reconstruct the full student lifecycle. Each flag represents a distinct institutional event:

The flags enforce an accounting rule at every semester transition. Every student that starts a semester has exactly one outcome:

Applied at Cohort + Program + Campus + Semester level, the flags produce a flow table where every student is accounted for across the full program lifecycle:

45 students started. 4 did not return after Semester 1. 3 moved to another program. By Semester 4, the 33 who remained completed the program — all flagged as is_pathway, continuing into their next credential.

The flag architecture produces two complementary views from the same source — consistent by construction, no reconciliation required:

This is not a statistical model. It is a formula architecture: reproducible, auditable, and built to be wrong-proof. Every flag is independently verifiable, every outcome traceable to a specific row. The pipeline can be re-run on any future dataset and produce consistent results by construction — applicable to any domain where lifecycle events are currently collapsed into aggregate subtraction metrics.

A Power BI report built on this flag layer — interact with it directly. Filter by cohort, campus, program, and demographic group to explore retention, persistence, and completion outcomes across the full student lifecycle.

Live embed — synthetic data for portfolio purposes. Built with Power BI Publish to Web.

The Ontario postsecondary sector faces a critical barrier: functional data silos and a lack of identity-based administrative data (HEQCO 2022). Current practices—often relying on static, disconnected reports—create analytical silos and inherent bias by focusing exclusively on the student (Micro level) in isolation. This fragmented approach fails to identify systemic bottlenecks and the diverse challenges of underrepresented populations, ignoring the interplay between the learner, the curriculum, and the institution.

More than a simple warning tool, this ecosystem transforms limited administrative data into a comprehensive analytical engine supporting multiple institutional workstreams. This framework bridges data gaps through a specialized Multidimensional Data Mart that integrates Historical, Real-time, and Forecast data.

Institutional data work doesn't sit in isolation — it operates within a decision-making process shared between the institution and its Institutional Research (IR) function. The diagram and table below map that process and where the data-mart architecture fits. The alert system that follows is the analytical system built to operate within it.

Figure — The Business Intelligence Decision Cycle: how strategic goals flow through data-mart analysis and BI into intervention and evaluation. Organizational flow adapted from Cannistrà (Politecnico di Milano); analytical dimensions based on the SAT Centinela model (Casanova et al., 2021, UCSC-Chile).

Table — Roles Across the BI Cycle: each phase and the function responsible for it, the Institution (strategy, decisions, intervention) or Institutional Research (analytical work, evidence, evaluation).

The system consolidates data from institutional warehouses through an ETL process into a specialized Educational Data Mart, implemented as a Constellation Schema that integrates three types of information — historical, real-time, and forecast data — segmented into multidimensional domains covering partial grades, enrollment trends, teaching competencies, retention, and graduation outcomes. Designed for monitoring, following up, predicting, and analyzing results, the analytical layer feeds into an interactive Power BI environment where threshold-based alerts trigger early intervention workflows coordinated across advisors, faculty, and student success teams.

Figure 1: EWS Data Architecture & Analytics Layer — from institutional databases through ETL to Risk Index Generation and Early Alert coordination. Based on the SAT Centinela early-warning model developed at Universidad Católica de la Santísima Concepción (Casanova Cruz, Miranda Díaz & Yáñez Corvalán, 2021), used with permission.

The Data Mart is implemented as a Constellation Schema in Power BI — four fact tables (Enrollment, Retention, Grades, Graduation) sharing dimension tables across Student, Faculty, Course, and Calendar. This structure enables cross-domain analysis without data duplication.

Figure 2: Constellation Schema implemented in Power BI — mock data for portfolio purposes.

The architecture is strategically designed to answer the four critical questions for equitable access:

• Who: Student Demographic Dimensions (monitoring).
• What: Academic Performance (Grades & Enrollment) (following up).
• Where/How: Structural Factors (Teaching & Curriculum) (predicting).
• Outcome: Long-term Success (Graduation & Retention) (analyzing results).

The multilevel nested analytics approach is validated by research from Politecnico di Milano, acknowledging that dropout probability is highly conditional on program-specific variables. Additional frameworks draw on UCSC-Chile's multilevel model and Purdue University's Course Signals risk predictor research.

Public Kaggle dataset of 9,648 rows covering Adidas US sales across 2020–2021. The original dataset exists as exploratory data analysis (EDA) in Python notebooks — descriptive visualizations with no analytical framework or business decision orientation. This project was deliberately developed outside the primary domain of post-secondary education to demonstrate the transferability of the same BI analytical framework to a commercial retail context.

No analytical questions guiding interpretation. No pricing analysis — revenue per unit, seasonal price variation, and retailer pricing gaps were unexplored. No profitability lens — margin by product, region, and channel was invisible. No interactivity — stakeholders could not explore by geography, channel, or product.

Four-page interactive dashboard structured around five analytical questions:

1. "How has operating margin evolved quarter over quarter across 2020–2021?"
2. "Which regions, states, and sales channels generate the most revenue?"
3. "Which products command the highest price per unit — and does pricing vary seasonally or by retailer?"
4. "Are the best-selling products also the most profitable, or is there a trade-off between volume and margin?"
5. "Where and what generates the most profit — not just revenue?"

Key Metrics: Total Sales, Operating Profit, AVG Operating Margin, Revenue per Unit, YOY Sales Growth, Units Sold

DAX Patterns: DIVIDE for margin calculations, MAXX + TOPN + SUMMARIZE for dynamic top performers, SELECTEDVALUE for context-aware narrative cards, Field Parameters for dynamic geographic granularity

Dataset retrieved from Kaggle. Final layout, color palette, and design are adapted for the target platform (SharePoint or embedded app) upon deployment.

Demonstrates transferability of the same BI analytical framework — analytical question framing, Python data preparation, DAX dynamic measures, and cross-filtering interactivity — from the post-secondary education domain to a commercial retail context.

Public Kaggle dataset (CC0 Public Domain) originally designed for predictive maintenance tutorials and machine learning feature engineering — not for operational dashboards. This project deliberately repurposed it to answer a different question: can a structured BI dashboard be built on top of a PdM-oriented dataset to deliver operational manufacturing intelligence?

The original dataset had no analytical framework, no business questions, and several inconsistencies that required resolution before any meaningful analysis could be produced.

Four-page interactive dashboard structured around a diagnostic narrative arc:

• Page 1 — "What is the current OEE state of the plant?" Monthly performance summary by machine and shift.
• Page 2 — "Where is effectiveness being lost?" Loss waterfall (Availability, Performance, Quality) + Downtime Pareto analysis.
• Page 3 — "Is the machine running consistently?" Statistical Process Control (X̄ chart) with UCL/LCL to detect process instability.
• Page 4 — "What would it take to reach the 75% Manufacturing Standard?" OEE improvement scenario simulation combining downtime reduction and Performance targets.

• Plant OEE: 55.3% — significantly below the 75% Manufacturing Standard.
• Performance loss (35.9%) is the dominant driver — not downtime (8.6%) or quality (0.2%).
• Eliminating the top 3 downtime causes (Mechanical, Changeover, Electrical) only recovers 3 OEE points — insufficient alone.
• Reaching 80% Performance efficiency combined with top 3 downtime reduction would achieve 76.9% OEE — exceeding the 75% target.
• SPC analysis confirms the process is statistically stable — Performance gap is structural, not caused by equipment instability.

Demonstrates that a structured OEE diagnostic dashboard can be built on top of a PdM-oriented dataset — establishing the descriptive analytical baseline that a future Predictive Maintenance model would require. Also demonstrates transferability of the same BI methodology across three domains: post-secondary education, retail, and manufacturing.

Four-page interactive dashboard — navigate between OEE Overview, Loss Analysis, SPC, and Scenario Simulation using the report controls.

Live embed — synthetic data for portfolio purposes. Built with Power BI Publish to Web.

Olist is a Brazilian e-commerce marketplace that connects small and mid-size sellers to larger sales channels. Before a seller becomes active, it moves through a B2B sales funnel: a lead lands on a marketing page (MQL) → a Sales Development Rep (SDR) contacts and schedules a consultation → a Sales Rep (SR) runs it and closes or loses the deal → a closed deal becomes an active seller. This dashboard turns two public Olist tables into an interactive funnel analysis: 8,000 marketing qualified leads and 842 closed deals.

The dataset records only the first stage (MQL) and the last stage (closed deal). Intermediate stages are not recorded for lost leads — so segment, lead type, behaviour profile, SDR and SR exist only for the 842 converted deals, never for the 7,158 that didn't convert. Every design decision respects this: where the data could only describe the converted population, it is presented as volume, share and velocity — never as a conversion rate that would require a denominator the data doesn't have.

A five-page Power BI report (Overview, Funnel Analysis, Sales Team Performance, Rep Efficiency, plus a documentation page) built on a star schema: fact_funnel (8,000 rows) with dimensions for origin, segment, seller, SDR, SR, and a Calendar spanning both the lead and deal windows. Python (pandas) handled cleaning and feature engineering; Power Query handled typing and the date relationship.

Live embed — public Olist data. Built with Power BI Publish to Web.

• No mid-funnel visibility — segment, rep, type and profile exist only for converted deals.
• No conversion rate by segment or rep — no denominator of leads at that grain; only volume, share and velocity are valid.
• No revenue dimension — the only revenue-like field is unreliable and conceptually wrong as deal revenue.
• Small-sample reps — reps with 1–3 deals show extreme close times; flagged by bubble size and notes.
• Incomplete period boundaries — lead and deal windows don't fully overlap, producing the visible lead-to-close lag.

The ~14% of leads with no recorded acquisition channel are surfaced as a data-governance finding (an attribution blind spot), not imputed. Labeling distinguishes "Not reported" (missing administrative data) from "Unknown" (undetermined marketing profile), and compound behaviour profiles are grouped as "Mixed Profile".

A sales funnel shows how sellers arrive; it says nothing about how they perform once they're selling. Marketplaces lose revenue quietly — sellers drift into poor delivery, low review scores, or inactivity, with no system flagging the decline until they've effectively churned. The gap isn't analytical, it's operational: there was no place to manage sellers as ongoing relationships rather than one-time conversions.

I built a model-driven CRM in Power Apps over a Dataverse relational model — seven tables connected through 1:N relationships, with the seller as the central business entity, plus an eighth table (Seller Interaction) as the management layer. Five KPIs — total revenue, order count, average review score, late-delivery rate, and recency — were engineered in Python from the raw Olist tables. A lifecycle stage (Active, At Risk, or Dormant) was then derived from those KPIs using transparent, data-driven thresholds.

The risk rules were replicated inside Dataverse via a dataflow and cross-validated against the Python snapshot — both produced identical results (1,893 Active / 292 At Risk / 910 Dormant), treating data agreement as a build checkpoint.

• Dormant — 180+ days without an order.
• At Risk — an active seller with a review score below 3.0 or a late-delivery rate above 20%.
• Priority — Dormant takes precedence: a seller who has gone quiet is no longer "at risk," they have already lapsed.

The CRM connects live to Power BI through the Dataverse connector, surfacing the seller portfolio across three views: an overview (KPIs, lifecycle split, performance validation), a geographic view (sellers mapped across Brazil's states with region-level cards), and an operational at-risk view (the 292 flagged sellers, ranked by severity and broken down by reason). Interact with it directly:

Live embed — public Olist data. Built with Power BI via the Dataverse connector.

The pattern generalizes beyond marketplaces: any organization managing a portfolio of ongoing relationships — vendors, accounts, students, members — needs the same architecture. Model the entity, compute performance metrics, derive a lifecycle stage from transparent rules, and surface it where someone can act. The tools change; the operational logic doesn't.