Business Intelligence
Expert-level business intelligence for data-driven decisions.
Core Competencies
- Dashboard design
- Data visualization
- Reporting automation
- KPI development
- Executive reporting
- Self-service BI
- Data storytelling
- Tool administration
BI Architecture
Data Flow
DATA SOURCES → ETL/ELT → DATA WAREHOUSE → SEMANTIC LAYER → DASHBOARDS
│ │ │ │ │
▼ ▼ ▼ ▼ ▼
CRM, ERP Transform Star Schema Metrics Def Tableau/PBI
APIs, DBs Clean, Load Fact/Dims Calculations Looker/etc
BI Stack Components
PRESENTATION LAYER
├── Executive dashboards
├── Operational reports
├── Self-service exploration
└── Embedded analytics
SEMANTIC LAYER
├── Business metrics definitions
├── Calculated fields
├── Hierarchies
└── Row-level security
DATA LAYER
├── Data warehouse (Snowflake/BigQuery/Redshift)
├── Data marts
├── Materialized views
└── Cached datasets
Dashboard Design
Dashboard Types
Executive Dashboard:
┌─────────────────────────────────────────────────────────────┐
│ EXECUTIVE SUMMARY │
├─────────────────────────────────────────────────────────────┤
│ Revenue Pipeline Customers NPS │
│ $12.4M $45.2M 2,847 72 │
│ +15% YoY +22% QoQ +340 MTD +5 pts │
├─────────────────────────────────────────────────────────────┤
│ REVENUE TREND │ REVENUE BY SEGMENT │
│ [Line chart: 12 months] │ [Pie chart: segments] │
├────────────────────────────────┼─────────────────────────────┤
│ TOP ACCOUNTS │ KEY METRICS STATUS │
│ [Table: top 10] │ [KPI cards with RAG] │
└─────────────────────────────────────────────────────────────┘
Operational Dashboard:
┌─────────────────────────────────────────────────────────────┐
│ DAILY OPERATIONS │
├─────────────────────────────────────────────────────────────┤
│ Orders Today Tickets Open Avg Response SLA Met │
│ 1,247 89 12 min 98.5% │
│ vs Avg: +8% vs Avg: -12% vs Target: ✓ vs Target: ✓ │
├─────────────────────────────────────────────────────────────┤
│ HOURLY VOLUME │ QUEUE STATUS │
│ [Area chart: 24h] │ [Stacked bar by team] │
├────────────────────────────────┼─────────────────────────────┤
│ ALERTS │ TEAM PERFORMANCE │
│ [Alert list with severity] │ [Table: agents + metrics] │
└─────────────────────────────────────────────────────────────┘
Design Principles
Visual Hierarchy:
- Most important metrics at top-left
- Summary → Detail flow (top to bottom)
- Related metrics grouped together
- White space for readability
Color Usage:
STATUS COLORS
├── Green (#28A745): Good/On Track
├── Yellow (#FFC107): Warning/At Risk
├── Red (#DC3545): Critical/Off Track
└── Gray (#6C757D): Neutral/No Status
BRAND COLORS
├── Primary: Use for emphasis
├── Secondary: Supporting elements
└── Accent: Highlights only
DATA COLORS
├── Sequential: Light → Dark for ranges
├── Diverging: Different hues for pos/neg
└── Categorical: Distinct colors per category
Chart Selection:
| Data Type | Best Charts | |-----------|-------------| | Trend over time | Line, Area | | Part of whole | Pie, Donut, Treemap | | Comparison | Bar, Column | | Distribution | Histogram, Box Plot | | Relationship | Scatter, Bubble | | Geographic | Map, Choropleth |
KPI Framework
KPI Development
# KPI Definition: [Metric Name]
## Business Context
- Owner: [Department/Role]
- Purpose: [Why this metric matters]
- Strategic alignment: [Goal it supports]
## Definition
- Formula: [Calculation]
- Data source: [System/Table]
- Granularity: [Daily/Weekly/Monthly]
## Targets
- Target: [Value]
- Threshold (Yellow): [Value]
- Critical (Red): [Value]
## Dimensions
- Time: [Day/Week/Month/Quarter/Year]
- Segments: [By region, product, etc.]
## Caveats
- [Known limitations]
- [Data quality issues]
Metric Categories
Financial: | Metric | Formula | Frequency | |--------|---------|-----------| | Revenue | Sum of closed won | Daily | | MRR | Monthly recurring | Monthly | | Gross Margin | (Rev - COGS) / Rev | Monthly | | CAC | S&M Spend / New Customers | Monthly | | LTV | ARPU × Margin × Lifetime | Quarterly |
Customer: | Metric | Formula | Frequency | |--------|---------|-----------| | Active Users | DAU, WAU, MAU | Daily | | Churn Rate | Lost / Total | Monthly | | NPS | Promoters - Detractors | Quarterly | | CSAT | Satisfied / Responses | Weekly |
Operations: | Metric | Formula | Frequency | |--------|---------|-----------| | Throughput | Units / Time | Hourly | | Error Rate | Errors / Total | Daily | | Cycle Time | End - Start | Daily | | Utilization | Active / Capacity | Daily |
Report Automation
Report Types
Scheduled Reports:
report:
name: Weekly Sales Report
schedule: "0 8 * * MON" # Every Monday 8am
recipients:
- sales-team@company.com
- leadership@company.com
format: PDF
pages:
- Executive Summary
- Pipeline Analysis
- Rep Performance
- Forecast
Threshold Alerts:
alert:
name: Revenue Below Target
metric: daily_revenue
condition: actual < target * 0.9
frequency: daily
channels:
- email: finance@company.com
- slack: #revenue-alerts
message: |
Daily revenue of ${actual} is ${pct_diff}% below target.
Top contributing factors: ${top_factors}
Automation Patterns
def generate_report(report_config):
"""
Automated report generation workflow
"""
# 1. Refresh data
refresh_data_sources(report_config['sources'])
# 2. Calculate metrics
metrics = calculate_metrics(report_config['metrics'])
# 3. Generate visualizations
charts = create_visualizations(metrics, report_config['charts'])
# 4. Build report
report = compile_report(
metrics=metrics,
charts=charts,
template=report_config['template']
)
# 5. Distribute
distribute_report(
report=report,
recipients=report_config['recipients'],
format=report_config['format']
)
return report
Self-Service BI
Enablement Framework
SELF-SERVICE MATURITY MODEL
Level 1: Report Consumers
├── View existing dashboards
├── Apply filters
└── Export data
Level 2: Data Explorers
├── Ad-hoc queries
├── Create simple charts
└── Share findings
Level 3: Report Builders
├── Design dashboards
├── Combine data sources
└── Create calculated fields
Level 4: Data Modelers
├── Create data models
├── Define metrics
└── Optimize performance
Data Catalog
# Data Catalog Entry
## Dataset: sales_opportunities
### Description
Contains all sales opportunities from CRM
### Schema
| Column | Type | Description |
|--------|------|-------------|
| opp_id | STRING | Unique identifier |
| account_id | STRING | Related account |
| amount | DECIMAL | Deal value |
| stage | STRING | Pipeline stage |
| close_date | DATE | Expected close |
| owner_id | STRING | Sales rep |
### Refresh
- Frequency: Every 4 hours
- Source: Salesforce API
- Last refresh: 2024-01-15 08:00 UTC
### Usage Notes
- Filter by is_deleted = false
- Amount is always in USD
- Stage values: Prospect, Discovery, Demo, Proposal, Negotiation, Closed Won, Closed Lost
### Related Datasets
- accounts
- sales_reps
- products
Data Storytelling
Narrative Structure
SITUATION → COMPLICATION → RESOLUTION
1. SITUATION (Context)
"Last quarter, we set a goal to increase customer retention by 10%"
2. COMPLICATION (Problem/Opportunity)
"However, churn increased by 5% in our enterprise segment"
3. RESOLUTION (Insight + Action)
"Analysis shows onboarding time correlates with churn.
Reducing onboarding from 30 to 14 days could save $2M annually"
Insight Framework
# Insight: [Title]
## What happened?
[Describe the observation in data]
## Why does it matter?
[Business impact and context]
## Why did it happen?
[Root cause analysis]
## What should we do?
[Recommended actions]
## Supporting Data
[Charts and metrics]
Presentation Template
EXECUTIVE PRESENTATION STRUCTURE
1. Headlines First (2-3 key takeaways)
2. Context (why we're looking at this)
3. Key Findings (data + insights)
4. Implications (what it means)
5. Recommendations (what to do)
6. Appendix (detailed data)
Tool Administration
Performance Optimization
Dashboard Performance:
OPTIMIZATION CHECKLIST
□ Limit visualizations per page (5-8 max)
□ Use data extracts vs live connections
□ Minimize calculated fields in viz
□ Use context filters effectively
□ Aggregate data at source when possible
□ Schedule refreshes during off-peak
□ Monitor query execution times
Query Optimization:
-- Bad: Full table scan
SELECT * FROM large_table
WHERE date >= '2024-01-01';
-- Good: Partitioned and filtered
SELECT required_columns
FROM large_table
WHERE partition_date >= '2024-01-01'
AND status = 'active'
LIMIT 10000;
Governance
Access Control:
security_model:
row_level_security:
- rule: region_access
filter: "region = user.region"
- rule: team_access
filter: "team_id IN user.teams"
object_permissions:
- role: viewer
permissions: [view, export]
- role: editor
permissions: [view, export, edit]
- role: admin
permissions: [view, export, edit, delete, publish]
Data Quality Monitoring:
DATA QUALITY CHECKS
├── Freshness: Is data current?
├── Completeness: Are all records present?
├── Accuracy: Do values make sense?
├── Consistency: Do related metrics align?
└── Uniqueness: Are there duplicates?
Reference Materials
references/dashboard_patterns.md- Dashboard design patternsreferences/visualization_guide.md- Chart selection guidereferences/kpi_library.md- Standard KPI definitionsreferences/storytelling.md- Data storytelling techniques
Scripts
# Dashboard performance analyzer
python scripts/dashboard_analyzer.py --dashboard "Sales Overview"
# KPI calculator
python scripts/kpi_calculator.py --config metrics.yaml --output report.json
# Report generator
python scripts/report_generator.py --template weekly_sales --format pdf
# Data quality checker
python scripts/data_quality.py --dataset sales_opportunities --checks all