Reduce multiple referenced queries sent from PBI to an On Premise Oracle DB due to error ORA-01652

[mkjit256]

Hello,

I have a report where i am querying two tables coming from two on premise Oracle data bases.  Inside power bi itself i have called the DBs in two of the queries, we will call them base_query1 and base_query2. Then I have referenced base_query1 4 times and base_query2 5  times. My idea was that when refreshing the data from power query, i will be only sending the base_query1 and base_query2, however what i am getting to know is that it is not the case. All the queries whether that was a base ro a referenced is being sent to the database, and this is causing the following error on the DB2: ORA-01652: unable to extend temp segment by 128 in tablespace temp.

The end result of referenced queries, are not that complicated, simple transformation, adding new columns, filtering and some appending (no joins happening). 
Is it possible in power query to prevent the referenced tables from querying the DB but instead to query the base tables instead? If not would doing calculated DAX tables help in my case?
Thanks

[Anonymous]

Hi @mkjit256 ,

Based on your description, you don't want to query the original database when refreshing the data. However, when you reference a query in Power Query, it does not cache the results of the base query, but instead re-executes the base query each time you run the referenced query. This can result in multiple calls to the database, as you have encountered. You can use the Table.Buffer function to load the data into memory. This way, the base query will only be executed once, and subsequent references will use the buffered data. For example

let
    base_query1 = ...,
    buffered_base_query1 = Table.Buffer(base_query1),
    reference1 = ... // Use buffered_base_query1 instead of base_query1
in
    reference1

If you are performing a simple transformation, consider creating the calculated table in DAX. This approach reduces the load on the database because the conversion is done inside Power BI.

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[mkjit256]

Hello Thanks for your reply. It is close to what i doing. 
What i was aiming to is:

basequery

let
    base_query1 = ...,
    buffered_base_query1 = Table.Buffer(base_query1),
in
    buffered_base_query1

 referencequery1:

let 
   reference1 = buffered_base_query1,
   transformation1 = sometranfomations
in
  transformation1

query2:

let 
   reference1 = buffered_base_query1,
   transformation2 = othertranfomations
in
  transformation2

hoping to send only one query to the database and the other referenced queries to be obtained from the buffer table.