FME 2013-SP2: Excelsior!: By Mark Ireland

Hi FME’ers,
FME 2013-SP2 has been released! It should be about build 13499 meaning there have been 50 builds since SP1 was released; 50 builds that include a whole bunch of updates. Usually by now we’re mostly adding just bug fixes and minor updates – but for 2013 we’re actually adding some new functionality that we believe you shouldn’t have to wait until 2014 for.

So, here’s my rundown on the top 10 new items in FME 2013-SP2.

OK, I wanted to show 10 new things, and I only managed to whittle the list down to 22. So I’m merging some items together (and dropping a few others).

If you want to just see the REALLY big-ticket items, then simply skip down to the top 5, all of which will be worth your time to examine. Likewise, if you are a full-on FME geek who wants to see the full list, then check out the What’s New file on – just be sure to finish reading here first!

10) Most-Needed Fixes
Here we have the most-needed updates; i.e. updates that have the most user requests against them. I list them here because if this many users wanted these fixed I’m guessing others may find them useful too.

– Fixed problems connecting to the FME License Server via a VPN (6 cases)
– Added a new Postgres/PostGIS reader parameter to control what tables are shown in the table picker (7 cases)
– Added support for Civil3D 2013 plus support for pipe and structure entities (12 cases)

Incidentally, this just goes to show you should always let us know of any suggestions or issues – the more requests we get, the more likely we are to do something!

9) New Transformers
SP2 has a couple of new transformers of note: the AttributePivoter and the JSONUpdater.

The JSONUpdater – as you can probably guess – is the JSON equivalent to the XMLUpdater. These updater transformers are great for applying changes to an existing snippet of JSON/XML without having to convert them to an FME (GIS) relational data structure.

The AttributePivoter is going to be less familiar to you and I have to play with it some more to fully understand the capabilities. It’s all about what we’re calling Data Analytics. Pivot tables generally involve the grouping and summing or counting of records in a group.

What I tried this on was election results in a spreadsheet with this format:

Poll Number, Name, Votes
1, Mark, 346
1, Dale, 321
1, Don, 412
2, Mark, 400
2, Dale, 402
2, Don, 232

The AttributePivoter lets me group that data by ROW, so I can group by Poll Number to start with and find out how many votes were cast in any particular poll. Then I can start grouping by COLUMN too, to create the idea of a pivot (how many votes for which candidate in a poll).

I’m going to have to play with this some more, but I think I can see the potential uses that are now available.

8) Performance
Just one item of note here. From the What’s New file:

Spatial Index: Improved spatial indexing performance in the FFS writer, and the Recorder and Inspector transformers. This reduces memory use and allows greater data volumes.

Why this is important is because the FFS writer gets used all over FME, particularly when sending data from Workbench to a Data Inspector. With all the talk about “Big Data” recently, this helps FME handle datasets larger than ever before.

7) Database Format Stuff
These are items of the type I find hard to categorize. They aren’t really major… unless you happen to be one of the users who will need them! Then they are of great interest.

Netezza: There are various improvements here. If you’re unaware, Netezza produces data warehousing hardware and software, and I’d say is at the “Big Data” end of the market. For SP2 we’ve added support on x64 for Netezza 6 and 7, but have also added writing support for their new spatial database – an Esri-flavour of Netezza that handles spatial data SDE-style (reading was introduced in 2013-SP1). So if you want to get spatial data into and out of Netezza, FME is the tool for you!

MariaDB: This is interesting. We had to pull MySQL support out of FME recently, for non-technical reasons. So we’ve instead implemented a new Reader/Writer for MariaDB (on 32-bit Windows, 64-bit Windows, and 64-bit Linux). Apparently MariaDB is a “drop-in replacement for MySQL” and so if you were pained at MySQL’s removal, you should be able to use this reader and writer with no visible difference to your workflow.

6) CSV Updates
There have been some interesting updates for CSV, including a change in methodology.

The Parameters dialog now lets you edit the source schema (attribute types) so FME knows what sort of values to expect in the fields. This is important especially when you only scan the first few rows (perhaps you have a huge dataset).

Secondly, we’ve added the format attribute csv_total_number_of_columns to indicate the total number of columns in a row. This compliments the existing csv_number_of_fields format attribute which indicates the number of non-empty values in a row

Finally, the CSV reader has been made more like the Text File reader, in that you only get a single feature type regardless of how many files you select. You could think of this as being automatically dynamic. If you do want individual types you can control this using the new parameter “Group By Filename”.

5) FeatureMerger
I love this update, and we have at least 7 cases that show users will agree.

This transformer is a way to merge or join data values and sometimes a join is made by multiple keys; for example if you are trying to match an address you might need to match: Number, Street, City. The new FeatureMerger lets you use all of those as a match. Previously you could only use one. The common workaround was to concatenate Number+Street+City into one and use that. Now you don’t need to.

As an additional bonus, the whole parameters dialog has gotten a nice redesign too:

4) AttributeCreator Updates
OK, this might not be something you’ll use all the time, but it will be fairly obvious in the AttributeCreator dialog, so you’ll need to be aware of this. In brief, we’ve added “multi-feature” functionality. This allows each feature to access attributes from prior/subsequent features in the workflow.

See, in 2012 we added the ability to construct strings within our “Integrated GUI“. That would look something like this:

So for any feature you could manipulate its attributes to construct a string or calculate a value. Now, in 2013-SP2, you can do the same thing, but using the attributes of other features; ones that have already passed through the transformer or are yet to come.

For example, I have a spreadsheet that counts rainfall for a given location;


You can see that these numbers are actually the cumulative value for the year. So, if I want to figure out the rainfall per month I must subtract the previous value from the current value, and I can do this very nicely now with the new AttributeCreator. Here I activate and set up the multi-feature functionality:

Notice how I specify what to happen with a null value (that’s because the first record won’t have a prior value). Now I can set my actual mathematical expression:

The actual expression is this:

(@Value(feature[0].Cumulative Rainfall))-(@Value(feature[-1].Cumulative Rainfall))

Notice how we have a sort of array here, so I am using feature (0) – feature (-1). I’m using the Arithmetic Editor dialog here (because this is a numeric calculation) but the same multi-feature functionality will work in the Text/String Editor as well. The result of my workspace is this:

…which is just what I wanted.

3) Dynamic
I think this is going to be BIG! I just won’t go into detail here because this isn’t that type of article. I’ll just say that dynamic schemas can now be very dynamic, as you can set them using incoming features.

So, instead of having to point a dynamic writer to an existing schema, you can create that schema dynamically within the workspace. You basically create a bunch of attributes to describe the schema. That gives you a lot of power there, and solves some scenarios I don’t think we could have supported before.

But what’s more, FME seems to create some of these schemas automatically. As an example, look at the output from the AttributePivoter I mentioned earlier:

Can you see that a schema has been created? If you have a dynamic writer and send this data to it then the output will be in this schema. Of course, it makes sense here because the AttributePivoter will create data in a way that we are less likely to know the schema in advance; therefore this dynamic-ness is a great match for that transformer. But in future this may be applied to similar transformers too.

I look forward to seeing what other uses you FME’ers put this to! For more information check out FMEpedia. My colleague, Iris, is writing an article about this functionality and it should be there shortly (if it’s not already).

2) Background Maps
Here’s one that’s more simple to explain: the Data Inspector now has the ability to show background maps. Simply choose Tools > FME Options in the Data Inspector to find the settings for this:

The maps can come from ArcGIS Online (needs an account of some sort) or Stamen Maps, which I believe is derived from OpenStreetMap data. The other possibility is to use an FME Format as the source of background data. For example, you could point to any set of raster data to be a backdrop, or you could use a vector dataset, or even an FME Custom Format!

The nice part is that FME handles any reprojection dynamically, so the data being inspected doesn’t need to be in the same coordinate system as the background data.

1) Excel R/W
Excelsior! We now have a new Excel Reader and Writer, and it’s smooth. It’s like Barry White compared to the old Reader/Writer’s Green Day; that level of smooth!

Here’s the Parameters Dialog:

Notice that you can now select which sheet(s) to read from the Excel file (top left). Also notice you can read a particular set of cells (top right) by specifying a range in the spreadsheet. Then there is the wonderful preview window and, finally, a representation of the schema for the sheet (bottom right).

In the schema dialog note that you can edit the field types to ensure FME interprets them correctly (sometimes it’s hard for FME to be sure whether a column is meant to be a string or a number) and there is also the option to set cells as being coordinates.

If you do this then the fields automatically get converted to geometry, without you having to use a 2DPointReplacer. Don is really happy about this because it saves him a transformer in his demo workspaces! But just remember to set a coordinate system on any data you create this way, if you want it to appear with a background map in the Data Inspector.

The new Excel Writer is similarly advanced. It allows you to update cells, rather than just overwrite everything. In fact, there are five different writer modes:

So you can choose to add data as a new row, or overwrite the existing rows. You can also update individual cells, delete cells, or even delete entire rows. Incidentally, this functionality you will find in the Feature Type properties dialog, under the Parameters tab.

I believe we support the fme_db_operation format attribute (the where has to be a row number, not a cell ID) and we also support “named ranges” in Excel (reading and writing). You may also set colours, fonts, fill, etc on the output with the writer.

Notice how you access these – through the actual attribute definitions. I imagine we do it like this because you wouldn’t want to set format attributes for every individual feature/cell. In this dialog you can set the format of a cell to be simple (e.g. red font, yellow fill) or can be more complex by using a custom definition. For example this custom string:


…means colour positive numbers green, negative numbers red, zero is magenta, and strings are blue. The syntax for these strings is defined by Excel (it’s not an FME construction) and you can find more information in the Excel Help system.

So, there we are. That’s FME2013-SP2. You can also see a movie showing the updates on our YouTube channel. And – of course – when you want to download this new version you can get it from the Downloads Page on our web site.


About FME Attributes Autodesk CAD Civil3D CSV Data Analysis Data Transformation Data Validation Databases Dynamic Schema Events Excel FME Desktop GIS JSON MariaDB Netezza Performance PostGIS PostgreSQL Usability VPN XML

Mark Ireland

Mark, aka iMark, is the FME Evangelist (est. 2004) and has a passion for FME Training. He likes being able to help people understand and use technology in new and interesting ways. One of his other passions is football (aka. Soccer). He likes both technology and soccer so much that he wrote an article about the two together! Who would’ve thought? (Answer: iMark)

Related Posts