Juicebox Reporting Path
How data moves from SSIS ETL jobs through the warehouse into client-facing reports. SSIS uses a truncate-rebuild pattern — tables are dropped and recreated each run.
flowchart LR
SSIS["SSIS Jobs\n(truncate + rebuild)"] --> EDW["EDW_Target\n(raw ETL output)"]
EDW --> DWRPT["DWRPT Views\n(pre-joined SQL views\nSL / ML / RL / CDXP schemas)"]
DWRPT --> Juice["Juicebox\n(drag-and-drop, no SQL joins)"]
Juice --> iframe["iframe embed\nin DAS Portal"]
iframe --> Client["Client-facing\ndealership reports"]
DWRPT --> DASAI["DAS AI Agents\n(execute_sql → mssqlclient)"]
DASAI --> HTML["Dynamic HTML\nanalysis reports"]
Full DAS Data Architecture
Complete picture including data sources, all three EC2 instances, both RDS nodes, and the reporting layer. Arrows show directional data flow as understood from session recordings.
flowchart TD
subgraph Sources["Data Sources"]
SFTP["Customer SFTPs\n(AWS-monitored)"]
DMS["DMS Systems\n(Dealership DMS)"]
Email["Email / SMS\nWebhooks"]
end
subgraph EC2["EC2 — ETL01 (r5a.8xlarge · 10.254.210.32)"]
SSIS["SSIS Jobs\n(13 jobs, daily schedule)"]
EDW_S["EDW_Staging DB"]
EDW_T["EDW_Target DB"]
SSISDB["SSISDB"]
end
subgraph EC2_BHS["EC2 — 3BHS001 (r7l.2xlarge · 10.254.210.10)"]
Auth["Auth DB"]
Autos["Automobiles DB"]
ClientDB["ClientDB"]
Lift["Lift DB"]
BirdBath["BirdBath DB (legacy)"]
end
subgraph EC2_SQL["EC2 — SQL01 (r6i.4xlarge · 10.254.210.28)"]
ActivityLog["ActivityLog DB"]
DmsWH["DmsWarehouse DB"]
DigiBun["DigitalBundle DB (legacy)"]
Inventory["Inventory DB"]
Tracking["Tracking DB"]
end
subgraph RDS["RDS Instances"]
Analytics["Analytics DB\n(MySQL · db.m6g.2xlarge)"]
Mautic["Analytics-Mautic\n(MySQL · db.m6g.2xlarge)"]
end
subgraph Reporting["Reporting Layer"]
DWRPT["DWRPT DB\n(pre-joined views — SL / ML / RL / CDXP)"]
Juice["Juicebox (221 reports)"]
Portal["3Birds Portal\n(admin.3birdsmarketing.com)"]
DASAI["DAS AI Agents\n(ai.das-technology.com)"]
end
subgraph Monitor["Monitoring"]
Dashboard["CDXP Job Status Dashboard\n(localhost:3000)"]
end
SFTP --> SSIS
DMS --> SSIS
Email --> RDS
SSIS --> EDW_S
SSIS --> EDW_T
SSIS --> EC2_BHS
SSIS --> EC2_SQL
EDW_T --> DWRPT
EC2_BHS --> DWRPT
EC2_SQL --> DWRPT
Analytics --> DWRPT
DWRPT --> Juice
DWRPT --> DASAI
Juice --> Portal
Mautic --> Portal
SSIS --> Dashboard
Key Observations
Juicebox is an endpoint, not a foundation
Team explicitly stated Juicebox is not on the long-term roadmap. 221 reports represent institutional knowledge to migrate. CDP should not add Juicebox dependencies.
DWRPT is the real integration point
Schema-prefixed views (SL, ML, RL, CDXP) are the cleanest data layer. CDP access to DWRPT is more valuable than access to Juicebox output.
DAS AI already queries DWRPT
ai.das-technology.com agents use execute_sql against mssqlclient. CDP AI should reuse this infrastructure rather than build parallel data access paths.
Use raw SSIS tables for CDP
Explicitly requested: pull from EDW_Staging or source DBs, not DWRPT views. Views may include manipulated/summarized data that limits CDP transformation flexibility.