ODBC - tables with underscores ('_') are not imported. Source is Teiid VDB (Jboss datavirtualisation

[exs_ruud]

See same kind of issue here:

https://developer.jboss.org/thread/271911

When i load an ODBC source into PowerBI that contains a teiid source, tables or tables within a model (schema) that contains underscores are not being loaded into PowerBI:

Error: DataSource.Error: The table has no visible columns and cannot be queried.
Details:
    STATION_PIR

When i look into the issue above it should have to do something with simplified sql syntax. Is there a way to change this when importing a source? I'm using PowerBI desktop Version: 2.51.4885.581 32-bit (oktober 2017).

The teiid log shows me the following when importing the source into PowerBI:

10:12:23,861 DEBUG [org.teiid.ODBC] (New I/O worker #9) Modified Query: SELECT k.Name AS attname, convert(Position, short) AS attnum, TableName AS relname, SchemaName AS nspname, TableName AS relname FROM SYS.KeyColumns k WHERE  UCASE(SchemaName) LIKE UCASE('cedlenergiediefstal') AND UCASE(TableName) LIKE UCASE('STATION_NEDU_SJV_DAG_AGG') AND KeyType LIKE 'Primary' ORDER BY attnum
10:12:23,862 DEBUG [org.teiid.PROCESSOR] (Worker50_QueryProcessorQueue22968) Request Thread ihyPSAKin1kW.4 with state NEW
10:12:23,862 DEBUG [org.teiid.PROCESSOR] (Worker50_QueryProcessorQueue22968) ihyPSAKin1kW.4 Command has no cache hint and result set cache mode is not on.
10:12:23,862 DEBUG [org.teiid.PROCESSOR] (Worker50_QueryProcessorQueue22968) ihyPSAKin1kW.4 executing  SELECT k.Name AS attname, convert(Position, short) AS attnum, TableName AS relname, SchemaName AS nspname, TableName AS relname FROM SYS.KeyColumns k WHERE  UCASE(SchemaName) LIKE UCASE('cedlenergiediefstal') AND UCASE(TableName) LIKE UCASE('STATION_NEDU_SJV_DAG_AGG') AND KeyType LIKE 'Primary' ORDER BY attnum
10:12:23,863 DEBUG [org.teiid.PLANNER] (Worker50_QueryProcessorQueue22968) [LOW [Relational Planner] convert function not supported by source SYS - convert(k.Position, short) was not pushed]
10:12:23,863 DEBUG [org.teiid.PROCESSOR] (Worker50_QueryProcessorQueue22968) ProcessTree for ihyPSAKin1kW.4 SortNode(0) output=[k.Name AS attname, convert(k.Position, short) AS attnum, k.TableName AS relname, k.SchemaName AS nspname, k.TableName AS relname] [SORT] [attnum]
  ProjectNode(1) output=[k.Name AS attname, convert(k.Position, short) AS attnum, k.TableName AS relname, k.SchemaName AS nspname, k.TableName AS relname] [k.Name AS attname, convert(k.Position, short) AS attnum, k.TableName AS relname, k.SchemaName AS nspname, k.TableName AS relname]
    AccessNode(2) output=[k.VDBName, k.SchemaName, k.TableName, k.Name, k.KeyName, k.KeyType, k.RefKeyUID, k.UID, k.Position, k.OID] SELECT SYS.KeyColumns.Name, SYS.KeyColumns.Position, SYS.KeyColumns.TableName, SYS.KeyColumns.SchemaName FROM SYS.KeyColumns WHERE (UCASE(SYS.KeyColumns.SchemaName) = 'CEDLENERGIEDIEFSTAL') AND (UCASE(SYS.KeyColumns.TableName) LIKE 'STATION_NEDU_SJV_DAG_AGG') AND (SYS.KeyColumns.KeyType = 'Primary')

[marimar]

Hello,

I have a similar error!

The DSN, a file and tables inside the DSN, all contain '_'

Do you have any idea what can I do in this case?

Many thanx!

[v-yuezhe-msft · Accepted solution]

@exs_ruud,

I create a view which name contains underscores in SQL Server database, then create a ODBC data source for SQL Server and connect to the ODBC data source in Power BI Desktop.

However, in your scenario , the tables with underscores are not imported, I suspect the issue is related to the driver you use to connect to teiid source. What is the result when you add a SQL statement as shown in the following screenshot to connect to the problem table?



Regards,
Lydia

[exs_ruud]

Hi Lydia, when i run a select like in your example, it works, but when i use the Get Data wizard it does not. Thanks, this works for me as a workaround 🙂

[shredhat]

The issue with Teiid pg/ODBC metadata and tables with '_' in the name likely stems from the differing expectations of the LIKE escape charcter. PostgreSQL and many of it's clients assume their default escape character of '\' while Teiid assumes the ansi sql compliant no default escape character. That is an issue for metadata queries with predicates similar to "col like 'name\_with\_underscore'". There are both a session property and a server wide property to setting the default like escape character behavior - see https://teiid.gitbooks.io/documents/content/client-dev/ODBC_Support.html

[exs_ruud]

Hi shredhat,

Thanks for your reply. Do you know if there is an alternate way to connect to Teiid without having to use ODBC?

Is there a way to import the JDBC driver perhaps?

Regards,

Ruud