BUSINESS INTELLIGENCE

PORTFOLIO

Aaron Moffat

aarongmoffat@yahoo.com

704.614.0471

SQL Server 2005 Integration Services Project

Project Summary

Build and Design a SQL Server 2005 database for Allworks Construction Company . Excel Spreadsheets and CSV files provided data for employee and customer information .

Project Overview

 

Create DTS packages to read each source file (Extraction), made conversions or (Transformation), and load them into the designated tables within AllWorks SQL Database (Loading). Emails were generated for the results of each package. Nightly schedules were create to midnight processing for all updates and new records. Create separate package to handle indexing and file truncation. Schedule the packages to run nightly at midnight to update the database with any new or changed data. Create a separate package to handle nightly database backups.

Division Master Table 

This package loads the division data from an Excel Spreadsheet, ClientGeographies.xls. Data cleansing takes place to verify that the data is in a useable format. Divisions can be added or updated to the division table. Database integrity is upheld through referencing the division identification number with the data provided from the source data file. A log is created to quantify the number of updated and inserted records which are sent to a specific destination. The records are sent to a mail recipient who will tell them if the package was a success along with the file name, user who performed action, and record results. In the event of a failure of the package during execution an error would be sent to specific address alerting them of the failure.

Client Grouping Master Table

This package loads the division data from an Excel Spreadsheet, ClientGeographies.xls. Data cleansing takes place to verify that the data is in a useable format. Divisions can be added or updated to the division table. Sorting of like data was used to help in avoiding repetitive or duplicates from data source. Database integrity is upheld through referencing the Grouping primary key and the grouping identification number with the data provided from the source data file. A log is created to quantify the number of updated and inserted records which are sent to a specific destination. The records are sent to a mail recipient who will tell them if the package was a success along with the file name, user who performed action, and record results. In the event of a failure of the package during execution an error would be sent to specific address alerting them of the failure.

Project(“Job”)Time Sheets (Labor) TableThis package loads the employee data from a comma separated value file which contained several files with different data. A counting mechanism was used to create an accumulation function on all the files that were processed. Data cleansing takes place to verify that the data is in a useable format. Errors are avoided by referencing the employeePK, job masterPK, worked hours with the client number on the source data file. If the record is null it will error that record to prevent corruption within database table. The error log will report the error to the error log table in the database which records the description and time of error. A log is created to quantify the number of updates, inserts and error records which are sent to a specific destination. The records are sent to a mail recipient who will tell them if the package was a success along with the file name, user who performed action, and record results. In the event of a failure of the package during execution an error would be sent to specific address alerting them of the failure.

Project(“Job”)Time Sheets (Labor) Table cont.

Client GroupingXref Table

This package loads the Special Grouping data from an Excel Spreadsheet, ClientGeographies.xls. Data cleansing takes place to verify that the data is in a useable format. Grouping is updated to the GroupingsXClients table. Database integrity is upheld through referencing the client’s account number with the data provided from the source data file. A second integrity search is performed through referencing the client’s grouping with the data provided from the source data file. A log is created to quantify the number of updated records which are sent to a specific destination. The records are sent to a mail recipient who will tell them if the package was a success along with the file name, user who performed action, and record results. In the event of a failure of the package during execution an error would be sent to specific address alerting them of the failure.

Employee Rate Table

 

This package loads the employee data from an Excel Spreadsheet, Employees.XLS. Data cleansing takes place to verify that the data is in a useable format. Errors are avoided by referencing the employee’s and rate with the data provided from the source data file. If the record is null it will error that record to prevent corruption within database table. A log is created to quantify the number of updates, inserts and error records which are sent to a specific destination. The records are sent to a mail recipient who will tell them if the package was a success along with the file name, user who performed action, and record results. In the event of a failure of the package during execution an error would be sent to specific address alerting them of the failure.

Project(“Job”) Master Table

This package loads the employee data from an Excel Project master, ProjectMaster.XLS. Data cleansing takes place to verify that the data is in a useable format. Errors are avoided by referencing the client number with the client number on the source data file. If the record is null it will error that record to prevent corruption within database table. The error log will report the error to the error log table in the database which records the description and time of error. A log is created to quantify the number of updates, inserts and error records which are sent to a specific destination. The records are sent to a mail recipient who will tell them if the package was a success along with the file name, user who performed action, and record results. In the event of a failure of the package during execution an error would be sent to specific address alerting them of the failure.

Master Package

This is a simple control flow task that creates order for each package to be executed. The purpose of this packaged is to align the packages so data dependencies would not be missing when other packages are ran. An example of this is the county table, which is a simple insert that checks data if it exists and if not it is inserted. However if not run at specific time package would fail.

Maintenance Package

After ETL process completion a backup of the database and also to perform some maintenance tasks such as indexing and truncating the database. This SQL Server Agent job was added to execute a Database Maintenance Package daily at midnight. Completion of this job is sent via email to Developer and database administrator.