For this case, you received a data file, Analytics_
mindset_case_studies_ETL_Case4.xlsx (go to the student
download page at http://www.pearsonhighered.com/romney to
download this file). It includes 789 lines of journal entries for 11
days from a hotel and conference center (on the tab labeled
JELineItems) as well as other important accounting-relevant
datasets on these other tabs: BusinessUnits,
ChartOfAccounts, PreparerInfo, and Source. The following is a
select list of data fields from this file noting the field name and
field descriiption tabs on which the data field is located.
You have been asked to prepare a single, flat file (i.e., spreadsheet) with all
of the data fields from each tab within the data file combined in a single
sheet labeled Case 6-1 solution. This sheet is already provided in the
workbook. The journal entry line item data and the required column headers
(attributes) have already been copied into this new spreadsheet. You are
required to populate the remaining fields with accurate data.
2. Management wants to create a repeatable ETL data process for this
scenario. This requires a template that uses a consistently formatted
dataset. Therefore, you should retain formulas in your final sheet for
columns K through Q, and you should not add, delete, or move any data in
any of the other sheets.
3. Your final sheet should look like the following screenshot. (The first three
rows are provided, showing the correct answer. Make sure your rows have
the formula entered in them).