Question of the Week: NEW Ask Me Anything with the FME Evangelist

Hi! If you could ask anything about dynamic workflows in FME, what would it be? 

Post your questions for the rest of this week and then Dan and I will create a video response and post it next week to answer as many questions as we can.

Amazing! Thanks Mark and Dan!

For me. Handling schemas has always been a challenge. From newer FeatureReaders where we have the option to “Add Schema Attributes to Features” to readers where we do not have that option. What is the best way to handle schemas + general port output and get them “back into the stream” so-to-speak without having to necessarily write all the data out to a temp feature? I just have never been able to think of it in an intuitive way (call it suffering from row and column brain :D).

I would like to ask how one could update dynamically user parameters in FME Form and Flow. No need to open up the workspace. 

I tried to combine a dynamic reader and writer with a fme_db_operation to update some topographic layers. I could not get the combination to work. In the end I had to create 3 dynamic readers with hardcoded change codes.

I also tried to edit this response, add a screenshot, attach a file to expand the question but all were rejected.

Can I double upvote this? I am trying to do this using scripted selection but progress is slow.
 

Thumbs up for the AMA format!

What is the most misunderstood concept regarding Dynamic workflows?

I currently have a workflow where I write from a series of staging tables in an SDE geodatabase to production tables in the same database. Each staging table has a different schema, but is identical to the schema for its corresponding production table. The tables are named identically with a different prefix.

What I would like to do is read features from both staging and production tables for each object type, run through a change detector and then send the edits to a geodatabase writer using fme_db_operation. The attribute that will be the update detection key is variable, but I think I could fix this with a scripted parameter. All other attributes and geometry should be checked, with the exception of a few attributes that are in every table (objectid, guid, jobid...).

The workspace will be run on FME server and I would like to choose which object type is going to be updated at runtime.

Hi ​@kimo! Would you mind trying this again and seeing if you encounter the same error? 

Still not working for edits. So here is a PNG image of my working workspace. TIFF images not accepted. I have a file geodatabase of changes with 20 featureclasses in 6 featuresets. I wanted to update an old set with the changes using one dynamic writer. But it won’t accept fme_db_operation, I have to replicate the writer and set the process for each one. Is this the best way? Should I use Featurewriters to enable me to catch failures?

Sample data for my topo update. All open source and shareable.

dropbox of data and workspaces

I wonder if it is be possible to create a dynamic excel reader and extract the attributes. 

What is your plan to get rid of the spam on the forum? Could FME help😅

I am imaging some AI generated art of FME Lizard fighting Spam Bots now.

FME is already used to scan accounts when they join. The community is supposed to have an inbuilt spam filter, though it doesn’t seem to help much. I think I’m going to have to run every new post through FME and try to spot spam ourselves. It’s not ideal, not least because I can only intervene after the item has been posted. 

Unsurprisingly, I do call our tool the suspicious lizard bot!

Thanks to everyone who submitted their questions! Find the video response here: 

• Ask Me Anything: Dynamic Workflows

You may want to use an MD5 hash (CRCCalculator) for comparing all attributes between your staging and production feature. However, you do not want to include the few fixed attributes (objectid etc) in the hash. Therefore I use the VariableSetter to "park" these few attributes (static names,.hence hardcoded) in a few variables, remove the attributes, CRCCalculator on all (I.e. without the removed) attributes. Retrieve the attributes immediately back again on the feature from their variables and do your change detection on the hash-attribute. (Indeed, it would be a nice enhancement for the CRCCalculator to be able to select what attributes to hash, or better, especially for dynamic workflows to have an All Attributes Except-option). Hope this helps the discussion 🙂

​@take_vf and ​@david.van.dalen I actually just had a similar need that I need to solve, I handed off the checksumming to the MSSQL database, and I passed the checksum into a ChangeDetector. For my need, I just need to find out if there is any change - if there is, then do not proceed.

First, query the “live” featureclass to get a list of attributes for checksumming:

SELECT
    string_agg(
        CASE
            WHEN DATA_TYPE = 'geometry' THEN 'SHAPE.STAsText()'
            ELSE COLUMN_NAME
        END
        , ','
    ) AS ChecksumCols
FROM
    myegdb.information_schema.columns
WHERE
    table_name = '$(featureclass)'
    AND NOT column_name IN (
        'OBJECTID', 'GDB_GEOMATTR_DATA', 'IDATUM', 'INLAST_AV', 'INLAST_DATUM',
        'NEDLADDAD_DATUM', 'NEDLADDAD_AV', 'GDB_FROM_DATE', 'GDB_TO_DATE'
    )

The pass this into two SqlExecutors, live/stage featureclass to retrieve checksums:

SELECT
    checksum(@Value(ChecksumCols)) AS ROW_CHECKSUM
FROM
    $(featureclass)

(if you need to find out which rows are changed, include e.g. globalid here)

Then feed into this into a changedetector (or featurejoiner) to match on the new ROW_CHECKSUM attribute.

One could even skip the ChangeDetector and do the comparison using CTEs and a join directly in the database...

continued...

To insert/update one could attach FeatureReaders/FeatureWriters to the ChangeDetector. Update is maybe trickier, but I guess one could just delete/insert.

For the advanced class: one could even skip the ChangeDetector and do the comparison using CTEs and a join directly in the database...

​@nordpil absolutely agree on the approach to offload work to your database. In my case I am also dealing with non-database data.

​@david.van.dalen for a dynamic workflow, I would do the checksum calculation in a PythonCaller, that way you can more easily specify the data to include - “all existing attributes + geometry, except [foo, bar]”

​@nordpil yes, you are right. But moving from no-code to Python is also a matter of taste. 🙂