Imported from PacoAcun/Bussiness_Inteligence (dw_e-commerce/AGENTS.md). Install upstream with npx skills add PacoAcun/Bussiness_Inteligence --skill dw_e-commerce. Copyright stays with the author.

dw_e-commerce — Agent Notes

What this repo is

• Academic BI / Data Warehouse project. Not an application service.
• Entry data: ecommerce_sales_34500.csv plus two pre-aggregated gold CSVs.
• Artifacts: SQL scripts for staging → DW → gold layer, a Tableau workbook (Graficos.twb), and PDF reports.

Execution model

• No build system, no CI, no tests, no package manager.
• SQL scripts are run manually in a MySQL/MariaDB environment.

SQL script order

Run sequentially; each depends on the previous:

1. querys/01_staging_ecommerce.sql — recreates ecommerce_stg.ecommerce_sales_34500 and expects the CSV already loaded (the comment says it was done via import wizard).
2. querys/02_create_dw_ecommerce.sql — creates ecommerce_dw with star-schema dims + fact table.
3. querys/03_load_dw_ecommerce.sql — idempotent inserts (INSERT IGNORE, WHERE NOT EXISTS by order_id). Safe to rerun.
4. querys/04_create_gold_layer.sql — drops and recreates ecommerce_gold summary tables (gold_sales_region_category, gold_sales_day).
5. querys/05_data_validations.sql — row counts, sums, orphaned rows, null checks; run after load.

Database assumptions

• MySQL 8 or MariaDB. Uses AUTO_INCREMENT, DECIMAL, TINYINT, INSERT IGNORE, NULLIF, and SIGNED casts.
• ecommerce_sales_34500.csv is treated as already imported into staging via the DB import wizard; the script only recreates the schema.

What not to guess

• Do not add Python scripts, Docker, or DBT unless explicitly asked. There is no evidence of such tooling.
• Do not invent requirements.txt, Makefile, or virtual environments.
• The folder agente de dashboards/ is empty; do not assume it contains code.

Artifacts to preserve

• ecommerce_sales_34500.csv: downstream SQL depends on exact column names and semantics.
• gold_sales_day.csv, gold_sales_region_category.csv: generated exports; keep in sync if gold SQL changes.
• Graficos.twb, Informe.pdf, Presentación.pdf: avoid hand-editing PDFs. If model or data changes, prefer regenerating outputs via the actual tool (Tableau / export) rather than patching binary/XML files.

Streamlit Dashboard (server.py + webui.py)

• Two-file Streamlit dashboard app with an integrated LLM agent.
• server.py: Data loading (3 CSVs), 11 calculated fields, 5 quick table calcs, 25 chart generators, LLM agent via Ollama (default model: kimi-k2.6:cloud).
• webui.py: Streamlit UI with 4 tabs (Dashboard 1, Dashboard 2, Historia, Explorador). Sidebar has global filters + LLM chat.
• Run: streamlit run webui.py from the project root directory.
• Dependencies: streamlit, pandas, plotly, ollama (install via pip if missing).
• Ollama: Must be running locally for LLM chat to work. Charts work without it.

Verification

• After running scripts, run 05_data_validations.sql to confirm stg_count == fact_count and totals match.
• For Streamlit: verify with python -m py_compile server.py and python -m py_compile webui.py.