With only a few steps and without writing any code, flat file data can be easily transformed and uploaded into BI reporting systems like Roambi using Talend Open Studio (Free).
At Calance, we utilize Talend Open Studio to the fullest for our clients. For this case, our automotive client was receiving data from a vendor called Polk. Polk provides sales data in flat file format that our automotive client wanted to use to generate interactive mobile reports using Roambi.
Below outlines the step-by-step process we took to migrate this data from flat file sources into Roambi using an extract transform and Talend Open Studio without writing ANY code.
ETL of Segment and Lease information
Talend Open Studio converts flat file data from Polk into a format that Roambi Analytics is expecting for a demo report that has already created for our automotive client as a proof of concept.
The report being generated in Roambi is for Segment and Lease information by month.
The workflow for this case use was as follows using Talend:
Flat file data from Polk:
March Polk Segment and Lease by District and Dealer.csv
Sample Polk file content:
Roambi file data to be transmitted to Roambi after Talend ETL:
LV - SLP_SAN_FRANCO_TOP_5_DATA_DUMP.XLS
Sample Roambi file content:
Talend Open Studio
Talend Open Studio can create “Jobs” using a designer and the Java programming language. In most cases, the end user will rarely need to write any Java code and can rely almost completely on the designer tools and component palette to do the required work.
Here is what the finished job will look like in Talend Open Studio:
Step 1 - Adding input files
On the Talend component palette tFileInputDelimited will be chosen for adding the Polk data to the job:
Drag and drop this onto the job designer and it looks like this:
The component view in the IDE will have all the settings you can change for this component:
Step 2 - Filtering and processing input
On the Talend component palette tFilterRow will be chosen for filtering data from our input file:
You drag and drop this onto the job designer then right click and drag from tFileInputDelimited to connect the input file to this new component:
The component view in the IDE will have all the settings you can change for this component, for this example we will filter by the month of April on the input file:
Step 4 - Adding output files
On the Talend component palette tFileOutputExcel will be chosen the output file format:
After connecting our Filtering component (right click/drag, select row and target) our job now looks like this:
Here are the settings for the Excel output file component:
Step 5 - Uploading new file to Roambi
Add the Roambi update component to the designer:
After connecting the Excel file output to the Roambi update component, our job design is complete:
Here are the Roambi update component settings (note: contents of Roambi account properties file not shown):
Schema
When adding a flat file to the flat file input component, some additional work will be necessary in order to map column names and types to the destination Excel file. On the Input file component properties, there is a “schema” button which will allow you the ability to add this information (if it is not already in the first line of the CSV file).
Clicking this button will display a popup window where you can easily edit this mapping information:
Later when you add the Excel output file component you can map this schema any way you wish to the destination Excel file sheet columns.
With only a few steps and without writing any code, flat file data can be easily transformed and uploaded into BI reporting systems like Roambi using Talend Open Studio (Free).
Need Help with Talend Open Studio?