cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
exs_ruud
New Member

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

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')

 

1 ACCEPTED SOLUTION
v-yuezhe-msft
Microsoft
Microsoft

@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?
1.JPG


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
marimar
Frequent Visitor

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
Microsoft
Microsoft

@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?
1.JPG


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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 🙂

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

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

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors