Power Query’s data privacy security system is one of the most difficult, and dare we say, one of the most under-documented Power BI (Business Intelligence) concepts. Yet, we have all gone through the dreaded ‘Formula.Firewall’ error messages once in our life, and they are extremely hard to debug. The characteristic that makes it hard to understand is its reliance on several other Power Query concepts. Without going into all these factors, we cannot understand and debug the Firewall errors in Power Query. Hence, we will cover the basics of a Data Privacy Firewall in this article and explain the subsequent concepts one by one in later articles.
What do you need to know?
This blog series is for Power BI users that have an intermediate experience with Power Query. Followers of this blog must at least know how to fetch and transform data in Power Query. Additionally, it is a plus if you have run into a firewall error while working with Power Query. Nevertheless, it is a great series for anyone who wants to understand the Power Query’s internal working.
What is a Data Privacy Firewall?
A Data Privacy Firewall is a component in Power BI that prevents unauthorized access to data. We know that Power BI sources data from many different data sources. Some of these data sources are public datasets, and some of them are highly private. We want to control which data should be exposed to users and which unauthorized users should never see data at all costs.
The security in Power BI is airtight. Once the data is in Power BI, there are all sorts of ways to secure access to it. We can control access on the Workspace level to limit a user’s ability to view reports. We can also limit whether a user can view what is there in a report or the ability to view the dataset. We can even implement finer security setting to limit the user’s view on some rows and let them view others, based on business rules. However, this is not the scope of this blog series, and we will not be concerned with security once the data is in Power BI. We will just be reviewing data security in Power Query fetching, transforming, and merging data.
When we source data in Power BI, it looks like one-way traffic. Power BI pulls data in, and there is no way that the source system can fetch data from Power BI, right? Wrong!!! Power BI itself sends data back to the source systems. In fact, Power BI can send data from one source system to the other. But why? And more importantly, how to stop it, or at least control it? This is where we need to learn about Query Folding [Reference Blog: Query Folding in Power BI] and Data Privacy Levels [Reference Blog: Data Privacy Settings in Power BI] in Power Query.
With the knowledge of Query Folding at our disposal, we can overcome most of the issues with the Data Privacy Firewall in Power BI. We still need to understand one other aspect of it before we have a complete understanding of Firewall errors. It is the concept of Partitioning in Power Query [Reference Blog: Partitioning in Power Query]. A partition can be a single step in Power Query or a collection of steps. It is important to understand because the Power Query sets up a firewall between them. So, you can merge various sources within a single partition but not across partitions. It is difficult to understand that an ‘Inner join’ in Power Query works fine sometimes, but if you add a simple ‘Rename Column’ step in between, it breaks. Also, it does not help that there is truly little documentation on partitioning in Power Query. Hence, it is especially important to understand what is going on behind the scenes.
Finally, we will go through the two Formulas.Firewall errors that usually haunt Power BI developers and debug them one by one:
- Formula.Firewall: Query ‘Query1’ (step ‘Source’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. [Reference Blog: Debug Formula.Firewall Error in Power BI: Part 1]
- Formula.Firewall: Query ‘Query1’ (step ‘Source’) is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination. [Reference Blog: Debug Formula.Firewall Error in Power BI: Part 2]
Conclusion:
If you follow these blogs in series, you will hopefully better understand what happens behind the scenes in Power Query. If you have questions or need further clarification, please reach out to us via the comment box below.
