Introduction
This article continues the previous two blogs on Query Folding and builds upon the concepts discussed there. Hence, you must understand query folding to know the motivation behind different privacy settings in Power BI. However, I will summarize the discussion in a single line here: Query Folding is the process of delegating more work to the source system rather than Power Query for efficient data processing, at the risk of exposing data between multiple data sources.
Read more: Query Folding in Power BI
To minimize this risk, Power BI has introduced Privacy Settings to control which data can be exposed through Query Folding and which data should be protected at all costs.
Privacy Levels in Power BI
There are four types of Privacy levels in Power BI:
1. None:
This is the default Privacy Level set to each data source when first queried in Power BI. If the report has only one data source, there is no need to change this privacy level because there is no risk of exposing data between various sources. However, as soon as a second data source is created in the report, Power BI asks for the privacy level to be determined for both data sources because it cannot be kept to None now. The privacy levels need to be determined before Power BI proceeds further.
2. Private:
If we set the Privacy Level of a data source to Private, then Query Folding will never occur on this dataset, nor will the data from this source pass to any other data source for Folding. This is a double-edged sword. The plus point is that we can rest assured that this data source’s data is completely protected from other sources and will never be exposed to them. The downside is that this may happen at the cost of performance. From the example of Query Folding in our previous blog, we see a massive performance increase in both time and storage, and we forgo it for additional security.
Read more: Handling Big Data: Performance and Limits of Power BI
Example:
Let’s say that you are building an Employee Performance report. The report pulls all the data for each employee from an Excel sheet. The data contains sensitive information such as Date of Birth, SSN, or Credit Card Number. You do not want to risk sending this data to other data sources for additional performance benefits. Hence, you can mark the Privacy Level of this source as Private, and the data will not leave Power BI.
Maximize your Data's Potential!
Begin your advanced analytics journey with AlphaBOLD and unlock the full capabilities of your data for smarter decision-making.
Request a Demo3. Organizational:
This Privacy Level is a particularly good balance between security and performance. It helps in securing data sources on an organizational level. Query Folding will take place between the same organization’s data sources. However, if there is a data source outside the organization, the data will not be shared between the two sources.
Example:
Let’s say that you are building an inventory report. You will need data from the warehouse to check how much stock you have for each product. You also need the sales data to analyze whether the stock you have in the inventory will be enough for the next week. We are sourcing data from two diverse sources, but they are within the same organization. Hence, if we merge the two sources, Query Folding will occur for the organizational privacy level.
However, you also need the data about the suppliers to use the report to contact suppliers for a product if its quantity is insufficient. This data is not from your organization and is hosted on a third-party source. This third-party source could be OneDrive from another domain, Azure Blob Storage, or SQL Server under another tenant. When you bring this data to your report and merge it with the existing data, Query Folding will not occur for the Organizational Privacy Level. Hence, there is no way to bring only the relevant suppliers. You must fetch them all and filter the relevant ones in the report. Depending upon the size of the data and the implementation logic, it could be very performance-intensive. Still, you will also mitigate the risk of exposing your organization’s data to external systems.
4. Public:
The name says it all. There is no security at this privacy level. The data from a public source can be passed to other sources as part of Query Folding. There are no restrictions on this data source, and it gives the best performance when combined with the benefits of query folding. However, you need to be sure there are no security concerns when marking a data source as public.
Read more: Database Optimization Techniques – Improve Performance of your Code
Example:
You are building a Sales Prediction dashboard for a beverage company, and you are pulling live weather data into your report to see how temperature affects the sales of a beverage. This weather data is a public dataset, and there is no harm in passing the weather data to other sources through Query Folding.
Conclusion
So, there we have it. If you follow along, you have a pretty good understanding of Query Folding and Data Privacy Settings in Power Query. Equipped with this knowledge, you can solve firewall errors half the time.
Secure Your Data, Enhance Your Insights!
Ready to elevate your data strategy with Power BI's privacy features? AlphaBOLD is here to navigate you through the advanced analytics landscape, where security meets innovation.
Request a DemoExplore Recent Blog Posts
