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.
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
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.
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/
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()”.
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
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
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…