EXPENSE PROJECT
This is a SQL ETL project that takes raw expense records from various credit cards and produces a final table that can be used for analysis by power BI or tableau.
The raw data originates in a series of CSV files that are imported into simple SQL Server raw tables. The SQL code builds the interim tables, cleans and categorises the data and creates a final table.
The SQL code could just be run as scripts but some of the code is compute heavy and takes some time. So those routines benefit from having been compiled into stored procedures. So for consistency of approach, all the steps will be compiled into stored procedures.
The data is then analysed with Power BI visuals - but the Power BI graphs and data are confidential and can't be shown
The raw data originates in a series of CSV files that are imported into simple SQL Server raw tables. The SQL code builds the interim tables, cleans and categorises the data and creates a final table.
The SQL code could just be run as scripts but some of the code is compute heavy and takes some time. So those routines benefit from having been compiled into stored procedures. So for consistency of approach, all the steps will be compiled into stored procedures.
The data is then analysed with Power BI visuals - but the Power BI graphs and data are confidential and can't be shown
SQL ETL STEPS
STEP 1 - Create the table structures needed for the interim steps and the final table.
![](http://www.weebly.com/weebly/images/file_icons/file.png)
expense_phase01_create_sps_step01_build_tables.sql | |
File Size: | 4 kb |
File Type: | sql |
STEP 2 - Import the raw data files and aggregate it into interim tables for each card.
![](http://www.weebly.com/weebly/images/file_icons/file.png)
expense_phase01_create_sps_step02_fill_tables.sql | |
File Size: | 1 kb |
File Type: | sql |
STEP 3 - REMOVE TRANSACTIONS THAT ARE NOT RELEVANT - THIS DIFFERS BY CARD
![](http://www.weebly.com/weebly/images/file_icons/file.png)
expense_phase01_create_sps_step03_clean_tables.sql | |
File Size: | 1 kb |
File Type: | sql |
STEP 4 - COMBINE CLEANED TABLES
![](http://www.weebly.com/weebly/images/file_icons/file.png)
expense_phase01_create_sps_step04_combine_tables.sql | |
File Size: | 0 kb |
File Type: | sql |
STEP 5 - CATEGORISE TRANSACTIONS BASED ON TRANSACTION DESCRIPTION
![](http://www.weebly.com/weebly/images/file_icons/file.png)
expense_phase01_create_sps_step05_categorise_tables.sql | |
File Size: | 2 kb |
File Type: | sql |
STEP 6 - MASTER SCRIPT THAT RUNS THE PROCEDURES AND BUILDS FINAL TABLE FOR VISUALS
![](http://www.weebly.com/weebly/images/file_icons/file.png)
expense_phase02_run_sps_step01.sql | |
File Size: | 2 kb |
File Type: | sql |