An end-to-end crime analytics and business intelligence solution combining Oracle PL/SQL, ETL processing, dimensional data warehousing, Oracle APEX, Microsoft Excel and Tableau to transform raw crime data into interactive analytical reports and dashboards.
Crime Analytics Data Warehouse is an end-to-end data warehousing and business intelligence project designed to transform raw police and crime datasets into structured information suitable for analytical reporting and decision support.
The project implements a complete data pipeline covering:
Data Staging → Validation → Cleaning → Transformation → Dimensional Modelling → Warehouse Loading → Analytical Reporting → Dashboard Visualisation
Oracle Database and PL/SQL form the core data-processing layer, while analytical outputs are presented through Oracle APEX, Microsoft Excel and Tableau.
Rather than focusing only on individual SQL queries or isolated visualisations, the project demonstrates how raw operational data can be processed through a structured ETL pipeline and converted into a dimensional analytical environment.
┌──────────────────────────────┐
│ Raw Crime Data │
│ Multiple Data Sources │
└───────────────┬──────────────┘
│
▼
┌──────────────────────────────┐
│ Staging Layer │
│ │
│ Raw data loaded into Oracle │
│ staging tables │
└───────────────┬──────────────┘
│
▼
┌──────────────────────────────┐
│ Data Quality Process │
│ │
│ Good / Bad Data Separation │
│ Cleaning & Re-cleaning │
└───────────────┬──────────────┘
│
▼
┌──────────────────────────────┐
│ Transformation Layer │
│ │
│ PL/SQL Procedures & Packages│
│ Standardisation & Mapping │
└───────────────┬──────────────┘
│
▼
┌──────────────────────────────┐
│ Dimensional Warehouse │
│ │
│ Dimensions + Crime Facts │
└───────────────┬──────────────┘
│
▼
┌──────────────────────────────┐
│ Analytical Layer │
│ │
│ SQL Reports • Views • KPIs │
└───────────────┬──────────────┘
│
┌───────┼────────┐
▼ ▼ ▼
APEX Excel Tableau
The design separates operational data processing from the analytical structures used for reporting.
The core of the project is the Oracle-based Extract, Transform and Load (ETL) pipeline.
Raw source data is first loaded into staging tables, providing an intermediate layer between the source datasets and the final warehouse.
The pipeline performs:
- Source data staging
- Data validation
- Good and bad record separation
- Data cleaning
- Re-cleaning of problematic records
- Data transformation
- Data standardisation
- Dimension loading
- Fact-table loading
- Analytical view generation
The separation between staging, transformation and warehouse layers prevents analytical reporting from depending directly on unprocessed source data.
Oracle Database provides the primary data-processing and warehouse environment.
The repository contains dedicated SQL and PL/SQL scripts for the major stages of the pipeline.
oracle/
├── apex/
│ └── police-analysis-system.sql
│
└── sql/
├── staging-tables.sql
├── staging-procedures.sql
├── data-cleaning.sql
├── data-separation.sql
├── transformation.sql
├── warehouse-load.sql
├── analytical-reports.sql
├── analytical-views.sql
└── analytical-update.sql
The implementation demonstrates database development techniques including:
- SQL
- PL/SQL procedures
- PL/SQL packages
- Cursors
- Sequences
- Triggers
MERGEoperations- SQL views
- Analytical queries
- Staging tables
- Dimension tables
- Fact tables
This allows the data-processing workflow to be implemented through database-side logic rather than relying entirely on manual data manipulation.
Following transformation, cleaned records are loaded into an analytical warehouse structured around dimension and fact data.
Conceptually, the warehouse follows the following structure:
┌─────────────────┐
│ Date │
│ Dimension │
└────────┬────────┘
│
│
┌─────────────────┐ │ ┌─────────────────┐
│ Crime Category │ │ │ Police Station │
│ Dimension │ │ │ Dimension │
└────────┬────────┘ │ └────────┬────────┘
│ │ │
└───────────┐ │ ┌───────────┘
▼ ▼ ▼
┌───────────────────────┐
│ Crime Fact │
│ │
│ Analytical Measures │
│ & Warehouse Keys │
└───────────────────────┘
▲ ▲ ▲
┌───────────┘ │ └───────────┐
│ │ │
┌────────┴────────┐ ┌───────┴────────┐ ┌──────┴─────────┐
│ Region │ │ Police Officer │ │ Additional │
│ Dimension │ │ Dimension │ │ Dimensions │
└─────────────────┘ └────────────────┘ └────────────────┘
The dimensional structure allows analytical questions to be evaluated across areas such as:
- Time
- Crime type
- Region
- Police station
- Police officer
- Crime status
This structure is better suited to analytical workloads than repeatedly querying raw operational data.
The processed warehouse data is analysed through three reporting and business-intelligence environments:
- Oracle APEX
- Microsoft Excel
- Tableau
Each demonstrates a different approach to transforming processed crime data into useful analytical information.
The project includes an exported Oracle APEX application used to provide interactive crime analytics and reporting.
The application provides a browser-based interface over the underlying Oracle data and analytical queries.
The Oracle analytical component explores areas including:
- Police officers by station and region
- Crime status distribution
- Crime workload per police officer
- Crimes reported over time
- Crime type analysis
- Police-station analysis
- Regional crime patterns
These reports transform warehouse records into information that can support operational analysis and decision-making.
The application supports hierarchical exploration of police information.
Region
↓
Police Station
↓
Police Officers
This structure enables users to move from a broader regional perspective towards more detailed station and officer-level information.
The project also contains dedicated SQL scripts for analytical reporting independent of the visual dashboards.
The analytical layer investigates areas such as:
Officer Distribution
│
├── Police Station
└── Region
Crime Analysis
│
├── Crime Status
├── Crime Category
└── Crime Trends
Operational Analysis
│
├── Officer Workload
├── Police Stations
└── Regional Patterns
Analytical views provide reusable database-level representations of important reporting queries.
This creates a separation between the warehouse and presentation layers:
Warehouse Data
↓
Analytical SQL
↓
Analytical Views
↓
Presentation Layer
Microsoft Excel provides another business-intelligence layer for exploring and presenting the crime data.
The analytical workbook contains multiple analyses covering temporal, demographic and operational aspects of the dataset.
The Excel dashboard consolidates important analytical results into a single visual interface.
The workbook supports both high-level and detailed temporal analysis.
These analyses make it possible to examine how recorded crime changes across different periods.
Year-level reporting provides a broader perspective, while monthly analysis enables more granular investigation of changes and patterns.
The workbook also analyses the distribution of crime across police stations.
This provides an operational perspective by allowing crime volumes to be compared between different stations.
Officer-level analysis provides another perspective on crime records and workload distribution.
This type of analysis can help identify differences in recorded workload across officers.
The Excel workbook also explores the data from a demographic perspective.
Age-range analysis demonstrates how the analytical dataset can be explored using demographic dimensions in addition to location, time and operational attributes.
The project also contains a Tableau workbook and its supporting analytical dataset.
Tableau provides interactive visual analytics across several dimensions of the crime data.
The Tableau implementation includes:
- Crime time-series analysis
- Crime rate by police officer
- Crime resolution analysis
- Year-to-year comparison
- What-if analysis
- Interactive dashboard reporting
The main Tableau dashboard combines multiple analytical views into an interactive business-intelligence interface.
Time-series analysis provides a visual representation of how recorded crime changes over time and helps identify temporal patterns.
Officer-level analysis provides insight into how crime records are distributed across individual police officers.
Resolution analysis provides a perspective on crime outcomes and the status of recorded incidents.
Comparative analysis allows crime patterns from different years to be evaluated against one another.
The Tableau component also demonstrates exploratory what-if analysis, extending the project beyond static descriptive reporting.
The project demonstrates how a dimensional crime warehouse can support questions such as:
How does recorded crime change over time?
Which police stations experience greater crime volumes?
How are crimes distributed across different regions?
Which crime categories occur most frequently?
How does workload vary between police officers?
What proportion of recorded crimes reach different resolution states?
How do crime patterns compare between different years?
Are there observable patterns across demographic groups?
These questions illustrate the transition from raw data storage to decision-support analytics.
| Technology | Role |
|---|---|
| Oracle Database | Core database and warehouse platform |
| SQL | Data definition, manipulation and analytical querying |
| PL/SQL | ETL procedures and database-side processing |
| Oracle APEX | Interactive analytical application and dashboards |
| Dimensional Modelling | Analytical warehouse design |
| ETL | Data staging, cleaning, transformation and loading |
| Microsoft Excel | Analytical reporting, charts and dashboard visualisation |
| Tableau | Interactive business-intelligence visualisation |
| CSV | Analytical data interchange |
Crime-Analytics-Data-Warehouse/
│
├── oracle/
│ ├── apex/
│ │ └── police-analysis-system.sql
│ │
│ └── sql/
│ ├── staging-tables.sql
│ ├── staging-procedures.sql
│ ├── data-cleaning.sql
│ ├── data-separation.sql
│ ├── transformation.sql
│ ├── warehouse-load.sql
│ ├── analytical-reports.sql
│ ├── analytical-views.sql
│ └── analytical-update.sql
│
├── tableau/
│ ├── crime-analysis.twb
│ └── crime-analysis.csv
│
├── excel/
│ └── crime-analysis.xlsx
│
├── documentation/
│ ├── project-documentation.docx
│ └── plsql-documentation.docx
│
├── docs/
│ └── screenshots/
│ ├── etl_architecture.png
│ ├── excel_age_range.png
│ ├── excel_crime_by_officer.png
│ ├── excel_crime_by_year.png
│ ├── excel_crime_monthly.png
│ ├── excel_crime_station.png
│ ├── excel_dashboard.png
│ ├── oracle_apex_dashboard.png
│ ├── tableau_crime_rate_officer.png
│ ├── tableau_dashboard.png
│ ├── tableau_resolution_rate.png
│ ├── tableau_time_series.png
│ ├── tableau_what_if_analysis.png
│ └── tableau_year_comparison.png
│
├── .gitignore
└── README.md
Source records are initially imported into staging tables.
The staging layer preserves incoming data separately from the final analytical warehouse and provides a controlled environment for validation and transformation.
Raw Source Data
↓
Staging Tables
↓
Data Quality Checks
Records are evaluated before warehouse loading.
The project contains dedicated logic for:
- Separating valid and problematic records
- Cleaning inconsistent data
- Reprocessing problematic records
- Preparing values for transformation
Staging
↓
Validation
↓
┌────────┴────────┐
│ │
▼ ▼
Good Data Bad Data
│ │
│ Cleaning
│ │
└────────┬────────┘
▼
Transformation
This prevents known data-quality problems from flowing directly into the analytical warehouse.
PL/SQL transformation logic converts cleaned operational records into structures appropriate for dimensional analysis.
This stage prepares:
- Dimension attributes
- Warehouse keys
- Standardised values
- Fact-table relationships
The transformation layer acts as the bridge between cleaned operational records and the final analytical model.
Transformed records are loaded into the dimensional model.
The loading process populates dimension data and establishes the corresponding fact records required for analysis.
Database-side mechanisms such as procedures, sequences, triggers and MERGE operations are used throughout the implementation.
Once warehouse loading is complete, analytical SQL and reusable views provide the reporting layer used by the business-intelligence tools.
Dimensional Warehouse
↓
Analytical SQL
↓
Analytical Views
↓
┌───────────┼───────────┐
│ │ │
▼ ▼ ▼
Oracle APEX Excel Tableau
This architecture separates data processing from visual presentation.
The project was designed to demonstrate the complete process of converting raw data into meaningful analytical information.
Key objectives include:
- Designing an ETL workflow
- Managing data quality
- Implementing database-side transformations
- Building a dimensional warehouse
- Creating reusable analytical queries
- Developing interactive reports
- Visualising crime trends and patterns
- Supporting hierarchical analysis
- Comparing analytical approaches across multiple BI platforms
- ETL pipeline development
- Data staging
- Data validation
- Data cleaning
- Data transformation
- Data-quality management
- Warehouse loading
- Oracle SQL
- PL/SQL
- Procedures
- Packages
- Cursors
- Sequences
- Triggers
- Views
MERGEoperations
- Dimensional modelling
- Dimension tables
- Fact tables
- Analytical schemas
- Warehouse-oriented data processing
- Analytical SQL
- Trend analysis
- Comparative analysis
- Hierarchical analysis
- Operational analysis
- Demographic analysis
- What-if analysis
- Oracle APEX
- Microsoft Excel
- Tableau
- Interactive dashboards
- Analytical visualisation
- Multi-dimensional reporting
The repository preserves the original implementation and analytical artefacts.
The oracle/sql/ directory contains the scripts responsible for staging, cleaning, transformation, warehouse loading and analytical reporting.
oracle/sql/
The exported Oracle APEX application is available under:
oracle/apex/
Importing and running the application requires a compatible Oracle Database/APEX environment and the associated database structures.
The analytical Excel workbook is available under:
excel/
It contains the original spreadsheet-based analytical work and dashboard.
The Tableau workbook and its supporting dataset are available under:
tableau/
A compatible Tableau environment is required to open and interact with the workbook.
If rebuilding and extending the platform today, possible improvements would include:
- Automated ETL orchestration
- Incremental warehouse loading
- Formal data-quality metrics
- ETL validation logging
- Automated data-quality tests
- Slowly Changing Dimensions where appropriate
- A dedicated date dimension for richer temporal analysis
- Direct BI connectivity to the analytical warehouse
- Role-based dashboard access
- Expanded KPI monitoring
- Geospatial crime analysis
- Crime forecasting
- Anomaly detection
- Automated database testing
- CI/CD for database changes
- Cloud-based analytical warehousing
These improvements would allow the original academic implementation to evolve towards a more modern production-style data platform.
This project was originally developed as an academic data warehousing and business intelligence project.
It is preserved here as a portfolio project because it demonstrates substantially more than dashboard creation alone.
The complete workflow covers:
Raw Crime Data
↓
Data Staging
↓
Data Validation
↓
Data Cleaning
↓
Data Transformation
↓
Dimensional Warehouse
↓
Analytical SQL
↓
┌────────────┬────────────┬────────────┐
│ │ │ │
▼ ▼ ▼ ▼
Reports Oracle APEX Excel Tableau
The repository therefore demonstrates the relationship between data engineering, database development, data warehousing, analytics and business intelligence within a single project.
The repository also preserves the original project documentation and PL/SQL documentation.
These documents provide additional detail regarding the implementation, development process and analytical components of the system.
documentation/
├── project-documentation.docx
└── plsql-documentation.docx
Completed
This repository is maintained as a portfolio demonstration of:
Oracle Data Warehousing • PL/SQL • ETL • SQL Analytics • Oracle APEX • Microsoft Excel • Tableau • Business Intelligence