Would Data gateway compress and encrypt the query result before sending back to Power BI Cloud

[pennyhoho117]

when refresh latest data from Power BI service, Data gateway would receive the request and decrpt the request and enquiry from DB, but Data gateway compress and encrypt the query result before sending back to Power BI Cloud? if yes, how the result being compressed and encrypt, what technology used in the compression and decrption?

I have raise same questions to the community and checked from Power BI document, but could not find a clear result.

<Note: as when a report publish to Power BI service, the 'dataset' is being compressed by VertiPaq Engine, and cannot exist 1GB after compression, hence if enquiry data from DB, the data gateway would also compress the result before sending back to power bi service?>

Meanwhile, would you please provide document for how pbix or dataset is being compressed in power bi Service?

Thanks

[v-menakakota · Accepted solution]

Hi @pennyhoho117 ,

Thank you for your detailed query. Let me address your concerns regarding data compression and encryption in Power BI when using the On-premises Data Gateway, as well as provide insights into the compression mechanism used for datasets in Power BI Service:

1. Data Gateway – Compression and Encryption of Query Results

When a refresh request is initiated from Power BI Service, the On-premises Data Gateway facilitates secure data retrieval from your on-premises data sources. Here’s a breakdown of what happens:
Encryption by the gateway are not publicly detailed by Microsoft due to some security reasons.

 While Microsoft does not explicitly document the exact compression algorithm used by the gateway, it is confirmed that the gateway optimizes and compresses the data before transmitting it to Power BI Service. This ensures efficiency and minimizes network bandwidth usage.

Reference:
About the On-premises data gateway - Power BI | Microsoft Learn

2. Power BI Dataset Compression (VertiPaq Engine)

When you publish a Power BI report (PBIX file) to the Power BI Service, the underlying dataset is processed and compressed using the VertiPaq storage engine. VertiPaq is an in-memory columnar database that applies advanced compression techniques, including:

• Value encoding
• Dictionary encoding
• Run-length encoding

This compression allows large datasets to fit into the 1 GB limit (for Pro workspaces) after compression.

Reference:
Power BI Dataset Size and Compression - Microsoft Learn

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

[v-menakakota · Accepted solution]

Hi @pennyhoho117 ,

Thank you for your detailed query. Let me address your concerns regarding data compression and encryption in Power BI when using the On-premises Data Gateway, as well as provide insights into the compression mechanism used for datasets in Power BI Service:

1. Data Gateway – Compression and Encryption of Query Results

When a refresh request is initiated from Power BI Service, the On-premises Data Gateway facilitates secure data retrieval from your on-premises data sources. Here’s a breakdown of what happens:
Encryption by the gateway are not publicly detailed by Microsoft due to some security reasons.

 While Microsoft does not explicitly document the exact compression algorithm used by the gateway, it is confirmed that the gateway optimizes and compresses the data before transmitting it to Power BI Service. This ensures efficiency and minimizes network bandwidth usage.

Reference:
About the On-premises data gateway - Power BI | Microsoft Learn

2. Power BI Dataset Compression (VertiPaq Engine)

When you publish a Power BI report (PBIX file) to the Power BI Service, the underlying dataset is processed and compressed using the VertiPaq storage engine. VertiPaq is an in-memory columnar database that applies advanced compression techniques, including:

• Value encoding
• Dictionary encoding
• Run-length encoding

This compression allows large datasets to fit into the 1 GB limit (for Pro workspaces) after compression.

Reference:
Power BI Dataset Size and Compression - Microsoft Learn

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

[pennyhoho117]

for 1,

Although Encryption by the gateway are not publicly detailed by Microsoft due to some security reasons,

but i would also want to know it (encryption then compression) or (compression then encryption)?

Meanwhile, as i know when setting up the data gateway, would generate ASK Key and RSA Keys after input Recoverey Key, May i know the enquiry result is not using these generated ASK and RSA Key to encrypt?<only use it when encrpting and decrpting Data source credentials, right?>

For 2, Data gateway optimizes and compresses the data before transmitting it to Power BI Service. so data gateway use VertiPaq to compress the enquired result? 

[v-menakakota]

Hi @pennyhoho117 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-menakakota]

Hi @pennyhoho117 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-menakakota]

Hi @pennyhoho117 ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-menakakota · Accepted solution]

Hi @pennyhoho117 ,

Microsoft has not publicly disclosed the exact sequence of encryption and compression in the On-premises Data Gateway for security reasons.
For 1
ASK and RSA Keys are utilized for encrypting and decrypting stored credentials of data sources in the Power BI Service, ensuring the protection of credentials when stored in Azure. These keys are not used for encrypting the actual data queries or results transmitted between the on-premises data source and Power BI Service.

The data transmission adheres to standard Microsoft security protocols. It is securely encrypted in transit using TLS (Transport Layer Security) to prevent unauthorized access. Additionally, the data may be compressed and optimized for performance before being sent to the Power BI Service.
For 2

The Data Gateway optimizes and compresses data before transmitting it to the Power BI Service to improve performance and reduce network load. However, it does not use VertiPaq for compression in this process.  It does not operate on the data during transmission through the Data Gateway but rather within the Power BI model after data has been imported.VertiPaq uses columnar storage, dictionary encoding, and run-length encoding to optimize performance in memory.

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.