ODBC (64-bit) not showing most recent data structure from TDV when source file is excel

[J_Sprengers]

Hello,

We are using Qlik Sense Enterprise. For our data connections we have set up a set of 64-bit ODBC connections to TDV (Tibco Data Virtualization) to access different data sources incl. excel files.

After recent changes on some excel files at source side, we notice that some new columns are missing and that a column that was removed, still appears in the Data Connections Data Preview/Metadata viewer.

When we do a SELECT * FROM, the correct columns will be used. 

This situations leads to a lot of confusion because our developers cannot trust on what they see from Data Connections perspective.

What do we need to do to make sure that the synchronization is always 100 % between what is shown within Qlik Sense & what is actually there as data structure? Within Qlik Sense? From ODBC connection definition?

Thanks in advance!

[diegozecchini]

Hi! 

In the In the Data Load Editor, add "REFRESH TABLE <table_name>;" before running SELECT * FROM, if TDV supports it

[J_Sprengers]

Hello Diegozecchini,

Many thanks for the suggestion. From a TDV perspective it will get translated to 

// Refresh the table before loading data TDV
SQL CALL SYSADMIN.refreshTable('schema', 'DATA_Table');

// Load data from the table into Qlik Sense
LOAD toto;
SQL SELECT *
FROM "schema"."DATA_Table";

// Exit the script
EXIT;

Unfortunately CALL SYSADMIN are not allowed from development perspective.

[diegozecchini]

Hi!
Even if CALL SYSADMIN.refreshTable is not allowed, you can force Qlik Sense to re-read metadata by using a dummy SQL statement before your SELECT statement.

Try adding:

// Force metadata refresh by creating a dummy alias
LOAD dummy_field;
SQL SELECT 1 AS dummy_field FROM "schema"."DATA_Table";
Then follow with:

// Now load actual data
LOAD toto;
SQL SELECT *
FROM "schema"."DATA_Table";
This forces Qlik to interact with the table and might help it recognize the updated schema.

[J_Sprengers]

Hi,

From a LOAD perspective there is no issue. 

The SELECT * performs what is expecting.

Issue is on the data/metadata previewer.

It looks like we need something like a "&clearcache=true" option in the data connection definition at QMC level

[diegozecchini]

Hi!
Sometimes, Qlik retains metadata in old connections, did you try creating a new ODBC connection with a different name and reloading the data model?

Or, try to change ODBC connection. In some systems it works, adding ClearCache=1 or MetadataRefresh=1 to the connection string may help (check TDV ODBC driver documentation for exact syntax).

[J_Sprengers · Accepted solution]

All,

Many thanks for providing suggestions. We have managed to solve the issue as result of advanced testing with our TDV team. It seems that although they can view the data structure, they need to perform a few additional steps + a re-introspect of the object. Then it seems that the 'caching' at TDV side is realigned.

Conclusion: Additional actions needed at TDV side, nothing at Qlik Sense side

[diegozecchini]

good to know, thanks for update!