Adding query hints in M (Vertica)

[SorenHElisa]

Hi, 

Is there some function or property in M to add query hints?

We are using Vertica as backend data warehouse and OleDB driver to access it from Power BI. When running queries in a SQL tool, we can specify 

select /*+ label('Power BI Report') */ col1 from table 1;

By specifying "/*+ label('') */", the query gets labelled in a system table. We can then track the query in the database and see running times, what tables are in use during query execution etc.

It would be really nice to see from which Power BI the query originates and gather statistics, but I've not found any hints about adding query hints in M. I've tried searching the Net but not found anything so far. 

Of course, there's always the possibility to run the query as hard coded SQL (OleDB driver property), but can it be done in M somehow?

Br,

Sören

[DAOS]

Understanding your problem I have come to a solution and it is to connect through ODBC, creating the dns and there if you can place a query, this tutorial will summarize your doubt in a better way: https://www.vertica.com/kb/Integration-with-Microsoft-Power-BI-Connection-Guide/Content/Partner/Inte...

[v-juanli-msft]

Hi @SorenHElisa 

Do you need to add comments in M query?

If so, please add "// balabala"

https://yodalearning.com/tutorials/top-steps-to-do-comments-in-query-steps/

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

[SorenHElisa]

Hi Maggie,

Thanks for your response, but this was unfortunately not what I was looking for.

I need the "comment" to go down to the database, i.e. the comment need to be part of the generated SQL. So when Power BI tranlsates the qurey to be run in the database, the format should be:

select /*+ label('blahblah') */ col1 from table1;

Can this be done?

Br,

Sören

[v-juanli-msft]

Hi @SorenHElisa 

When you connect to sql sever with power bi desktop, you can choose tables/columns from sql database on a pop-out window.

This would then send query like "select balabala from table bb".

Or you could write sql query in advanced statement under the connnection information.

When you import data, open Transform data, on the right pane, select one step and right-click on it, you would see "view native query" where shows sql queries sending to sql database.

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

[SorenHElisa]

Hi Maggie, 

Yes, I'm aware of the functionality with the columns and how to show the native query. But these functionalities does not give you the opportunity to give a query hint. 

And since the header of this discussion intantiate that it is Vertica (not SQL Server) that we are using as backend, the functionality might differ.

I was thinking, are there any M-language functions that we can use?

Since the query hint must be placed at a special point in the query, i.e. right after the word "select" and before any column names, "distinct" or aggregation functions.

It seems that M-language cannot handle these kind of scenarios. Am I right?

Example:

select

/*+ label('PowerBI: Customer invoice sums') */

t1.customer_id,

t2.customer_name,

sum(t1.amount)

from fct_invoice t1

inner join dim_customer t2 on t1.customer_id = t2.customer_id

where t1.invoice_date between '2020-05-01' and '2020-05-31'

group by t1.customer_id, t2.customer_name

This can be tracked on basis of label "PowerBI: Customer invoice sums" inside Vertica and get execution time, tables involved, user running the query etc etc. 

As I stated in the original message, we can of course directly input the query in the SQL field when creating the connection, but it would be nice to be able to add the labelling if users have used Power Query functionality to build the query. 

So, is this possible in M-language / Power Query editor?

Would be nice to be able to do something like:

...

Labelled_query = Table.QueryHint(Query,"label('PowerBI: Invoice report')"),

...

and it would generate the sql code accordingly.

Br,

Sören