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 ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 25, 2024

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.

image text in transcribed

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