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 pa...
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