This assignment uses a GCU provided virtual machine ETL is a process used to move data from one system and data structure to another One tool used for this process is called SQL Server Integration Services (SSIS) View the SQL Server Integration Services (SSIS) video within the Video Playlist Relational Databases for Business Applications, located in the topic Resources For this assignment, refer to the SYM 408 ELT Process Data Employee Hours zipped file to complete the assignment Use SQL Server Data Tools (SSDT) within the virtual machine to build an SSIS package For this activity, include the following First, extract the datafile provided by your instructor and use SSMS to map the datafile's columns into a SQL table Then, in SSDT, Create Data Flow Task to perform the following Use the Other Sources section to import the datafile Use the DerivedColumn component to transform the datafile concatenate first and last names into one column, Full Name, in your SQL table, and map the Hire date column as date in your SQL table with the following format mm dd yyyy Use the Other Destination section to load the datafile in your SQL table Verify the completion of the ETL process by retrieving the data from your SQL table as follows Create and execute a query that shows how many records are in the table Take a screenshot of the query and results Create and execute a query that shows the Full Name, Hire date and Week 4 hours for everyone who worked fewer than 40 hours in week 4 Take a screenshot of the query and results Finally, in a succinct paragraph, explain two data flow components of SSIS, data location differences with relational and multidimensional models, data transformation tools, BI OLAP tools within SSIS and SSIS optimization techniques Include examples in your response Submit the following in a single document compatible with the digital classroom (e g , Microsoft Word) Include a one line description above each screenshot Screenshot of query and results showing how many records are in the table Screenshot of query and results showing, in updated format, who worked fewer than 40 hours in week four Paragraph of explanations While APA style is not required for the body of this assignment, solid academic writing is expected, and documentation of sources should be presented using APA formatting guidelines, which can be found in the APA Style Guide, located in the Student Success Center This assignment uses a rubric Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion

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

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Feb 16, 2024

This assignment uses a GCU-provided virtual machine. ETL is a process used to move data from one system and data structure to another. One tool

This assignment uses a GCU-provided virtual machine.

ETL is a process used to move data from one system and data structure to another. One tool used for this process is called SQL Server Integration Services (SSIS).

View the "SQL Server Integration Services (SSIS)" video within the "Video Playlist: Relational Databases for Business Applications," located in the topic Resources.

For this assignment, refer to the "SYM-408 ELT Process Data Employee Hours" zipped file to complete the assignment.

Use SQL Server Data Tools (SSDT) within the virtual machine to build an SSIS package. For this activity, include the following:

First, extract the datafile provided by your instructor and use SSMS to map the datafile's columns into a SQL table.

Then, in SSDT, Create Data Flow Task to perform the following:

Use the Other Sources section to import the datafile.
Use the Derived Column component to transform the datafile: concatenate first and last names into one column, "Full Name," in your SQL table, and map the "Hire date" column as date in your SQL table with the following format mm-dd-yyyy.
Use the Other Destination section to load the datafile in your SQL table.
Verify the completion of the ETL process by retrieving the data from your SQL table as follows:
- Create and execute a query that shows how many records are in the table. Take a screenshot of the query and results.
- Create and execute a query that shows the Full Name, Hire date and Week 4 hours for everyone who worked fewer than 40 hours in week 4. Take a screenshot of the query and results.

Finally, in a succinct paragraph, explain two data flow components of SSIS, data location differences with relational and multidimensional models, data transformation tools, BI/OLAP tools within SSIS and SSIS optimization techniques. Include examples in your response.

Submit the following in a single document compatible with the digital classroom (e.g., Microsoft Word). Include a one-line description above each screenshot.

Screenshot of query and results showing how many records are in the table
Screenshot of query and results showing, in updated format, who worked fewer than 40 hours in week four
Paragraph of explanations

While APA style is not required for the body of this assignment, solid academic writing is expected, and documentation of sources should be presented using APA formatting guidelines, which can be found in the APA Style Guide, located in the Student Success Center.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.