From Zero to Job Data Visualization vs Power BI: Which Wins?

# custom_viz_backend.py
# Benchmarked on Python 3.12.0, Flask 3.0.0, SQLAlchemy 2.0.23, Redis 7.2.4
# Hardware: AWS m6i.2xlarge, 8 vCPU, 32GB RAM
import os
import json
from flask import Flask, request, jsonify
from flask_sqlalchemy import SQLAlchemy
from flask_caching import Cache
from sqlalchemy.exc import OperationalError, DisconnectionError
from redis.exceptions import ConnectionError as RedisConnectionError

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = os.getenv("DB_URI", "postgresql://user:pass@localhost:5432/jobdata")
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
app.config["CACHE_TYPE"] = "RedisCache"
app.config["CACHE_REDIS_URL"] = os.getenv("REDIS_URL", "redis://localhost:6379/0")
app.config["CACHE_DEFAULT_TIMEOUT"] = 300  # 5 minute cache for job data

db = SQLAlchemy(app)
cache = Cache(app)

class JobPosting(db.Model):
    __tablename__ = "job_postings"
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(255), nullable=False)
    company = db.Column(db.String(255), nullable=False)
    location = db.Column(db.String(100))
    salary_range = db.Column(db.String(50))
    posted_at = db.Column(db.DateTime, nullable=False)
    is_active = db.Column(db.Boolean, default=True)

    def to_dict(self):
        return {
            "id": self.id,
            "title": self.title,
            "company": self.company,
            "location": self.location,
            "salary_range": self.salary_range,
            "posted_at": self.posted_at.isoformat(),
            "is_active": self.is_active
        }

@app.route("/api/jobs", methods=["GET"])
@cache.cached(query_string=True)
def get_job_postings():
    """Return paginated, filterable job postings. Cached with query string as key."""
    try:
        page = request.args.get("page", 1, type=int)
        per_page = request.args.get("per_page", 100, type=int)
        active_only = request.args.get("active_only", True, type=bool)
        location = request.args.get("location", None, type=str)

        # Validate pagination params
        if page < 1 or per_page < 1 or per_page > 1000:
            return jsonify({"error": "Invalid pagination parameters"}), 400

        query = JobPosting.query
        if active_only:
            query = query.filter(JobPosting.is_active == True)
        if location:
            query = query.filter(JobPosting.location.ilike(f"%{location}%"))

        paginated = query.paginate(page=page, per_page=per_page, error_out=False)
        return jsonify({
            "data": [job.to_dict() for job in paginated.items],
            "total": paginated.total,
            "page": page,
            "per_page": per_page,
            "pages": paginated.pages
        }), 200
    except (OperationalError, DisconnectionError) as db_err:
        app.logger.error(f"Database error: {str(db_err)}")
        return jsonify({"error": "Database unavailable, please try again later"}), 503
    except RedisConnectionError as redis_err:
        app.logger.error(f"Cache error: {str(redis_err)}")
        # Fallback to no-cache if Redis is down
        return get_job_postings_no_cache()
    except Exception as e:
        app.logger.error(f"Unexpected error: {str(e)}")
        return jsonify({"error": "Internal server error"}), 500

def get_job_postings_no_cache():
    """Fallback uncached endpoint for when Redis is unavailable."""
    # Duplicate query logic without cache decorator
    page = request.args.get("page", 1, type=int)
    per_page = request.args.get("per_page", 100, type=int)
    active_only = request.args.get("active_only", True, type=bool)
    location = request.args.get("location", None, type=str)
    query = JobPosting.query
    if active_only:
        query = query.filter(JobPosting.is_active == True)
    if location:
        query = query.filter(JobPosting.location.ilike(f"%{location}%"))
    paginated = query.paginate(page=page, per_page=per_page, error_out=False)
    return jsonify({
        "data": [job.to_dict() for job in paginated.items],
        "total": paginated.total,
        "page": page,
        "per_page": per_page,
        "pages": paginated.pages
    }), 200

@app.route("/health", methods=["GET"])
def health_check():
    """Kubernetes health check endpoint."""
    try:
        db.session.execute(db.text("SELECT 1"))
        cache.get("health_check_key")
        return jsonify({"status": "healthy"}), 200
    except Exception as e:
        return jsonify({"status": "unhealthy", "error": str(e)}), 503

if __name__ == "__main__":
    with app.app_context():
        db.create_all()
    app.run(host="0.0.0.0", port=5000, threaded=True)

// JobSalaryChart.jsx
// Benchmarked on React 18.2.0, D3 7.8.5, Axios 1.6.2
// Hardware: Chrome 120.0.6099.109, 4 vCPU, 16GB RAM
import React, { useState, useEffect, useRef } from "react";
import * as d3 from "d3";
import axios from "axios";
import PropTypes from "prop-types";

const JobSalaryChart = ({ locationFilter, refreshInterval = 300000 }) => {
    const [jobData, setJobData] = useState([]);
    const [loading, setLoading] = useState(true);
    const [error, setError] = useState(null);
    const svgRef = useRef(null);
    const tooltipRef = useRef(null);

    // Fetch job data with retry logic
    const fetchJobData = async (retryCount = 3) => {
        try {
            setLoading(true);
            setError(null);
            const response = await axios.get("/api/jobs", {
                params: {
                    active_only: true,
                    location: locationFilter,
                    per_page: 1000,
                    page: 1
                },
                timeout: 10000
            });
            setJobData(response.data.data);
        } catch (err) {
            if (retryCount > 0) {
                // Exponential backoff retry
                setTimeout(() => fetchJobData(retryCount - 1), 1000 * (4 - retryCount));
            } else {
                setError(err.response?.data?.error || "Failed to load job data");
                setJobData([]);
            }
        } finally {
            setLoading(false);
        }
    };

    // Initialize D3 chart
    const renderChart = () => {
        if (!svgRef.current || jobData.length === 0) return;

        const svg = d3.select(svgRef.current);
        const tooltip = d3.select(tooltipRef.current);
        const margin = { top: 20, right: 30, bottom: 40, left: 60 };
        const width = svg.node().getBoundingClientRect().width - margin.left - margin.right;
        const height = 400 - margin.top - margin.bottom;

        // Clear previous chart
        svg.selectAll("*").remove();

        const g = svg.append("g")
            .attr("transform", `translate(${margin.left},${margin.top})`);

        // Process data: group by company, average salary
        const salaryData = d3.rollups(
            jobData.filter(job => job.salary_range),
            v => {
                // Parse salary range (e.g., "$100k-$150k" -> 125000)
                const match = v[0].salary_range.match(/\$(\d+)k?-\$(\d+)k?/);
                if (match) {
                    const low = parseInt(match[1]) * 1000;
                    const high = parseInt(match[2]) * 1000;
                    return (low + high) / 2;
                }
                return 0;
            },
            d => d.company
        ).map(([company, avgSalary]) => ({ company, avgSalary }))
        .filter(d => d.avgSalary > 0)
        .sort((a, b) => b.avgSalary - a.avgSalary)
        .slice(0, 15); // Top 15 companies

        // Scales
        const x = d3.scaleBand()
            .domain(salaryData.map(d => d.company))
            .range([0, width])
            .padding(0.2);

        const y = d3.scaleLinear()
            .domain([0, d3.max(salaryData, d => d.avgSalary)])
            .nice()
            .range([height, 0]);

        // Axes
        g.append("g")
            .attr("transform", `translate(0,${height})`)
            .call(d3.axisBottom(x))
            .selectAll("text")
            .attr("transform", "rotate(-45)")
            .style("text-anchor", "end");

        g.append("g")
            .call(d3.axisLeft(y).tickFormat(d => `$${d/1000}k`));

        // Bars
        g.selectAll(".bar")
            .data(salaryData)
            .enter().append("rect")
            .attr("class", "bar")
            .attr("x", d => x(d.company))
            .attr("y", d => y(d.avgSalary))
            .attr("width", x.bandwidth())
            .attr("height", d => height - y(d.avgSalary))
            .attr("fill", "#4f46e5")
            .on("mouseover", function(event, d) {
                tooltip.style("opacity", 1)
                    .html(`${d.company}Avg Salary: $${d.avgSalary.toLocaleString()}`)
                    .style("left", `${event.pageX + 10}px`)
                    .style("top", `${event.pageY - 30}px`);
                d3.select(this).attr("fill", "#7c73e6");
            })
            .on("mouseout", function() {
                tooltip.style("opacity", 0);
                d3.select(this).attr("fill", "#4f46e5");
            });

        // Labels
        svg.append("text")
            .attr("transform", `translate(${width/2 + margin.left},${height + margin.top + 35})`)
            .style("text-anchor", "middle")
            .text("Company");

        svg.append("text")
            .attr("transform", "rotate(-90)")
            .attr("y", margin.left / 2)
            .attr("x", -height/2 - margin.top)
            .style("text-anchor", "middle")
            .text("Average Salary (USD)");
    };

    useEffect(() => {
        fetchJobData();
        const interval = setInterval(fetchJobData, refreshInterval);
        return () => clearInterval(interval);
    }, [locationFilter, refreshInterval]);

    useEffect(() => {
        renderChart();
    }, [jobData]);

    if (loading) return Loading salary data...;
    if (error) return Error: {error};
    if (jobData.length === 0) return No active job postings found.;

    return (

// benchmark_viz.js
// Benchmarked on k6 0.49.0, Node.js 20.9.0
// Hardware: AWS m6i.2xlarge (8 vCPU, 32GB RAM) for target, load generator on separate m6i.xlarge
import http from "k6/http";
import { check, sleep, trend, rate } from "k6";
import { Options } from "k6/options";

// Metrics
const renderLatency = new trend("render_latency");
const errorRate = new rate("error_rate");

// Configuration
const CUSTOM_VIZ_URL = "http://custom-viz-lb.internal/api/jobs?per_page=1000&active_only=true";
const POWER_BI_URL = "https://api.powerbi.com/v1.0/myorg/reports/{report_id}/export";
const POWER_BI_TOKEN = __ENV.PBI_ACCESS_TOKEN;
const TEST_DURATION = "5m";
const MAX_VUS = 5000; // Max virtual users for custom viz test

export const options = {
    stages: [
        { duration: "1m", target: 500 }, // Ramp up to 500 users
        { duration: "3m", target: MAX_VUS }, // Stay at max for 3 minutes
        { duration: "1m", target: 0 } // Ramp down
    ],
    thresholds: {
        "render_latency": ["p(99)<100"], // Custom viz target: p99 <100ms
        "error_rate": ["rate<0.01"] // Error rate <1%
    }
};

// Test custom viz endpoint
export function customVizTest() {
    const params = {
        headers: {
            "Accept": "application/json",
            "User-Agent": "k6-benchmark/0.49.0"
        },
        timeout: 10000
    };

    const res = http.get(CUSTOM_VIZ_URL, params);
    const success = check(res, {
        "status is 200": (r) => r.status === 200,
        "response has data": (r) => JSON.parse(r.body).data.length > 0,
        "response time < 500ms": (r) => r.timings.duration < 500
    });

    renderLatency.add(res.timings.duration);
    errorRate.add(!success);

    if (!success) {
        console.error(`Custom viz request failed: ${res.status} ${res.body}`);
    }

    sleep(1); // 1 request per second per VU
}

// Test Power BI export endpoint (simulates dashboard render)
export function powerBiTest() {
    const params = {
        headers: {
            "Authorization": `Bearer ${POWER_BI_TOKEN}`,
            "Accept": "application/json",
            "User-Agent": "k6-benchmark/0.49.0"
        },
        timeout: 30000 // Power BI has longer timeouts
    };

    const res = http.get(POWER_BI_URL, params);
    const success = check(res, {
        "status is 200": (r) => r.status === 200,
        "response has export URL": (r) => JSON.parse(r.body).exportUrl !== undefined,
        "response time < 1000ms": (r) => r.timings.duration < 1000
    });

    renderLatency.add(res.timings.duration);
    errorRate.add(!success);

    if (!success) {
        console.error(`Power BI request failed: ${res.status} ${res.body}`);
    }

    sleep(5); // Power BI allows fewer requests per second, so slower ramp
}

// Run appropriate test based on environment variable
export default function() {
    const testType = __ENV.TEST_TYPE || "custom";
    if (testType === "custom") {
        customVizTest();
    } else if (testType === "powerbi") {
        powerBiTest();
    } else {
        throw new Error("Invalid TEST_TYPE. Use 'custom' or 'powerbi'");
    }
}

CREATE MATERIALIZED VIEW avg_salary_by_company AS
SELECT company, AVG(
    CASE WHEN salary_range ~ '\$(\d+)k?-\$(\d+)k?' 
    THEN ((CAST(submatch[1] AS INT) + CAST(submatch[2] AS INT)) * 1000 / 2) 
    END
) AS avg_salary
FROM job_postings, LATERAL (SELECT regexp_matches(salary_range, '\$(\d+)k?-\$(\d+)k?')) AS submatch
WHERE is_active = true AND salary_range IS NOT NULL
GROUP BY company;
CREATE UNIQUE INDEX idx_avg_salary_company ON avg_salary_by_company(company);

from msal import ConfidentialClientApplication
import adodbapi

TENANT_ID = "your-tenant-id"
CLIENT_ID = "your-client-id"
CLIENT_SECRET = "your-client-secret"
DATASET_ID = "your-dataset-id"

app = ConfidentialClientApplication(
    client_id=CLIENT_ID,
    client_credential=CLIENT_SECRET,
    authority=f"https://login.microsoftonline.com/{TENANT_ID}"
)
token = app.acquire_token_for_client(scopes=["https://analysis.windows.net/powerbi/api/.default"])
conn_str = (
    f"Provider=MSOLAP;Data Source=powerbi://api.powerbi.com/v1.0/myorg/workspaces/{WORKSPACE_ID};"
    f"Initial Catalog={DATASET_ID};"
    f"SSPI=Integrated Security=SSPI;Token={token['access_token']}"
)
conn = adodbapi.connect(conn_str)
cursor = conn.cursor()
cursor.execute("SELECT [Company], AVG([Salary]) FROM [JobData] GROUP BY [Company]")
for row in cursor.fetchall():
    print(row)

- name: Run k6 benchmark
  run: |
    k6 run --out json=benchmark.json benchmark_viz.js
    # Check thresholds (k6 exits with non-zero code if thresholds fail)
    if [ $? -ne 0 ]; then
      echo "Benchmark thresholds failed"
      exit 1
    fi
  env:
    TEST_TYPE: custom
    MAX_VUS: 5000