The Client
A leading pharmaceutical company operating across multiple therapy areas and molecule portfolios, this organization manages a large and geographically distributed sales and marketing function spanning medical representatives, regional managers, and finance teams. The ability to analyze sales performance, KPI attainment, and brand-level trends at molecule and therapy-area granularity is critical to commercial decision-making, yet the organization’s analytics capability was locked behind fixed dashboards and inaccessible to the business users who needed it most.
The Challenge
Business teams across the organization had no practical way to perform ad-hoc analysis on the data that drove their daily decisions. A combination of misaligned terminology, rigid pre-configured dashboards, unstable KPI logic, and an AI layer that generated confident but incorrect SQL meant that self-serve analytics was unreliable in practice, regardless of what the tooling theoretically offered.
-
Misaligned Business Terminology
Business terminology and data structures lacked alignment when analyzing complex pharmaceutical KPIs, hindering reliable analytics and producing inconsistent outputs across users and geographies.
-
Fixed Dashboard Limitations
Pre-configured dashboards reported only fixed metrics such as Sales MTD and YTD targets, with no mechanism to perform ad-hoc analysis at molecule-level or by therapy-area trend.
-
Imprecise Brand-Level Filtering
Precise brand-level filtering required explicit canonical SKU mappings that the existing broad pattern-based filters could not provide, making granular analysis impossible.
-
Unstable KPI Definitions
Business rules, hierarchy logic, fact table structures, and KPI formulas changed repeatedly during implementation, triggering rework across Genie instructions, SQL examples, and Metric View definitions, and delaying production readiness.
-
Genie SQL Fabrication
Without sufficient business context, Genie fabricated plausible but incorrect SQL, producing outputs that appeared reasonable but were analytically unreliable, eroding business user trust.
The Impact
- ~95% Genie benchmark accuracy, up from 20–40% at project start
- Enterprise-Wide Single Source business users now empowered with self-serve pharma analytics
- One governed semantic layer replacing scattered SQL, tables, and KPI definitions
The Solution
Hoonartek replaced the fragmented multi-table, multi-query analytics approach with a single governed YAML-defined Metric View, a centralized semantic layer that eliminated scattered SQL instructions and provided Genie with a consistent, structured foundation for every query. Therapy-area and molecule-level roll-ups were implemented as Materialized Metric Views, enabling real-time NLQ responses that eliminated full table scans and delivered consistent query performance across geographies.
SME alignment was achieved through prompt-a-thon workshops with medical representatives, regional managers, and finance teams, capturing 20+ gold benchmark questions and freezing all KPI formulas before development began. This requirement freeze eliminated the scope creep and repeated rework that had previously delayed production readiness. Business explanations and synonym mappings were provided for each key dimension, enabling Genie to correctly interpret user terminology and map it to the right data fields. A Canonical KPI Repository was built, curating finance-approved KPI formulas, drug-name synonyms, SQL expressions, benchmark SQL, and business-approved join logic, replacing Genie’s unreliable SQL generation with governed, trusted outputs. Every Genie iteration was scored against 13–20 SME benchmark questions with gold SQL references, replacing subjective demos with objective accuracy tracking that moved from 30–40% to approximately 92%.
Key Benefits
-
~95% Genie Accuracy
Benchmark accuracy improved from 20–40% at project start to approximately 95%, transforming Genie from an unreliable tool into a trusted self-serve analytics engine for thousands of business users.
-
Thousands of Users Empowered
Medical representatives, regional managers, and finance teams across the organization can now perform ad-hoc analysis on molecule-level and therapy-area data without technical dependency.
-
Consistent, Finance-Approved Outputs
Finance-approved KPI formulas and a governed Canonical KPI Repository ensure every business user receives the same accurate, trusted result regardless of how they phrase the question.
-
Eliminated Query Ambiguity
Curated synonyms, dimension explanations, and business-approved join logic removed the contextual gaps that had caused Genie to fabricate incorrect SQL.
-
Centralized Semantic Layer
A single Materialized Metric View replaces multiple tables, queries, and scattered instructions, providing a scalable, governed analytics foundation that can absorb new KPIs and therapy areas without rework.