import { useSQLQuery } from "@motherduck/react-sql-query"; import { useState, useMemo, useCallback, useEffect } from "react"; import { BarChart, Bar, LineChart, Line, PieChart, Pie, Cell, XAxis, YAxis, CartesianGrid, Tooltip, ResponsiveContainer, } from "recharts"; export const REQUIRED_DATABASES = [ { type: 'share', path: 'md:_share/sample_data/23b0d623-1361-421d-ae77-62d701d471e6', alias: 'sample_data' } ]; // ── Numeric helper ────────────────────────────────────────────── const N = (v: unknown): number => (v != null ? Number(v) : 0); // ── Color helpers for 3D chart faces ──────────────────────────── function hexToRgb(hex: string): [number, number, number] { const h = hex.replace("#", ""); return [parseInt(h.slice(0, 2), 16), parseInt(h.slice(2, 4), 16), parseInt(h.slice(4, 6), 16)]; } function rgbToHex(r: number, g: number, b: number): string { return "#" + [r, g, b].map(v => Math.max(0, Math.min(255, Math.round(v))).toString(16).padStart(2, "0")).join(""); } function lighten(hex: string, amt: number): string { const [r, g, b] = hexToRgb(hex); return rgbToHex(r + amt, g + amt, b + amt); } function darken(hex: string, amt: number): string { const [r, g, b] = hexToRgb(hex); return rgbToHex(r - amt, g - amt, b - amt); } // ── Zone Lookup (top ~70 by trip volume) ──────────────────────── const ZONE_MAP: Record = { 132: ["JFK Airport", "Queens"], 237: ["Upper East Side South", "Manhattan"], 236: ["Upper East Side North", "Manhattan"], 161: ["Midtown Center", "Manhattan"], 142: ["Lincoln Square East", "Manhattan"], 162: ["Midtown East", "Manhattan"], 186: ["Penn Station/Madison Sq W", "Manhattan"], 230: ["Times Square/Theatre Dist", "Manhattan"], 138: ["LaGuardia Airport", "Queens"], 170: ["Murray Hill", "Manhattan"], 163: ["Midtown North", "Manhattan"], 239: ["Upper West Side South", "Manhattan"], 48: ["Clinton East", "Manhattan"], 141: ["Lenox Hill West", "Manhattan"], 234: ["Union Square", "Manhattan"], 68: ["East Chelsea", "Manhattan"], 79: ["East Village", "Manhattan"], 164: ["Midtown South", "Manhattan"], 107: ["Gramercy", "Manhattan"], 263: ["Yorkville West", "Manhattan"], 238: ["Upper West Side North", "Manhattan"], 229: ["Sutton Place/Turtle Bay N", "Manhattan"], 140: ["Lenox Hill East", "Manhattan"], 249: ["West Village", "Manhattan"], 43: ["Central Park", "Manhattan"], 100: ["Garment District", "Manhattan"], 90: ["Flatiron", "Manhattan"], 246: ["West Chelsea/Hudson Yards", "Manhattan"], 231: ["TriBeCa/Civic Center", "Manhattan"], 262: ["Yorkville East", "Manhattan"], 113: ["Greenwich Village North", "Manhattan"], 233: ["UN/Turtle Bay South", "Manhattan"], 114: ["Greenwich Village South", "Manhattan"], 143: ["Lincoln Square West", "Manhattan"], 137: ["Kips Bay", "Manhattan"], 148: ["Lower East Side", "Manhattan"], 151: ["Manhattan Valley", "Manhattan"], 158: ["Meatpacking/W Village W", "Manhattan"], 144: ["Little Italy/NoLiTa", "Manhattan"], 211: ["SoHo", "Manhattan"], 50: ["Clinton West", "Manhattan"], 75: ["East Harlem North", "Manhattan"], 13: ["Battery Park City", "Manhattan"], 87: ["Financial District North", "Manhattan"], 261: ["World Trade Center", "Manhattan"], 76: ["East Harlem South", "Manhattan"], 166: ["Morningside Heights", "Manhattan"], 41: ["Central Harlem", "Manhattan"], 42: ["Central Harlem North", "Manhattan"], 152: ["Marble Hill/Inwood", "Manhattan"], 119: ["Hamilton Heights", "Manhattan"], 244: ["Washington Heights South", "Manhattan"], 7: ["Astoria", "Queens"], 82: ["Elmhurst", "Queens"], 129: ["Jackson Heights", "Queens"], 56: ["Corona", "Queens"], 17: ["Bedford-Stuyvesant", "Brooklyn"], 61: ["Crown Heights North", "Brooklyn"], 97: ["Fort Greene", "Brooklyn"], 25: ["Boerum Hill", "Brooklyn"], 66: ["DUMBO/Vinegar Hill", "Brooklyn"], 37: ["Bushwick South", "Brooklyn"], 80: ["East Williamsburg", "Brooklyn"], 256: ["Williamsburg North", "Brooklyn"], 257: ["Williamsburg South", "Brooklyn"], 23: ["Bay Ridge", "Brooklyn"], 167: ["Morrisania/Melrose", "Bronx"], 169: ["Mott Haven/Port Morris", "Bronx"], 168: ["Mott Haven", "Bronx"], 174: ["Norwood", "Bronx"], 247: ["West Concourse", "Bronx"], 225: ["St. George/New Brighton", "Staten Island"], }; // Build zone-by-borough hierarchy const ZONES_BY_BOROUGH: Record = {}; Object.values(ZONE_MAP).forEach(([zone, borough]) => { if (!ZONES_BY_BOROUGH[borough]) ZONES_BY_BOROUGH[borough] = []; if (!ZONES_BY_BOROUGH[borough].includes(zone)) ZONES_BY_BOROUGH[borough].push(zone); }); Object.values(ZONES_BY_BOROUGH).forEach(arr => arr.sort()); const BOROUGHS = ["Manhattan", "Brooklyn", "Queens", "Bronx", "Staten Island", "Other"]; // Build SQL CASE expressions from the zone map const BOROUGH_CASE = `CASE PULocationID ${Object.entries(ZONE_MAP).map(([id, [, b]]) => `WHEN ${id} THEN '${b}'`).join(" ")} ELSE 'Other' END`; const ZONE_CASE = `CASE PULocationID ${Object.entries(ZONE_MAP).map(([id, [n]]) => `WHEN ${id} THEN '${n.replace(/'/g, "''")}'`).join(" ")} ELSE 'Zone ' || CAST(PULocationID AS VARCHAR) END`; // ── Dimension Metadata ────────────────────────────────────────── interface Level { id: string; label: string; sqlExpr: string; members?: string[]; } interface Dimension { id: string; label: string; levels: Level[]; hierarchical?: boolean; } const DIMENSIONS: Dimension[] = [ { id: "geography", label: "Geography", hierarchical: true, levels: [ { id: "borough", label: "Borough", sqlExpr: BOROUGH_CASE, members: BOROUGHS }, { id: "zone", label: "Zone", sqlExpr: ZONE_CASE, members: Object.values(ZONE_MAP).map(([n]) => n).sort() }, ], }, { id: "time", label: "Time", levels: [ { id: "week", label: "Week", sqlExpr: "strftime(tpep_pickup_datetime, 'Week %W')", members: ["Week 44", "Week 45", "Week 46", "Week 47", "Week 48"] }, { id: "dow", label: "Day of Week", sqlExpr: "strftime(tpep_pickup_datetime, '%A')", members: ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"] }, { id: "day", label: "Date", sqlExpr: "strftime(tpep_pickup_datetime, '%Y-%m-%d')", members: ["2022-11-01","2022-11-02","2022-11-03","2022-11-04","2022-11-05","2022-11-06","2022-11-07","2022-11-08","2022-11-09","2022-11-10","2022-11-11","2022-11-12","2022-11-13","2022-11-14","2022-11-15","2022-11-16","2022-11-17","2022-11-18","2022-11-19","2022-11-20","2022-11-21","2022-11-22","2022-11-23","2022-11-24","2022-11-25","2022-11-26","2022-11-27","2022-11-28","2022-11-29","2022-11-30"] }, ], }, { id: "payment", label: "Payment Type", levels: [ { id: "payment_type", label: "Payment Type", sqlExpr: "CASE payment_type WHEN 1 THEN 'Credit Card' WHEN 2 THEN 'Cash' WHEN 3 THEN 'No Charge' WHEN 4 THEN 'Dispute' ELSE 'Unknown' END", members: ["Credit Card", "Cash", "No Charge", "Dispute", "Unknown"] }, ], }, { id: "vendor", label: "Vendor", levels: [ { id: "vendor", label: "Vendor", sqlExpr: "CASE CAST(VendorID AS INT) WHEN 1 THEN 'Creative Mobile' WHEN 2 THEN 'VeriFone' ELSE 'Other' END", members: ["Creative Mobile", "VeriFone", "Other"] }, ], }, { id: "ratecode", label: "Rate Code", levels: [ { id: "ratecode", label: "Rate Code", sqlExpr: "CASE CAST(RatecodeID AS INT) WHEN 1 THEN 'Standard' WHEN 2 THEN 'JFK' WHEN 3 THEN 'Newark' WHEN 4 THEN 'Nassau/Westchester' WHEN 5 THEN 'Negotiated' WHEN 6 THEN 'Group Ride' ELSE 'Unknown' END", members: ["Standard", "JFK", "Newark", "Nassau/Westchester", "Negotiated", "Group Ride"] }, ], }, ]; const ALL_LEVELS: Record = {}; DIMENSIONS.forEach(d => d.levels.forEach(l => { ALL_LEVELS[l.id] = l; })); // ── Measures ──────────────────────────────────────────────────── interface Measure { id: string; label: string; sqlAgg: string; format: "integer" | "currency" | "currency2" | "decimal"; } const MEASURES: Measure[] = [ { id: "trip_count", label: "Trip Count", sqlAgg: "COUNT(*)", format: "integer" }, { id: "total_revenue", label: "Total Revenue", sqlAgg: "SUM(total_amount)", format: "currency" }, { id: "avg_fare", label: "Avg Fare", sqlAgg: "AVG(fare_amount)", format: "currency2" }, { id: "total_tips", label: "Total Tips", sqlAgg: "SUM(tip_amount)", format: "currency" }, { id: "avg_distance", label: "Avg Distance (mi)", sqlAgg: "AVG(trip_distance)", format: "decimal" }, ]; function formatMeasure(val: number, fmt: Measure["format"]): string { if (fmt === "integer") return val.toLocaleString("en-US", { maximumFractionDigits: 0 }); if (fmt === "currency") return "$" + val.toLocaleString("en-US", { maximumFractionDigits: 0 }); if (fmt === "currency2") return "$" + val.toLocaleString("en-US", { minimumFractionDigits: 2, maximumFractionDigits: 2 }); return val.toLocaleString("en-US", { minimumFractionDigits: 1, maximumFractionDigits: 1 }); } // ── Office 2003 Styles ────────────────────────────────────────── const XP: Record = { titleBar: { background: "linear-gradient(180deg, #3a6ea5 0%, #1e4a7a 100%)", color: "#fff", padding: "4px 8px", fontFamily: "Tahoma, Verdana, sans-serif", fontSize: 12, fontWeight: "bold", display: "flex", alignItems: "center", justifyContent: "space-between", userSelect: "none" }, menuBar: { background: "#ece9d8", borderBottom: "1px solid #aca899", padding: "2px 4px", fontFamily: "Tahoma, Verdana, sans-serif", fontSize: 11, display: "flex", gap: 2 }, toolbar: { background: "linear-gradient(180deg, #f6f6f6 0%, #dedad2 100%)", borderBottom: "1px solid #aca899", padding: "3px 6px", fontFamily: "Tahoma, Verdana, sans-serif", fontSize: 11, display: "flex", alignItems: "center", gap: 6, flexWrap: "wrap" }, statusBar: { background: "#ece9d8", borderTop: "1px solid #fff", padding: "2px 6px", fontFamily: "Tahoma, Verdana, sans-serif", fontSize: 10, display: "flex", gap: 4, color: "#444" }, statusPanel: { border: "1px inset #d4d0c8", padding: "1px 6px" }, select: { border: "1px inset #d4d0c8", padding: "1px 4px", fontSize: 11, fontFamily: "Tahoma, Verdana, sans-serif", background: "#fff" }, button: { background: "linear-gradient(180deg, #f6f6f6 0%, #e0e0e0 50%, #d4d4d4 100%)", border: "1px outset #d4d0c8", padding: "2px 8px", fontSize: 11, fontFamily: "Tahoma, Verdana, sans-serif", cursor: "pointer" }, }; const crosstabHeader: React.CSSProperties = { background: "linear-gradient(180deg, #d6dfe8 0%, #bcc7d4 100%)", border: "1px solid #a0a0a0", padding: "2px 6px", fontWeight: "bold", fontSize: 11, fontFamily: "Tahoma, Verdana, sans-serif", whiteSpace: "nowrap" }; const crosstabCell: React.CSSProperties = { border: "1px solid #c0c0c0", padding: "2px 6px", fontSize: 11, fontFamily: "Tahoma, Verdana, sans-serif", textAlign: "right", whiteSpace: "nowrap" }; const crosstabRowHeader: React.CSSProperties = { background: "#eef2f7", border: "1px solid #a0a0a0", padding: "2px 6px", fontWeight: "bold", fontSize: 11, fontFamily: "Tahoma, Verdana, sans-serif", whiteSpace: "nowrap", textAlign: "left" }; const dropZoneBase: React.CSSProperties = { border: "2px dashed #a0a0a0", borderRadius: 2, padding: "4px 8px", minHeight: 28, display: "flex", alignItems: "center", gap: 4, flexWrap: "wrap", fontSize: 11, fontFamily: "Tahoma, Verdana, sans-serif", color: "#888" }; const dropZoneActive: React.CSSProperties = { border: "2px dashed #3a6ea5", background: "#e8f0f8" }; const chipStyle: React.CSSProperties = { background: "linear-gradient(180deg, #d6dfe8 0%, #b8c4d4 100%)", border: "1px outset #a0a0a0", padding: "1px 6px", fontSize: 10, fontFamily: "Tahoma, Verdana, sans-serif", display: "inline-flex", alignItems: "center", gap: 3, cursor: "default", whiteSpace: "nowrap" }; const chipXStyle: React.CSSProperties = { cursor: "pointer", fontWeight: "bold", color: "#666", marginLeft: 2, fontSize: 10 }; const CHART_COLORS = ["#3a6ea5", "#c0392b", "#27ae60", "#e67e22", "#8e44ad", "#95a5a6", "#2c3e50", "#d35400"]; // Tree item styles const treeNode: React.CSSProperties = { padding: "1px 0", cursor: "pointer", display: "flex", alignItems: "center", gap: 2, userSelect: "none", fontSize: 11, fontFamily: "Tahoma, Verdana, sans-serif" }; const toggleIcon: React.CSSProperties = { width: 14, textAlign: "center", fontSize: 9, fontFamily: "monospace", color: "#555", flexShrink: 0 }; // ── SQL Builder ───────────────────────────────────────────────── function buildSQL( rowAxes: string[], colAxes: string[], measures: string[], filters: Record ): string { const dims = [...rowAxes, ...colAxes]; if (dims.length === 0) return ""; const selectParts: string[] = []; const groupParts: string[] = []; let idx = 1; for (const lid of dims) { const level = ALL_LEVELS[lid]; if (!level) continue; selectParts.push(`${level.sqlExpr} AS "${lid}"`); groupParts.push(String(idx)); idx++; } for (const mid of measures) { const m = MEASURES.find(x => x.id === mid); if (m) selectParts.push(`${m.sqlAgg} AS "${mid}"`); } const wheres: string[] = [ "tpep_pickup_datetime >= '2022-11-01'", "tpep_pickup_datetime < '2022-12-01'", ]; for (const [lid, vals] of Object.entries(filters)) { if (vals && vals.length > 0) { const level = ALL_LEVELS[lid]; if (level) { const inList = vals.map(v => `'${v.replace(/'/g, "''")}'`).join(", "); wheres.push(vals.length === 1 ? `${level.sqlExpr} = ${inList}` : `${level.sqlExpr} IN (${inList})`); } } } return [ `SELECT ${selectParts.join(", ")}`, `FROM "sample_data"."nyc"."taxi"`, `WHERE ${wheres.join(" AND ")}`, groupParts.length > 0 ? `GROUP BY ${groupParts.join(", ")}` : "", `ORDER BY ${dims.length + 1} DESC`, `LIMIT 200`, ].filter(Boolean).join("\n"); } // ── Main Component ────────────────────────────────────────────── export default function Slapshot() { const [rowAxes, setRowAxes] = useState(["borough"]); const [colAxes, setColAxes] = useState(["payment_type"]); const [activeMeasures, setActiveMeasures] = useState(["trip_count", "total_revenue"]); const [filters, setFilters] = useState>({}); const [expanded, setExpanded] = useState>({ geography: true, time: false, payment: false, vendor: false, ratecode: false }); const [expandedLevels, setExpandedLevels] = useState>({}); const [expandedBoroughs, setExpandedBoroughs] = useState>({}); const [chartType, setChartType] = useState<"bar" | "bar3d" | "line" | "line3d" | "pie" | "pie3d">("bar"); const [chartMeasure, setChartMeasure] = useState("trip_count"); const [dragOverTarget, setDragOverTarget] = useState(null); const [openFilter, setOpenFilter] = useState(null); const [showSplash, setShowSplash] = useState(true); const [splashStep, setSplashStep] = useState(0); const [showAbout, setShowAbout] = useState(false); const [hoveredMenu, setHoveredMenu] = useState(null); // Splash screen useEffect(() => { if (!showSplash) return; const steps = [500, 900, 1400, 1800, 2400]; const timers = steps.map((ms, i) => setTimeout(() => setSplashStep(i + 1), ms)); const done = setTimeout(() => setShowSplash(false), 3000); return () => { timers.forEach(clearTimeout); clearTimeout(done); }; }, [showSplash]); // Close filter dropdown on outside click useEffect(() => { if (!openFilter) return; const handler = (e: MouseEvent) => { const t = e.target as HTMLElement; if (!t.closest?.('[data-filter-dropdown]')) setOpenFilter(null); }; document.addEventListener("mousedown", handler); return () => document.removeEventListener("mousedown", handler); }, [openFilter]); // Build SQL const sql = useMemo(() => buildSQL(rowAxes, colAxes, activeMeasures, filters), [rowAxes, colAxes, activeMeasures, filters]); const { data, isLoading, isError, error } = useSQLQuery(sql, { enabled: sql.length > 0 }); const rows = Array.isArray(data) ? data : []; // Pivot const pivoted = useMemo(() => { if (rows.length === 0) return { colKeys: [] as string[], rowKeys: [] as string[], lookup: new Map>>() }; const colKeys = new Set(); const rowKeys = new Set(); const lookup = new Map>>(); for (const row of rows) { const rk = rowAxes.map(a => String(row[a] ?? "")).join("|"); const ck = colAxes.map(a => String(row[a] ?? "")).join("|"); rowKeys.add(rk); colKeys.add(ck); if (!lookup.has(rk)) lookup.set(rk, new Map()); const vals: Record = {}; for (const m of activeMeasures) vals[m] = N(row[m]); lookup.get(rk)!.set(ck, vals); } return { colKeys: Array.from(colKeys), rowKeys: Array.from(rowKeys), lookup }; }, [rows, rowAxes, colAxes, activeMeasures]); // Chart data const chartData = useMemo(() => { if (rows.length === 0 || rowAxes.length === 0) return []; const grouped = new Map(); for (const row of rows) { const rk = rowAxes.map(a => String(row[a] ?? "")).join(" / "); grouped.set(rk, (grouped.get(rk) ?? 0) + N(row[chartMeasure])); } return Array.from(grouped.entries()).sort((a, b) => b[1] - a[1]).slice(0, 12).map(([name, value]) => ({ name, value })); }, [rows, rowAxes, chartMeasure]); // DnD — drag data is "levelId" for a whole level, or "levelId::member" for a specific member const onDragStart = useCallback((e: React.DragEvent, levelId: string, member?: string) => { e.dataTransfer.setData("text/plain", member ? `${levelId}::${member}` : levelId); e.dataTransfer.effectAllowed = "copy"; }, []); const makeDrop = useCallback((target: "rows" | "cols") => ({ onDragOver: (e: React.DragEvent) => { e.preventDefault(); e.dataTransfer.dropEffect = "copy"; setDragOverTarget(target); }, onDragLeave: () => setDragOverTarget(null), onDrop: (e: React.DragEvent) => { e.preventDefault(); const raw = e.dataTransfer.getData("text/plain"); setDragOverTarget(null); const [lid, member] = raw.includes("::") ? [raw.split("::")[0], raw.split("::").slice(1).join("::")] : [raw, undefined]; // If a member was dragged, set the filter for that level to just that member if (member) { setFilters(prev => ({ ...prev, [lid]: [member] })); } else { // Dragging a whole level clears any member filter setFilters(prev => { const next = { ...prev }; delete next[lid]; return next; }); } if (target === "rows") { setColAxes(prev => prev.filter(x => x !== lid)); setRowAxes(prev => prev.includes(lid) ? prev : [...prev, lid]); } else { setRowAxes(prev => prev.filter(x => x !== lid)); setColAxes(prev => prev.includes(lid) ? prev : [...prev, lid]); } }, }), []); const removeFromAxis = useCallback((lid: string, axis: "rows" | "cols") => { if (axis === "rows") setRowAxes(prev => prev.filter(x => x !== lid)); else setColAxes(prev => prev.filter(x => x !== lid)); // Also clear any member filter when removing from axis setFilters(prev => { const next = { ...prev }; delete next[lid]; return next; }); }, []); // Filterable levels const filterLevels = useMemo(() => { return [...rowAxes, ...colAxes].map(lid => ALL_LEVELS[lid]).filter(l => l?.members); }, [rowAxes, colAxes]); const toggleMeasure = (mid: string) => { setActiveMeasures(prev => prev.includes(mid) ? (prev.length > 1 ? prev.filter(x => x !== mid) : prev) : [...prev, mid]); }; // Is this a 3D chart type? const is3d = chartType.endsWith("3d"); const baseChartType = chartType.replace("3d", "") as "bar" | "line" | "pie"; // ── Splash ──────────────────────────────────────────────────── if (showSplash) { const msgs = ["Initializing OLAP Engine...", "Connecting to Cube Server...", "Hydrating Dimension Cache...", "Compiling MDX Expressions...", "Ready."]; const pct = Math.min(100, (splashStep / 5) * 100); return (

SLAPSHOT

OLAP Explorer v1.0.3

{msgs[Math.min(splashStep, msgs.length - 1)]}

); } // ── About Dialog ────────────────────────────────────────────── const aboutDialog = showAbout ? (

setShowAbout(false)}>

e.stopPropagation()} style={{ background: "#ece9d8", border: "2px outset #d4d0c8", width: 380, fontFamily: "Tahoma, Verdana, sans-serif", boxShadow: "3px 3px 8px rgba(0,0,0,0.3)" }}>

About Slapshot OLAP Explorer setShowAbout(false)}>X

SLAPSHOT

OLAP Explorer v1.0.3 (Build 2003.04.01)

Featuring patent-pending CubeStream™ data acceleration and CrossTab Pro™ rendering engine.

Licensed to: MotherDuck, Inc.

Slapshot Analytics, a division of Ryan Dolley Consulting

linkedin.com/in/ryandolley

youtube.com/@superdatabrothers

Built with MotherDuck Dives. Happy April Fools!

) : null; // ── Dimension Tree Renderer ─────────────────────────────────── const renderDimensionTree = () => { return DIMENSIONS.map(dim => (

{/* Dimension header */}

setExpanded(prev => ({ ...prev, [dim.id]: !prev[dim.id] }))} > {expanded[dim.id] ? "\u2212" : "+"} {"\u{1F4C1}"} {dim.label}

{expanded[dim.id] && dim.hierarchical ? ( // Geography: Borough > Zones hierarchy <> {/* Borough level (draggable) */}

onDragStart(e, "borough")} style={{ ...treeNode, paddingLeft: 22, cursor: "grab" }} onMouseEnter={e => { (e.currentTarget as HTMLDivElement).style.background = "#e8f0f8"; }} onMouseLeave={e => { (e.currentTarget as HTMLDivElement).style.background = "transparent"; }} > { e.stopPropagation(); setExpandedLevels(prev => ({ ...prev, borough: !prev.borough })); }}>{expandedLevels.borough ? "\u2212" : "+"} {"\u2630"} Borough {(rowAxes.includes("borough") || colAxes.includes("borough")) && ( {rowAxes.includes("borough") ? "(rows)" : "(cols)"} )}

{/* Borough members, each expandable to show zones */} {expandedLevels.borough && BOROUGHS.filter(b => b !== "Other").map(borough => (

onDragStart(e, "borough", borough)} style={{ ...treeNode, paddingLeft: 40, cursor: "grab" }} onMouseEnter={e => { (e.currentTarget as HTMLDivElement).style.background = "#e8f0f8"; }} onMouseLeave={e => { (e.currentTarget as HTMLDivElement).style.background = "transparent"; }} onClick={() => setExpandedBoroughs(prev => ({ ...prev, [borough]: !prev[borough] }))} > {ZONES_BY_BOROUGH[borough]?.length ? (expandedBoroughs[borough] ? "\u2212" : "+") : " "} {borough} ({ZONES_BY_BOROUGH[borough]?.length || 0})

{/* Zones under this borough */} {expandedBoroughs[borough] && ZONES_BY_BOROUGH[borough]?.map(zone => (

onDragStart(e, "zone", zone)} style={{ ...treeNode, paddingLeft: 60, fontSize: 10, color: "#555", cursor: "grab" }} onMouseEnter={e => { (e.currentTarget as HTMLDivElement).style.background = "#e8f0f8"; }} onMouseLeave={e => { (e.currentTarget as HTMLDivElement).style.background = "transparent"; }} > {zone}

))}

))} {/* Zone level (draggable + expandable) */}

onDragStart(e, "zone")} style={{ ...treeNode, paddingLeft: 22, cursor: "grab" }} onMouseEnter={e => { (e.currentTarget as HTMLDivElement).style.background = "#e8f0f8"; }} onMouseLeave={e => { (e.currentTarget as HTMLDivElement).style.background = "transparent"; }} > { e.stopPropagation(); setExpandedLevels(prev => ({ ...prev, zone: !prev.zone })); }} > {expandedLevels.zone ? "\u2212" : "+"} {"\u2630"} Zone {(rowAxes.includes("zone") || colAxes.includes("zone")) && ( {rowAxes.includes("zone") ? "(rows)" : "(cols)"} )}

{expandedLevels.zone && ALL_LEVELS.zone.members?.map(zone => (

onDragStart(e, "zone", zone)} style={{ ...treeNode, paddingLeft: 44, fontSize: 10, color: "#555", cursor: "grab" }} onMouseEnter={e => { (e.currentTarget as HTMLDivElement).style.background = "#e8f0f8"; }} onMouseLeave={e => { (e.currentTarget as HTMLDivElement).style.background = "transparent"; }} > {zone}

))} ) : expanded[dim.id] && dim.levels.map(level => ( // Standard flat dimension rendering

onDragStart(e, level.id)} style={{ ...treeNode, paddingLeft: 22, cursor: "grab" }} onMouseEnter={e => { (e.currentTarget as HTMLDivElement).style.background = "#e8f0f8"; }} onMouseLeave={e => { (e.currentTarget as HTMLDivElement).style.background = "transparent"; }} > {level.members ? ( { e.stopPropagation(); setExpandedLevels(prev => ({ ...prev, [level.id]: !prev[level.id] })); }} > {expandedLevels[level.id] ? "\u2212" : "+"} ) : } {"\u2630"} {level.label} {(rowAxes.includes(level.id) || colAxes.includes(level.id)) && ( {rowAxes.includes(level.id) ? "(rows)" : "(cols)"} )}

{expandedLevels[level.id] && level.members?.map(member => (

onDragStart(e, level.id, member)} style={{ ...treeNode, paddingLeft: 44, fontSize: 10, color: "#555", cursor: "grab" }} onMouseEnter={e => { (e.currentTarget as HTMLDivElement).style.background = "#e8f0f8"; }} onMouseLeave={e => { (e.currentTarget as HTMLDivElement).style.background = "transparent"; }} > {member}

))}

)); }; // ── 3D Bar shape: draws a front face, top face, and right side face ── const Bar3DShape = (props: Record) => { const { x: _x, y: _y, width: _w, height: _h, fill } = props as { x: number; y: number; width: number; height: number; fill: string }; const dx = 8; const dy = -6; const frontFill = fill as string; const topFill = lighten(frontFill, 30); const sideFill = darken(frontFill, 30); return ( {/* Right side face */} {/* Top face */} {/* Front face */} ); }; // ── 3D Pie: renders an SVG with stacked ellipses for depth ── const render3DPie = () => { const total = chartData.reduce((s, d) => s + d.value, 0); if (total === 0) return null; const cx = 200, cy = 80, rx = 100, ry = 50, depth = 18; let startAngle = 0; const slices: { start: number; end: number; fill: string; name: string; pct: number }[] = []; chartData.forEach((d, i) => { const sweep = (d.value / total) * 360; slices.push({ start: startAngle, end: startAngle + sweep, fill: CHART_COLORS[i % CHART_COLORS.length], name: d.name, pct: d.value / total }); startAngle += sweep; }); const toRad = (deg: number) => (deg * Math.PI) / 180; const arcPath = (sa: number, ea: number, ccy: number) => { const s = { x: cx + rx * Math.cos(toRad(sa)), y: ccy + ry * Math.sin(toRad(sa)) }; const e = { x: cx + rx * Math.cos(toRad(ea)), y: ccy + ry * Math.sin(toRad(ea)) }; const large = ea - sa > 180 ? 1 : 0; return `M ${cx} ${ccy} L ${s.x} ${s.y} A ${rx} ${ry} 0 ${large} 1 ${e.x} ${e.y} Z`; }; const sidePath = (sa: number, ea: number) => { const s1 = { x: cx + rx * Math.cos(toRad(sa)), y: cy + ry * Math.sin(toRad(sa)) }; const e1 = { x: cx + rx * Math.cos(toRad(ea)), y: cy + ry * Math.sin(toRad(ea)) }; const large = ea - sa > 180 ? 1 : 0; return `M ${s1.x} ${s1.y} A ${rx} ${ry} 0 ${large} 1 ${e1.x} ${e1.y} L ${e1.x} ${e1.y + depth} A ${rx} ${ry} 0 ${large} 0 ${s1.x} ${s1.y + depth} Z`; }; return ( ); }; // ── Chart Renderer ──────────────────────────────────────────── const renderChart = () => { if (chartData.length === 0) return

No data to chart

; const tickFmt = (v: number) => v >= 1000000 ? `${(v / 1000000).toFixed(1)}M` : v >= 1000 ? `${(v / 1000).toFixed(0)}K` : String(v); if (is3d && baseChartType === "pie") { return render3DPie(); } if (is3d && baseChartType === "bar") { return ( } /> ); } if (is3d && baseChartType === "line") { return ( ); } if (baseChartType === "pie") { return ( {chartData.map((_, i) => )} ); } if (baseChartType === "line") { return ( ); } return ( ); }; // ── Render ──────────────────────────────────────────────────── return (

{aboutDialog} {/* Title Bar */}

SLAPSHOT OLAP Explorer - [NYC Taxi Cube]

_ {"\u25A1"} X

{/* Menu Bar */}

{["File", "Edit", "View", "Insert", "Format", "Tools", "Help"].map(m => ( setHoveredMenu(m)} onMouseLeave={() => setHoveredMenu(null)} onClick={() => setShowAbout(true)} >{m} ))}

{/* Toolbar / Filter Bar */}

Filter: {filterLevels.map(level => { const selected = filters[level.id] || []; const isOpen = openFilter === level.id; const label = selected.length === 0 ? "All" : selected.length <= 2 ? selected.join(", ") : `${selected.length} selected`; return ( {level.label}: {/* XP combo box */} setOpenFilter(isOpen ? null : level.id)} style={{ display: "inline-flex", alignItems: "center", border: "1px solid #7f9db9", background: "#fff", cursor: "pointer", height: 18, minWidth: 80, maxWidth: 140, fontFamily: "Tahoma, Verdana, sans-serif", fontSize: 11 }} > {label} {"\u25BC"} {/* Dropdown list */} {isOpen && (

{ setFilters(prev => { const next = { ...prev }; delete next[level.id]; return next; }); setOpenFilter(null); }} style={{ padding: "2px 6px", cursor: "pointer", color: "#444", borderBottom: "1px solid #e0e0e0" }} onMouseEnter={e => { (e.currentTarget as HTMLDivElement).style.background = "#316ac5"; (e.currentTarget as HTMLDivElement).style.color = "#fff"; }} onMouseLeave={e => { (e.currentTarget as HTMLDivElement).style.background = "#fff"; (e.currentTarget as HTMLDivElement).style.color = "#444"; }} >All (clear)

{level.members?.map(m => { const checked = selected.includes(m); return (

{ setFilters(prev => { const cur = prev[level.id] || []; const next = cur.includes(m) ? cur.filter(v => v !== m) : [...cur, m]; const out = { ...prev }; if (next.length === 0) { delete out[level.id]; } else { out[level.id] = next; } return out; }); }} style={{ padding: "2px 6px", cursor: "pointer", display: "flex", alignItems: "center", gap: 4 }} onMouseEnter={e => { (e.currentTarget as HTMLDivElement).style.background = "#316ac5"; (e.currentTarget as HTMLDivElement).style.color = "#fff"; }} onMouseLeave={e => { (e.currentTarget as HTMLDivElement).style.background = "#fff"; (e.currentTarget as HTMLDivElement).style.color = "#000"; }} > {checked ? "\u2713" : ""} {m}

); })}

)} ); })} Measures: {MEASURES.map(m => ( toggleMeasure(m.id)} /> {m.label} ))}

{/* Main Content Area */}

{/* Left: Dimension Tree */}

Dimensions

{renderDimensionTree()}

{/* Right: Drop Zones + Crosstab + Chart */}

{/* Row Axis Drop Zone (TOP) */}

Rows: {rowAxes.length === 0 && Drop dimensions here for row axis} {rowAxes.map(lid => { const mf = filters[lid]; return ( {ALL_LEVELS[lid]?.label}{mf?.length ? `: ${mf.length <= 2 ? mf.join(", ") : mf.length + " selected"}` : ""} removeFromAxis(lid, "rows")}>x ); })}

{/* Column Axis Drop Zone */}

Columns: {colAxes.length === 0 && Drop dimensions here for column axis} {colAxes.map(lid => { const mf = filters[lid]; return ( {ALL_LEVELS[lid]?.label}{mf?.length ? `: ${mf.length <= 2 ? mf.join(", ") : mf.length + " selected"}` : ""} removeFromAxis(lid, "cols")}>x ); })}

{/* Crosstab */}

{sql.length === 0 ? (

Drag dimensions from the tree to Rows or Columns to build a crosstab.

) : isLoading ? (

Querying cube...

) : isError ? (

Error: {error?.message}

) : rows.length === 0 ? (

No data for current selection.

) : ( {colAxes.length > 0 ? ( {rowAxes.map(lid => )} {pivoted.colKeys.map(ck => activeMeasures.map((mid, mi) => { const m = MEASURES.find(x => x.id === mid); return ( ); }) )} ) : ( {rowAxes.map(lid => )} {activeMeasures.map(mid => { const m = MEASURES.find(x => x.id === mid); return ; })} )} {pivoted.rowKeys.map((rk, ri) => ( {rowAxes.map((lid, li) => )} {colAxes.length > 0 ? ( pivoted.colKeys.map(ck => { const vals = pivoted.lookup.get(rk)?.get(ck); return activeMeasures.map(mid => { const m = MEASURES.find(x => x.id === mid)!; return ; }); }) ) : ( activeMeasures.map(mid => { const m = MEASURES.find(x => x.id === mid)!; const vals = pivoted.lookup.get(rk)?.values().next().value as Record | undefined; return ; }) )} ))}

{ALL_LEVELS[lid]?.label}	{mi === 0 && {ck.replace(/\\|/g, " / ")} } {activeMeasures.length > 1 && {m?.label} } {activeMeasures.length === 1 && mi === 0 && {m?.label} }
{ALL_LEVELS[lid]?.label}	{m?.label}
{rk.split("\|")[li]}	{vals ? formatMeasure(vals[mid] ?? 0, m.format) : "-"}	{vals ? formatMeasure(vals[mid] ?? 0, m.format) : "-"}

)}

{/* Chart Area */}

Chart: Measure:

{renderChart()}

{/* Status Bar */}

Ready 3,252,717 facts loaded Cube: NYC Taxi Nov 2022 setShowAbout(true)}>Slapshot OLAP v1.0.3 | Build 2003.04.01

); }

Slapshot OLAP Explorer

Comments