cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mozaid Frequent Visitor
Frequent Visitor

Is any data stored on Power BI service in case of Direct Query?

Hi Team,

 

Can you confirm what data is stored in Power BI backend service in case of Direct Query?

I am publishing dashboards using pbix file performing Direct Query on my SQL database. Is any data from SQL stored on Power BI or only the Query and connection strings?

1 ACCEPTED SOLUTION

Accepted Solutions
PowerDAX
Advisor

Re: Is any data stored on Power BI service in case of Direct Query?

Hi mozaid - please see whitepaper on security here:  http://download.microsoft.com/download/4/8/C/48CFCF8A-2025-4B97-B249-7B505E26E7ED/Power%20BI%20Secur...

 

Based on this whitepaper, some data is cached in the cloud.  There is a recent discussion about this in which these statements were extracted:

 

The DirectQuery and Data In Process sections of the whitepaper talk about “visualization data” being cached regardless of whether the dataset is DirectQuery. 
 
“The data associated with a Direct Query is stored by reference only, which means source data is not stored in Power BI when the Direct Query is not active (except for visualization data used to display dashboards and reports, as described in the Data in process (data movement) section”
 
“When any of those events occur and put data in process, the Data Role in the Power BI service creates an in-memory Analysis Services (AS) database and the dataset is loaded into that in-memory Analysis Services database. Whether the dataset is based on a Direct Query or not, data loaded in the AS database is unencrypted to allow for access by the Data Role, and held in memory for further access until the Power BI service no longer needs the dataset. Once data is acted upon, which includes initially loading data into Power BI, the Power BI service may cache the visualization data in an encrypted Azure SQL Database, regardless of whether the dataset is based on a Direct Query.”
 
When describing the Data Storage Architecture the whitepaper mentions a time frame for persisting an in-memory Analysis Services database. Is this also true of DirectQuery datasets?
“An in-memory Analysis Services tabular database is created, and the data is stored in-memory for approximately an hour (or until memory pressure occurs on the system).”

 
powerdax.com
4 REPLIES 4
Super User
Super User

Re: Is any data stored on Power BI service in case of Direct Query?

@mozaid with directquery feature each time you interact with visual a query is generated and sent back to data source on-premise to be executed. I think there maybe some very low level of caching at the power bi service level but as per my understanding no data is imported into power bi cloud.

mozaid Frequent Visitor
Frequent Visitor

Re: Is any data stored on Power BI service in case of Direct Query?

Can someone confirm this 100% if no source data is hosted on cloud service or if some dta is hosted then what?

 

Need to dig deeper as related to compliance for a customer.

PowerDAX
Advisor

Re: Is any data stored on Power BI service in case of Direct Query?

Hi mozaid - please see whitepaper on security here:  http://download.microsoft.com/download/4/8/C/48CFCF8A-2025-4B97-B249-7B505E26E7ED/Power%20BI%20Secur...

 

Based on this whitepaper, some data is cached in the cloud.  There is a recent discussion about this in which these statements were extracted:

 

The DirectQuery and Data In Process sections of the whitepaper talk about “visualization data” being cached regardless of whether the dataset is DirectQuery. 
 
“The data associated with a Direct Query is stored by reference only, which means source data is not stored in Power BI when the Direct Query is not active (except for visualization data used to display dashboards and reports, as described in the Data in process (data movement) section”
 
“When any of those events occur and put data in process, the Data Role in the Power BI service creates an in-memory Analysis Services (AS) database and the dataset is loaded into that in-memory Analysis Services database. Whether the dataset is based on a Direct Query or not, data loaded in the AS database is unencrypted to allow for access by the Data Role, and held in memory for further access until the Power BI service no longer needs the dataset. Once data is acted upon, which includes initially loading data into Power BI, the Power BI service may cache the visualization data in an encrypted Azure SQL Database, regardless of whether the dataset is based on a Direct Query.”
 
When describing the Data Storage Architecture the whitepaper mentions a time frame for persisting an in-memory Analysis Services database. Is this also true of DirectQuery datasets?
“An in-memory Analysis Services tabular database is created, and the data is stored in-memory for approximately an hour (or until memory pressure occurs on the system).”

 
powerdax.com

Re: Is any data stored on Power BI service in case of Direct Query?

Would it be correct to state that the "visualization data" would remain in the Microsoft Cloud (in Azure SQL Database) after the user has ended their session?

 

For example, my dataset uses DirectQuery to an on-premises SQL Server. I'm happy to know that in-process data that's in memory is either droppped or expires in an hour, whn I don't use it. But, when I log back into PowerBI, all my visualizations using the DirectQuery are instantly visible. A few seconds later, they refresh with the latest data obtained. But, to draw that intial visualization, the data must have come from somewhere, presumably the MSFT Azure SQL DB?

 

If so, is there any way to ensure that data is cleared after a session? Some, if not most, of the clients I know would not engage PowerBI if any data is stored in Microsoft's cloud. For example, a report showing Revenue by Vendor. Sure, the detail rows are not there, but they would not want the aggregates stored either. No matter how secure it may be.

 

Any thought?