This section in the picture is what i am needing.

MergerETL Memo DATE 9/5/2018 MEMORANDUM FOR Young Jolly, LLC Merger Project Com FROM SmartBuy SUBJECT Merger Project ETL Parameters Now that ther e are signed, we are providing more detailed guidance for them that will help with our Merger Project. This memo addresses the following topics Background Information Specife Files Provided Deliverables Background Information Recently, Largemart, Incorporated and Bullseye, Incorporated, two large, regional retailers, went through a merger. The newly formed company operates under the name Smart-Buy, Incorporated. SmartBuy. Inc is a large retailer with $11.SB USD in annual sales and 80.000 employees. Smart-Buy is in the process of merging its operations, Currently, Smart-Buy has retail stores on the West Coast, as well as in Plain and Southern Mates, but intends to expand into other regions. The company is in the process of determining where its new headquarters will be so Smart-Buy executives are splitting their time between Largemart's and Bullseye's headquarters in California and Oklahoma, respectively. The combined company has retails stores, transportation and distribution centers, as well as a few manufacturing plants. There were many factors weighed by the company executives and board of directors prior to merging the Two companies. One company had distribution centers and manufacturing plants that could prove to be advantageous to the combined company since there would be no need to ramp up new facilities or create facilities from green fields. And, the fact that Largemart already have retail stores in California, meant that the newly formed company would have a Human Resource and Legal Department already well versed in the strict workplace laws and regulations used in California. Additionally, with a significantly larger workforce and store locations, the executives saw that the new combined revenue and synergies between the two companies would be a powerful force to take into the marketplace Smart-Buy's workforce has full-time, part-time, and seasonal workers. During the holiday sales period, their levels generally peak since this is the time of year that Smart-Buy generates their highest sales Smart-Buy is in the process of changing its store banners (e.g. Largemart store and Bullseye store signage and related company logos) and implementing SAP as their enterprise resource planning (ERP) system. It has taken a considerable longer time to implement the new system as Smart-Buy works through taking on new best practices as it relates to their business processes and upgrading their technical infrastructure to support the larger company. In the meantime, financials, including payroll, are still being done in separate Say s . While this is cumbersome Smarty 's Chief Information Officer Cloth more per dhe new ERP systemple d as quickly as possible than to have do che currently wo m erim solution that will be the ERP was fully funcil Ass e sch unter and may sama.al press of combi n andoporting this process based earning tements and desin providing financial informatie We chose Young A Jolly (YJ LLC, a Big 6 Accounting Firm to help expedie r eporting tical that Y ompletes this task as expeditiously as possible, especially g o t all of the emplom a tic changes. A.YAJ Consultants, your team has been asked to tackle this The Managing Partner of the Ollahwa City practice specifically asked for your team to on the effort s of the outstanding results she won your last assignment and became the nows that you do not take shortcuts or wait until the last minute to get your work done. The Partner also gave your team specific instructions to get started on this s t w became Large amount of data, "This is more data than we typically work with the said. "This is Big Data! Specific Files Provided Smartly has provide you with sployee files. The employee files consist of demographic information. Each company provided files from scrate legacy systems. In addition, Smart-Buy has provided you with quarterly payroll files from fiscal year 2016 for each of the two companies Specifically, you should have the following files for each company: Addresses This file contains address information sorted by employee id. Both large-mart and Bulls Eye will provide these files to YoungJolly Inc. Any queries that need to understand the location of the employee by city, state or zipcode, should use this table as all employees have an address record. These files will need to be merged together. The address id will be the primary key for the merged file. Calendar This file contains information about the calendar utilized to calculate payroll Pay checks are cut for hourly employees on the land the 15 of each month. This means that the number of hours available to work in any given pay period must be calculated based on the number of week days in the pay period. The calendar file will provide the number of hours available for an employee to work in a pay period. Both companies will provide this file. If the files are exact matches, only one of these files are required for use in your new database. Usually only one of these files is necessary. This file will be useful for limiting the results by period, month or quarter. Departments The departments at both companies will have the same names: however, not both of the companies have all departments. For reporting and data integrity purposes, please merge the department files provided. The Departments from each convention, and therefore the department ID is a unique identifier of each each company uses a different naming any * a unique identifier of each row of data Employee Information Employee Files are provided by each company and we would like one Employee files of the merger. Not all employees are assigned to a store as those in support jobs. resources and accounting do not work for just one store. This means that there are this field over the data for Store ID for many employees. Pay attention to how access brings this as it is a foreign key and must be a number. For Querving anything that has to do with employee city or state, the address table should be used. The store information is useful to understanding how many employees are assigned to a store, etc. Another field to watch is the SSN, please ensure the SSN is brought over in a format that works for both companies. The employee_id uniquely identifies cach row in this table. Payroll Register Payroll information has been downloaded warterly for each company. This means that Carpe-Mart has S Payroll files and Bulls Eye has 5 Payroll files that need to be combined me one large payroll file (this may exceed 1.000.000) records. Each employee has multiple payroll records in this file. It is sorted by employee id, which repeats and cannot be used as a primary key. Stores This file includes information about the stores, including the address of the store and the number of customers in the store's immediate area (estimated population). The store address does not reflect where employees are located, the address table should be used for this. Store ID will uniquely identify cach row in the table. Revenue-Stores This file includes information about the Revenue brought in by each store. The Revenue is reported using the same calendar that is used to calculate payroll. Each store's revenue is reported by period, meaning that period cannot be used as a primary key and store id cannot be used as a primary key. Smart-Buy provided this picture of how the tables are related: addresses payrol_registry departments meno Stores Cena revenue stores Deliverables This memo specifies the requirements to extract, translate and that as the ETL Deliverable. the data into a datase. We refer to ETL Deliverable: For the purposes of insuring data integrity, we want to understand how you create the database against which you will eventually run your analysis. All the analysis should be run from the same database. So, we ask that you create a database and write a process memo to explain what you have done so that we can ensure the integrity of our data through the Extraction, Translation, and Load Process This is common requirement for data analysis by consultants or auditors. We have extracted the data, so no write up of the extraction process is necessary. Your memo (word document) should have the following sections Process In this section we want you to write the steps of creating your database in enough detail that should we need to recreate your work in an audit we are capable of such. A picture of your final tables is required. Translation In this section you will outline any formatting or changes to the data that occurred. Load In this section you will outline the final disposition of the database. This includes screen shots of sample data for each table, and total record counts against your tables. Start by describing the final structure of your database. Tell your audience what tables you now in the database. A screen shot of the access screens) to support this is required Technical Specifications: Given the size of the files, we are keeping them in a dropbox. You will be provided a link appropriate files through canvas. There is also an ETI Quy where you will verify that you your tables correctly by verifying your co u nts and answering a few simple database questo empts at this "Quiz". Both the wond document and the Oui are due at the same time. specified in Canvas. The Quiz -anvas. The "Quiz" is worth 10 points and the ETI write-up is worth 10 points for a tota 20 points for the ETL activities. ropbox. You will be provided a link to the ou will verify that you have loaded MergerAnalysis Memo 2018 Young Jolly, LLC Merger Project Com MEMORANDUM FOR FROM: SmartBuy SUBJECT Merger Project ETL Parameters Now that the contracts are signed, we are providing more detailed guidance for the team that will help with our Merger Project. This memo addresses the following topics Analysis Final Deliverable Analysis Given the new make-up of the organization Smart-Buy wants to understand some specifics of the new employee hase. Below are a few questions that will aid in that endeavor. 1. Several employees believe that their paychecks were calculated incorrectly. Management has asked that you produce a report that shows all employees hired after-2/15/2018 living in Oklahoma in the accounting department that made more than $55,000 in the 3 Quarter of 2018. Report the employee_id, last name, department name, total gross salary. This report should be in alphabetical order from A-Z 2. What were the top 75 seasonal employees that worked for Smart-Buy that worked more than 180 hours in December of 2018? Report the city and state in which they worked, their hourly rate, total number of hours worked, and marital status, in descending order based upon the number of hours worked. 3. Smart-Buy wants to understand if most of their seasonal workers worked more than 4 periods during the busy season (4 quarter). List (by employee id and last name) the number of periods worked and the total gross_pay for seasonal retail workers in the 4 quarter of 2017 and the 4 quarter of 2018. If the results of this query are more than 100 records, only report the first 10 in your write-up. 4. If smart-buy wants to relocate headquarters, where should they do this? Please use at least 2 queries to answer this question. Take into consideration where the employees that work jobs that would be associated with headquarters (for example: accountants, IT workers, executives, and possibly others) currently live. Also consider the location of our most lucrative stores. Figure out how to provide a heatmap of the location of the most lucrative stores. Deliverables Analysis Deliverable: For the purposes of insuring data integrity, we want to understand how you create the database against which you will eventually run your analysis. All the analysis should be run from the same database Turn in a final report in a word document. You will need to have at least 2 visualizations. One visualization is required for 4 and you can choose one other query to visualize. You may do your visualization in Excel or Tableau, but the picture must be included in the word document. This document must list the question and then the answer to the question mohou D SA ovolen yno Boston o no bashow contoh tromo2 Deliverables This memo specifies the requirements to extract, translate and load the data into a database. We refer to that as the ETL Deliverable. ETL Deliverable: For the purposes of insuring data integrity, we want to understand how you create the database against which you will eventually run your analysis. All the analysis should be run from the same database. So, we ask that you create a database and write a process memo to explain what you have done so that we can ensure the integrity of our data through the Extraction, Translation, and Load Process. This is common requirement for data analysis by consultants or auditors. We have extracted the data, so no write up of the extraction process is necessary. Your memo (word document) should have the following sections: Process In this section we want you to write the steps of creating your database in enough detail that should we need to recreate your work in an audit we are capable of such. A picture of your final tables is required. Translation In this section you will outline any formatting or changes to the data that occurred. Load In this section you will outline the final disposition of the database. This includes screen shots of sample data for each table, and total record counts against your tables. Start by MergerETL Memo DATE 9/5/2018 MEMORANDUM FOR Young Jolly, LLC Merger Project Com FROM SmartBuy SUBJECT Merger Project ETL Parameters Now that ther e are signed, we are providing more detailed guidance for them that will help with our Merger Project. This memo addresses the following topics Background Information Specife Files Provided Deliverables Background Information Recently, Largemart, Incorporated and Bullseye, Incorporated, two large, regional retailers, went through a merger. The newly formed company operates under the name Smart-Buy, Incorporated. SmartBuy. Inc is a large retailer with $11.SB USD in annual sales and 80.000 employees. Smart-Buy is in the process of merging its operations, Currently, Smart-Buy has retail stores on the West Coast, as well as in Plain and Southern Mates, but intends to expand into other regions. The company is in the process of determining where its new headquarters will be so Smart-Buy executives are splitting their time between Largemart's and Bullseye's headquarters in California and Oklahoma, respectively. The combined company has retails stores, transportation and distribution centers, as well as a few manufacturing plants. There were many factors weighed by the company executives and board of directors prior to merging the Two companies. One company had distribution centers and manufacturing plants that could prove to be advantageous to the combined company since there would be no need to ramp up new facilities or create facilities from green fields. And, the fact that Largemart already have retail stores in California, meant that the newly formed company would have a Human Resource and Legal Department already well versed in the strict workplace laws and regulations used in California. Additionally, with a significantly larger workforce and store locations, the executives saw that the new combined revenue and synergies between the two companies would be a powerful force to take into the marketplace Smart-Buy's workforce has full-time, part-time, and seasonal workers. During the holiday sales period, their levels generally peak since this is the time of year that Smart-Buy generates their highest sales Smart-Buy is in the process of changing its store banners (e.g. Largemart store and Bullseye store signage and related company logos) and implementing SAP as their enterprise resource planning (ERP) system. It has taken a considerable longer time to implement the new system as Smart-Buy works through taking on new best practices as it relates to their business processes and upgrading their technical infrastructure to support the larger company. In the meantime, financials, including payroll, are still being done in separate Say s . While this is cumbersome Smarty 's Chief Information Officer Cloth more per dhe new ERP systemple d as quickly as possible than to have do che currently wo m erim solution that will be the ERP was fully funcil Ass e sch unter and may sama.al press of combi n andoporting this process based earning tements and desin providing financial informatie We chose Young A Jolly (YJ LLC, a Big 6 Accounting Firm to help expedie r eporting tical that Y ompletes this task as expeditiously as possible, especially g o t all of the emplom a tic changes. A.YAJ Consultants, your team has been asked to tackle this The Managing Partner of the Ollahwa City practice specifically asked for your team to on the effort s of the outstanding results she won your last assignment and became the nows that you do not take shortcuts or wait until the last minute to get your work done. The Partner also gave your team specific instructions to get started on this s t w became Large amount of data, "This is more data than we typically work with the said. "This is Big Data! Specific Files Provided Smartly has provide you with sployee files. The employee files consist of demographic information. Each company provided files from scrate legacy systems. In addition, Smart-Buy has provided you with quarterly payroll files from fiscal year 2016 for each of the two companies Specifically, you should have the following files for each company: Addresses This file contains address information sorted by employee id. Both large-mart and Bulls Eye will provide these files to YoungJolly Inc. Any queries that need to understand the location of the employee by city, state or zipcode, should use this table as all employees have an address record. These files will need to be merged together. The address id will be the primary key for the merged file. Calendar This file contains information about the calendar utilized to calculate payroll Pay checks are cut for hourly employees on the land the 15 of each month. This means that the number of hours available to work in any given pay period must be calculated based on the number of week days in the pay period. The calendar file will provide the number of hours available for an employee to work in a pay period. Both companies will provide this file. If the files are exact matches, only one of these files are required for use in your new database. Usually only one of these files is necessary. This file will be useful for limiting the results by period, month or quarter. Departments The departments at both companies will have the same names: however, not both of the companies have all departments. For reporting and data integrity purposes, please merge the department files provided. The Departments from each convention, and therefore the department ID is a unique identifier of each each company uses a different naming any * a unique identifier of each row of data Employee Information Employee Files are provided by each company and we would like one Employee files of the merger. Not all employees are assigned to a store as those in support jobs. resources and accounting do not work for just one store. This means that there are this field over the data for Store ID for many employees. Pay attention to how access brings this as it is a foreign key and must be a number. For Querving anything that has to do with employee city or state, the address table should be used. The store information is useful to understanding how many employees are assigned to a store, etc. Another field to watch is the SSN, please ensure the SSN is brought over in a format that works for both companies. The employee_id uniquely identifies cach row in this table. Payroll Register Payroll information has been downloaded warterly for each company. This means that Carpe-Mart has S Payroll files and Bulls Eye has 5 Payroll files that need to be combined me one large payroll file (this may exceed 1.000.000) records. Each employee has multiple payroll records in this file. It is sorted by employee id, which repeats and cannot be used as a primary key. Stores This file includes information about the stores, including the address of the store and the number of customers in the store's immediate area (estimated population). The store address does not reflect where employees are located, the address table should be used for this. Store ID will uniquely identify cach row in the table. Revenue-Stores This file includes information about the Revenue brought in by each store. The Revenue is reported using the same calendar that is used to calculate payroll. Each store's revenue is reported by period, meaning that period cannot be used as a primary key and store id cannot be used as a primary key. Smart-Buy provided this picture of how the tables are related: addresses payrol_registry departments meno Stores Cena revenue stores Deliverables This memo specifies the requirements to extract, translate and that as the ETL Deliverable. the data into a datase. We refer to ETL Deliverable: For the purposes of insuring data integrity, we want to understand how you create the database against which you will eventually run your analysis. All the analysis should be run from the same database. So, we ask that you create a database and write a process memo to explain what you have done so that we can ensure the integrity of our data through the Extraction, Translation, and Load Process This is common requirement for data analysis by consultants or auditors. We have extracted the data, so no write up of the extraction process is necessary. Your memo (word document) should have the following sections Process In this section we want you to write the steps of creating your database in enough detail that should we need to recreate your work in an audit we are capable of such. A picture of your final tables is required. Translation In this section you will outline any formatting or changes to the data that occurred. Load In this section you will outline the final disposition of the database. This includes screen shots of sample data for each table, and total record counts against your tables. Start by describing the final structure of your database. Tell your audience what tables you now in the database. A screen shot of the access screens) to support this is required Technical Specifications: Given the size of the files, we are keeping them in a dropbox. You will be provided a link appropriate files through canvas. There is also an ETI Quy where you will verify that you your tables correctly by verifying your co u nts and answering a few simple database questo empts at this "Quiz". Both the wond document and the Oui are due at the same time. specified in Canvas. The Quiz -anvas. The "Quiz" is worth 10 points and the ETI write-up is worth 10 points for a tota 20 points for the ETL activities. ropbox. You will be provided a link to the ou will verify that you have loaded MergerAnalysis Memo 2018 Young Jolly, LLC Merger Project Com MEMORANDUM FOR FROM: SmartBuy SUBJECT Merger Project ETL Parameters Now that the contracts are signed, we are providing more detailed guidance for the team that will help with our Merger Project. This memo addresses the following topics Analysis Final Deliverable Analysis Given the new make-up of the organization Smart-Buy wants to understand some specifics of the new employee hase. Below are a few questions that will aid in that endeavor. 1. Several employees believe that their paychecks were calculated incorrectly. Management has asked that you produce a report that shows all employees hired after-2/15/2018 living in Oklahoma in the accounting department that made more than $55,000 in the 3 Quarter of 2018. Report the employee_id, last name, department name, total gross salary. This report should be in alphabetical order from A-Z 2. What were the top 75 seasonal employees that worked for Smart-Buy that worked more than 180 hours in December of 2018? Report the city and state in which they worked, their hourly rate, total number of hours worked, and marital status, in descending order based upon the number of hours worked. 3. Smart-Buy wants to understand if most of their seasonal workers worked more than 4 periods during the busy season (4 quarter). List (by employee id and last name) the number of periods worked and the total gross_pay for seasonal retail workers in the 4 quarter of 2017 and the 4 quarter of 2018. If the results of this query are more than 100 records, only report the first 10 in your write-up. 4. If smart-buy wants to relocate headquarters, where should they do this? Please use at least 2 queries to answer this question. Take into consideration where the employees that work jobs that would be associated with headquarters (for example: accountants, IT workers, executives, and possibly others) currently live. Also consider the location of our most lucrative stores. Figure out how to provide a heatmap of the location of the most lucrative stores. Deliverables Analysis Deliverable: For the purposes of insuring data integrity, we want to understand how you create the database against which you will eventually run your analysis. All the analysis should be run from the same database Turn in a final report in a word document. You will need to have at least 2 visualizations. One visualization is required for 4 and you can choose one other query to visualize. You may do your visualization in Excel or Tableau, but the picture must be included in the word document. This document must list the question and then the answer to the question mohou D SA ovolen yno Boston o no bashow contoh tromo2 Deliverables This memo specifies the requirements to extract, translate and load the data into a database. We refer to that as the ETL Deliverable. ETL Deliverable: For the purposes of insuring data integrity, we want to understand how you create the database against which you will eventually run your analysis. All the analysis should be run from the same database. So, we ask that you create a database and write a process memo to explain what you have done so that we can ensure the integrity of our data through the Extraction, Translation, and Load Process. This is common requirement for data analysis by consultants or auditors. We have extracted the data, so no write up of the extraction process is necessary. Your memo (word document) should have the following sections: Process In this section we want you to write the steps of creating your database in enough detail that should we need to recreate your work in an audit we are capable of such. A picture of your final tables is required. Translation In this section you will outline any formatting or changes to the data that occurred. Load In this section you will outline the final disposition of the database. This includes screen shots of sample data for each table, and total record counts against your tables. Start by