Direct Query and Native Query

[Anonymous]

Hi Experts,

I'm connecting to an Azure SQL Server database in Direct Query Mode:

Then, I'm creating SQL sentences to import the tables with Value.NativeQuery:

let
   Source = Value.NativeQuery(#"SQL/mydatabase database windows net;mydatabsae","

	SELECT *
      
FROM TABLEA A
INNER JOIN TABLEB ln on ln.KEY =  A.Key
INNER JOIN TABLEC gc on gc.id =  A.Key

	   ")
in
Source

Then, I create all the relations between the tables in order to create my Star model. When I create the report, it is not working as I can not see the data, instead I've got this error:

Is it because the value.nativeQuery() statement? 

Is it possible to create a model with relations between tables and creating my own querys to combine data with Direct Query On?

Is it strictly necessary to use composite models? I've been reading the documentation and I just want to be sure about it.

I am working with compatibility level: SQL Server 2017 / Azure Analysis Services (1400)

Thanks

[Anonymous · Accepted solution]

There is a trick that worked to me: 

-First of all, you need to create your model with the property "Direct Query Mode: Off".

-Then, If you want to create your own query, it is necessary to use Sql.Database()

let
   Source = Sql.Database("MyConection", "DATABASE", [Query="
select * from your_table 
.......
	   "])
in
Source

 -Once you import tables and create all relations between them, switch to "Direct Query Mode: On"

That's it... it should work!

[lbendlin]

I would think that using Value.NativeQuery() will prevent query folding from working - which makes Direct Query a bit pointless.  

Try loading your three tables independently and then let the DAX data model do the joins for you.