Section $1$: Examine the Superstore Data.

$$ Open the Superstore data file and look at the Order Info tab.

$$ The Superstore data file is a fictional open source dataset that contains a variety of order data for a company that does wholesale

equipment sales across the US$.$

o You should see $22$ different fields that correspond to a variety of different categories.

o You should also see that there are $9954$ rows $($the top row doesn$$t count as it is the attribute name$).$

o You should also see that the Row IDs are color coded with five colors. They have been color$-$coded based on the table they

will be in$.$

o There are additional tabs $($Customer$,$ Employee, Order, OrderLine ID$,$ and Product$)$ along the bottom of the worksheet that

have the data sorted by their eventual table location. Notice, those tabs have the same colors as the Row IDs.

o In each of the additional tabs you will see only the relevant fields, and you should see the Primary Keys $($PK$)$ and Foreign

Keys $($FK$).$ Note: Not all tables need FKs$,$ as the FKs are only required to connect back to a specific PK where there is a table

relationship in place.

o Where there are FKs$,$ they have been color coded to indicate which table they connect back to$.$

$$ Click the Order Recreation tab.

o You should see all the fields from all the color$-$coded tabs $($Customer$,$ Employee, Order, OrderLine ID$,$ and Product$).$

o These fields are organized based on the data model for the Superstore data $($See Figure $4)$

Section $2$: Re$-$create your Superstore Order.

$$ Here, you will use the same process used in the Intro Assignment to re$-$create your Order.

$$ The OrderLine ID data is already added to the Order Re$-$creation tab.

$$ Start by using the OrderLine ID data to retrieve the data from the tables directly connected to it in the data model $($Figure $4).$

$$ Use the fields labeled with a $$FK$$ to connect to the corresponding fields labeled with a $$PK$$ to retrieve the desired data.

o Note: Remember to use a mixed reference for your lookup$\_$value, and an absolute reference for your table$\_$array. This will

ensure you can paste your formula and only must make minor adjustments.

$$ After you retrieve the data from the tables connected to the OrderLine ID table, use the added FKs to populate the remaining tables.

$8)$ What Fields are in Columns AB and AC$?$ What are the values in AB$317$ and AC$317$ once you have retrieved

the Product Data?

$$ Reminders:

Finn $201922$

Based on Superstore data from Tableau

o You will need to populate the Fields corresponding to the Product tab $($Columns AA through AD$).$

o You will need to use the Product ID $($FK$)$ as your lookup$\_$value and the data contained in the Product tab as your

lookup$\_$array.

Check Figure: What are the values in AB$2048$ and AC$4096$

AB$2048$ is Other

AC$4096$ is Office Suppliers.

$9)$ What Fields are in Columns K and N$?$ What are the values in K$951$ and N$951$ once you have retrieved the

Order Data?

$$ Reminders:

o You will need to populate the Fields corresponding to the Order tab $($Columns H through P$).$

o You will need to use the Order ID $($FK$)$ as your lookup$\_$value and the data contained in the Order tab as your lookup$\_$array.

$$ Remember to change the number format on both date columns once you have autofilled them.

Check Figure: What are the values in K$2048$ and N$4096$

K$2048$ is $2/6/2016$

N$4096$ is Texas.

$10)$ What Fields are in Columns E and F$?$ What are the values in E$317$ and F$317$ once you have retrieved the

Employee Data?

$$ Reminders:

o You will need to populate the Fields corresponding to the Employee tab $($Columns E through F$).$

o You will need to use the Employee ID $($FK$)$ as your lookup$\_$value and the data contained in the Employee tab as your

lookup$\_$array.

Check Figure: What are the values in E$2048$ and F$4096$

E$2048$ is Anna Andreadi.

F$4096$ is Central.

$11)$ What Fields are in Columns B and C$?$ What are the values in B$951$ and C$951$ once you have retrieved the

Customer Data?

$$ Reminders:

o You will need to populate the Fields corresponding to the Customer tab $($Columns B through C$).$

o You will need to use the Customer ID $($FK$)$ as your lookup$\_$value and the data contained in the Customer tab as your

lookup$\_$array.

Check Figure: What are the values in B$2048$ and C$4096$

B$2048$ is Charles Crestani.

C$2048$ is Consumer.

Superstore Assignment Screen Clipping $1$

$$ Re$-$orientation of Order Re$-$creation tab

o Compare your results in the Order Re$-$creation tab to the Order Info tab. You should notice you have a few additional

columns $(26$ vs $22),$ and your columns are organized in a different order.

o First, highlight Columns A through H and Insert Column. This will give you the space to move things around easily.

o Reorganize your columns by the same general organization found in the Order Info tab.

o Right click and Hide each of the extra columns $($All of the FKs other than Order ID $($FK$)).$

$$ it is important to Hide them, and not delete them since many of your VLOOKUPs are using the results of these

columns. If you were to delete them, many of your formulas would error.

o T