Refresh the address table in your group's Oracle Central Donation Repository with the addresses in the master
Question:
- Refresh the address table in your group's Oracle Central Donation Repository with the addresses in the master SQL Server table. You will need to transform some data types and generate a sequential id for the address. Note the following:
- The IDs of some addresses will be used as foreign keys in the Central Donation Repository, so your solution should accommodate this fact.
- The master address table does not contain postal codes, but your views (see task 5 below) will require them. Your solution should accommodate this fact.
- show a process that loads the donation CSV files into the Central Donation Repository. Your process must allow for a variable number of CSV input files to allow for a variable number of volunteer leaders. The process must ensure that only valid donation entries are inserted into the Central Donation Repository.
Your process must be repeatable. That is, it must be able to be re-run.
Donation entries must be rejected in the following cases: (a) if they have invalid addresses, or (b) if they have nulls in the mandatory (NOT NULL) columns in the Repository.
Your process must produce one CSV file of rejected entries for each volunteer leader.
- show a star schema and ER diagram for the donations data mart. The grain of the schema should be the combination of date at the level of day, the address and the volunteer containing the number (count) of donations and the sum of the value of the donations.
The date dimension should include the following fields: date (2021-07-01, 2021-07-02), year (2020, 2021), month_number (1...12), day_number_in_month (1...31), month_name_short (Jan, Feb...), month_name_long (January, February...)
The address dimension should include the fields: postal code, address.
The volunteer dimension should include the fields: volunteer number, volunteer name.
- show a process to load the data mart from the Central Donation Repository. You will need to determine whether to load as a full refresh or incrementally. This process must be repeatable.
- show views to provide the following:
- The number of donations and total donation value for each date showing the date hierarchy (year, month number, long month name, day).
- The number of donations, sums and average donations by location hierarchy (postal code and address).
- The number of donations, sums and average by volunteer leader and volunteer.
- Basic Security
- Create a user named ETLUser. Give the user permissions to read all the Central Donation Repository data. Give the user write and update permissions to the DataMart tables.
- Create a user named Dashboard. Give the user read permissions on the views
Systems analysis and design
ISBN: 978-0136089162
8th Edition
Authors: kenneth e. kendall, julie e. kendall