Reference no: EM132346673

Assignment -

Introduction - In this project you will assist a pet rescue charity with managing their donations related data. The charity organizes an annual donation drive. The city is divided to donations areas (every donation area is approximately 6 postal codes) and assign a group of volunteers to every area. The volunteers will go door to door in their designated areas in order to collect donations from the residents. They can collect cash, checks, or credit card payments. At the end of every week the volunteering group leaders will fill in a list with the donation record that were collected in their area, and send it to the charity main office. The staff in the main office will load the list to a central table after rejecting non-valid entries and then use the data to perform analysis.

Data Sources - Central Donations Repository

The tables created by projectTables.sql script resides in the charity's oracle database server.

Donation List - The list of donors is stored in a comma separated file named donorsList.csv. This is the list that the volunteer group lead sends to the main office. It shows the schema and contains only two entries, but each member of the group (presumably a volunteer with a distinct volunteer no) must provide a separate list for their "area" with at least 15 entries. As a result each group will have three (or four) donor lists that contain 15 additional entries in the donors list.

Make sure that there are both valid and invalid entries. The invalid entries will be rejected and sent back to the volunteer coordinator.

Master Addresses Table - The address table that the charity maintains is not updated and it often gets out of date. However, there is another department in the organization has an address table that they regularly update and keep current. The table is in SQLServer database and below is the connection information to the server.

Tasks - Refresh the address table in the oracle database with the addresses in the master table. You will need to transform some data types and generate a sequential id for the address. Also note that the ids of some addresses will be used as foreign key in donation table your solution should accommodate this fact.

Create a process that loads the donations list to the central donations table: In this task you will load the file into the central repository (Oracle tables). You need to make sure that only the donations with valid addresses are inserted into the table. Donation records with erroneous addresses must be rejected. Also make sure to reject the donations that have nulls for the mandatory columns in your database (Not null columns). You must generate csv files to the volunteer group leaders with the records that were rejected in their area.

Create a star schema for the donations. The grain of the schema should be the combination of day, address, and volunteer.

Create a process to load the data to the star schema from the central donation repository.

Create views that shows:

• The average and sum of the donation by day, month, year
• The average and sum of the donations by address, postal code
• The average and sum of the donations by volunteer and volunteer group leader

Basic Security -

Create a user named DMLUser and give the user permissions to implement all DML on address, donation, and volunteer tables.

Create a user named Dashboard and give the user read permissions on the views.

Deliverables - Group Work: Create a Microsoft word report documenting the processes. The report must include 6 sections. Each of the section will be describing one of the 6 tasks. You must describe the processes in details and include all the code that you used to create the process. Use embedded screenshot and text descriptions in the document to illustrate the ETL processes. The screenshots must be proportional to the page and yet must be clear. Restrict the screen shots only to the areas that are relevant to the description. The code that will be embedded in the document must be text not screen shots.

Individual work - You must write a one or two pages summary that describes your design for the project in your own words and details your involvement in the project. The summary must include:

A brief description of your solution to the project and why did you choose to solve it that way

State your contribution to the project.

State the difficulties that you faced in the project.

Suggestions to improve the data flow/storage operations for this ogranization.

Attachment:- Assignment File.rar