Final Transformation and Table Writing. Version 4.6.6.6.


The current scenario entails two distinct data transformation processes: one originating from Excel, containing customer, city, state, and segment data, and the other from CSV, encompassing state and region information. The objective is to merge these two transformations based on the state code to obtain all the necessary columns for writing to the customer table in the Data Warehouse.


To achieve this, the user initiates the merging process by selecting the transformation originating from the Excel spreadsheet, containing customer data, as the base transformation. Within the Transformations tab, a join operation is performed to combine the results from the Excel and CSV sources.


The user selects the transformation responsible for fetching state and region data from the CSV source. In the join parameters, the state code (Codestado) from the Excel spreadsheet is matched with the state code (Codestate) from the CSV source to establish the association between the two datasets.


Upon previewing the results, it becomes apparent that there are duplicate columns present, specifically R-Codestate and L-Codestate. To address this, one of the duplicate columns, L-Codestate, is removed using the Delete Columns transformation.


With the duplicate column removed, the next step involves standardizing column names, data types, and sizes to ensure compatibility with the destination table in the Data Warehouse. The Alias Column transformation is utilized to rename columns and specify their respective data types and sizes.


Additionally, the primary key column is identified and designated as such in the Metadata tab. The appropriate data types and sizes are assigned to each column, and the primary key is specified.


Once these modifications are applied, the user proceeds to create an Automatic Box to facilitate the data transfer process. Within the Automatic Box settings, the user specifies the source (Excel) and destination (SQL Server) databases, ensuring alignment with the intended data flow.


Upon configuring the Automatic Box, the user proceeds to execute the transformation, resulting in the successful transfer of data from the Excel and CSV sources to the target table in the Data Warehouse. The completion of this process is verified through the Thread Monitor, confirming the successful loading of customer dimension data into the target table.


This meticulous approach ensures the seamless integration of disparate data sources, ultimately contributing to enhanced data management and analysis capabilities within the Data Warehouse environment.