Why Not All SQL Queries Can Be Used As A Source In Power BI DirectQuery Mode

A few days ago I was tagged on a thread on Twitter: my old pal Mim was upset to find that he couldn’t use a SQL query with a Common Table Expression as the source of a table in DirectQuery mode. He worked out why without my help but I thought that it was worth me writing up an explanation for other people who might be struggling with the same problem.

In Power BI DirectQuery mode you can – for most data sources – use your own SQL query as the source for a table in your dataset. For example when connecting to the AdventureWorksDW 2017 sample database in SQL Server using DirectQuery mode, I can use the following SQL query as the source of a table:

SELECT 
FullDateAlternateKey, 
EnglishDayNameOfWeek, 
EnglishMonthName
FROM DimDate

Now, let’s say I use this table in a dataset and build the following report with two slicers and a table, and with a month and a day name selected in the slicers:

The SQL generated by Power BI to populate the table looks like this:

SELECT 
TOP (501) [t0].[FullDateAlternateKey]
FROM 
(
(
SELECT 
FullDateAlternateKey, 
EnglishDayNameOfWeek, 
EnglishMonthName
FROM DimDate
)
)
 AS [t0]
WHERE 
(
(
[t0].[EnglishDayNameOfWeek] = N'Sunday'
)
 AND 
(
[t0].[EnglishMonthName] = N'April'
)
)

GROUP BY [t0].[FullDateAlternateKey],[t0].[EnglishDayNameOfWeek],[t0].[EnglishMonthName]
ORDER BY [t0].[EnglishDayNameOfWeek]
ASC
,[t0].[EnglishMonthName]
ASC
,[t0].[FullDateAlternateKey]
ASC

Notice how the original SQL query I used as the source of the table is used as a subquery in this query. This is how Power BI generates its SQL and this is why there are limitations on the SQL you can use in DirectQuery mode: not all SQL queries that run standalone can be used in subqueries.

Take the following variation on the SQL above for example:

SELECT
FullDateAlternateKey,
EnglishDayNameOfWeek,
EnglishMonthName
FROM DimDate
ORDER BY
EnglishMonthName

It’s the same query as before but with an ORDER BY clause. It works fine in the Power Query Editor but when you go to your report you’ll see the following error:

“Microsoft SQL: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries and common table expressions unless TOP, OFFSET or FOR XML is also specified”

This is the error message generated by the SQL query that Power BI is trying to run – and as you will have guessed by now, it’s being generated because Power BI is trying to use your query with an ORDER BY clause inside its own subquery. You can’t put an ORDER BY clause in a subquery in TSQL except in the scenarios mentioned in the error message. The same goes for Common Table Expressions.

What can you do to work around this? There is really just one answer: model your data the way Power BI likes it (ie as a star schema) before it gets to Power BI. It’s the now-famous Roche’s Maxim. Even if you aren’t running into this limitation using SQL queries as the source of a table is a bad idea because it makes maintenance difficult and you end up doing any expensive transformations at query time and paying the performance penalty over and over again. And yes, I know, some of you don’t have permissions to create views or get the data modelled correctly in the source and I know it’s tough, but that’s the way it is.

10 responses

  1. Thank you for the great article, Chris. Do you know if we can use SQL stored procedures with parameters instead of query? Any guidance on this?

    • let
      StartDate=Date.StartOfMonth(Date.AddMonths(DateTime.LocalNow(),-6)),
      EndDate=Date.AddDays(Date.StartOfMonth(Date.AddMonths(DateTime.LocalNow(),0)),-1),
      Conta=”F”,
      FechaIni = DateTime.ToText(StartDate,”yyyyMMdd”),
      FechaFin = DateTime.ToText(EndDate,”yyyyMMdd”),
      Source = Sql.Database(“10.50.88.77”, “BI”, [Query=”EXEC [EMPRESA].[SpCargaSaldosMayorxCC] ‘” & FechaIni & “‘,'” & FechaFin & “‘,'” & Conta & “‘”, CreateNavigationProperties=false])
      in
      Source

  2. Liked Sun Tzu’s quote in Roche’s Maxim. Sun Tzu also laid out the five dimensions to consider for data modeling. First of all, you need a date dimension – Sun Tzu call it “Tian” – to help you to understand the timing.

  3. Thanks, Chris, for the details, but my use case is a bit different, I was trying to port a script from BigQuery to SQL Server as M Parameter is supported now,

    unfortunately, it has to be direct Query, let’s say you have 10,000 points, pre calculating is not practical as it will generate 100 Million combination, so there is always exception to a good practice.

    Now regarding Views, I don’t think it is a realistic proposition, Business users don’t have write access to DB, and honestly they should not, PowerBI is popular because we can do our transformation at the Front end level, maybe it is bad practice, but it is very convenient and the only way in a lot of cases.

    as in my case, it did work with BigQuery, I suspect I need to figure out how to write T-SQL in such a way to get the same results
    https://datamonkeysite.com/2021/08/15/using-powerbi-m-parameter-for-geospatial-calculation/

  4. Pingback: Dew Drop – March 7, 2022 (#3636) – Morning Dew by Alvin Ashcraft

  5. Pingback: DirectQuery and SQL Query Limitations – Curated SQL

  6. I’ve found a number of times where I’ve had to create a table-valued function to return the data I want (obviously reques higher-level SQL rights). You can include complex logic, then I can just use a simple SQL query in PowerBI like “SELECT * FROM do.MyFunc()”.

  7. This will work:

    SELECT * FROM (
    SELECT TOP 99.999999 PERCENT *
    FROM [GLOBALES].[vwCalendarTable]
    WHERE [Year] = 2022
    ORDER BY [Month]
    ) AS Calendar

    and also this from SQL Server 2012 and later:

    SELECT * FROM (
    SELECT *
    FROM [GLOBALES].[vwCalendarTable]
    WHERE [Year] = 2022
    ORDER BY [Month] OFFSET 0 ROWS
    ) AS Calendar

  8. Great explanation Chris.

    As per me, one solution could be to always write the required T-SQL which one wants to use with DirectQuery, in a view in SQL and then simple do a SELECT * FROM vwPowerBI

    This way we can eliminate almost all the limitations of T-SQL with Power BI.

    Please let me know if I am mistaken with this understanding.

    Thanks,
    Vinay

  9. Here is an other oddity – A Union Query in an Access DB does not get listed in the Power Query navigation dialog – so the only work around is to build a select query on the union query and use this in PQ.

    Of course the PQ team is aware of it but are waiting for the 1000+ votes…

Leave a Reply to Vinay Pugalia Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: