Lab 7 Preparing Data for Loading Statement purpose To understand how to gather data by joining multiple tables in order to loading a data warehouse dimensional tables Activity Outcomes Students will be able to prepare data (raw and or calculated data) issued from many tables They need to solve the problem of identifiers for the DW tables Identifiers could be values coming directly from the data source or as surrogate key A surrogate key is a replacement identifier automatically generated (by an Oracle Sequence for example) Instructor Note Implement all the questions below and submit the code and its result displayed by Oracle after each execution Tables to use To load the Star schema in Figure 7 1 you will use the following Temporary tables prepared during the previous Labs T DATE, CUSTOMERS COPY, and ORDERS COPY Questions to Answer To have all data on hand, print the content of the tables mentioned above Because the OE database does not have a SALESMAN table, we CREATE one called T SALESMAN (SALES REP ID, SNAME) from the OE ORDERS SALES REP ID its columns are SALES REP ID unique values Replace Null IDs with 999 it is better than ignore Nulls in analysis Do not define a PK because the table has too few rows SNAME is a new column Unfortunately, we do not have the names of the sales representatives in the OE database To overcome this problem and facilitate filling the new table, set the SNAME as the concatenation of the SALES REP ID and the constant NAME (for example, Salesman 153 will have name NAME 153 ) note this tip is just for the lab work but does not apply is real situations You should get the following content for T SALESMAN SALES REP ID SNAME 153 NAME 153 154 NAME 154 155 NAME 155 156 NAME 156 158 NAME 158 159 NAME 159 160 NAME 160 161 NAME 161 163 NAME 163 999 NAME 999 Preferably, print the contents of the T SALESMAN table Assume we implement the star schema depicted in Figure 7 1 as a set of relational tables Each dimension is transformed into a table the fact also becomes a table Figure 7 1 A Simplified Star Schema Year Month Name Quarter T SALESMAN SName ID S Month No Date ORDERS COPY D SALESMAN ID T T DATE OMode D TIME F ORDERS D ORDERS ID O Status ORDER AMOUNT C FName ID C C LName D CUSTOMERS Gender CUSTOMER COPY o Type Year Month Name Quarter T SALESMAN SName ID S Month No Date ORDERS COPY D SALESMAN ID T T DATE OMode D TIME F ORDERS D ORDERS ID O Status ORDER AMOUNT C FName ID C C LName D CUSTOMERS Gender CUSTOMER COPY o Type

The Answer is in the image, click to view ...

Question: Lab # 7 Preparing Data for Loading Statement purpose To understand how to gather data by joining multiple tables in order to loading a data

Lab # 7

Preparing Data for Loading

Statement purpose

To understand how to gather data by joining multiple tables in order to loading a data warehouse dimensional tables.

Activity Outcomes

Students will be able to prepare data (raw and/or calculated data) issued from many tables. They need to solve the problem of identifiers for the DW tables: Identifiers could be values coming directly from the data source or as surrogate key. A surrogate key is a replacement identifier automatically generated (by an Oracle Sequence for example).

Instructor Note

Implement all the questions below and submit the code and its result displayed by Oracle after each execution.

Tables to use

To load the Star schema in Figure 7.1 you will use the following Temporary tables prepared during the previous Labs: T_DATE, CUSTOMERS_COPY, and ORDERS_COPY.

Questions to Answer

To have all data on hand, print the content of the tables mentioned above.

Because the OE database does not have a SALESMAN table, we CREATE one called T_SALESMAN (SALES_REP_ID, SNAME) from the OE.ORDERS.SALES_REP_ID; its columns are:
- SALES_REP_ID unique values. Replace Null IDs with 999; it is better than ignore Nulls in analysis. Do not define a PK because the table has too few rows.
- SNAME is a new column. Unfortunately, we do not have the names of the sales representatives in the OE database. To overcome this problem and facilitate filling the new table, set the SNAME as the concatenation of the SALES_REP_ID and the constant NAME (for example, Salesman 153 will have name NAME-153 .); note this tip is just for the lab work but does not apply is real situations.

You should get the following content for T_SALESMAN:

SALES_REP_ID	SNAME
153	NAME-153
154	NAME-154
155	NAME-155
156	NAME-156
158	NAME-158
159	NAME-159
160	NAME-160
161	NAME-161
163	NAME-163
999	NAME-999

Preferably, print the contents of the T_SALESMAN table.

Assume we implement the star schema depicted in Figure 7.1 as a set of relational tables: Each dimension is transformed into a table; the fact also becomes a table.

Lab # 7 Preparing Data for Loading Statement purpose To understand how

Figure 7.1. A Simplified Star Schema

Year Month-Name Quarter T_SALESMAN SName ID_S Month-No Date ORDERS COPY D_SALESMAN ID T T_DATE OMode D TIME F_ORDERS D_ORDERS ID_O Status ORDER_AMOUNT C FName ID C C LName D_CUSTOMERS Gender CUSTOMER_COPY o Type Year Month-Name Quarter T_SALESMAN SName ID_S Month-No Date ORDERS COPY D_SALESMAN ID T T_DATE OMode D TIME F_ORDERS D_ORDERS ID_O Status ORDER_AMOUNT C FName ID C C LName D_CUSTOMERS Gender CUSTOMER_COPY o Type

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!

I need this in SQL, please. this is the DW example. -- CUSTOMERS_COPY CREATE TABLE CUSTOMERS_COPY AS SELECT CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, NLS_LANGUAGE, NLS_TERRITORY, CREDIT_LIMIT,...

see the new one CCOS221 DW Labs 1 to 3.STO.pd 701 2. Use the CREATE and SELECT us to Deemaldabled_PRODUCT Dating PRODUCT_COPY and CATEGORY_COPY PRODUCTS PRODUCT MODUCT NAME CATEGORY CATEGORY_NAME...

CCDS - 2 2 1 Data Warehouse 1 | P a g e Lab # 1 0 This Lab has 2 parts. First, you develop SQL SLECT statements for OLAP queries in SQL and then, in the second part you Visualize OLAP Query results...

University of Jeddah Academic Year 2020/2021 College of Computer Science & Engineering IST Dept. CCDS-221 Data Warehouse Lab # 4 ETL Process Derive data using Correlated statements, Grouping and...

INSERT INTO It is used to add one or more rows to a table Attribute values should be listed in the same order as the attributes were specified in the CREATE TABLE command INSERT INTO tablename...

5, Putting Together an Evaluation Matrix An evaluation plan is a written document that describes the cWill/mat\Questions to ask yourself when putting together an evaluation matrix: Evaluation...

U.S. Army Cost Benefit Analysis Guide 12 JANUARY 2010 Prepared by Office of the Deputy Assistant Secretary of the Army (Cost and Economics) Version 1.0 U.S. Army Cost Benefit Analysis Guide - V 1.0 2...

The Final Paper demonstrate of the reading as well as the discussion ponts. The purpose of this assignment is to the concepts studied in this into a cohesive and paper. The Final Paperwill be titled...

Computer Graphics (CG) is a small manufacturer of electronic products for computers with graphics capabilities. The company has succeeded by being very innovative in product design. As a spin-off of...

4. A 5 g lead bullet traveling at 300 m/s is stopped by a large tree. If half the kinetic energy of the bullet is transformed into internal energy and remains with the bullet while the other half is...

Question 1 8 3 pts Tina purchased a personal umbrella policy with a $ 1 million limit and a $ 1 , 0 0 0 self - insured retention. Her insurer required her to carry a $ 3 0 0 , 0 0 0 liability limit...

What cell reference should you enter if you want to create a formula that refers to cell A3 on the Expenses worksheet? A. Expenses A3 B. ExplA3 C. ExpensesA3 D. Expenses Which of the following images...

3. Assume that as an HR staff member, you have been asked to research consumer-driven health plans because your employer is considering implementing one. Go to a leading benefits information...

1. How would you evaluate The Limiteds approach for communicating information about benefits to its employees? What might you have done differently if you were an HR professional in the company?

2. Discuss the following statement: Health care costs are out of control in the United States, and increasing conflicts between employers and employees are likely as employers try to reduce their...