Create a Data Mart

Submit: A zipped folder of the entire solution Scenario: XYZ Corp. is a parent corporation with 2 handbag stores located in – New Jersey and New York. XYZ needs to setup a system that will gather customer data from all of the different stores and put it into one place. Blindly copying data files from each store is not going to be good enough most of the time. The corporation needs to have a standardized set of data in order to analyze it. Task: You will need to do the following: Create a Data Mart – Create a table called: DimXYZCustomers (Create using: [login to view URL]) Build an SSIS solution to get the data from all 2 stores and load to a single data table Review/analyze the data from all sources. Source files are on Blackboard. Determine what needs to be standardized based on the requirements below. The data collected should be changed to a standard format. For instance, the state value should all be 2-character value such as NY, NJ etc. Extract data from all sources Source file 1: [login to view URL] Source file 2: [login to view URL] Transform the data as follows State – 2 character abbreviation First and Last names – Upper case Load to the a single data location - DimXYZCustomers Note: custAcct will hold the PK of the source tables, however, CustomerKey is the PK of this dimension (it will auto increment) Only good data should go to the database; bad data (assume: no account number) should go to an error log file Add annotation to your design space Be sure to add meaningful names to each object Project name: LastnameFirstname_Assignment2 HINT: In addition to the objects we are familiar with, use the transformation component (can be found in the Common toolbox) – Union All