Purpose Statement
First Due's Open Database Connection (ODBC) provides departments with direct access to their data through a PostgreSQL data warehouse hosted on AWS. This connection enables departments to analyze, extract, and integrate First Due data with external analytics applications, business intelligence tools, and custom reporting solutions to generate deeper operational insights and advanced reporting capabilities.
Background Information
The ODBC connection acts as a bridge between a department's local environment and First Due's cloud-based data warehouse.
The warehouse contains approximately 100 tables that organize data from nearly all First Due modules into analytics-ready datasets. These tables include a combination of raw operational data and pre-computed columns designed to simplify reporting and analysis.
The data warehouse is not a direct replication of the production database. Instead, it reflects production data through scheduled refresh cycles optimized for analytics workloads. This design ensures efficient data access for reporting and analysis without impacting system performance.
Departments commonly use ODBC connections to:
Connect business intelligence tools such as Power BI, Tableau, or other analytics platforms
Run custom SQL queries
Build custom dashboards and reports
Integrate First Due data into external reporting environments
Perform advanced operational analysis
Required Permissions
The following permissions and requirements must be met to obtain and use ODBC access:
Administrator or Data Manager permissions to request access
The ODBC Connection feature must be purchased and activated
IT Administrator access may be required to install and configure the PostgreSQL ODBC driver locally
Departments must contact their Implementation Manager or Client Success Manager to initiate ODBC access setup
Data Warehouse Overview
Data Structure
The First Due data warehouse contains roughly 100 tables designed to support analytics and reporting. These tables consolidate data across most First Due modules and present it in a format that is optimized for querying and analysis.
Tables may include:
Raw data captured from operational workflows
Pre-computed fields designed to simplify reporting
Relationships between datasets for advanced analysis
If a department has not purchased a specific module, the associated tables will still exist but will not contain data.
Data Flow
Data is hosted in First Due’s cloud environment and accessed through the ODBC connection from the department’s local environment.
Important characteristics of the data flow include:
Data is pulled by external tools, not pushed from First Due
The warehouse reflects production data but is not intended as a backup system
The structure is designed specifically for reporting and analytics workloads
Data Refresh Frequency
The data warehouse is updated regularly to reflect production data changes.
Typical refresh schedules include:
Standard refresh rate: Once per day
Premium refresh options: Up to four times per day
Departments should plan reporting schedules accordingly to ensure they are working with the most current available data.
Best Practices
Time Zone Considerations
Most timestamp fields in the warehouse are stored in UTC format.
For accurate reporting:
Convert timestamps to your department's local time zone
Look for fields labeled with the
_localsuffix, which contain pre-converted local timestamps
Query Performance
To ensure efficient reporting and data retrieval:
Use filters and WHERE clauses to limit large dataset queries
Avoid retrieving entire tables when only specific data is required
Test large or complex queries during off-peak hours
Schema Awareness
The warehouse schema may evolve as new features and improvements are released.
Best practices include:
Monitoring product release notes for schema updates
Designing queries that can tolerate minor schema changes
Documenting internal reports, queries, and integrations for easier maintenance
Troubleshooting & FAQs
What is ODBC?
First Due’s Open Database Connection allows departments to access their data using external applications designed for data analysis or extraction. The connection provides access to a PostgreSQL data warehouse hosted on AWS.
How does the connection work?
The ODBC connection allows external applications to connect directly to the First Due data warehouse. Users pull data from the warehouse into their analytics environment for reporting or analysis.
What data is included in the warehouse?
The warehouse contains approximately 100 tables that organize data across most First Due modules. Tables contain both raw operational data and pre-computed analytics fields.
Why don’t I see data for certain modules?
Tables exist for most modules, but they will only contain data if your department has purchased and enabled that module.
How recent is the data?
Most departments receive daily refreshes, though premium options allow updates up to four times per day.
Why are timestamps stored in UTC?
UTC timestamps provide a consistent standard across all environments. To display the correct local time, convert the timestamp to your department’s time zone or use fields labeled with the _local suffix.
Why do some ID fields not have matching reference tables?
Some foreign keys reference information that is not included in the warehouse. Lookup tables for these IDs can be provided upon request through support.
How are schema updates communicated?
Changes to the warehouse schema are communicated through product release notes, and departments may receive advance email notifications for significant updates.
Who can help with questions about fields or tables?
Submit a support ticket or contact your Implementation or Client Success Manager to connect with a reporting product specialist.