Data Privacy Settings In Power BI/Power Query, Part 1: Performance Implications

One of the most confusing, under-documented and widely-misunderstood features of Power BI and Power Query (or Excel “Get & Transform” or whatever you want to call it) are the data privacy settings. I get caught out by them all the time, so I thought it would be a good idea to write a series of blog posts demonstrating how they work and what effect they have in different scenarios using example M queries.

Before carrying on, I suggest you read the official Microsoft documentation on the subject here:
It gives you a good grounding in what the different data privacy levels are and where you can set them in the Power BI UI. The same options are available in Excel with Power Query/Get & Transform.

In this first post I’m going to look at what the performance implications of different privacy levels can be. Let’s say you have two data sources. First, an Excel workbook with a single table in that contains the name of a day of the week:


The second is the DimDate table in the Adventure Works DW SQL Server sample database:


Here’s an M query called FilterDay that returns the day name from the table in the Excel workbook:

    Source = Excel.Workbook(
		, null, true),
    FilterDay_Table = Source{[Item="FilterDay",Kind="Table"]}[Data],
    ChangedType = 
		{{"Parameter", type text}}),
    Output = ChangedType{0}[#"Parameter"]


Here’s an M query called DimDate that filters data from the DimDate table in the Adventure Works DW database, returning only the rows where the EnglishDayNameOfWeek column matches the value returned by the FilterDay query above.

    Source = Sql.Databases("localhost"),
    DB = Source{[Name="Adventure Works DW"]}[Data],
    dbo_DimDate = DB{[Schema="dbo",Item="DimDate"]}[Data],
    RemovedColumns = Table.SelectColumns(dbo_DimDate,
		{"DateKey", "EnglishDayNameOfWeek"}),
    FilteredRows = Table.SelectRows(RemovedColumns, 
		each ([EnglishDayNameOfWeek] = FilterDay))

The first time you run this second query you’ll be prompted to enter credentials to connect to SQL Server, and then (assuming you haven’t set any of the options that get Power BI to ignore privacy levels) you’ll see the “Information is required about data privacy” prompt:


Clicking “Continue” allows you to set a privacy level for the SQL Server database (but, interestingly, not for the Excel workbook):


You can choose any of the three privacy levels for the SQL Server database and the query will still run:


In fact it is the the privacy level for the Excel workbook that is important here and I’m not really sure why Power BI doesn’t prompt you to set that too. At this point the workbook has a privacy level set to None, which is the default for newly-created data sources (I’m still researching what this level actually means and hope to cover it in a future blog post):


If you right-click on the FilteredRows step of the query that returns data from the DimDate table in SQL Server and select “View Native Query” (see here for more details on this feature) to see the SQL query generated in the background for this step like so:


You’ll see that query folding is taking place for this step and the filter on the EnglishDayNameOfWeek column is taking place in the SQL query:


select [_].[DateKey],
    select [DateKey],
    from [dbo].[DimDate] as [$Table]
) as [_]
where [_].[EnglishDayNameOfWeek] = 'Friday'

Query folding is almost always a good thing for the performance of a query. For more details on what query folding is, see here.

Nothing changes if you set the privacy level of the Excel workbook to Public. If, however, you set the privacy level for the Excel workbook to Private like so:


…even though the DimDate query still works, query folding does not take place. The View Native Query right-click option is greyed out, and Profiler shows that the following SQL is executed when the query is refreshed:

select [$Ordered].[DateKey],
    select [DateKey],
    from [dbo].[DimDate] as [$Table]
) as [$Ordered]
order by [$Ordered].[DateKey]

Note that there is no WHERE clause in this query and that the whole of the DimDate table is returned from SQL Server.

Data from a data source that has a privacy level of Private can never be sent to another data source. That is exactly what needs to happen for query folding to take place though: a value from the Excel workbook – the text “Friday” – needs to be embedded in the WHERE clause of the SQL query sent to SQL Server in order for filtering to happen inside the database.  The risk with query folding is that a DBA could monitor the queries that are being run on SQL Server, look at the WHERE clauses, and see data from your Excel workbook. That’s maybe not a problem with day names, but potentially an issue if you were working with more sensitive data like customer names or addresses. Therefore, with the Excel workbook’s privacy level set to Private, the whole of the DimDate table is downloaded into the M engine and the filtering has to take place there to maintain the privacy of the data in Excel. The query still runs but it will probably be a lot slower than it would have been had query folding taken place. With the privacy level of the Excel workbook set to Public, on the other hand, it is ok to send data from Excel to SQL Server so query folding does take place.

To sum up, in this post I have shown how different data privacy settings can affect the performance of a query by determining whether query folding takes place or not. In part 2 of this series I will show how different data privacy settings can determine whether a query executes at all.

SSAS Multidimensional: Are Your Indexes Processed?

If you are using SSAS Multidimensional and you use Process Update to process your dimensions, here’s something for you to try: run a Process Default on your cube. Does it finish in a few seconds? Then you’re ok. If it doesn’t, and it takes minutes or even longer then read on – you might have a problem that’s causing slow query performance.

One of the most common sources of query performance problems I see with my SSAS Multidimensional customers is unprocessed aggregations and indexes. If you run a Process Update on a dimension it may result in indexes and aggregations being dropped from partitions in your cubes; for more details on this, and why it happens, see this post:

This classic post by Darren Gosbell explains how you can check if you have unprocessed aggregations on a partition:

However, unprocessed indexes can also be a problem for query performance too. You can tell if the indexes on a partition are built by using the discover_partition_dimension_stat DMV. Here’s an example of how to use it for a partition in the Adventure Works database:

FROM SystemRestrictSchema($system.discover_partition_dimension_stat
        ,DATABASE_NAME = 'Adventure Works DW 2008'
        ,CUBE_NAME = 'Adventure Works'
        ,MEASURE_GROUP_NAME = 'Internet Sales'
        ,PARTITION_NAME = 'Internet_Sales_2003')


[For some background on running SSAS DMV queries, see here]

Here’s what the above query returns, a list of dimensions and attributes that are related to the partition:


If the ATTRIBUTE_INDEXED column shows false then indexes are not built for the attribute on the dimension. In this example no indexes are built at all on the partition; if I do a Process Index or Process Default on this partition, here’s what the DMV returns:


Now you can see the ATTRIBUTE_INDEXED property is set to true for most attributes. Note that there is an (All) attribute that is never indexed, and if you have set the AttributeHierarchyEnabled property to false or the AttributeHierarchyOptimizedState property to NotOptimized on an attribute, it will not have indexes built for it either (this is typically done to improve processing performance – see here for a few more details).

In a real-world cube it is likely that only a few indexes will be dropped on partitions as a result of a Process Update on a dimension, and even then this will depend on whether any changes take place in the dimension’s data, so you will have to look down the list of attributes returned by this DMV very carefully to see if ATTRIBUTE_INDEXED returns false when it should be returning true.

The solution to this problem, as several of the posts I’ve linked to above suggest, is to always run a Process Default on your cube as the last step in your processing schedule. A Process Default will process any object that is in an unprocessed state, so it will automatically rebuild any aggregations or indexes that are dropped as a result of Process Updates on dimensions.

Power BI Custom Data Connectors

Amid all the other announcements at Microsoft Build today came one I’ve been waiting for a long time: we can now build our own custom data connectors for Power BI. Here’s the announcement on the Power BI blog:

…and here are the docs:

Although it might not sound that exciting, I think this is one of the biggest things that’s happened to Power BI in a long time. Microsoft’s most successful products are always the ones that are platforms for its partners to build their own solutions on top of, and Power BI is no exception. Custom visuals have made an important contribution to the success of Power BI as a whole, allowing third parties to build visuals that Microsoft either didn’t have the resources to build itself or didn’t realise people wanted. Similarly, custom data connectors will only increase the already impressive number of data sources that Power BI can connect to. My guess is that a lot of SaaS and data providers will want to build their own custom data connectors, and that the number of custom data connectors available is going to explode. Why go to all the trouble of trying to build BI and reporting features into your product when it’s quicker and easier to send the data to Power BI and your users get a much better experience as a result?

This doesn’t just affect Power BI though. Since the new Get & Transform functionality for loading data into Excel (see here) is based on the same Power Query technology that is used by Power BI, these custom data connectors will at some point work with Excel too. Although it will be a while before most Excel users upgrade to a version that supports custom data connectors, when that happens the sheer size of the Excel market means that there will be even more demand for custom connectors and this will in turn strengthen the Power BI ecosystem. Don’t forget that Analysis Services Tabular 2017 also now uses Power Query for loading data, so this will benefit corporate BI users too.

From a purely technical point of view today’s announcement included some welcome new capabilities for M coders like me. These include Visual Studio integration (intellisense for M!) and the ability to connect to web services and use OAuth for authentication, something that wasn’t really possible before. It’s also interesting that you can create custom data connectors that enable DirectQuery connections from Power BI, and that you aren’t limited to importing data.

I can’t wait to start using all these features and I’m sure they will be the subject of many future blog posts. Also, if you’re looking to hire someone with a good understanding of M to build a custom data connector, I’m your guy. You can find my contact details at

DAX Functions, DirectQuery And Unrestricted Measures

If you use Power BI to connect to a data source such as SQL Server in DirectQuery mode, you’ll find that by default you cannot use the complete range of DAX functions inside your calculations. For example, here’s a screenshot from Power BI Desktop in DirectQuery mode with a measure where I’m trying to use the RankX() function:


Whenever you get an error message like “Function ‘RANKX’ is not supported in DirectQuery mode” you know you’re trying to use a DAX function that isn’t allowed by default in DirectQuery mode.

Why does this limitation exist? Well, as the documentation points out here:

…this is done for performance reasons. In Power BI, every time you interact with a report (by opening it, clicking on a slicer etc) a series of DAX queries is sent back to the Data Model to get the data needed by the report. In DirectQuery mode, all of these DAX queries – including any calculations – are translated into queries that are sent back to the data source. If that data source is SQL Server, then Power BI generates SQL queries. Not everything that you can do in a DAX calculation can be translated back into efficient SQL, so to prevent you from accidentally building calculations that will perform badly in DirectQuery mode Power BI tries to prevent you from using any DAX functions that it thinks it can’t generate efficient SQL for.

You can remove this restriction by clicking File/Options and Settings/Options to open the Options dialog and then going to the DirectQuery pane and selecting “Allow unrestricted measures in DirectQuery mode”:


There is no documented list of functions that work in DirectQuery mode (probably because it might change with each release) but there is a way to get one. Using SQL Server Management Studio it is possible to connect to Power BI Desktop and run DMV queries against the Power BI Data Model. If you query the MDSCHEMA_FUNCTIONS DMV like so:


You’ll see that it returns a list of all of the DAX and MDX functions understood by Power BI; the last column, called DIRECTQUERY_PUSHABLE contains information used by Power BI to decide whether a calculation is available in DirectQuery mode or not:


[At the time of writing this query doesn’t work in DAX Studio for some reason, I think because of the data returned in the PARAMETERINFO column, but if you try to restrict the rows or columns returned by the DMV in any way the DIRECTQUERY_PUSHABLE column only returns empty values. This is why I say you have to use SQL Server Management Studio]

For the DAX functions in the list, if DIRECTQUERY_PUSHABLE is 0 then it is not available in DirectQuery mode by default. If it returns 1 then it is available in measures but not calculated columns; if it returns 3 it is available in both measures and calculated columns (I guess 2 means that it is only available in calculated columns, but there are no functions with this property value right now). However this is not a definitive guide to whether a function is actually ‘pushable’ or not: some functions could be pushable in some scenarios, or with certain combinations of parameters, and not others and the UI may perform additional checks to decide whether a function can be used.

Thanks to Jeffrey Wang and Marius Dumitru for answering my questions about this.

By the way, if you’re reading this, you might also be interested in this post I wrote a while back on the functionality available in the Query Editor in DirectQuery mode:

UPDATE – as Marco points out in his comments below, there *is* documentation on which functions are supported in DirectQuery mode now. I just didn’t know about it…

New Book: Tabular Modelling In SQL Server Analysis Services

My good friends Marco Russo and Alberto Ferrari have published a second edition of the SSAS Tabular book that we wrote together a few years ago. It’s called “Tabular Modelling In SQL Server Analysis Services” and it covers pretty much everything you need to know about building models in SSAS 2016 Tabular. Although I didn’t have anything to do with preparing the second edition, it does include a few things I wrote for the first edition so I can’t pretend that this is an unbiased review – that said, I think it’s fair to say that everyone working with SSAS Tabular 2016 should have a copy of this book on their shelf. No-one knows SSAS Tabular better than Marco and Alberto and the chapters on engine internals and performance tuning are worth the price of the book alone. The only topic it doesn’t cover in detail is DAX, and of course if you want to learn DAX you should get a copy of Marco and Alberto’s equally brilliant book “The Definitive Guide To DAX”.

More details on the new book can be found in Marco’s blog post here and you can buy the book from Amazon UK here.