Power BI desktop load more rows from Oracle than requested

[Smpona]

Hi all,

How can I ensure that Power BI refresh pulls only the row requested by my queries?

I am building a report, for which the main source table is very big. However, I need only a small part of it.

My queries are all fully folded, and they select data for only one country and for the past 3 months.

I even have a query (not loaded) to count the number of rows in my main queries: 50k rows (and 5 columns).

Suprisingly though, when a close the editor and click apply change, I see that power bi is pulling way more rows (it shows 85 millions and counting)

I have written my query to pull a limited number of rows so I can test the model quickly, write and debug measure/calculated columns fast before executing on the full dataset, but now I have this endless refresh going on

How can I ensure that Power BI refresh pulls only the rows requested by my queries

[v-juanli-msft]

Hi @Smpona

Please check:

1.Did the "Select Related Tables" in the "Navigator" window enable?

If this enables, it will load data from other table which is related to the selected table, thus, the data loaded is more than expected.

2.My queries are all fully folded, and they select data for only one country and for the past 3 months.

How do you write the query, did you write this query in the "SQL statement" box under the connection selection or inside the code in the Advanced editor?

Could you show it here?

3.How can I ensure that Power BI refresh pulls only the rows requested by my queries

As you said, write a query to load limited rows can refresh quickly.

In your scenario with some error, it may load more data and refresh slowly. 

If possible, please end task for power bi desktop, then check your pbix as steps above.

Best Regards

Maggie

[Smpona]

Hi

Thank you for taking the time to reply

To you point 2: I write the query using the visual interface. I know it is fully folded because when I right click on the last step I get the option to see the native query. The native query looks like this:

select "rows"."DATE_CODE" as "DATE_CODE",
    "rows"."PARTY" as "PARTY",
    "rows"."MARKET_ID" as "MARKET_ID",
    sum("rows"."UNITS_COUNT") as "UNITS_COUNT",
    sum("rows"."UNITS_MONEY") as "UNITS_MONEY"
from 
(
    select "_"."DATE_CODE",
        "_"."PARTY",
        "_"."MARKET_ID",
        "_"."UNITS_COUNT",
        "_"."UNITS_MONEY"
    from 
    (
        select "_"."DATE_CODE",
            "_"."PARTY",
            "_"."MARKET_ID",
            "_"."UNITS_COUNT",
            "_"."UNITS_MONEY"
        from 
        (
            select "DATE_CODE",
                "PARTY",
                "MARKET_ID",
                "UNITS_COUNT",
                "UNITS_MONEY"
            from "MINSAT"."FT_TOPUPS" "$Table"
        ) "_"
        where "_"."MARKET_ID" in (35)
    ) "_"
    where "_"."DATE_CODE" > '20180630' and "_"."DATE_CODE" <= '20181031'
) "rows"
group by "DATE_CODE",
    "PARTY",

This query is named "recharge". I have a separate query that I use to know how many row "recharge". The M code for it is:

let
    Source = Table.RowCount(recharge)
in
    Source

It currently tells me that "recharges" has 11k rows. However when I close the editor and press "apply change" power bi says the table recharge is loading 85 millions rows and counting...

So I ended the task

To your point 1: can you do a screen cap? I am not familiar with this option

once again thanks a lot for taking the time to respond

Samuel