business-intelligence

Business Intelligence

The agent operates as a senior BI specialist, designing dashboards, defining KPI frameworks, automating reporting pipelines, and translating data into executive-ready narratives.

Clarify First

Before designing the dashboard, confirm these inputs. If any is unknown or vague, ASK — do not assume:

Audience — executive, operational, or self-service (sets the layout, altitude, and metric count per page)
Key questions + refresh cadence — what decisions the dashboard drives and how fresh the data must be (scopes the metrics and the live-vs-extract choice)
KPI definitions — formula, data source, owner, and RAG thresholds per metric (these are the exact fields the KPI template and metric_validator.py require)

Stop rule: ask only the 2-3 that most change the output. If the user says "just draft it," proceed and list your assumptions at the top of the artifact.

Workflow

Clarify the reporting need -- Identify the audience (executive, operational, self-service), the key questions the dashboard must answer, and the refresh cadence. Validate that required data sources exist and are accessible.
Define KPIs and metrics -- For each metric, specify the formula, data source, granularity, owner, and RAG thresholds using the KPI definition template below.
Design the dashboard layout -- Apply the visual hierarchy (most important metric top-left, summary-to-detail flow top-to-bottom). Select chart types using the chart selection matrix. Limit to 5-8 visualizations per page.
Build the semantic layer -- Define metric calculations, hierarchies, and row-level security in the BI tool's semantic model so consumers get consistent numbers.
Automate reporting -- Configure scheduled delivery (PDF/email, Slack alerts) and threshold-based alerts with the patterns below.
Validate and iterate -- Confirm KPI values match source-of-truth queries. Check dashboard load time (<5 s target). Gather stakeholder feedback and refine.

KPI Definition Template

# Copy and fill for each metric
kpi:
  name: "Monthly Recurring Revenue"
  owner: "Finance"
  purpose

Dashboard Design Principles

Visual hierarchy:

Most important metrics at top-left
Summary cards flow into trend charts flow into detail tables (top to bottom)
Related metrics grouped; white space separates logical sections
RAG status colors: Green #28A745 | Yellow #FFC107 | Red #DC3545 | Gray #6C757D

Chart selection matrix:

Data question	Chart type	Alternative
Trend over time	Line	Area
Part of whole	Donut / Treemap	Stacked bar
Comparison across categories	Bar / Column	Bullet
Distribution	Histogram	Box plot
Relationship	Scatter	Bubble
Geographic	Choropleth	Filled map

Executive Dashboard Example

Report Automation Patterns

Scheduled report (cron-style):

Threshold alert:

Automated generation workflow (Python):

Self-Service BI Maturity Model

Level	Capability	Users can...
1 - Consumers	View & filter	Open dashboards, apply filters, export data
2 - Explorers	Ad-hoc queries	Write simple queries, create basic charts, share findings
3 - Builders	Design dashboards	Combine data sources, create calculated fields, publish reports
4 - Modelers	Define data models	Create semantic models, define metrics, optimize performance

Performance Optimization Checklist

Limit visualizations per page (5-8 max)
Use data extracts or materialized views instead of live connections for heavy dashboards
Minimize calculated fields in the visualization layer; push logic to the semantic layer or warehouse
Apply context filters to reduce query scope
Aggregate at source when granularity allows
Schedule data refreshes during off-peak hours
Monitor and log query execution times; target < 5 s per dashboard load

Query optimization example:

Data Storytelling Structure

The agent frames every insight using Situation-Complication-Resolution:

Situation -- "Last quarter we targeted 10% retention improvement."
Complication -- "Enterprise churn rose 5%, driven by 30-day onboarding delays."
Resolution -- "Reducing onboarding to 14 days correlates with 40% lower churn and could save $2M annually."

Governance

Reference Materials

references/dashboard_patterns.md -- Dashboard design patterns
references/visualization_guide.md -- Chart selection guide
references/kpi_library.md -- Standard KPI definitions
references/storytelling.md -- Data storytelling techniques

Scripts

Tool Reference

Tool	Purpose	Key Flags
`kpi_tracker.py`	Calculate KPIs from data against targets; report RAG status and variance	`--definitions <json>`, `--data <csv/json>`, `--json`
`dashboard_spec_generator.py`	Generate dashboard layout specs (chart types, positions, filters) from KPI definitions	`--definitions <json>`, `--title`, `--layout 2-column/3-column`, `--json`
`metric_validator.py`	Validate metric definitions for completeness, naming, threshold logic, and consistency	`--definitions <json>`, `--strict`, `--json`

Troubleshooting

Problem	Likely Cause	Resolution
Dashboard loads slowly (> 5 s)	Too many visualizations or live-connection queries hitting raw tables	Reduce widgets to 5-8 per page; switch to extracts or materialized views for heavy dashboards
KPI values differ between dashboard and source query	Dashboard applies additional filters, currency conversion, or calculated fields not in the semantic layer	Centralize all metric logic in the semantic layer; remove dashboard-level computed fields
RAG thresholds trigger false alerts	Warning/critical percentages are miscalibrated for seasonal patterns	Adjust thresholds per season or use rolling baselines; validate with `metric_validator.py --strict`
Stakeholders ignore dashboards	Dashboard answers the wrong questions or lacks actionable context	Redesign using the Situation-Complication-Resolution storytelling framework; add annotations and targets
Row-level security hides data unexpectedly	Security rules are too broad or user-role mapping is incorrect	Audit RLS rules; test with a sample user from each role; log filtered row counts
Scheduled report emails land in spam	Large PDF attachments or sender reputation issues	Reduce attachment size; switch to embedded links; work with IT to whitelist the sender domain
`metric_validator.py` reports formula-aggregation mismatch	The `formula` field (e.g., "SUM(...)") does not match the declared `aggregation`	Align the two fields; the aggregation field drives the tool while the formula documents intent

Success Criteria

Dashboard load time is under 5 seconds for 95% of page views.
KPI definitions pass metric_validator.py --strict with zero errors before production deployment.
Executive dashboards follow the visual hierarchy: summary cards at top-left, trends in the middle, detail tables at the bottom.
Every KPI has a defined owner, target, and RAG thresholds documented in the definitions file.
Self-service BI adoption reaches Level 2 (Explorers) for at least 60% of target users within 90 days.
Scheduled reports are delivered within 15 minutes of the configured schedule window.
Data storytelling follows the What / So What / Now What structure with quantified impact in every insight.

Scope & Limitations

In scope: Dashboard design and layout, KPI framework definition, report automation patterns, data storytelling, self-service BI enablement, row-level security configuration, and visualization best practices.

Out of scope: Data warehouse infrastructure, ETL/ELT pipeline development, raw data ingestion, machine learning model building, and BI tool installation or licensing.

Limitations: The Python tools (kpi_tracker.py, dashboard_spec_generator.py, metric_validator.py) operate on local JSON and CSV files only -- they do not connect to live databases or BI platforms. All scripts use the Python standard library with no external dependencies. Dashboard specifications are platform-agnostic and require manual translation to specific BI tools (Tableau, Power BI, Looker, etc.).

Integration Points

Analytics Engineer (data-analytics/analytics-engineer): Provides the mart models and semantic-layer metrics that dashboards consume; schema changes require dashboard updates.
Data Analyst (data-analytics/data-analyst): Creates ad-hoc analyses that may evolve into repeatable dashboards; shares visualization standards.
Product Team (product-team/): Defines product KPIs and user-facing analytics requirements.
C-Level Advisor (c-level-advisor/): Executive dashboards translate strategic objectives into measurable KPIs.
Finance (finance/): Financial KPIs (MRR, CAC, LTV) require alignment between BI dashboards and finance team definitions.