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.