DM-StudentDevelopment
Student Development Data Mart (DM-StudentDevelopment)
The DM-StudentDevelopment database on the IWUDATA warehouse SQL instance holds Student Development reporting data. A major consumer is the Sodexo / Transact Campus (IDX) meal-card integration: cardholder demographics CSVs, conference cardholders, and meal-plan (semesterload) exports to Transact SFTP.
The sodexo schema contains views, functions, staging tables, stored procedures, and SSRS-backed diagnostics for that pipeline.
Key benefits
- Single warehouse surface for Sodexo export logic instead of ad hoc Colleague / Access It queries
- Nightly automation for cardholder and meal-plan CSV generation and SFTP upload
- SSRS reports for per-person troubleshooting and balance reconciliation
- Read-only comparison against Transact IDX balance snapshots (
csv.IDX_Balances)
Common use cases
- Investigate why a student or employee is missing from Sodexo cardholder export
- Trace meal-plan assignment through Colleague, warehouse, and Transact export history
- Compare live IDX balances to what IWU last committed to Transact
- Review export gaps before opening a Sodexo or Colleague ticket
Data refresh schedule (typical)
| Area | Refresh |
|---|---|
| RP-COLLEAGUE snapshot | Daily (automation job RP-COLLEAGUE) |
| RP-ACCESSIT (Access It cards) | Daily |
| DW-IWUDATA people / student / employee loads | Daily (Sodexo jobs wait on these) |
| Cardholder CSV export | After upstream loads (job DM-StudentDevelopment-SodexoTransactFTP) |
| Meal-plan CSV export | After cardholder job (job DM-StudentDevelopment-SodexoMealPlanTransactFTP) |
IDX balance snapshot (csv.IDX_Balances) | Nightly from Transact SFTP (dataexports task ~1:30 AM ET) |
Run SSRS diagnostics after the relevant daily loads when investigating missing-from-export cases.
Sodexo / Transact IDX integration
For the full export architecture (upstream systems, SFTP paths, job ordering, CSV columns), see:
- Sodexo / Transact IDX integration (Dev space - export pipeline)
Datamart-specific objects live in schema sodexo on DM-StudentDevelopment.
| Object area | Examples |
|---|---|
| Cardholder export | [sodexo].[IDX_CardholderDemographics], [sodexo].[fn_IDX_CardholderDemographics] |
| Meal-plan export | [sodexo].[sp_IDX_MealPlanTransact_ExportForAutomation], [sodexo].[IDX_MealPlanTransactExportRow] |
| IDX balance import | [csv].[IDX_Balances] (from Transact /Output/IDX_Balances.csv) |
| Diagnostics | [sodexo].[SP_IDX_CardholderDemographics_Diagnostics] |
| Reconciliation | [sodexo].[SP_IDX_Balances_Reconciliation] |
SSRS reports (Student Development folder)
Deploy and run these from SSRS under Student Development:
| Report | KB guide | Purpose |
|---|---|---|
| Sodexo Demographics Diagnostics | Sodexo Demographics Diagnostics (SSRS) | Per-person trace: why someone is in or out of demographics / meal-plan export |
| Sodexo IDX Balance Reconciliation | Sodexo IDX Balance Reconciliation (SSRS) | Compare IDX live balances to IWU committed exports and Colleague expected totals |
IT linked report: /IT/Diagnostics/Sodexo Demographics Diagnostics points at the canonical report under Student Development (same RDL, easier path for IT support).
Access
Users need:
- SSRS Browser permission on the report (Student Development folder, or IT Diagnostics linked copy for demographics).
- Membership in
IWUNET\istafforIWUNET\DML-STUDEV-SF-LIST(grantsdb_executoronDM-StudentDevelopment- EXECUTE on schemasodexoonly, not direct SELECT on RP-COLLEAGUE).
Reports use WITH EXECUTE AS 'iwudata_reporting' on the stored procedures so callers do not need Colleague table grants.
Support and resources
| Need | Action |
|---|---|
| Missing from cardholder or meal export | Run Sodexo Demographics Diagnostics with Colleague person id |
| IDX balance does not match Colleague | Run Sodexo IDX Balance Reconciliation; review Sync Suspects |
| Export / SFTP / job failure | See Sodexo / Transact IDX integration; check automation logs on datamanager |
| SQL or report defect | Submit ticket to Data / IWUDATA team with Customer ID and report run time |