Native Query in Analysis Services with ODBC

MARTIN PEREZ Miguel 41

Hi,

We are trying to import data by using a native query in Visual Studio ; we use on our side ODBC for our Teradata servers
Problem is when we try to create the expression, we receive a popup saying that "native queries aren't supported by this value"

https://learn.microsoft.com/en-us/analysis-services/tabular-models/ssas-import-query?view=asallproducts-allversions

Does the Value.NativeQuery don't work with the ODBC ?

Thanks for your advice

Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2022-10-19T18:28:57.297+00:00

Hello @MARTINPEREZMiguel-8928 ,

Welcome to the MS Q&A platform.

In general ODBC allows using native queries.

After adding the data source, I was able to query using nativequery in the power query editor(express editor). Can you please share more details about the query that you are using in the expression editor?
MARTIN PEREZ Miguel 41 Reputation points

2022-10-20T18:39:54.407+00:00

Hello , I think we are not using the same wording :)
in our case we use the ODBC and define a DSN with some settings to access our cloudera and teradata. We don't use directly the driver to access our data;
That's right if we use directly the driver it works as in your example. But that's not our normal way of working for those two data sources.
When we do a "get data", we select ODBC and then DSN.
It's when doing as in my example, we face the "issue"
Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2022-10-21T01:07:58.827+00:00

Hello @MARTINPEREZMiguel-8928 ,
Thank you for the reply.
Please correct me if my understanding is wrong. On the DSN drop-down, you have selected Cloudera or Teradata and connected to the Data Sources using the connection string.
When you try to build an expression using the blank query (via native query), you see the error message, "native queries aren't supported by this value"

If this is correct, I will check with my internal team and get back to you with more details.
MARTIN PEREZ Miguel 41 Reputation points

2022-10-21T07:46:28.663+00:00

That's right. Here is more details that can help your colleague to reproduce the problem

. We have today Visual Studio 2019 (2022 foresee for next year) and the latest powerbi desktop. To access our core-it datasource (teradata and cloudera) we use DSN define in the ODBC; the 32 and 64 bits are set with the same settings (as Visual Studio is a 32 bits app)

User create an AAS tabular model, do a GET DATA in Visual Studio in order to create the datasource by selecting “ODBC”

And select one the “yellow” one. Usually the BIPDEV (which is set to use the teradata driver); the user enter his/her uid/pwd
Once done the connection is created with the following name :

Now if I want to create an expression,

The error is not linked to the name of the connection (I’ve rename it to see if there is a change but same error message)

Thanks for your support , really appreciate
Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2022-10-21T19:41:43.777+00:00

Hello @MARTINPEREZMiguel-8928 ,
Thank you so much for the details. I will check with my internal team and get back to you.
MARTIN PEREZ Miguel 41 Reputation points

2022-10-24T08:51:37.793+00:00

Hello , yes we are aware of this; the problem is we've notice terrible performance issue when using odbc.query in comparison with Value.NativeQuery

But thanks, you've confirm us the Value.NativeQuery does not work with DSN/ODBC (which was the question ^^)

Miguel
Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2022-10-24T14:10:08.003+00:00

thank you, @MARTINPEREZMiguel-8928

Accepted answer

Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2022-10-21T23:57:14.733+00:00
Hello @MARTINPEREZMiguel-8928 ,

I got a confirmation from the product team that Value.NativeQuery doesn't support ODBC data sources. But ODBC has a special function for native query,

Odbc.Query(connectionString as any, query as text, optional options as nullable record) as table Example: Odbc.Query("dsn=your_dsn", "select * from Customers") output: table

Reference document:
https://learn.microsoft.com/en-us/powerquery-m/odbc-query

------------------------------

Please don't forget to click on and upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

Want a reminder to come back and check responses? Here is how to subscribe to a notification

If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Native Query in Analysis Services with ODBC

0 additional answers

Your answer