Question

Error writing to an SDE with relationship classes.


Here is the error that I get when the translation fails: "Unexpected execution path. Please contact Safe Software GeodatabaseRelationshipFeaturesPipeline::GeodatabaseRelationshipFeatures(SortFactory): A fatal error has occurred. Check the logfile above for details. Some error has occurred closing the pipeline factory, see previous warning None of the edits made during the edit session were saved."

I'm using the Data Interoperability Extension through ESRI (ArcGIS 10.4.1). I am new to spatial ETL processes, but I followed the FME tutorial on Writing to Relationship Classes so I believe that the workflow is set-up properly.

I haven't found much doc on this issue, so I'm reaching out to the experts for help on this.

Thanks!!


8 replies

Userlevel 3
Badge +13
Hi @tfinke.

 

What version of FME are you using (including the build number)?

 

Would you please share your full log file from running the translation? Would it be possible to share your workspace too? Thank you!

 

Hi Natalie.

I'm running Version: FME(R) 2016.0.1.2 (20160224 - Build 16177 - WIN32).

I was actually able to find a workaround that has allowed me to successfully write to the relationship classes. Here is an explanation: I believe that the way in which I set up the relationship classes for the output database was causing a disconnect in my workflow. The fme relationship ID's (geodb_rel_destination_oid & geodb_rel_origin_oid) were set up correctly so the feature-table relationships were good as long as the data was in the fme workspace, but I had the relationship classes set up for the output sde using GlobalID for Pk/Fk. Since the GlobalID gets auto populated when the data hits the sde, fme doesn't see these values. And when the data leaves fme the geodb_ attributes get dropped, and the sde has no way of knowing what features were related to what rows in the tables.

Solution: Deleted relationship classes in output sde and recreated using source data specific unique ID values for Pk/Fk fields to base relationship from. Since these values are retained through the translation, it ran successfully, and the relationships were written. Unfortunately, this means that each source database (there are multiple databases that this tool will be pulling data from) that I bring in to the ETL tool will need a separate relationship class since they each have their own unique IDs. Is there perhaps a better solution, possibly a way to assign GUIDs as Pk/Fk values within the FME workspace so there's consistency across all of these datasets?
Badge +6

Hi Natalie.

I'm running Version: FME(R) 2016.0.1.2 (20160224 - Build 16177 - WIN32).

I was actually able to find a workaround that has allowed me to successfully write to the relationship classes. Here is an explanation: I believe that the way in which I set up the relationship classes for the output database was causing a disconnect in my workflow. The fme relationship ID's (geodb_rel_destination_oid & geodb_rel_origin_oid) were set up correctly so the feature-table relationships were good as long as the data was in the fme workspace, but I had the relationship classes set up for the output sde using GlobalID for Pk/Fk. Since the GlobalID gets auto populated when the data hits the sde, fme doesn't see these values. And when the data leaves fme the geodb_ attributes get dropped, and the sde has no way of knowing what features were related to what rows in the tables.

Solution: Deleted relationship classes in output sde and recreated using source data specific unique ID values for Pk/Fk fields to base relationship from. Since these values are retained through the translation, it ran successfully, and the relationships were written. Unfortunately, this means that each source database (there are multiple databases that this tool will be pulling data from) that I bring in to the ETL tool will need a separate relationship class since they each have their own unique IDs. Is there perhaps a better solution, possibly a way to assign GUIDs as Pk/Fk values within the FME workspace so there's consistency across all of these datasets?
Hi @tfinke,

 

I just wanted to ask a few more questions to try and gain a greater understanding of the workflow you are using. As of now, you are migrating data into an ArcSDE database, when each feature is inserted into the ArcSDE a GlobalID is generated for the feature, is that correct? At first glance, I am unsure if there is a better solution. Is it possible you could upload your workspace and a small sample of your data(can use the recorder transformer to save data to an FFS file). If so, I could do some in-depth analysis and try to find a better solution for you. If you do provide the workspace and sample data, could you also provide an XML template of your output ArcSDE table(feature class+relationship class) - This would allow me to mimic your work environment.Thanks!

 

Hi Trent,

That is correct, the GlobalID is generated for each feature when inserted into the ArcSDE. Unfortunately, my organization will not allow sharing of this data.

In the FME tutorial "Working with Relationship Classes," there is a custom transformer (GUID Generator) and I'm wondering if this is what others are using to set up the relationship classes in their SDE databases (when there are no natural keys in the source datasets). Can you tell me if this is a common practice? Do you have the code for the GUIDGenerator transformer?

In the Working with Relationship Classes Tutorial Article, there is a section on Relationship Keys that states:

"In the geodatabase, the relationship keys do not have to be of type Object ID (integers). They could be a GUID for example. In this case, it's tempting to load the foreign key attributes in the relationship. You don't need to do this. You only need to set the values of geodb_rel_origin_oid & geodb_rel_destination_oid. The FME Geodb writer will populate the relationship feature class foreign keys.

How does the FME Geodb writer know what features are related if a GUID is used that is unique for every feature? A feature merger transformer would not work to set up the relationship because the GUID values won't match, right?

As you can see, I'm pretty confused about reading and writing relationship classes especially when there are no natural keys present in the data being read. Is there more documentation on how FME handles this?

Thank you!

Hi Trent,

That is correct, the GlobalID is generated for each feature when inserted into the ArcSDE. Unfortunately, my organization will not allow sharing of this data.

In the FME tutorial "Working with Relationship Classes," there is a custom transformer (GUID Generator) and I'm wondering if this is what others are using to set up the relationship classes in their SDE databases (when there are no natural keys in the source datasets). Can you tell me if this is a common practice? Do you have the code for the GUIDGenerator transformer?

In the Working with Relationship Classes Tutorial Article, there is a section on Relationship Keys that states:

"In the geodatabase, the relationship keys do not have to be of type Object ID (integers). They could be a GUID for example. In this case, it's tempting to load the foreign key attributes in the relationship. You don't need to do this. You only need to set the values of geodb_rel_origin_oid & geodb_rel_destination_oid. The FME Geodb writer will populate the relationship feature class foreign keys.

How does the FME Geodb writer know what features are related if a GUID is used that is unique for every feature? A feature merger transformer would not work to set up the relationship because the GUID values won't match, right?

As you can see, I'm pretty confused about reading and writing relationship classes especially when there are no natural keys present in the data being read. Is there more documentation on how FME handles this?

Thank you!

Facing the same problem.

 

Source: Feature class #1 in ArcSDE database using GlobalId as Pk/Fk in relationsship

 

Destination: Feature class #2 in ArcSDE database using GlobalId as Pk/Fk in relationsship

When merging features from #1 to #2, the features created in #2 receives a brand new GlobalId, thus losing the relationship.

Should I place my hope in http://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_ReadersWriters/geodatabase/GEODATABASE_SDE_writer.htm?Highlight=globalid

Or is there a solution using GlobalId as Pk/Fk?

Maybe I should set up a workbench visualizing the problem?

 

 

Badge +6

Facing the same problem.

 

Source: Feature class #1 in ArcSDE database using GlobalId as Pk/Fk in relationsship

 

Destination: Feature class #2 in ArcSDE database using GlobalId as Pk/Fk in relationsship

When merging features from #1 to #2, the features created in #2 receives a brand new GlobalId, thus losing the relationship.

Should I place my hope in http://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_ReadersWriters/geodatabase/GEODATABASE_SDE_writer.htm?Highlight=globalid

Or is there a solution using GlobalId as Pk/Fk?

Maybe I should set up a workbench visualizing the problem?

 

 

Hello @mapixse

The Preserve GlobalID setting I believe should be accomplishing what you are trying to do. I have done some testing where I was able to update/insert features and preserve the GlobalID. Are you able to mock up an example with a source dataset where you are seeing this behavior not be true?

 

 

Hello @mapixse

The Preserve GlobalID setting I believe should be accomplishing what you are trying to do. I have done some testing where I was able to update/insert features and preserve the GlobalID. Are you able to mock up an example with a source dataset where you are seeing this behavior not be true?

 

 

mergeglobalidbasedrelationship.zip

Unfortunately I only have access to FME 2018 at the moment, can't use "Preserve GlobalId" yet:)

 

I've set up one workspace using OBJECTID/ATTACHMENTID as geodb_oid, and one with GlobalID as geodb_oid (but I'm taking a wild guess the oid stands for OBJECTID).

 

The OBJECTID and GlobalId is non editable in the destination geodb.

mergeglobalidbasedrelationship.zip

Unfortunately I only have access to FME 2018 at the moment, can't use "Preserve GlobalId" yet:)

 

I've set up one workspace using OBJECTID/ATTACHMENTID as geodb_oid, and one with GlobalID as geodb_oid (but I'm taking a wild guess the oid stands for OBJECTID).

 

The OBJECTID and GlobalId is non editable in the destination geodb.

Just got FME 2019, solved all the problems

Reply