Category Archives: SQL Server

Power BI DirectQuery Query Labels For SQL Server-Related Sources

December 23, 2021 By in DirectQuery, Power BI, SQL Server, Synapse 3 Comments

If you’re using Power BI in DirectQuery mode against a SQL Server-related data source (ie SQL Server on-prem, Azure SQL DB or Synapse) you may have noticed a new feature that was released a few weeks ago: query labels that allow you to link a SQL query to the Power BI visual that generated it.

There’s nothing you need to do to enable it – it happens automatically. Here’s an example of a TSQL query generated by a DirectQuery mode dataset with it in:

You can see that an OPTION clause in the query adds a label that contains three GUIDs that identify the dataset, report and visual that the query was generated for. These are the same GUIDs that you’ll see used for this purpose in other places, for example Log Analytics. This post has more details on how you can work out which datasets, reports and and visuals these GUIDs relate to.

As I said, this only works for SQL Server-related sources at the moment, but if you think this would be useful for other sources (and I’m sure you do…) please leave a comment below!

Setting SQL Server CONTEXT_INFO In Power Query

November 28, 2021 By in Power BI, Power Query, SQL Server 2 Comments

In my quest to check out every last bit of obscure Power Query functionality, this week I looked into the ContextInfo option on the Sql.Database and Sql.Databases M functions. This option allows you to set CONTEXT_INFO in SQL Server (see here for a good article explaining what this is) and here’s an example of how to use it:

let
  Source = Sql.Database(
    "localhost", 
    "AdventureWorksDW2017", 
    [
      Query = "SELECT * FROM DIMDATE", 
      ContextInfo = Text.ToBinary(
        "Hello"
      )
    ]
  )
in
  Source

This Power Query query runs a simple SQL SELECT statement against the SQL Server Adventure Works DW 2017 database. Note that since you need to pass a binary value to the ContextInfo option, in this example I had to use the Text.ToBinary function to convert my text to binary.

Here’s what happens in SQL Server when this Power Query query is run:

Here’s a simple example of how to retrieve this data on the SQL Server side:

SELECT session_id, login_time, program_name, context_info 
FROM sys.dm_exec_sessions
WHERE session_id=57

I’ll leave it to the SQL Server experts to decide what this can be used for and no doubt to complain that it would be more useful to support SESSION_CONTEXT too – although I’ve heard that might already be used for something, so I need to do more research here…

%d bloggers like this: