Citi bike trips data analysis
Budget: $10 – $30 USD
RoadMap
OLTP DATABASE DESIGN (NORMALIZED DESIGN)
Enterprise Database Model
Logical Database Model
Relational Database Model
DDL Generation
OLTP DATABASE IMPLEMENTATION
>Create Objects (Tables, Indexes, Triggers, Function, Procedures)
> Populate Data
> Test Data, Constraints, Triggers for validations
DATA WAREHOUSE DESIGN and IMPLEMENTATION
DE-Normalization from OLTP design (STAR SCHEMA with DIM_DATE dimension)
Enterprise, Logical, and Relational Model
DDL code generation and Creation of Schema
ETL CODE
Extract from OLTP database based on timestamp (CDC-change data
capture)
Transform Data to match DW schema (using External tables, and PL/SQL)
Load Data to DW schema
Reports and Analytics from DW database, e.g. using Tableau
> Use any reporting tool for querying data
> Generate reports, Data analytics charts/graphs, Dashboard
Suggested Inclusion:
The project will have two different database systems of different vendor
products
(E.g. Oracle and MySQL)
>One database will be of OLTP database (Online Transaction Processing
System) and other database will be DW (Data Warehouse – DSS)
> Reporting tool / Analytics tool will be interfaced with DW database
> There will be ETL (Extract, Transform, and Load) database code to load
data from OLTP database, transform it matching DW schema design, and
then loading it to DW database.
>There will be CDC approach (Change Data Capture) for ETL database
routine to implement incremental ETL
> Will use advance database features e.g. External tables, Partitioned Tables,
function base indexes, PL/SQL(procedures, functions, packages, triggers,
data masking)
> The DW systems, will have more analytics and reporting for making
business decisions
> There will be development for EDI (Electronic Data Interface), SQL Loader
(DB utility for loading data to database from a text file)
> There will be use of data movement utilities (exports/import, SQL Loader
etc.)
> There will be use of data analytics using data visualization tool, such as
Tableau, ClickView etc.
OLTP DATABASE DESIGN (NORMALIZED DESIGN)
Enterprise Database Model
Logical Database Model
Relational Database Model
DDL Generation
OLTP DATABASE IMPLEMENTATION
>Create Objects (Tables, Indexes, Triggers, Function, Procedures)
> Populate Data
> Test Data, Constraints, Triggers for validations
DATA WAREHOUSE DESIGN and IMPLEMENTATION
DE-Normalization from OLTP design (STAR SCHEMA with DIM_DATE dimension)
Enterprise, Logical, and Relational Model
DDL code generation and Creation of Schema
ETL CODE
Extract from OLTP database based on timestamp (CDC-change data
capture)
Transform Data to match DW schema (using External tables, and PL/SQL)
Load Data to DW schema
Reports and Analytics from DW database, e.g. using Tableau
> Use any reporting tool for querying data
> Generate reports, Data analytics charts/graphs, Dashboard
Suggested Inclusion:
The project will have two different database systems of different vendor
products
(E.g. Oracle and MySQL)
>One database will be of OLTP database (Online Transaction Processing
System) and other database will be DW (Data Warehouse – DSS)
> Reporting tool / Analytics tool will be interfaced with DW database
> There will be ETL (Extract, Transform, and Load) database code to load
data from OLTP database, transform it matching DW schema design, and
then loading it to DW database.
>There will be CDC approach (Change Data Capture) for ETL database
routine to implement incremental ETL
> Will use advance database features e.g. External tables, Partitioned Tables,
function base indexes, PL/SQL(procedures, functions, packages, triggers,
data masking)
> The DW systems, will have more analytics and reporting for making
business decisions
> There will be development for EDI (Electronic Data Interface), SQL Loader
(DB utility for loading data to database from a text file)
> There will be use of data movement utilities (exports/import, SQL Loader
etc.)
> There will be use of data analytics using data visualization tool, such as
Tableau, ClickView etc.