does data get copied into Power BI service from Live connect or DirectQuery

[Anonymous]

Does data get copied into the Power BI service when using a Live connection or DirectQuery?  I know that data does not get copied into the dataset in these two connection types.  But, does data get copied into the service in another storage area, such as the cache?  I do not see how no data can get copied into the service at all in these two connections types.  At the very least, I think data has to be copied into the cache.  Is there official Microsoft documentation stating such?  I can't seem to find any documentation about non-dataset data storage in these two connection types, e.g., the cache.

[bcdobbs]

When a visual is refreshed a query is sent to the source to retrieve the information needed. You can see this query if you refresh the visual while recording using performance analyser in Power Bi.

So for example if you had a graph that displayed quantity of sales by country running against a SQL server it would send a GROUP BY query to get the total sales for each country. That information does get sent to the service to render the visuals but is not stored.

I think the only exception to that is if you pin a visual to a dashboard (not a report) you can set it to send the query in advance so the dashbaord loads quickly.

[Anonymous]

@bcdobbs In the return of data to the service, doesn't the data have to sit somewhere in the service, at least temporarily?  After all, the service has to "know" the data values in order to generate each visual.  And, in an export operation out of a visual in the service, the data is already prepared by the service (as opposed to, say, outputting an Excel file with a query inside of it).  So, at least in an export operation, it has to already be sitting somewhere inside of the service.  Since we know that no data lives in the dataset, my only guess would be somewhere in the cache.

I'm being particularly careful on this because I have a client that requires that absolutely none of their data be copied into Power BI (whether it be disk, RAM, or cache).

[bcdobbs]

Ok in that case yes there is movement of data. You connect to a remote source with direct query which is the storage engine but the results from that are passed back in memory and processed in the formula engine which sits inside the service.

If your client can't have any movement of data from on prem services you could look at power bi report server which runs on prem and is available with premium capacity.

[Anonymous]

@bcdobbs That was my suspicion.  Do you know of any official Microsoft documentation on this?  I can't seem to find any.  (And, any time that the documentation gets specific -- which is rare -- it talks only about the dataset.  It makes no mention of the cache or how often it gets cleared.)

[bcdobbs · Accepted solution]

Best I could find is an article from sqlbi:

https://www.sqlbi.com/articles/formula-engine-and-storage-engine-in-dax/

Near the end there is a section called Tasks of the Formula Engine which talks about the datacache. This happens regardless of what storage engine you use so should be consistent. I can't find any reference but my assumption is the cache exists long enough to render the visual.

[arvindsingh802]

Please refer Use DirectQuery in Power BI Desktop - Power BI | Microsoft Docs

[Anonymous]

@arvindsingh802 This does not help answer my question.  All PBI documentation I could find about this only talk about the dataset (the RAM?).  (The particular link that you gave doesn't say this specifically, but other documentation does.)  It says nothing about other storage areas (e.g., cache).  Just thinking about how this could be done at all, I don't see how Microsoft can avoid storing the data nowhere.  I think it has to at least involve the cache (and at least hold the data there temporarily).