Forum Discussion

Smpona's avatar
Smpona
Regular Visitor
7 years ago

Power BI desktop load more rows from Oracle than requested

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

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      Smpona
      Regular Visitor
      7 years ago

      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