New M Functionality And Behaviour In Power BI Custom Data Connectors

Over the past few weeks I’ve spent some time playing around with Power BI custom data connectors and while I don’t have anything to share publicly yet (other people are way ahead of me in this respect – see the work of Igor Cotruta, Miguel Escobar and Kasper de Jonge among others) I have learned some interesting things that are worth blogging about.

First of all, the data privacy rules around combining data from different data sources do not apply in custom data connector code. As the docs say here:

Data combination checks do not occur when accessing multiple data sources from within an extension. Since all data source calls made from within the extension inherit the same authorization context, it is assumed they are “safe” to combine. Your extension will always be treated as a single data source when it comes to data combination rules. Users would still receive the regular privacy prompts when combining your source with other M sources.

Those of you who have followed my recent series on this topic, or who have struggled with the Formula.Firewall error, will appreciate how much easier this makes combining data from different sources.

Secondly, you have a lot more flexibility when it comes to different types of authentication for web services. As I showed in my session on web services and M at the Data Insights Summit, there are a lot of limitations when it comes to working with web services in Power BI or Excel. Within a custom data connector, however, you can connect to web services that use OAuth for authentication, you can make POST requests to web services that require authentication and you can pass a web API key from the credentials store through an HTTP custom header and not just through a query parameter – none of which are possible in Power BI or Excel.

I’m sure there are a lot of other useful bits of functionality or behaviour that are only available in custom data connectors – I know I’ve only just begun to learn what’s possible. Even with what I’ve listed here, though, I get the feeling that there will be a lot of cases where you will have no choice but to build a custom data connector just to be able to access certain data sources, even if you only need to create a single report. There may also be cases where it’s preferable to build a custom data connector rather than embed lots of complex M code in a Power BI report or Excel workbook, perhaps to make code portability easier. It’s a bit of a pain to have to have Visual Studio and the SDK installed in order to do this, but building a custom data connector is fairly easy if you already know M and the development experience in Visual Studio (with intellisense!) is much better than in the Advanced Query Editor window.

Configuring Power BI Gateway Data Sources For Files And Folders

Recently I’ve been building a lot of Power BI reports from csv and Excel files, and to make sure that scheduled refresh works I have been setting up data sources in an On Premises Data Gateway (what used to be called the Enterprise Gateway). I had assumed that if I was connecting to file-based data sources in my Power BI dataset then, in the gateway, I would need to set up one data source for each file that I’m connecting to – which is a bit of a pain. In fact it turns out that you can set up a gateway data source for the folder that the files are in instead.

Let me give you an example. Imagine that you have three Excel files in a folder called C:\Sales Data:

image

Now imagine that you have three queries in Power BI that get data from these three files:

image

Here’s an example of the M code for one of these queries:

let
    Source = 
        Excel.Workbook(
        File.Contents("C:\Sales Data\SalesData_1.xlsx")
        , null, true),
    SalesDataTable_Table = 
        Source{[Item="SalesDataTable",Kind="Table"]}[Data]
in
    SalesDataTable_Table

There’s nothing really to notice here except that the code uses File.Contents() to get the data from a single file – I’m not using Folder.Contents().

However, once the report has been published only one data source needs to be set up in the On Premises Data Gateway for it to refresh successfully, even though the report connects to three different files. Here’s a screenshot of the gateway data source I set up in the Power BI service:

image

Two things to point out:

  • The data source type is set to Folder
  • The full path property is set to the path of the folder that the files used by the report are in, ie C:\Sales Data

Setting up a single gateway data source for a folder is obviously a much better option than setting up multiple data sources for all the files in the folder. Did everyone else know this but me? I guess this is all related to the inheritance of data privacy settings that I blogged about here.

Data Privacy Settings In Power BI/Power Query, Part 5: The Inheritance Of Data Privacy Settings And The None Data Privacy Level

Something I didn’t understand at all when I started writing this series was how the “None” data privacy level worked. Now, however, the ever- helpful Curt Hagenlocher of the Power Query dev team has explained it to me and in this post I’ll demonstrate how it behaves and show how data privacy levels can be inherited from other data sources.

Let’s go back to the original example I used in part 1 of this series where I showed how data from an Excel workbook can be combined with data from SQL Server, and how the data privacy settings on each data source determine whether query folding takes place or not (I suggest you read that post before continuing to get some background). Now, imagine that the Excel workbook is in a folder called C:\Data Privacy Demo, and a query called FilterDay is used to get data from it:

let
    Source = 
	Excel.Workbook(
		File.Contents(
		"C:\Data Privacy Demo\FilterParameter.xlsx"
		)
	, null, true),
    FilterDay_Table = 
	Source{[Item="FilterDay",Kind="Table"]}[Data],
    ChangedType = 
	Table.TransformColumnTypes(
		FilterDay_Table,
		{{"Parameter", type text}}
	),
    Output = 
	ChangedType{0}[#"Parameter"]
in
    Output

This query gets the name of a weekday from a table in the workbook, for example the text “Friday”:

image

When this query is referenced in a second query that uses the day name to filter the data in a table in SQL Server, like so:

let
    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))
in
    FilteredRows

…and the query is run for the first time, then you will get prompted for credentials to access SQL Server and after that you’ll get prompted to set data privacy levels on both data sources used:

image

The dropdown boxes in the second column allow you to set the data privacy settings for each data source, but look at the data sources listed in the first column. There are two things to point out:

  • The data sources the two queries are accessing are the DimDate table in the Adventure Works DW database on localhost, and the file C:\Data Privacy Demo\FilterParameter.xlsx. However you’re not being prompted to set data privacy levels on those exact data sources, you’re being prompted to set data privacy levels on the localhost instance and the c:\ drive
  • The data source names are displayed in dropdown boxes, so there are other options to select here

Clicking each dropdown box is revealing:

image

image

For the SQL Server database you can set the data privacy level at two places: the localhost instance (the default), or the Adventure Works DW database on that instance. For the Excel workbook you get set the data privacy level at three places: the c:\ drive (the default), the folder c:\Data Privacy Demo that the Excel workbook is in, or the Excel workbook itself.

Let’s say you accept the defaults and set the data privacy settings to Public on localhost and the c:\ drive:

image

As you would expect after reading part 1 of this series, the query runs and query folding takes place:

image

image

Now, let’s say you copy the Excel file up to the root of the c:\ drive and rename it to filterparameter2.xlsx, then update the FilterDay query above to load data from this new Excel file instead:

let
    Source = 
	Excel.Workbook(
		File.Contents(
		"C:\FilterParameter2.xlsx"
		)
	, null, true),
    FilterDay_Table = 
	Source{[Item="FilterDay",Kind="Table"]}[Data],
    ChangedType = 
	Table.TransformColumnTypes(
		FilterDay_Table,
		{{"Parameter", type text}}
	),
    Output = 
	ChangedType{0}[#"Parameter"]
in
    Output

 

At this point, when you click the Data Source Settings button and look at the permissions for the file c:\filterparameter2.xlsx you will see that the privacy level is set to None:

image

However, it behaves as if it has a data privacy level of Public: the second query that gets data from SQL Server runs successfully, query folding still takes place and you are not prompted to set a data privacy level for this data source. Why?

The “None” data privacy level means that no privacy level has been set for this exact data source. However, when this happens the engine checks to see if a data privacy level has been set for the folder that this file is in and then for all folders up to the root. In this case, since the data privacy level has been set to Public for the c:\ drive, all files in all folders on that drive that have a data privacy level set to None (like this one) will inherit the c:\ drive’s setting of Public:

image

The same goes for databases on a SQL Server instance: they can inherit the data privacy settings set for the instance. The same is also true for web services, where data privacy settings can be set for different parts of a URL; for example, here’s the list of options for a call to the https://data.gov.uk/api/3/action/package_search web service described in part 2 of this series:

image

The general rule is that the engine looks for permissions for the exact data source that it’s trying to access, and if none are set then it keeps looking for more general permissions until it runs out of places to look.

In my opinion, I don’t think the way the “None” privacy level and inheritance works is very clear right now – it makes sense now I’ve had it explained to me, but the UI does nothing to help you understand what’s going on. Luckily it sounds like the dev team are considering some changes to make it more transparent. I would like to see the fact that data privacy levels have been inherited for a data source, and where they have been inherited from, called out in the Edit Permissions dialog.

Data Privacy Settings In Power BI/Power Query, Part 4: Disabling Data Privacy Checks

So far in this series, I have shown how changing the data privacy settings for a data source can affect the performance of queries and even prevent them from executing completely. What I haven’t mentioned yet is that you also have the option of disabling data privacy checks completely in Power BI Desktop and Excel. In this post I will show you how you can disable data privacy checks and discuss the pros and cons of doing so.

In Power BI Desktop you can change whether data privacy checks are applied when a query executes by going to File/Options And Settings and selecting Options:

image_thumb[7]

The same settings can be found in Excel 2016 by going to the Data tab, clicking Get Data and then selecting Query Options.

image

In both cases this brings up the Options dialog.

There are two panes in the Options dialog with properties that are relevant to how data privacy checks are applied. First of all, in Global/Privacy, there are global properties that are relevant for every .pbix or Excel file that you open on your PC:

image_thumb[6]

The three options here need a little bit of explanation:

  1. Always combine data according to your Privacy Level settings for each source means that data privacy settings are always applied for every .pbix or Excel file you open, regardless of the properties (described below) that you have saved for individual files.
  2. Combine data according to each file’s Privacy Level settings means that the properties set on individual .pbix or Excel files control how the data privacy checks are applied.
  3. Always ignore Privacy Level settings means that data privacy settings are always ignored, in every .pbix or Excel file you open, regardless of settings saved for individual files.

Then, in the Current File/Privacy pane, there are properties that are saved in and apply to the current .pbix or Excel file that you have open:

image

The radio buttons here are greyed out if you have options #1 or #3 selected in the previous pane; it’s only if you have selected option #2, Combine data according to each file’s Privacy Level settings, that these properties are taken into account. You may need to close and reopen the Options dialog if you have changed settings in the previous pane but the radio buttons here remain greyed out.

The two options here are:

  1. Combine data according to your Privacy Level settings for each source, which means that the data privacy settings that you have set for each data source are used to control how queries that combine data from multiple data sources behave. This is the default setting.
  2. Ignore the Privacy Levels and potentially improve performance, which means that data privacy settings are completely ignored when queries combine data from multiple data sources.

To sum up, these two groups of properties allow you to choose whether data privacy settings are applied differently for different .pbix or Excel files, or whether, on your PC, they are always applied or always ignored.

For Power BI users it is important to remember that these settings only apply to Power BI Desktop. After a report has been published, if you are using the On-Premises Data Gateway, you also need to configure data privacy settings on the data sources used by your dataset in the Power BI portal. If you are using the On-Premises Data Gateway in Personal Mode (what used to be called the Personal Gateway) then you can configure it to ignore data privacy settings as described here. Unfortunately if you are not using Personal Mode (ie you are using what used to be called the Enterprise Gateway, and what is now just called the On-Premises Data Gateway) then at the time of writing there is no way to configure the gateway to ignore data privacy levels. You can vote here to get this changed. It’s also worth mentioning that right now you can’t combine data from online and on-premises data sources in a gateway either, although it sounds like this limitation will be addressed soon. To work around these limitations you have to import data into separate tables in the dataset and then use DAX calculated tables to combine the data instead – a nasty hack I know, but one that I’ve had to implement myself a few times.

It can be incredibly tempting to avoid the problems associated with data privacy checks by setting Power BI and Excel to ignore them completely. Doing this certainly avoids a lot of headaches and confusion with the Formula.Firewall error message and so on. It also ensures that your queries execute as fast as they can: this is not just because query folding happens whenever possible but because the act of applying the data privacy checks alone can hurt query performance. Recently I saw a case where the only data source used was an Excel workbook (so no query folding was possible) and turning off the data privacy checks made a massive difference to query performance.

However, I cannot recommend that you turn off data privacy checks for all your Excel workbooks and .pbix files by default. Firstly, if you are working with sensitive or highly-regulated data, leaving the data privacy checks in place at least forces you to consider the privacy implications of query folding on a case-by-case basis. On the other hand ignoring data privacy checks by default makes it more likely that you or one of your users will create a query that accidentally sends data to an external data source and breaches your organisation’s rules – or even the law – concerning how this data should be handled. Secondly, if you are a Power BI user and need to use the On-Premises Data Gateway, then you risk creating reports that work fine in Power BI Desktop when the data privacy checks are ignored but which cannot be refreshed after they have been published because the On-Premises Gateway still applies those checks.

Power BI, DAX, Data Science & DevOps Training In London This Autumn

If you’re looking for classroom-based training on Microsoft BI, data science or SQL Server then check out the list of courses I have coming up at Technitrain this autumn:

  • Introduction to Power BI, taught by me, September 25th-27th – a three day course covering the basics of Power BI suitable for BI pros and business analysts with no previous experience of the product.
  • Database DevOps, taught by Alex Yates, September 25th-27th – a course for DBAs, developers or anyone who plays a role in writing, testing or deploying changes to SQL Server.
  • Practical Data Science with Cortana Intelligence, taught by Rafal Lukawiecki, November 6th-10th – an introductory course for budding data scientists covering theory and how to apply it to Microsoft tools including Azure Machine Learning and SQL Server 2016 R Services.
  • Mastering DAX, taught by Alberto Ferrari, November 27th-29th – a three day introductory course that will teach you how to write DAX calculations and queries in Power BI, Power Pivot and Analysis Services Tabular.
  • Optimising DAX, taught by Alberto Ferrari, November 30th-1st December – learn about performance tuning DAX from one of the leading experts in the field!

All of the courses are in central London.

Data Privacy Settings In Power BI/Power Query, Part 3: The Formula.Firewall Error

In the first two parts of this series (see here and here) I showed how Power BI/Power Query/Excel Get & Transform’s data privacy settings can influence whether query folding takes place or even whether a query is able to run or not. In this post I’m going to talk about the situations where, whatever data privacy level you use, the query will not run at all and you get the infamous Formula.Firewall error.

I’ll admit I don’t understand this particular topic perfectly (I’m not sure anyone outside the Power Query dev team does) so what I will do is explain what I do know, demonstrate a few scenarios where the error occurs and show how to work around it.

Assume you have the two data sources described in my previous posts: an Excel workbook that contains just a single day name, and the DimDate table in SQL Server that can be filtered by the day name from Excel. Let’s also assume that both data sources have their data privacy levels set to Public. The following query, called FilterDay, loads the data from Excel and returns a text value containing the day name:

let
    Source = 
	Excel.Workbook(
		File.Contents("C:\FilterParameter.xlsx"), 
	null, true),
    FilterDay_Table = 
	Source{[Item="FilterDay",Kind="Table"]}[Data],
    ChangedType = 
	Table.TransformColumnTypes(
		FilterDay_Table,
		{{"Parameter", type text}}
	),
    Output = 
	ChangedType{0}[#"Parameter"]
in
    Output

image

Now, look at the following query:

let
    Source = 
	Sql.Database(
		"localhost", 
		"adventure works dw",
		[Query="select DateKey, EnglishDayNameOfWeek 
		from DimDate"]),
    FilteredRows = 
	Table.SelectRows(Source, 
		each ([EnglishDayNameOfWeek] = FilterDay)
	)
in
    FilteredRows

It filters the contents of the DimDate table and only returns the rows where the EnglishDayNameOfWeek column matches the day name returned by the FilterDay query. Notice that there are two steps in the query, Source (which runs a SQL query) and FilteredRows (which does the filtering). Here’s the output:

image

As you can see from the screenshot, the query runs. In fact it runs whatever data privacy settings you have set on both the data sources, although it’s worth pointing out that if you use your own SQL in an M query (as I do in this case) this stops query folding in all subsequent steps, as described here.

Now take a look at the following version of the query:

let
    Source = 
	Table.SelectRows(
		Sql.Database(
			"localhost", 
			"adventure works dw",
			[Query="select DateKey, 
				EnglishDayNameOfWeek 
				from DimDate"]
		), 
		each ([EnglishDayNameOfWeek] = FilterDay)
	)
in
    Source

The important difference here is that there is now one step in this query instead of two: the query and the filtering take place in the same step. Even more importantly, regardless of the data privacy settings, the query fails with the error:

Formula.Firewall: Query ‘DimDate With Native Query Single Step Fails’ (step ‘Source’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

image

The problem here is that the Power Query engine is not allowed to access two different data sources originating from different queries in the same step – as far as I understand it this is because it makes it too hard for the engine to work out whether a step connects to a data source or not, and so which data privacy rules should be applied.

At this point you might think that it’s straightforward to break your logic up into separate steps, as in the first example above. However there are some situations where it’s not so easy to work around the problem. For example, consider the following query:

let
    Source = 
	Sql.Database(
		"localhost", 
		"adventure works dw",
		[Query="
		 select DateKey, EnglishDayNameOfWeek 
		 from DimDate 
		 where 
		 EnglishDayNameOfWeek='" & FilterDay & "'" 
		]
	)
in
    Source

In this example I’m dynamically generating the SQL query that is being run and passing the name of the day to filter by into the WHERE clause. In the two previous examples the query that was run had no WHERE clause and the filtering on day name took place inside Power BI – in this case the filtering is happening inside the query, so in order to generate the WHERE clause I have to refer to the value that the FilterDay query returns in the same step. Therefore, this query also gives the same Formula.Firewall error seen above.

How can you work around this? Well, the following version of the query that attempts to reference FilterDay in a separate step doesn’t work either:

let
    DayAsStep = FilterDay,
    Source = 
	Sql.Database(
		"localhost", 
		"adventure works dw",
		[Query="
		 select DateKey, EnglishDayNameOfWeek 
		 from DimDate 
		 where 
		 EnglishDayNameOfWeek='" & DayAsStep & "'" 
		]
	)
in
    Source

 

Luckily, it turns out that if you use the Value.NativeQuery() function to run your query instead you can avoid the error. As I showed here, you can use this function to pass parameters to SQL queries. If you generate the record containing the parameters for the query as a separate step (called ParamRecord here), like so:

let
    Source = Sql.Database("localhost", "adventure works dw"),
    ParamRecord = [FilterParameter=FilterDay],
    Query = Value.NativeQuery(
                Source, 
                "select DateKey, EnglishDayNameOfWeek 
		from DimDate 
		where 
		EnglishDayNameOfWeek=@FilterParameter",
                ParamRecord)
in
    Query

Then the query runs successfully.

There is another way to avoid the error. In all the examples above I have two queries: one to get data from Excel, one to get filtered data from SQL Server. If these two queries are combined into a single query, it doesn’t matter if data from different data sources is accessed in the same step. So, for example, unlike all of the queries above the following query does not reference any other queries; instead it gets the day name from the Excel workbook in the ExcelSource step and then runs the dynamic SQL query in the SQLSource step, and runs successfully:

let
    ExcelSource = 
	Excel.Workbook(
		File.Contents("C:\FilterParameter.xlsx")
	, null, true),
    FilterDay_Table = 
	ExcelSource{[Item="FilterDay",Kind="Table"]}[Data],
    ChangedType = 
	Table.TransformColumnTypes(FilterDay_Table,
		{{"Parameter", type text}}),
    FilterDayStep = 
	ChangedType{0}[#"Parameter"],
    SQLSource = Sql.Database(
	"localhost", 
	"adventure works dw",
	[Query="
		select DateKey, EnglishDayNameOfWeek 
		from DimDate 
		where 
		EnglishDayNameOfWeek='" 
		& FilterDayStep & 
		"'" ])
in
    SQLSource

Clearly the M engine doesn’t get confused about accessing data from different sources in the same step if those data sources are created in the same query.

Of course you can avoid the Formula.Firewall error and make query folding happen as often as possible by turning off data privacy checks completely in the Options dialog. This will be the subject of the next post in this series.

White Paper On “Planning A Power BI Enterprise Deployment”

I’m pleased to announce that a white paper I co-authored with Melissa Coates on “Planning a Power BI enterprise deployment” has now been published. You can download it from the Power BI white papers site here: https://aka.ms/pbienterprisedeploy

Melissa has already blogged about the white paper here.

Topics covered include the different ways that Power BI can be deployed (as a self-service BI tool or as a corporate BI tool); licensing; preparing data for use in Power BI; choosing a data storage mode (import vs Live connections to SSAS vs DirectQuery); data refresh and the on-premises gateway; best practices for report development; collaboration and sharing (covering apps and content packs); options for consuming reports and data published to Power BI; and security, compliance and administration. If that sounds like a lot, it is: it’s 105 pages long!

It was a real pleasure working with Melissa on this, and I’d also like to thank Meagan Longoria for reviewing it. I’m also extremely grateful to Adam Wilson and an army of Microsoft employees for providing information, answering our questions and correcting our mistakes.