Implementation Guide: Analyze project profitability and utilization rates by consultant and engagement type

az login
az group create --name rg-profitability-analytics --location eastus
az sql server create --name sql-profitability-[clientname] --resource-group rg-profitability-analytics --location eastus --admin-user sqladmin --admin-password [GenerateStrongPassword]
az sql db create --resource-group rg-profitability-analytics --server sql-profitability-[clientname] --name db-analytics --service-objective S0 --backup-storage-redundancy Local
az sql server firewall-rule create --resource-group rg-profitability-analytics --server sql-profitability-[clientname] --name AllowAzureServices --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0
az sql server firewall-rule create --resource-group rg-profitability-analytics --server sql-profitability-[clientname] --name AllowOfficeIP --start-ip-address [CLIENT_OFFICE_IP] --end-ip-address [CLIENT_OFFICE_IP]

-- Connect via Azure Data Studio or SSMS to sql-profitability-[clientname].database.windows.net

CREATE SCHEMA analytics;
GO

-- Dimension Tables
CREATE TABLE analytics.dim_consultant (
    consultant_id INT PRIMARY KEY,
    consultant_name NVARCHAR(200),
    email NVARCHAR(200),
    role_level NVARCHAR(100), -- e.g., Analyst, Senior Consultant, Manager, Partner
    department NVARCHAR(100),
    hire_date DATE,
    termination_date DATE NULL,
    hourly_cost_rate DECIMAL(10,2), -- Fully loaded cost rate
    standard_bill_rate DECIMAL(10,2),
    annual_available_hours INT DEFAULT 2080, -- Adjusted for PTO/holidays
    utilization_target DECIMAL(5,2), -- e.g., 0.75 for 75%
    is_active BIT DEFAULT 1,
    last_updated DATETIME2 DEFAULT GETDATE()
);

CREATE TABLE analytics.dim_project (
    project_id INT PRIMARY KEY,
    project_name NVARCHAR(500),
    project_code NVARCHAR(50),
    client_id INT,
    client_name NVARCHAR(200),
    engagement_type NVARCHAR(100), -- From defined taxonomy
    billing_method NVARCHAR(50), -- T&M, Fixed-Fee, Retainer, Milestone
    project_status NVARCHAR(50), -- Active, Complete, On-Hold, Cancelled
    start_date DATE,
    end_date DATE NULL,
    budget_hours DECIMAL(10,2),
    budget_amount DECIMAL(12,2),
    contract_value DECIMAL(12,2),
    project_manager_id INT,
    created_date DATE,
    last_updated DATETIME2 DEFAULT GETDATE()
);

CREATE TABLE analytics.dim_date (
    date_key INT PRIMARY KEY, -- YYYYMMDD format
    full_date DATE,
    day_of_week INT,
    day_name NVARCHAR(10),
    week_number INT,
    month_number INT,
    month_name NVARCHAR(10),
    quarter_number INT,
    quarter_name NVARCHAR(6),
    year_number INT,
    fiscal_year INT,
    fiscal_quarter INT,
    is_weekday BIT,
    is_holiday BIT DEFAULT 0
);

CREATE TABLE analytics.dim_engagement_type (
    engagement_type_id INT PRIMARY KEY IDENTITY(1,1),
    engagement_type_name NVARCHAR(100),
    engagement_type_category NVARCHAR(100), -- Advisory, Implementation, Managed Services, etc.
    description NVARCHAR(500),
    target_margin DECIMAL(5,2)
);

-- Fact Tables
CREATE TABLE analytics.fact_time_entry (
    time_entry_id BIGINT PRIMARY KEY,
    consultant_id INT REFERENCES analytics.dim_consultant(consultant_id),
    project_id INT REFERENCES analytics.dim_project(project_id),
    date_key INT REFERENCES analytics.dim_date(date_key),
    hours_worked DECIMAL(6,2),
    is_billable BIT,
    bill_rate_applied DECIMAL(10,2),
    cost_rate_applied DECIMAL(10,2),
    billable_amount AS (hours_worked * bill_rate_applied),
    cost_amount AS (hours_worked * cost_rate_applied),
    time_entry_status NVARCHAR(50), -- Submitted, Approved, Invoiced
    notes NVARCHAR(1000),
    source_system NVARCHAR(50), -- BigTime, manual import, etc.
    last_updated DATETIME2 DEFAULT GETDATE()
);

CREATE TABLE analytics.fact_invoice (
    invoice_id INT PRIMARY KEY,
    project_id INT REFERENCES analytics.dim_project(project_id),
    client_id INT,
    invoice_date DATE,
    date_key INT REFERENCES analytics.dim_date(date_key),
    invoice_amount DECIMAL(12,2),
    amount_paid DECIMAL(12,2) DEFAULT 0,
    payment_date DATE NULL,
    invoice_status NVARCHAR(50), -- Draft, Sent, Paid, Overdue, Written-Off
    source_system NVARCHAR(50),
    last_updated DATETIME2 DEFAULT GETDATE()
);

CREATE TABLE analytics.fact_project_monthly_snapshot (
    snapshot_id BIGINT IDENTITY(1,1) PRIMARY KEY,
    project_id INT REFERENCES analytics.dim_project(project_id),
    snapshot_month DATE, -- First day of month
    total_hours_to_date DECIMAL(10,2),
    billable_hours_to_date DECIMAL(10,2),
    revenue_recognized_to_date DECIMAL(12,2),
    total_cost_to_date DECIMAL(12,2),
    budget_consumed_pct DECIMAL(5,2),
    gross_margin_pct AS CASE WHEN revenue_recognized_to_date > 0 THEN (revenue_recognized_to_date - total_cost_to_date) / revenue_recognized_to_date ELSE 0 END,
    last_updated DATETIME2 DEFAULT GETDATE()
);

-- Populate date dimension (5 years)
DECLARE @StartDate DATE = '2020-01-01';
DECLARE @EndDate DATE = '2029-12-31';
WHILE @StartDate <= @EndDate
BEGIN
    INSERT INTO analytics.dim_date (date_key, full_date, day_of_week, day_name, week_number, month_number, month_name, quarter_number, quarter_name, year_number, fiscal_year, fiscal_quarter, is_weekday)
    VALUES (
        CAST(FORMAT(@StartDate,'yyyyMMdd') AS INT),
        @StartDate,
        DATEPART(WEEKDAY, @StartDate),
        DATENAME(WEEKDAY, @StartDate),
        DATEPART(WEEK, @StartDate),
        MONTH(@StartDate),
        DATENAME(MONTH, @StartDate),
        DATEPART(QUARTER, @StartDate),
        'Q' + CAST(DATEPART(QUARTER, @StartDate) AS NVARCHAR),
        YEAR(@StartDate),
        CASE WHEN MONTH(@StartDate) >= 7 THEN YEAR(@StartDate)+1 ELSE YEAR(@StartDate) END,
        CASE WHEN MONTH(@StartDate) >= 7 THEN DATEPART(QUARTER, @StartDate)-2 ELSE DATEPART(QUARTER, @StartDate)+2 END,
        CASE WHEN DATEPART(WEEKDAY, @StartDate) BETWEEN 2 AND 6 THEN 1 ELSE 0 END
    );
    SET @StartDate = DATEADD(DAY, 1, @StartDate);
END;

# Nightly Azure Function to sync QBO invoices into Azure SQL analytics
# schema

# Python script: qbo_to_azure.py (runs nightly via Azure Function Timer Trigger)
# pip install intuitlib quickbooks-python pyodbc

import pyodbc
from quickbooks import QuickBooks
from quickbooks.objects.account import Account
from quickbooks.objects.invoice import Invoice
from intuitlib.client import AuthClient

# OAuth2 connection to QBO
auth_client = AuthClient(
    client_id='[QBO_CLIENT_ID]',
    client_secret='[QBO_CLIENT_SECRET]',
    redirect_uri='https://[your-function-app].azurewebsites.net/api/callback',
    environment='production'
)

qb_client = QuickBooks(
    auth_client=auth_client,
    refresh_token='[REFRESH_TOKEN]',
    company_id='[COMPANY_ID]'
)

# Fetch invoices updated in last 24 hours
from datetime import datetime, timedelta
yesterday = (datetime.now() - timedelta(days=1)).strftime('%Y-%m-%dT00:00:00')
invoices = Invoice.filter(
    MetaData_LastUpdatedTime=yesterday,
    qb=qb_client
)

# Write to Azure SQL
conn = pyodbc.connect('Driver={ODBC Driver 18 for SQL Server};Server=sql-profitability-[clientname].database.windows.net;Database=db-analytics;Uid=sqladmin;Pwd=[password];Encrypt=yes;TrustServerCertificate=no;')
cursor = conn.cursor()

for inv in invoices:
    cursor.execute('''
        MERGE analytics.fact_invoice AS target
        USING (VALUES (?, ?, ?, ?, ?, ?, ?, ?)) AS source
            (invoice_id, project_id, client_id, invoice_date, invoice_amount, amount_paid, payment_date, invoice_status)
        ON target.invoice_id = source.invoice_id
        WHEN MATCHED THEN UPDATE SET
            amount_paid = source.amount_paid,
            payment_date = source.payment_date,
            invoice_status = source.invoice_status,
            last_updated = GETDATE()
        WHEN NOT MATCHED THEN INSERT
            (invoice_id, project_id, client_id, invoice_date, date_key, invoice_amount, amount_paid, payment_date, invoice_status, source_system)
            VALUES (source.invoice_id, source.project_id, source.client_id, source.invoice_date,
                    CAST(FORMAT(source.invoice_date,'yyyyMMdd') AS INT),
                    source.invoice_amount, source.amount_paid, source.payment_date, source.invoice_status, 'QuickBooksOnline');
    ''', inv.Id, None, inv.CustomerRef.value, inv.TxnDate, float(inv.TotalAmt), float(inv.Balance or 0), None, 'Sent')

conn.commit()
conn.close()

# Azure Function (Timer Trigger) to sync BigTime time entries and projects
# into Azure SQL every 4 hours

# Python script: bigtime_etl.py (Azure Function - Timer Trigger, runs every 4 hours)
# pip install requests pyodbc

import requests
import pyodbc
import os
from datetime import datetime, timedelta

BIGTIME_API_BASE = 'https://iq.bigtime.net/BigtimeData/api/v2'
BIGTIME_TOKEN = os.environ['BIGTIME_API_TOKEN']  # Store in Azure Key Vault
AZURE_SQL_CONN = os.environ['AZURE_SQL_CONNECTION_STRING']

headers = {
    'X-Auth-Token': BIGTIME_TOKEN,
    'X-Auth-Realm': os.environ['BIGTIME_FIRM_ID'],
    'Content-Type': 'application/json'
}

def sync_time_entries():
    """Fetch time entries updated in last 24 hours and upsert to Azure SQL"""
    yesterday = (datetime.now() - timedelta(days=1)).strftime('%Y-%m-%d')
    
    # Get time entries
    response = requests.get(
        f'{BIGTIME_API_BASE}/time/Sheet',
        headers=headers,
        params={'UpdatedAfter': yesterday}
    )
    entries = response.json()
    
    conn = pyodbc.connect(AZURE_SQL_CONN)
    cursor = conn.cursor()
    
    for entry in entries:
        cursor.execute('''
            MERGE analytics.fact_time_entry AS target
            USING (VALUES (?,?,?,?,?,?,?,?,?,?)) AS source
                (time_entry_id, consultant_id, project_id, date_key, hours_worked,
                 is_billable, bill_rate_applied, cost_rate_applied, time_entry_status, notes)
            ON target.time_entry_id = source.time_entry_id
            WHEN MATCHED THEN UPDATE SET
                hours_worked = source.hours_worked,
                bill_rate_applied = source.bill_rate_applied,
                time_entry_status = source.time_entry_status,
                last_updated = GETDATE()
            WHEN NOT MATCHED THEN INSERT
                (time_entry_id, consultant_id, project_id, date_key, hours_worked,
                 is_billable, bill_rate_applied, cost_rate_applied, time_entry_status, notes, source_system)
                VALUES (source.time_entry_id, source.consultant_id, source.project_id,
                        CAST(FORMAT(source.date_key,'yyyyMMdd') AS INT),
                        source.hours_worked, source.is_billable, source.bill_rate_applied,
                        source.cost_rate_applied, source.time_entry_status, source.notes, 'BigTime');
        ''', entry.get('Id'), entry.get('StaffSid'), entry.get('ProjectSid'),
            entry.get('Dt'), entry.get('Hours_Worked'), 
            1 if entry.get('Billable') else 0,
            entry.get('BillRate', 0), entry.get('CostRate', 0),
            entry.get('ApprovalStatus', 'Submitted'), entry.get('Notes', ''))
    
    conn.commit()
    conn.close()
    return f'Synced {len(entries)} time entries'

def sync_projects():
    """Fetch all active projects and update dim_project"""
    response = requests.get(
        f'{BIGTIME_API_BASE}/project',
        headers=headers
    )
    projects = response.json()
    
    conn = pyodbc.connect(AZURE_SQL_CONN)
    cursor = conn.cursor()
    
    for proj in projects:
        cursor.execute('''
            MERGE analytics.dim_project AS target
            USING (VALUES (?,?,?,?,?,?,?,?,?,?)) AS source
                (project_id, project_name, project_code, client_name, engagement_type,
                 billing_method, project_status, start_date, budget_hours, budget_amount)
            ON target.project_id = source.project_id
            WHEN MATCHED THEN UPDATE SET
                project_status = source.project_status,
                budget_hours = source.budget_hours,
                budget_amount = source.budget_amount,
                last_updated = GETDATE()
            WHEN NOT MATCHED THEN INSERT
                (project_id, project_name, project_code, client_name, engagement_type,
                 billing_method, project_status, start_date, budget_hours, budget_amount)
                VALUES (source.project_id, source.project_name, source.project_code,
                        source.client_name, source.engagement_type, source.billing_method,
                        source.project_status, source.start_date, source.budget_hours, source.budget_amount);
        ''', proj.get('ProjectSid'), proj.get('ProjectNm'), proj.get('ProjectCode'),
            proj.get('ClientNm'), proj.get('ProjectType', 'Unclassified'),
            proj.get('BillingType', 'T&M'), proj.get('Status', 'Active'),
            proj.get('StartDt'), proj.get('BudgetHrs', 0), proj.get('BudgetAmt', 0))
    
    conn.commit()
    conn.close()
    return f'Synced {len(projects)} projects'

# Main execution
if __name__ == '__main__':
    print(sync_time_entries())
    print(sync_projects())

bcp analytics.fact_time_entry in historical_time_entries.csv -S sql-profitability-[clientname].database.windows.net -d db-analytics -U sqladmin -P [password] -c -t ',' -F 2

SELECT 'Time Entries' AS dataset, COUNT(*) AS record_count, 
       MIN(full_date) AS earliest_date, MAX(full_date) AS latest_date
FROM analytics.fact_time_entry t
JOIN analytics.dim_date d ON t.date_key = d.date_key
UNION ALL
SELECT 'Projects', COUNT(*), MIN(start_date), MAX(start_date)
FROM analytics.dim_project
UNION ALL
SELECT 'Consultants', COUNT(*), MIN(hire_date), MAX(hire_date)
FROM analytics.dim_consultant
UNION ALL
SELECT 'Invoices', COUNT(*), MIN(invoice_date), MAX(invoice_date)
FROM analytics.fact_invoice;

SELECT 
    YEAR(d.full_date) AS year,
    MONTH(d.full_date) AS month,
    SUM(CASE WHEN t.is_billable = 1 THEN t.hours_worked ELSE 0 END) AS billable_hours,
    SUM(t.hours_worked * t.bill_rate_applied) AS gross_revenue,
    SUM(t.hours_worked * t.cost_rate_applied) AS total_cost
FROM analytics.fact_time_entry t
JOIN analytics.dim_date d ON t.date_key = d.date_key
GROUP BY YEAR(d.full_date), MONTH(d.full_date)
ORDER BY year, month;

# create these in Power BI Desktop → Model view → New Measure

-- ===== UTILIZATION MEASURES =====

Total Hours = SUM(fact_time_entry[hours_worked])

Billable Hours = CALCULATE(SUM(fact_time_entry[hours_worked]), fact_time_entry[is_billable] = TRUE())

Non-Billable Hours = CALCULATE(SUM(fact_time_entry[hours_worked]), fact_time_entry[is_billable] = FALSE())

Available Hours = 
    SUMX(
        VALUES(dim_consultant[consultant_id]),
        DIVIDE(
            LOOKUPVALUE(dim_consultant[annual_available_hours], dim_consultant[consultant_id], dim_consultant[consultant_id]),
            12
        ) * DISTINCTCOUNT(dim_date[month_number])
    )

Utilization Rate = DIVIDE([Billable Hours], [Available Hours], 0)

Utilization Target = 
    AVERAGEX(
        VALUES(dim_consultant[consultant_id]),
        LOOKUPVALUE(dim_consultant[utilization_target], dim_consultant[consultant_id], dim_consultant[consultant_id])
    )

Utilization Variance = [Utilization Rate] - [Utilization Target]

Utilization RAG = 
    SWITCH(TRUE(),
        [Utilization Rate] >= [Utilization Target], "Green",
        [Utilization Rate] >= [Utilization Target] * 0.9, "Amber",
        "Red"
    )

-- ===== REVENUE MEASURES =====

Gross Revenue = SUMX(fact_time_entry, fact_time_entry[hours_worked] * fact_time_entry[bill_rate_applied])

Net Revenue = SUM(fact_invoice[invoice_amount])

Revenue per Billable Hour = DIVIDE([Gross Revenue], [Billable Hours], 0)

Realization Rate = DIVIDE([Net Revenue], [Gross Revenue], 0)

-- ===== COST MEASURES =====

Direct Labor Cost = SUMX(fact_time_entry, fact_time_entry[hours_worked] * fact_time_entry[cost_rate_applied])

Cost per Hour = DIVIDE([Direct Labor Cost], [Total Hours], 0)

-- ===== PROFITABILITY MEASURES =====

Gross Margin = [Gross Revenue] - [Direct Labor Cost]

Gross Margin % = DIVIDE([Gross Margin], [Gross Revenue], 0)

Net Margin = [Net Revenue] - [Direct Labor Cost]

Net Margin % = DIVIDE([Net Margin], [Net Revenue], 0)

Effective Multiplier = DIVIDE([Revenue per Billable Hour], [Cost per Hour], 0)

-- ===== PROJECT MEASURES =====

Budget Consumed % = 
    DIVIDE(
        SUM(fact_time_entry[hours_worked]),
        LOOKUPVALUE(dim_project[budget_hours], dim_project[project_id], MAX(dim_project[project_id])),
        0
    )

Budget Revenue Consumed % = 
    DIVIDE(
        [Gross Revenue],
        LOOKUPVALUE(dim_project[budget_amount], dim_project[project_id], MAX(dim_project[project_id])),
        0
    )

Project Health = 
    SWITCH(TRUE(),
        [Budget Consumed %] > 1.0, "Over Budget",
        [Budget Consumed %] > 0.85 && [Gross Margin %] < 0.15, "At Risk",
        [Gross Margin %] >= 0.20, "Healthy",
        "Monitor"
    )

-- ===== TREND MEASURES =====

Revenue MoM Growth = 
    VAR CurrentMonth = [Gross Revenue]
    VAR PriorMonth = CALCULATE([Gross Revenue], DATEADD(dim_date[full_date], -1, MONTH))
    RETURN DIVIDE(CurrentMonth - PriorMonth, PriorMonth, 0)

Utilization 3Mo Rolling Avg = 
    CALCULATE(
        [Utilization Rate],
        DATESINPERIOD(dim_date[full_date], MAX(dim_date[full_date]), -3, MONTH)
    )

# restricts each user to their own data

[consultant_id] = USERPRINCIPALNAME()

# restricts view to the manager's department

[department] = LOOKUPVALUE(...)

'Alert: {AlertTitle} - Current value: {AlertValue}'

[department] = LOOKUPVALUE(
    dim_consultant[department],
    dim_consultant[email],
    USERPRINCIPALNAME()
)

[email] = USERPRINCIPALNAME()

Connect-AzureAD
New-AzureADGroup -DisplayName 'PS-Analytics-Partners' -MailEnabled $false -SecurityEnabled $true -MailNickName 'ps-analytics-partners'
New-AzureADGroup -DisplayName 'PS-Analytics-Managers' -MailEnabled $false -SecurityEnabled $true -MailNickName 'ps-analytics-managers'
New-AzureADGroup -DisplayName 'PS-Analytics-Consultants' -MailEnabled $false -SecurityEnabled $true -MailNickName 'ps-analytics-consultants'

import azure.functions as func
import pyodbc
import json
import requests
import os
from datetime import datetime, timedelta
import statistics

def main(timer: func.TimerRequest) -> None:
    """Daily anomaly detection for project profitability and utilization."""
    
    conn_str = os.environ['AZURE_SQL_CONNECTION_STRING']
    teams_webhook = os.environ['TEAMS_WEBHOOK_URL']
    
    conn = pyodbc.connect(conn_str)
    cursor = conn.cursor()
    
    anomalies = []
    
    # === ANOMALY 1: Projects trending toward negative margin ===
    cursor.execute('''
        WITH ProjectMetrics AS (
            SELECT 
                p.project_id,
                p.project_name,
                p.client_name,
                p.engagement_type,
                p.budget_amount,
                SUM(t.hours_worked * t.bill_rate_applied) AS revenue_to_date,
                SUM(t.hours_worked * t.cost_rate_applied) AS cost_to_date,
                SUM(t.hours_worked) AS hours_to_date,
                p.budget_hours,
                CASE WHEN SUM(t.hours_worked * t.bill_rate_applied) > 0
                    THEN (SUM(t.hours_worked * t.bill_rate_applied) - SUM(t.hours_worked * t.cost_rate_applied)) 
                         / SUM(t.hours_worked * t.bill_rate_applied)
                    ELSE 0 END AS current_margin_pct,
                CASE WHEN p.budget_hours > 0
                    THEN SUM(t.hours_worked) / p.budget_hours
                    ELSE 0 END AS budget_consumed_pct
            FROM analytics.dim_project p
            JOIN analytics.fact_time_entry t ON p.project_id = t.project_id
            WHERE p.project_status = 'Active'
            GROUP BY p.project_id, p.project_name, p.client_name, 
                     p.engagement_type, p.budget_amount, p.budget_hours
        )
        SELECT * FROM ProjectMetrics
        WHERE current_margin_pct < 0.10  -- Margin below 10%
           OR (budget_consumed_pct > 0.80 AND current_margin_pct < 0.15)  -- 80%+ budget used, thin margin
           OR budget_consumed_pct > 1.0  -- Over budget
        ORDER BY current_margin_pct ASC
    ''')
    
    at_risk_projects = cursor.fetchall()
    for proj in at_risk_projects:
        severity = 'CRITICAL' if proj.current_margin_pct < 0 else 'WARNING'
        anomalies.append({
            'type': 'Project Profitability',
            'severity': severity,
            'entity': proj.project_name,
            'client': proj.client_name,
            'detail': f'Margin: {proj.current_margin_pct:.1%} | Budget consumed: {proj.budget_consumed_pct:.1%} | Revenue: ${proj.revenue_to_date:,.0f} | Cost: ${proj.cost_to_date:,.0f}'
        })
    
    # === ANOMALY 2: Consultant utilization outside normal range ===
    cursor.execute('''
        WITH WeeklyUtil AS (
            SELECT 
                c.consultant_id,
                c.consultant_name,
                c.role_level,
                c.utilization_target,
                d.year_number,
                d.week_number,
                SUM(CASE WHEN t.is_billable = 1 THEN t.hours_worked ELSE 0 END) AS billable_hours,
                SUM(t.hours_worked) AS total_hours
            FROM analytics.dim_consultant c
            JOIN analytics.fact_time_entry t ON c.consultant_id = t.consultant_id
            JOIN analytics.dim_date d ON t.date_key = d.date_key
            WHERE c.is_active = 1
              AND d.full_date >= DATEADD(WEEK, -12, GETDATE())  -- Last 12 weeks
            GROUP BY c.consultant_id, c.consultant_name, c.role_level, 
                     c.utilization_target, d.year_number, d.week_number
        ),
        ConsultantStats AS (
            SELECT
                consultant_id,
                consultant_name,
                role_level,
                utilization_target,
                AVG(billable_hours / 40.0) AS avg_utilization,
                STDEV(billable_hours / 40.0) AS stdev_utilization,
                -- Current week (most recent)
                (SELECT TOP 1 billable_hours / 40.0 
                 FROM WeeklyUtil w2 
                 WHERE w2.consultant_id = WeeklyUtil.consultant_id 
                 ORDER BY year_number DESC, week_number DESC) AS current_week_util
            FROM WeeklyUtil
            GROUP BY consultant_id, consultant_name, role_level, utilization_target
            HAVING COUNT(*) >= 4  -- Need at least 4 weeks of data
        )
        SELECT * FROM ConsultantStats
        WHERE ABS(current_week_util - avg_utilization) > 2 * ISNULL(NULLIF(stdev_utilization, 0), 0.1)
           OR current_week_util < utilization_target * 0.7  -- More than 30% below target
        ORDER BY (current_week_util - utilization_target) ASC
    ''')
    
    util_anomalies = cursor.fetchall()
    for cons in util_anomalies:
        severity = 'WARNING' if cons.current_week_util < cons.utilization_target * 0.5 else 'INFO'
        anomalies.append({
            'type': 'Utilization Anomaly',
            'severity': severity,
            'entity': cons.consultant_name,
            'client': cons.role_level,
            'detail': f'Current: {cons.current_week_util:.1%} | 12-wk avg: {cons.avg_utilization:.1%} | Target: {cons.utilization_target:.1%}'
        })
    
    # === ANOMALY 3: Engagement type margin degradation ===
    cursor.execute('''
        WITH MonthlyEngagement AS (
            SELECT
                p.engagement_type,
                d.year_number,
                d.month_number,
                SUM(t.hours_worked * t.bill_rate_applied) AS revenue,
                SUM(t.hours_worked * t.cost_rate_applied) AS cost,
                CASE WHEN SUM(t.hours_worked * t.bill_rate_applied) > 0
                    THEN (SUM(t.hours_worked * t.bill_rate_applied) - SUM(t.hours_worked * t.cost_rate_applied))
                         / SUM(t.hours_worked * t.bill_rate_applied)
                    ELSE 0 END AS margin_pct
            FROM analytics.dim_project p
            JOIN analytics.fact_time_entry t ON p.project_id = t.project_id
            JOIN analytics.dim_date d ON t.date_key = d.date_key
            WHERE d.full_date >= DATEADD(MONTH, -6, GETDATE())
            GROUP BY p.engagement_type, d.year_number, d.month_number
        ),
        MarginTrend AS (
            SELECT
                engagement_type,
                AVG(margin_pct) AS avg_margin,
                -- Simple slope: compare last 2 months avg vs prior 4 months avg
                AVG(CASE WHEN year_number * 100 + month_number >= 
                    (SELECT MAX(year_number * 100 + month_number) - 1 FROM MonthlyEngagement)
                    THEN margin_pct END) AS recent_margin,
                AVG(CASE WHEN year_number * 100 + month_number < 
                    (SELECT MAX(year_number * 100 + month_number) - 1 FROM MonthlyEngagement)
                    THEN margin_pct END) AS prior_margin
            FROM MonthlyEngagement
            GROUP BY engagement_type
            HAVING COUNT(*) >= 3
        )
        SELECT *, (recent_margin - prior_margin) AS margin_change
        FROM MarginTrend
        WHERE (recent_margin - prior_margin) < -0.05  -- Margin dropped 5+ percentage points
        ORDER BY margin_change ASC
    ''')
    
    engagement_anomalies = cursor.fetchall()
    for eng in engagement_anomalies:
        anomalies.append({
            'type': 'Engagement Margin Decline',
            'severity': 'WARNING',
            'entity': eng.engagement_type,
            'client': '',
            'detail': f'Recent margin: {eng.recent_margin:.1%} | Prior margin: {eng.prior_margin:.1%} | Change: {eng.margin_change:+.1%}'
        })
    
    conn.close()
    
    # === Send Teams Notification ===
    if anomalies:
        critical_count = sum(1 for a in anomalies if a['severity'] == 'CRITICAL')
        warning_count = sum(1 for a in anomalies if a['severity'] == 'WARNING')
        
        sections = []
        for a in sorted(anomalies, key=lambda x: ('CRITICAL','WARNING','INFO').index(x['severity'])):
            icon = '🔴' if a['severity'] == 'CRITICAL' else '🟡' if a['severity'] == 'WARNING' else '🔵'
            sections.append({
                'activityTitle': f"{icon} {a['severity']}: {a['type']}",
                'activitySubtitle': a['entity'] + (f" ({a['client']})" if a['client'] else ''),
                'text': a['detail']
            })
        
        card = {
            '@type': 'MessageCard',
            '@context': 'http://schema.org/extensions',
            'themeColor': 'FF0000' if critical_count > 0 else 'FFA500',
            'summary': f'Profitability Analytics: {critical_count} critical, {warning_count} warnings',
            'title': f'📊 Daily Profitability Alert — {datetime.now().strftime("%B %d, %Y")}',
            'sections': sections[:20],  # Teams cards limited to ~20 sections
            'potentialAction': [{
                '@type': 'OpenUri',
                'name': 'Open Analytics Dashboard',
                'targets': [{'os': 'default', 'uri': os.environ.get('POWERBI_APP_URL', '#')}]
            }]
        }
        
        requests.post(teams_webhook, json=card)

{
  "scriptFile": "__init__.py",
  "bindings": [
    {
      "name": "timer",
      "type": "timerTrigger",
      "direction": "in",
      "schedule": "0 0 7 * * 1-5"
    }
  ]
}

func azure functionapp publish func-profitability-alerts-[clientname]

# Weekly Azure Function that generates 8-week forward-looking utilization
# predictions

import azure.functions as func
import pyodbc
import numpy as np
from datetime import datetime, timedelta
import os
import json

def main(timer: func.TimerRequest) -> None:
    """Weekly utilization forecast — generates 8-week forward-looking predictions."""
    
    conn = pyodbc.connect(os.environ['AZURE_SQL_CONNECTION_STRING'])
    cursor = conn.cursor()
    
    cursor.execute('''
        SELECT 
            c.consultant_id,
            c.consultant_name,
            c.role_level,
            c.utilization_target,
            c.annual_available_hours,
            d.year_number,
            d.week_number,
            ISNULL(SUM(CASE WHEN t.is_billable = 1 THEN t.hours_worked ELSE 0 END), 0) AS billable_hours,
            ISNULL(SUM(t.hours_worked), 0) AS total_hours
        FROM analytics.dim_consultant c
        CROSS JOIN (
            SELECT DISTINCT year_number, week_number 
            FROM analytics.dim_date 
            WHERE full_date BETWEEN DATEADD(WEEK, -52, GETDATE()) AND GETDATE()
        ) d
        LEFT JOIN analytics.fact_time_entry t 
            ON c.consultant_id = t.consultant_id 
            AND t.date_key IN (SELECT date_key FROM analytics.dim_date 
                               WHERE year_number = d.year_number AND week_number = d.week_number)
        WHERE c.is_active = 1
        GROUP BY c.consultant_id, c.consultant_name, c.role_level, 
                 c.utilization_target, c.annual_available_hours,
                 d.year_number, d.week_number
        ORDER BY c.consultant_id, d.year_number, d.week_number
    ''')
    
    historical_data = cursor.fetchall()
    
    # Organize by consultant
    consultants = {}
    for row in historical_data:
        cid = row.consultant_id
        if cid not in consultants:
            consultants[cid] = {
                'name': row.consultant_name,
                'role': row.role_level,
                'target': float(row.utilization_target),
                'weekly_available': float(row.annual_available_hours) / 52,
                'weekly_billable': [],
                'weekly_total': []
            }
        consultants[cid]['weekly_billable'].append(float(row.billable_hours))
        consultants[cid]['weekly_total'].append(float(row.total_hours))
    
    # Generate 8-week forecast for each consultant
    forecasts = []
    today = datetime.now()
    
    for cid, data in consultants.items():
        billable_series = np.array(data['weekly_billable'])
        available = data['weekly_available']
        
        if len(billable_series) < 8:
            continue  # Not enough history
        
        # Simple Exponential Smoothing (Holt's method for trend)
        alpha = 0.3  # Smoothing factor for level
        beta = 0.1   # Smoothing factor for trend
        
        # Initialize
        level = billable_series[-12:].mean()  # Recent 12-week average
        trend = (billable_series[-4:].mean() - billable_series[-12:-4].mean()) / 8
        
        # Seasonal adjustment (compare same period last year if available)
        seasonal_factors = np.ones(8)
        if len(billable_series) >= 52:
            for w in range(8):
                # Lookup same week last year
                historical_week_idx = len(billable_series) - 52 + w
                if 0 <= historical_week_idx < len(billable_series):
                    hist_val = billable_series[historical_week_idx]
                    recent_avg = billable_series[-12:].mean()
                    if recent_avg > 0:
                        seasonal_factors[w] = hist_val / recent_avg
                    seasonal_factors[w] = max(0.5, min(1.5, seasonal_factors[w]))  # Bound
        
        # Generate forecasts
        for week_offset in range(1, 9):
            forecast_hours = max(0, (level + trend * week_offset) * seasonal_factors[week_offset - 1])
            forecast_util = min(1.0, forecast_hours / available) if available > 0 else 0
            forecast_date = today + timedelta(weeks=week_offset)
            
            forecasts.append((
                cid,
                data['name'],
                data['role'],
                forecast_date.strftime('%Y-%m-%d'),
                week_offset,
                round(forecast_hours, 1),
                round(forecast_util, 3),
                round(data['target'], 3),
                'exponential_smoothing',
                datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            ))
    
    # Write forecasts to Azure SQL
    cursor.execute('''
        IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'forecast_utilization' AND schema_id = SCHEMA_ID('analytics'))
        CREATE TABLE analytics.forecast_utilization (
            forecast_id BIGINT IDENTITY(1,1) PRIMARY KEY,
            consultant_id INT,
            consultant_name NVARCHAR(200),
            role_level NVARCHAR(100),
            forecast_week_start DATE,
            weeks_ahead INT,
            forecast_billable_hours DECIMAL(6,1),
            forecast_utilization DECIMAL(5,3),
            utilization_target DECIMAL(5,3),
            model_version NVARCHAR(50),
            generated_at DATETIME2
        )
    ''')
    
    # Clear previous forecasts
    cursor.execute('DELETE FROM analytics.forecast_utilization')
    
    # Insert new forecasts
    cursor.executemany('''
        INSERT INTO analytics.forecast_utilization 
        (consultant_id, consultant_name, role_level, forecast_week_start, weeks_ahead,
         forecast_billable_hours, forecast_utilization, utilization_target, model_version, generated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ''', forecasts)
    
    conn.commit()
    conn.close()
    
    return f'Generated {len(forecasts)} utilization forecasts for {len(consultants)} consultants'

{
  "scriptFile": "__init__.py",
  "bindings": [
    {
      "name": "timer",
      "type": "timerTrigger",
      "direction": "in",
      "schedule": "0 0 6 * * 1"
    }
  ]
}

Forecast Utilization = AVERAGE(forecast_utilization[forecast_utilization])

Forecast vs Target Gap = AVERAGE(forecast_utilization[forecast_utilization]) - AVERAGE(forecast_utilization[utilization_target])

Bench Risk Flag = IF([Forecast Utilization] < 0.50, "HIGH RISK", IF([Forecast Utilization] < 0.65, "MODERATE RISK", "OK"))

# paste into the 'Help Copilot give better answers' text box in Power BI
# report Copilot settings

You are an analytics assistant for a professional services firm. You help partners, managers, and consultants understand project profitability, consultant utilization, and engagement performance.

## Data Model Context
- **fact_time_entry**: Every hour logged by a consultant on a project. Key fields: hours_worked, is_billable, bill_rate_applied, cost_rate_applied
- **dim_consultant**: Consultant profiles with role_level (Analyst, Senior Consultant, Manager, Senior Manager, Partner), department, cost_rate, bill_rate, and utilization_target
- **dim_project**: Projects with client_name, engagement_type, billing_method (T&M, Fixed-Fee, Retainer), budget_hours, budget_amount, project_status
- **fact_invoice**: Invoices with amounts and payment status
- **forecast_utilization**: 8-week forward utilization forecasts per consultant

## Key Metrics Definitions
- **Utilization Rate** = Billable Hours / Available Hours (available = annual_available_hours / 12 per month)
- **Gross Margin %** = (Revenue - Direct Labor Cost) / Revenue
- **Realization Rate** = Invoiced Revenue / Gross Revenue (captures write-downs)
- **Effective Multiplier** = Revenue per Hour / Cost per Hour (target: 2.5-3.5x)
- **Budget Consumed %** = Actual Hours / Budget Hours

## Engagement Types
[CUSTOMIZE: List the client's specific engagement types here, e.g.]
- Strategy Consulting: Advisory engagements, typically T&M, target margin 35%+
- IT Implementation: Technology deployment projects, typically fixed-fee, target margin 20-25%
- Staff Augmentation: Resource placement, T&M, target margin 15-20%
- Training & Workshops: Delivery of training, fixed-fee, target margin 40%+
- Managed Services: Ongoing support, retainer, target margin 25-30%

## Utilization Targets by Role
- Analyst/Associate: 80%
- Senior Consultant: 75%
- Manager: 65%
- Senior Manager: 55%
- Partner/Director: 40%

## When answering questions:
1. Always specify the time period being analyzed
2. Compare metrics to targets when available
3. Highlight outliers (both positive and negative)
4. Suggest actionable next steps when identifying issues
5. Reference specific projects, consultants, or engagement types by name
6. Use currency formatting for financial values and percentage formatting for rates

Pipeline: PL_Daily_ETL_Orchestrator
├── Activity 1: Extract BigTime Time Entries (Copy Activity → REST to SQL)
├── Activity 2: Extract BigTime Projects (Copy Activity → REST to SQL)
├── Activity 3: Extract BigTime Consultants (Copy Activity → REST to SQL)
├── Activity 4: Extract QBO Invoices (Copy Activity → REST to SQL)
├── Activity 5: Extract QBO Chart of Accounts (Copy Activity → REST to SQL)
├── Activity 6: Load HRIS Cost Rates (Copy Activity → CSV/SFTP to SQL)
├── Activity 7: Data Quality Validation (Stored Procedure Activity)
├── Activity 8: Monthly Snapshot Calculation (Stored Procedure Activity)
├── Activity 9: Notify on Failure (Web Activity → Teams webhook)
└── Activity 10: Trigger Power BI Refresh (Web Activity → Power BI REST API)

{
  "name": "PL_Daily_ETL_Orchestrator",
  "properties": {
    "activities": [
      {
        "name": "Extract_BigTime_TimeEntries",
        "type": "Copy",
        "dependsOn": [],
        "policy": {
          "retry": 3,
          "retryIntervalInSeconds": 60
        },
        "typeProperties": {
          "source": {
            "type": "RestSource",
            "httpRequestTimeout": "00:05:00",
            "requestInterval": "00.00:00:01.000",
            "requestMethod": "GET"
          },
          "sink": {
            "type": "AzureSqlSink",
            "writeBehavior": "upsert",
            "upsertSettings": {
              "useTempDB": true,
              "keys": ["time_entry_id"]
            },
            "sqlWriterStoredProcedureName": "[analytics].[usp_UpsertTimeEntries]"
          }
        }
      }
    ],
    "triggers": [
      {
        "name": "TR_Daily_0600",
        "type": "ScheduleTrigger",
        "typeProperties": {
          "recurrence": {
            "frequency": "Day",
            "interval": 1,
            "startTime": "2025-01-01T06:00:00Z",
            "timeZone": "Eastern Standard Time"
          }
        }
      }
    ]
  }
}

CREATE PROCEDURE [analytics].[usp_DataQualityCheck]
AS
BEGIN
    DECLARE @issues TABLE (check_name NVARCHAR(200), severity NVARCHAR(20), detail NVARCHAR(1000));
    
    -- Check 1: Orphaned time entries (no matching project)
    INSERT INTO @issues
    SELECT 'Orphaned Time Entries', 'ERROR',
           CAST(COUNT(*) AS NVARCHAR) + ' time entries with no matching project'
    FROM analytics.fact_time_entry t
    LEFT JOIN analytics.dim_project p ON t.project_id = p.project_id
    WHERE p.project_id IS NULL
    HAVING COUNT(*) > 0;
    
    -- Check 2: Time entries with zero bill rate on billable projects
    INSERT INTO @issues
    SELECT 'Zero Bill Rate', 'WARNING',
           CAST(COUNT(*) AS NVARCHAR) + ' billable entries with $0 bill rate'
    FROM analytics.fact_time_entry
    WHERE is_billable = 1 AND bill_rate_applied = 0
    HAVING COUNT(*) > 0;
    
    -- Check 3: Consultants with no time entries in last 2 weeks (active only)
    INSERT INTO @issues
    SELECT 'Missing Time Entries', 'WARNING',
           c.consultant_name + ' (' + c.role_level + ') has no time entries in last 14 days'
    FROM analytics.dim_consultant c
    WHERE c.is_active = 1
      AND NOT EXISTS (
          SELECT 1 FROM analytics.fact_time_entry t
          JOIN analytics.dim_date d ON t.date_key = d.date_key
          WHERE t.consultant_id = c.consultant_id
            AND d.full_date >= DATEADD(DAY, -14, GETDATE())
      );
    
    -- Check 4: Revenue variance vs accounting (monthly)
    INSERT INTO @issues
    SELECT 'Revenue Variance', 'ERROR',
           'Month ' + CAST(MONTH(GETDATE()) AS NVARCHAR) + ': PSA revenue $' + 
           CAST(CAST(psa_rev AS INT) AS NVARCHAR) + ' vs Invoice revenue $' + 
           CAST(CAST(inv_rev AS INT) AS NVARCHAR) + ' (variance: ' + 
           CAST(CAST(ABS(psa_rev - inv_rev) / NULLIF(psa_rev, 0) * 100 AS INT) AS NVARCHAR) + '%)'
    FROM (
        SELECT 
            SUM(t.hours_worked * t.bill_rate_applied) AS psa_rev
        FROM analytics.fact_time_entry t
        JOIN analytics.dim_date d ON t.date_key = d.date_key
        WHERE d.month_number = MONTH(GETDATE())
          AND d.year_number = YEAR(GETDATE())
          AND t.is_billable = 1
    ) psa
    CROSS JOIN (
        SELECT SUM(invoice_amount) AS inv_rev
        FROM analytics.fact_invoice
        WHERE MONTH(invoice_date) = MONTH(GETDATE())
          AND YEAR(invoice_date) = YEAR(GETDATE())
    ) inv
    WHERE ABS(psa_rev - inv_rev) / NULLIF(psa_rev, 0) > 0.05;  -- >5% variance
    
    -- Check 5: Cost rate validation
    INSERT INTO @issues
    SELECT 'Missing Cost Rate', 'ERROR',
           c.consultant_name + ' has $0 cost rate — profitability calculations will be inaccurate'
    FROM analytics.dim_consultant c
    WHERE c.is_active = 1 AND (c.hourly_cost_rate IS NULL OR c.hourly_cost_rate = 0);
    
    -- Log results
    INSERT INTO analytics.data_quality_log (check_date, check_name, severity, detail)
    SELECT GETDATE(), check_name, severity, detail FROM @issues;
    
    -- Return issues for pipeline notification
    SELECT * FROM @issues;
END;

CREATE PROCEDURE [analytics].[usp_GenerateMonthlySnapshot]
AS
BEGIN
    DECLARE @snapshot_month DATE = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1);
    
    -- Delete existing snapshot for current month (allows re-run)
    DELETE FROM analytics.fact_project_monthly_snapshot 
    WHERE snapshot_month = @snapshot_month;
    
    INSERT INTO analytics.fact_project_monthly_snapshot
    (project_id, snapshot_month, total_hours_to_date, billable_hours_to_date,
     revenue_recognized_to_date, total_cost_to_date, budget_consumed_pct)
    SELECT
        p.project_id,
        @snapshot_month,
        SUM(t.hours_worked),
        SUM(CASE WHEN t.is_billable = 1 THEN t.hours_worked ELSE 0 END),
        SUM(CASE WHEN t.is_billable = 1 THEN t.hours_worked * t.bill_rate_applied ELSE 0 END),
        SUM(t.hours_worked * t.cost_rate_applied),
        CASE WHEN p.budget_hours > 0 
             THEN SUM(t.hours_worked) / p.budget_hours 
             ELSE 0 END
    FROM analytics.dim_project p
    JOIN analytics.fact_time_entry t ON p.project_id = t.project_id
    JOIN analytics.dim_date d ON t.date_key = d.date_key
    WHERE d.full_date <= EOMONTH(@snapshot_month)
    GROUP BY p.project_id, p.budget_hours;
END;

az login

az datafactory create --resource-group rg-profitability-analytics --factory-name adf-profitability-[clientname] --location eastus

az deployment group create --resource-group rg-profitability-analytics --template-file adf-pipeline-template.json

import azure.functions as func
import pyodbc
import os
import json
import requests
from datetime import datetime, timedelta

def main(timer: func.TimerRequest) -> None:
    conn = pyodbc.connect(os.environ['AZURE_SQL_CONNECTION_STRING'])
    cursor = conn.cursor()
    
    # Gather key metrics for the past week and comparison period
    metrics = {}
    
    # Firm-wide utilization this week vs last week
    cursor.execute('''
        SELECT
            SUM(CASE WHEN d.full_date >= DATEADD(WEEK, -1, GETDATE()) AND t.is_billable = 1 
                     THEN t.hours_worked ELSE 0 END) AS this_week_billable,
            SUM(CASE WHEN d.full_date >= DATEADD(WEEK, -1, GETDATE()) 
                     THEN t.hours_worked ELSE 0 END) AS this_week_total,
            SUM(CASE WHEN d.full_date >= DATEADD(WEEK, -2, GETDATE()) 
                     AND d.full_date < DATEADD(WEEK, -1, GETDATE()) AND t.is_billable = 1 
                     THEN t.hours_worked ELSE 0 END) AS last_week_billable,
            SUM(CASE WHEN d.full_date >= DATEADD(WEEK, -2, GETDATE()) 
                     AND d.full_date < DATEADD(WEEK, -1, GETDATE()) 
                     THEN t.hours_worked ELSE 0 END) AS last_week_total
        FROM analytics.fact_time_entry t
        JOIN analytics.dim_date d ON t.date_key = d.date_key
    ''')
    row = cursor.fetchone()
    
    active_consultants_query = cursor.execute(
        'SELECT COUNT(*) FROM analytics.dim_consultant WHERE is_active = 1'
    ).fetchone()[0]
    
    weekly_available = active_consultants_query * 40
    metrics['this_week_util'] = row.this_week_billable / weekly_available if weekly_available > 0 else 0
    metrics['last_week_util'] = row.last_week_billable / weekly_available if weekly_available > 0 else 0
    metrics['this_week_billable_hours'] = row.this_week_billable or 0
    metrics['active_consultants'] = active_consultants_query
    
    # Revenue and margin this week
    cursor.execute('''
        SELECT 
            SUM(t.hours_worked * t.bill_rate_applied) AS revenue,
            SUM(t.hours_worked * t.cost_rate_applied) AS cost
        FROM analytics.fact_time_entry t
        JOIN analytics.dim_date d ON t.date_key = d.date_key
        WHERE d.full_date >= DATEADD(WEEK, -1, GETDATE()) AND t.is_billable = 1
    ''')
    rev_row = cursor.fetchone()
    metrics['weekly_revenue'] = rev_row.revenue or 0
    metrics['weekly_cost'] = rev_row.cost or 0
    metrics['weekly_margin'] = (metrics['weekly_revenue'] - metrics['weekly_cost']) / metrics['weekly_revenue'] if metrics['weekly_revenue'] > 0 else 0
    
    # Top performing projects (by margin)
    cursor.execute('''
        SELECT TOP 3 p.project_name, p.client_name, p.engagement_type,
            SUM(t.hours_worked * t.bill_rate_applied) AS revenue,
            (SUM(t.hours_worked * t.bill_rate_applied) - SUM(t.hours_worked * t.cost_rate_applied)) 
                / NULLIF(SUM(t.hours_worked * t.bill_rate_applied), 0) AS margin_pct
        FROM analytics.fact_time_entry t
        JOIN analytics.dim_project p ON t.project_id = p.project_id
        JOIN analytics.dim_date d ON t.date_key = d.date_key
        WHERE d.full_date >= DATEADD(WEEK, -1, GETDATE()) AND t.is_billable = 1
        GROUP BY p.project_name, p.client_name, p.engagement_type
        HAVING SUM(t.hours_worked) > 10
        ORDER BY margin_pct DESC
    ''')
    metrics['top_projects'] = [{'name': r.project_name, 'client': r.client_name, 
                                 'type': r.engagement_type, 'margin': f'{r.margin_pct:.1%}'} 
                                for r in cursor.fetchall()]
    
    # At-risk projects
    cursor.execute('''
        SELECT TOP 5 p.project_name, p.client_name,
            (SUM(t.hours_worked * t.bill_rate_applied) - SUM(t.hours_worked * t.cost_rate_applied)) 
                / NULLIF(SUM(t.hours_worked * t.bill_rate_applied), 0) AS margin_pct,
            SUM(t.hours_worked) / NULLIF(p.budget_hours, 0) AS budget_pct
        FROM analytics.fact_time_entry t
        JOIN analytics.dim_project p ON t.project_id = p.project_id
        WHERE p.project_status = 'Active'
        GROUP BY p.project_name, p.client_name, p.budget_hours
        HAVING (SUM(t.hours_worked * t.bill_rate_applied) - SUM(t.hours_worked * t.cost_rate_applied)) 
                / NULLIF(SUM(t.hours_worked * t.bill_rate_applied), 0) < 0.15
        ORDER BY margin_pct ASC
    ''')
    metrics['at_risk_projects'] = [{'name': r.project_name, 'client': r.client_name,
                                     'margin': f'{r.margin_pct:.1%}', 'budget': f'{r.budget_pct:.0%}'}
                                    for r in cursor.fetchall()]
    
    # Under-utilized consultants
    cursor.execute('''
        SELECT TOP 5 c.consultant_name, c.role_level, c.utilization_target,
            ISNULL(SUM(CASE WHEN t.is_billable = 1 THEN t.hours_worked ELSE 0 END), 0) / 40.0 AS this_week_util
        FROM analytics.dim_consultant c
        LEFT JOIN analytics.fact_time_entry t ON c.consultant_id = t.consultant_id
            AND t.date_key IN (SELECT date_key FROM analytics.dim_date WHERE full_date >= DATEADD(WEEK, -1, GETDATE()))
        WHERE c.is_active = 1
        GROUP BY c.consultant_name, c.role_level, c.utilization_target
        HAVING ISNULL(SUM(CASE WHEN t.is_billable = 1 THEN t.hours_worked ELSE 0 END), 0) / 40.0 < c.utilization_target * 0.7
        ORDER BY this_week_util ASC
    ''')
    metrics['underutilized'] = [{'name': r.consultant_name, 'role': r.role_level,
                                  'actual': f'{r.this_week_util:.0%}', 'target': f'{r.utilization_target:.0%}'}
                                 for r in cursor.fetchall()]
    
    conn.close()
    
    # Generate narrative with Azure OpenAI
    prompt = f"""
    Generate a concise executive summary for a professional services firm's weekly performance.
    Write in a professional but direct tone. Use bullet points for key items. Include specific numbers.
    
    ## This Week's Data (ending {datetime.now().strftime('%B %d, %Y')})
    - Active consultants: {metrics['active_consultants']}
    - Firm utilization: {metrics['this_week_util']:.1%} (last week: {metrics['last_week_util']:.1%})
    - Billable hours: {metrics['this_week_billable_hours']:,.0f}
    - Weekly revenue: ${metrics['weekly_revenue']:,.0f}
    - Weekly gross margin: {metrics['weekly_margin']:.1%}
    
    ## Top Performing Projects
    {json.dumps(metrics['top_projects'], indent=2)}
    
    ## At-Risk Projects (below 15% margin)
    {json.dumps(metrics['at_risk_projects'], indent=2)}
    
    ## Under-Utilized Consultants (>30% below target)
    {json.dumps(metrics['underutilized'], indent=2)}
    
    Structure the summary as:
    1. **Week in Review** (2-3 sentences on overall performance)
    2. **Wins** (top projects/metrics to celebrate)
    3. **Watch Items** (at-risk projects and under-utilized staff)
    4. **Recommended Actions** (specific, actionable steps for this week)
    """
    
    openai_response = requests.post(
        f"{os.environ['AZURE_OPENAI_ENDPOINT']}/openai/deployments/{os.environ['AZURE_OPENAI_DEPLOYMENT']}/chat/completions?api-version=2024-02-01",
        headers={
            'api-key': os.environ['AZURE_OPENAI_KEY'],
            'Content-Type': 'application/json'
        },
        json={
            'messages': [
                {'role': 'system', 'content': 'You are a professional services firm analytics advisor. Write clear, actionable executive summaries.'},
                {'role': 'user', 'content': prompt}
            ],
            'temperature': 0.3,
            'max_tokens': 1000
        }
    )
    
    summary_text = openai_response.json()['choices'][0]['message']['content']
    
    # Send to Teams
    teams_card = {
        '@type': 'MessageCard',
        '@context': 'http://schema.org/extensions',
        'themeColor': '0078D4',
        'summary': 'Weekly Profitability Summary',
        'title': f'📊 Weekly Executive Summary — {datetime.now().strftime("%B %d, %Y")}',
        'sections': [{
            'text': summary_text.replace('\n', '<br>')
        }],
        'potentialAction': [{
            '@type': 'OpenUri',
            'name': 'Open Full Dashboard',
            'targets': [{'os': 'default', 'uri': os.environ.get('POWERBI_APP_URL', '#')}]
        }]
    }
    
    requests.post(os.environ['TEAMS_WEBHOOK_URL'], json=teams_card)

{
  "scriptFile": "__init__.py",
  "bindings": [
    {
      "name": "timer",
      "type": "timerTrigger",
      "direction": "in",
      "schedule": "0 30 7 * * 1"
    }
  ]
}