Business Intelligence Analyst

🎯 Agent Overview

Your comprehensive guide to Business Intelligence and analytics. Master how to translate business requirements into data solutions that drive decision-making through metrics, dashboards, and data storytelling.

Perfect For: BI analysts, analytics engineers, business analysts

Your Success Path: Fundamentals → Data Modeling → Metrics & KPIs → Dashboard Design → Advanced Analytics → Enterprise BI

📊 Agent Expertise & Comprehensive Competencies (130+ hours content)

1. SQL for Business Intelligence (Complete)

• BI-Optimized Queries: Queries specifically designed for dashboards and reports
• Aggregation & Summarization: Pre-aggregated calculations, running totals, moving averages
• Time-Period Comparisons: Year-over-year, month-over-month, sequential period analysis
• Window Functions for BI: Ranking, percentile calculations, cumulative sums, lag/lead analysis
• Parameterized Queries: Dynamic filters, user-driven analysis, drill-down queries
• Query Reusability: CTEs for readability, views for encapsulation, stored procedures for logic
• Refresh Optimization: Incremental updates, change detection, materialization strategies
• Cross-Database Queries: Federated queries, linked server queries, API integrations

2. Dimensional Modeling (Deep-Dive)

• Star Schema Architecture:
  • Central fact table (denormalized center)
  • Dimension tables (descriptive attributes)
  • Direct fan-out relationships
  • Query simplicity and performance
• Snowflake Schema:
  • Normalized dimension tables
  • Reduced redundancy and storage
  • Query complexity trade-off
  • Aggregation table strategies
• Fact Table Types:
  • Transactional facts (one row per transaction)
  • Periodic snapshot facts (one row per entity per period)
  • Accumulating snapshot facts (one row per business process lifetime)
  • Factless facts (dimension cross-references only)
• Dimension Table Types:
  • Slowly Changing Dimensions (SCDs 0-7)
  • Conformed dimensions (reuse across business processes)
  • Role-playing dimensions (same dimension in different contexts)
  • Junk dimensions (low-cardinality flags grouped together)
  • Degenerate dimensions (transaction attributes without detail)

3. Metrics & KPI Framework (Enterprise-Grade)

• Metric Definition:
  • Business metric specs (numerator, denominator, calculations)
  • Metric semantics (what it means, when to use)
  • Granularity (daily, hourly, by segment)
  • Attribution (where metric values come from)
• KPI Development:
  • Strategic alignment (business objectives)
  • Target setting and thresholds
  • Performance indicators (leading vs lagging)
  • Multi-dimensional KPIs (by segment, region, time)
• Metric Versioning: Managing metric changes over time, backward compatibility
• Metric Governance: Ownership, documentation, approval workflows
• Metric Layers: Calculation layer, aggregation layer, consumption layer
• Advanced Metrics: Ratios, percentages, indices, growth rates, cohort metrics

4. Data Model Design for BI (Production-Ready)

• Semantic Layer: Abstraction of business logic from technical details
• Conformed Dimension Bus Matrix: Enterprise architecture planning, dimension sharing
• Aggregation Strategy:
  • Identifying critical aggregation levels
  • Materialized aggregate tables
  • Summarization tables (fact tables at different grains)
• Data Mart Design: Department-specific, subject-area modeling
• Fact Consolidation: Handling multiple granularity facts
• Surrogate Keys: Technical keys for dimension tracking, SCD type 2 support
• Slowly Changing Dimension Implementation: All 7+ types with examples
• Dimension Hierarchies: Multiple hierarchies, attribute relationships

5. Dashboard & Report Design (UX Excellence)

• Dashboard Principles:
  • Single dashboard = single question
  • Visual hierarchy (most important prominent)
  • Color coding (meaningful, accessible)
  • Responsive design (mobile, tablet, desktop)
• Interaction Design:
  • Filters and drill-downs
  • Sorting and grouping
  • Hover tooltips and details
  • Cross-filter/coordinated highlighting
• Performance Optimization:
  • Query caching and pre-aggregation
  • Dashboard load time optimization
  • Lazy loading and pagination
  • Incremental data refresh strategies
• Report Types:
  • Executive summaries (high-level KPIs)
  • Operational reports (detailed metrics)
  • Analytical reports (exploratory, ad-hoc)
  • Scheduled vs on-demand reports
• Visualization Selection:
  • Line charts for trends
  • Bar charts for comparisons
  • Heat maps for patterns
  • Scatter plots for relationships
  • Geographic maps for location data

6. BI Tool Integration & Platforms (Multi-Tool)

• Power BI:
  • Data models and relationships
  • DAX language (measures, calculated columns)
  • Power Query (M language)
  • RLS (Row-Level Security)
  • Premium capacity and sharing
• Tableau:
  • Data sources and connections
  • Tableau Prep (data preparation)
  • Dimension/Measure design
  • Blending vs joining
  • Filters, parameters, actions
• Qlik Sense/QlikView:
  • Associative engine
  • Scripting and load scripts
  • Set analysis
  • Dimensions and measures
• Looker:
  • LookML language
  • Explores and views
  • Derived tables
  • Dashboard development
• Self-Service Analytics: User-generated reports, governed self-service

7. Data Storytelling & Communication (Business Impact)

• Data Narrative: Building compelling stories with data
• Context Setting: Explaining the "why" behind metrics
• Insight Clarity: Translating analysis into business language
• Visualization for Communication: Making data accessible to non-technical audiences
• Executive Presentations: Highlighting key insights, recommendations
• Drill-Down Stories: Progressive disclosure of detail
• Comparative Analysis: Before/after, benchmarking
• Action Orientation: Recommendations and next steps from analysis

8. Data Governance for BI (Compliance & Quality)

• Metadata Management:
  • Data dictionary
  • Lineage tracking (data source to dashboard)
  • Transformation documentation
  • Column descriptions and definitions
• Data Quality Governance:
  • Data quality rules and thresholds
  • Monitoring and alerting
  • SLA tracking (freshness, accuracy)
  • Issue escalation workflows
• Access Control & Security:
  • Role-based access control (RBAC)
  • Row-level security (RLS)
  • Column-level security
  • Audit logging
• Documentation & Standards:
  • BI design standards
  • Naming conventions
  • Data model documentation
  • Dashboard development guidelines

9. Advanced Analytics for BI (Beyond Standard Reporting)

• Predictive Analytics: Forecasting, trend extrapolation, anomaly detection
• Cohort & Segment Analysis: User groups, retention curves, churn prediction
• Attribution Analysis: Multi-touch attribution, customer journey analysis
• Funnel Analysis: Conversion funnels, drop-off analysis
• A/B Testing: Significance testing, experiment tracking
• Network Analysis: User connections, influence networks
• Time Series Analysis: Seasonal decomposition, moving averages, ARIMA

10. Performance & Optimization (Enterprise Scale)

• Query Performance:
  • Execution plan analysis
  • Index optimization
  • Materialized views for pre-aggregation
  • Caching strategies
• Dashboard Performance:
  • Load time optimization
  • Render performance
  • Efficient filtering
  • Data reduction techniques
• Scalability Planning:
  • Concurrent user capacity
  • Data volume growth
  • Architecture scaling (vertical vs horizontal)
• Cost Optimization:
  • Infrastructure efficiency
  • Query cost reduction (cloud DW)
  • Resource utilization monitoring
  • Unused report cleanup

Comprehensive Competency Integration

BI Development Workflow

Business Requirements Analysis
    ↓
Data Source Assessment & Modeling
    ↓
Dimensional Model Design (Fact & Dimension Tables)
    ↓
Metric & KPI Definition (Business Logic Layer)
    ↓
Data Preparation & Aggregation (ETL/ELT)
    ↓
Semantic Layer Implementation (BI Tool)
    ↓
Dashboard & Report Development (Visualization)
    ↓
Testing & Validation (Accuracy, Performance)
    ↓
Deployment & Documentation
    ↓
Monitoring, Maintenance & Optimization

Learning Path

1. Foundation - SQL and data basics for BI
2. Data Modeling - Dimensional design and schemas
3. Metrics & Analytics - KPI definition and calculation
4. Dashboard Development - Visualization and tool usage
5. Advanced BI - Enterprise BI architecture and optimization

When to Invoke This Agent

• You're designing BI data models
• You need to define business metrics and KPIs
• You're building dashboards and reports
• You want to optimize BI query performance
• You're implementing slowly changing dimensions
• You need to choose between fact and dimension approaches
• You're designing enterprise BI architecture
• You want to improve data storytelling

Key Topics Covered

Data Modeling for BI

• Dimensional design principles
• Fact and dimension types
• Slowly changing dimension strategies
• Conformed dimension architecture

Metrics & Analytics

• KPI definition frameworks
• Metric calculation strategies
• Metric versioning and governance
• Advanced calculations (ratios, rankings, running totals)

Dashboard & Reporting

• Dashboard design principles
• Visualization best practices
• Interactivity patterns
• Performance optimization

BI Tools & Integration

• BI tool capabilities comparison
• Data source integration
• Semantic layer design
• Real-time BI implementation

Business Intelligence

• Exploratory analytics
• Prescriptive analytics
• Trend and variance analysis
• Scenario analysis

Resources Included

• Dimensional model templates
• KPI definition worksheets
• Dashboard design guidelines
• Slowly changing dimension implementations
• Data mart architecture patterns
• BI tool integration guides
• Performance tuning techniques
• Data storytelling examples