In this tutorial we will use FME to prepare data for Tableau.
More specifically, we will perform some data validation, manipulation and cleanup on business
license data in CSV format before loading it to Tableau.
We will start by creating a simple translation, look at the results in Tableau, then come
back to FME to do the data validation and cleanup.
Let's start FME Workbench and select the New option under Create Workspace.
The first step is to read the CSV file.
Drag the business license CSV file from the file explorer onto the blank canvas.
Notice, that FME has already filled in the reader format and dataset.
We can tell FME to convert the CSV file latitude and longitude values to points AS
it reads the data.
To do this, click on the Parameters button.
First double-check that the File Preview is correct, then under Schema Attributes,
scroll down to the Latitude and Longitude columns.
Change their Type to y_coordinate and x_coordinate respectively.
This tells fme to create a point for each record with a latitude and longitude value.
Finally, the Database Parameter allows us to choose different naming schemes for
the layers, or feature types, that end up on the canvas.
Make sure it is set to "Feature Type from File Names".
When the CSV source data is added to the canvas, click on the arrow to see the full
attribute list.
To confirm points are created, as the CSV file is read, right-click on the source, and
select Connect Inspector.
Go ahead and run the workspace.
Data is read and directed to the FME Data Inspector.
We can see that we do, in fact, have points.
Let's add a Tableau writer.
From the Writers menu, select Add Writer.
For format, start typing Tableau, and select the Tableau Data Extract format.
For Dataset, choose a directory in which to write the .tde file.
Click OK to add the Tableau writer to the workspace.
We can now specify the name of the table we'd like to write to.
Call it BusinessLicenses.
Set the geometry to tde_point.
Go ahead and run the workspace.
You may have noticed a number of blue warnings go by in the log file.
These are related to problems with the CSV data, which We will soon fix
with FME.
For now, let's take a look at the output Tableau file.
At this stage, here is what the TDE file we have created looks like in Tableau.
We can see that all of the columns here correspond to the columns we read in from the CSV file.
Notice that the data types on the columns have all been set automatically by FME based
on it's best guess at what kind of data is inside each one.
We can see that the LicenseRSN has correctly been set to a Number type and BusinessName
is a String.
Let's take a closer look at the business license data and discuss some of our upcoming
tasks with FME.
The data is in a CSV file; viewing it in Excel, we can see that there are a number of fields.
Notice that some of the records don't contain any location information - we will
want to filter those out first.
Since Tableau can use postal code information to automatically map areas, we will FIRST
want to filter out records that don't have a PostalCode value, and THEN we will extract
the first 3 characters, for Tableau to make use of.
We will set up a conditional value for the FeePaid field - checking if there are
empty values and, if so, we will set the value to 0.
Finally, We will create a new attribute called BusinessDisplayName, and populate it
with the value of BusinessTradeName (if it exists) - if it does not exist, we will use
BusinessName
For reference, here is a list of tasks We will accomplish with FME.
We have already completed the first one.?
Let's get back to our workspace.
Now, let's perform some data validation.
You may recall that some of the records in the CSV file did not have latitude and longitude
values.
Since FME would not have been able to create points for them, we want to filter them out;
a GeometryFilter will help us accomplish this.
In the Tranformers Gallery, expand the All folder, and find the GeometryFilter.
Drag it (or double-click on it) to add it to the canvas and connect it to the CSV
source.
Open the properties of the GeometryFilter.
Select Point for the Geometry Types to Filter.
Click OK.
Go ahead and run the workspace, making sure that the Run with Full Inspection option is
selected.
We can confirm that 731, out of our 10,000 records, did not have latitude longitude values.
Before we continue processing the points, let's write these records out to a Data
Cleanup file, so that they may be fixed.
We will write them out to Excel.
Add an Excel writer.
Enter the name of a file to write to.
Connect the new writer feature type up to the unfiltered port, then open up it's
properties and change the sheet name to Missing Latitude Longitude.
Now that we have dealt with the missing values, let's continue processing the points.?
Now we will perform attribute validation on the PostalCode values.
Place an AttributeValidator on the canvas and connect it to the GeometryFilter.
Open it's properties and select PostalCode for the Attribute to Validate.
The validation rule is that PostalCode MUST have a value.
While we are here, we will also validate that some of the string fields have string
values, and that the numeric fields have numeric values.
Again, before continuing to process the valid data, let's write the data that fails validation
out to a different sheet in the Data Cleanup excel file we've already set up.
Right-click on the canvas and select Insert Writer Feature Type.
Call the new sheet Failed Validation.
Connect it to the Failed port of the AttributeValidator.
Now that we know that the records output from the AttributeValidator all have a value
for Postal Code, let's extract the first 3 characters.
We do this because Tableau uses the first 3 characters of the postal code to automatically
map the areas.
Place a SubstringExtractor on the canvas, and configure it to extract the first 3 characters
from the PostalCodeAttribute.
We will call the resulting attribute PostCodeTrimmed.
The AttributeManager is a transformer that allows us to do many attribute manipulations,
including setting up conditional values.
So, if the FeePaid attribute is empty, set the value to 0, otherwise leave it as is.
Create a new attribute called BusinessDisplayName.
Set it's value to BusinessTradeName, but only if BusinessTradeName has a value,
otherwise set it to BusinessName.
Connect the AttributeManager output to the Writer feature type.
Notice that the attributes we removed just now are still on the output schema, and have
turned red.
The original attribute schema was a copy of the source schema.
It has changed, as a result of our data transformation, but we may update it to reflect
what we have done.
Open the properties of the destination feature type, go to the User Attributes tab, and click
on Automatic for Attribute Definition.
Now we see that the attribute schema reflects the changes we have made.
Since we want to overwrite the Tableau file we initially wrote, let's open the properties
again and change the Table Handling to Drop and Create.
Let's run our final workspace!
Click on the Run button.
Here is the output .tde file in Windows Explorer, and our DataCleanup excel file.
Let's open the extract file in Tableau and take a look at our final results.
In the Data Source view, we can see all of the records that we have imported from
the CSV file.
We can also see that the PostCodeTrimmed attribute, that we created within our workspace, is present
along with the other fields imported from the CSV file.
Now that our data is imported into Tableau, we can begin to create Data Views to explore
this data.
For example, we can create a simple map view to see the data points overlaid on a map.
Let's create a new sheet.
Double-click on latitude and longitude to add our coordinates.
Let's split those up based on their geometry id to see the individual points.
We can colour them by status to get an overall view of which businesses currently have active
licenses.
We could also make use of the postal code field to show our data.
Let's create a new view.
Add PostalCodeTrimmed, from Dimensions, and colour them- by the unique count of
business licenses in the area.
And there it is!
We have just used FME to prepare spreadsheet data for Tableau.
In the next video, we will modify the workspace we just built to process multiple CSV files.