Column from SQL is not showing in Spotfire

[djones]

I'm connecting to Enverus, which just updated their backend. Here's a screenshot from the new data source:

Notice that FirstProdDate (as well as FirstDay) do not show a data type. These columns do not appear in Spotfire:

UNLESS I do a custom SQL query. Query here is just: select top 1000 * from foundations_wells.

 

Any idea why this is?

[David Boot-Olazabal]

Hi djones,

Looking at your screenshots, you have a direct connection to this data source.
In that case, you can refresh the schema to populate your table with the newly added columns.

To do so, go to the menu Data --> Data Connection properties

You then select the proper connection and click on Settings:

When you have the next window in front of you, the Data Connection settings, click on the Refresh Schema button.
This will add the newly added columns to your data source. Click on OK and you will see a new window popping up refreshing the table columns. Once everything has been 'refreshed', you should be able to see the columns in your data table.

Kind regards,

David

[djones]

Hey @David Boot-Olazabal - thanks for the response! So these aren't actually new columns. They're columns that seem to have a data type change. I've tried replacing/refreshing the schema and the columns still do not show up unfortunately.

[David Boot-Olazabal]

Hi djones,

That is strange. A refresh schema should the trick.

Could it have something to do with the 2 columns not having any data type (as presented in the screenshot from SQL Server)? Do you have another new column with data type that you test this hypothesis with?

Kind regards,

David

[djones]

Every other column that has a data type is brought in correctly. The odd thing is that if I change from the direct connect to a custom query, the column is available and is brought in correctly.

Notice how the custom query allows me to bring in the DateTime column "FirstProdDate", but that column is missing in the direct connect. The issue is I'm having to replace every table with custom queries now, and when I do that, the "replace data" option is not saving all my calculated columns/transformations that occur afterward. It's just causing a mess unfortunately. Getting there, but I'd love to be able to just replace the connection and schema and have it work since the column names are all the same.

[barchiel33]

I'm not an expert on the data connection interface due to the company I work with blocking it, but I have some ideas.

1. When you do the custom query, you only pull in 1000 rows from the table, right?
   1. Could it, potentially, be that there are some entries outside of those rows which contain data that invalidates the data type?
   2. For example, could there be a FirstProdDate that has a string or integer value? 
2. Have you used these tables via the direct connection in other reports or in multiple imports in the same report?
   1. Maybe the connection is unable to properly update its schema if it's still being used somewhere else?

[djones]

Hey @barchiel33 - thanks for the response. I'm actually not limiting to top 1000, I'm basically just doing select *. The data types coming in from Enverus are all consistent and correct. Tableau has no problem ingesting all columns with their data type... Wish Spotfire could as well. 

I've not used these connections in other reports. 

 

Thanks!

[David Boot-Olazabal]

Hi djones,

Not sure which version of Spotfire you use, but this should normally work as expected.
It may be possible that your query has run into something which is blocking the normal behavior of refreshing the schema. So, you could try this out in a new analysis file and see if the same behavior applies or that it could be considered as an isolated incident.

I would encourage you to create a support ticket for this via https://support.tibco.com though. As you said, it's not a viable solution to replace each data table with a custom query and if this happens more often, our support team is capable of digging deeper into it, by checking the log files, the code, etc.

Kind regards,

David

[djones]

I've tried it in several new files just to make sure. It's definitely an issue with Spotfire at this point. The custom query brings all the columns in, but the direct connect does not. I think natively, the datetime data type is actually a timestamp? Enverus told me: "The new backend is essentially a postgres database masquerading as a MS SQL server." I think this is what's causing the issues. Just interesting that Tableau can still ingest the data without issues, and the custom query works as well. It's only direct connect, which unfortunately is what I'm using. Additionally, with custom queries, people using this dashboard are having to verify each custom query for it to be trusted - even though I verify when bringing it in. Just a really clunky solution. I know a lot of people on Spotfire work in Oil & Gas and use Enverus as well. I'm curious if anyone else has a solution for this.

[David Boot-Olazabal]

Hi djones,

I would suggest to create a support ticket for this: https://support.tibco.com. There may be something you have to do, since it's PostgreSQL masquerading as SQL Server.

Kind regards,

David