Create data warehouse solution in SQL

Assignment Help PL-SQL Programming

Reference no: EM132831782

You can use Internet to check your syntax, but cannot ask anyone to do the exam for you. Partial credit might be given even if your answers are incorrect. Your submitted files should contain one SQL file for part 1, and aDTSX file part 2. If you use Flat File or Excel Data Source for Part 2, please include the file also. It is important that you specify your name in the assignment document/attachment:

The attachment name should follow the following convention for Part I:
FIRSTNAME_LASTNAMEPART1.SQL
The attachment name should follow the following convention for Part II:
FIRSTNAME_LASTNAME PART2.DTSX

PART I: Use the OLTP logical schema below to build data warehouse tablesin SQL:

Question 1: CEO of Entertainment Inc. wants to keep track ofperformance of engagement that the companyhasengaged.For each customer and agent, he wantsto know total number of contracts by month, and monthly contract success ratio (calculated by total number of contracts expiredeach monthdivided by total amount of contract price each month).In addition, for each customer and agent he wants to know total numbers of contracts expired each week, and total number of contracts still active each week. Provide 4-step dimensional model in the SQL comment section (4 points) and create data warehouse solution in SQL for the dimensional model that you propose.

Question 2: CEO of Entertainment Inc. wants to keep track of the life cycle of engagement contract. Particularly, he wants to know the total number of agents involved for each engagement contract phrase when the contractisauthorized, engaged,monitored and closeout. Provide 4-step dimensional model in the SQL comment section (4 points) and create data warehouse solution in SQL for the dimensional model that you propose.

PART II:

Import the midtermdata codefor the OLTP logical schema in Part Iand develop ETL process to create data warehouse tablesin SSIS.
Your dimensional and fact tables should meet these requirements:
- DimDatehas DateKey values ranging from 20120901to 20130312(YYYYMMDDinteger data type). DimDateshould contain IsMartinLutherKingHolidayattribute in boolean data type, LastDayOfMonthattribute in booleandata type, and other required attributes(10points).If you use Flat File or Excel Data Source to import datekey data, include the datesource file in the submission also.

- DimEntertainershould contain entertainer's full address, entertainer's last 4 digit of SSN, entertainer's current year of active until today, and other required attributes. For example, for EntertainerID1 you should return "4110 Old Redmond Rd., Redmond, WA 98052" as full address, and 22as year of active.

- DimEntertainershould be able to handle slowly changing dimension issues.

- DimAgent should contain agent' full name and agent key. For example, "William Thompson" is a full name for AgentKey 1. This table doesn't need to handle slowly changing dimension issues.

- FactlessAgent should contain 3 keys (DateKey, AgentKey, and EngagementNumber).

Attachment:- Logical schema.rar

Reference no: EM132831782

Questions Cloud

Develop strategic plan : Identify the agency, organization, or community for which you will develop a strategic plan.

How should the cash receipts be recorded by Hairy Happy : The payment is in cash. How should the cash receipts be recorded by Hairy Happy Harry Limited

What type of industry would use six sigma methods : What type of industry would use Six Sigma methods that you may work for or if the firm you work for doesn't use it,

Summarizing an international leader in business : Summarizing an international leader in business. It is important to show that you have put in time, thought, and energy into the assignment.

Create data warehouse solution in SQL : Create data warehouse solution in SQL for the dimensional model that you propose - Import the midtermdata codefor the OLTP logical schema in Part I

Prepare a trial balance as of the end of September : The company received $4,000 cash in partial payment on the receivable created in transaction k. Prepare a trial balance as of the end of September

What are the health risks associated with egg products : What are the health risks associated with egg products and alternative egg products and what would done as commercial cook to minimise the risks associated with

Promoted therapeutic nurse-patient relationship : How did you engage in patient care coordination when caring for your patients with helping techniques that promoted therapeutic nurse-patient relationship?

Provide a proposal for a qualitative research : A company has introduced a new customer management system but it seems some of its employees still prefer doing things the "old fashioned" way.

User Account

All Pages