Alteryx Gallery loader - Wrong Schema in DB relationships

[HarryM123]

Hey there,

I am having issues with the Alteryx Gallery loader whereby the DB relationships associated with the workflows have some incorrect schema names. In Alteryx Connect and H2 they show up with a schema name which does not exist on the DB.

It looks as though the Schema name is set to the user ID used in the ODBC connection in the file input rather than the associated tables schema.

Upon some investigation it seems it may be due to the SQL used in the file input tools to call the table which do not specify the Schema, only the Table name. For example:

SQL1

Select * from SCHEMA1.TABLE1 : This will give the correct schema in the DB relationship table

SQL 2

Select * from TABLE1 : This will give the username as the Schema, it doesn't seem to identify the correct Schema for the table and just reverts to username.

Is someone able to shed some light on how the Alteryx Gallery loader functions in terms of extracting DB relationship information ?

I am not 100% sure if the above is correct though it is an observation from a few workflows i have investigated. If this is the way in which it pulls the schema then i may need to build a workaround as this effects a large amount of flows.

Any ideas would be welcome, cheers,

Harry

[PetrH]

Hi @HarryM123 , 

In Alteryx loader while we parsing the input and output tools and looking for the lineage, we need to utilize the information stored in tool itself. So if such information such as schema is not directly present, we might need to guess or keep it empty. 
However if we search for the lineage, we might succeed, also while some of fragments (server name/database name / schema name) are missing. 

So I suggest to continue with use case, as I believe you are trying to establish lineage, and load also database content. If you don't se linage established, let us know once again, and we will try to help on your specific use case and go deeper reported issue.

Best regards,

Petr / Alteryx Connect team

[HarryM123]

Hey @PetrH ,

Thanks for the response,

In my use case i am running the Oracle loader 1st as the DB where the Gallery workflows are extracting their data from. After this the Gallery loader will be ran to establish lineage to these tables (without checking the load DB content option).

In order to get the correct DB information I was thinking of running a check on the relationships in the gallery loader to isolate the rows where it has given an incorrect schema, then cross reference the table names in this list with the DB names from H2 to give the correct schema and relationship values to the affected rows.

I assume the above will work as a workaround since it is unfeasible to go in and edit the file inputs for all the affected workflows.

Do you think this would be a suitable solution?

Cheers,

Harry

[PetrH]

Hi @HarryM123 , If I understand well, in some cases the lineage is not established automatically, and you plan some workaround in loader, am I right?

For the lineage discovery its better to have nothing, that incorrect value, if we don't have the right value.
This is a bit high level for me to judge, I would need to see the real objects in database, and how we parsed it in Connect. Also the exact input tools with sql might help to understand the middle part (parsing)
But at high level, your solution sounds like good plan for time being.