Forum Discussion

devM's avatar
devM
New Member
4 years ago
Solved

Shared dataset with incremental refresh growing rapidly

Dear community members,

I have an issue regarding shared dataset with incremental refresh.

Dataset contains 1 table with incremental refresh - set by date refreshing last month of data.

The size of dataset is roughly 500MB after initial refresh. 18 months of data, that is 540 days. Row count incoming to this dataset is roughly the same everyday. Therefore each day the dataset should grow by 0.9MB (540 days / 500MB).

Here comes the issue. Each new day the dataset in powerbi.com service grows by 5MB. This seems too much. With this rate, the dataset could grow to unmanable size quite fast.

Dataset is shared for two reports (reports consume it with live connection).
 
Any idea why it is growing so rapidly?
Could incremental refresh somehow cache old data?
Need Help
  • devM's avatar
    devM
    4 years ago

    I mananged to solve the issue with modifying query for the table. Following two steps were removed:

    #"Inserted Date" = Table.AddColumn(TableData, "Date", each DateTime.Date([DatetimeLocal]), type date),
#"Inserted Hour" = Table.AddColumn(#"Inserted Date", "Hour", each Time.Hour([DatetimeLocal]), Int64.Type),

    Instead of power query, these are now calculated in DAX (after data is loaded from database). Hint for this change was warning message (shown on previous screenshot) saying Unable to confirm if the M query can be folded. Now the query is clean and dataset growth is smaller. Final query:

    let
    Source = Sql.Databases(DB_Server),
    Table = Source{[Name=DB]}[Data],
    TableData = Table{[Schema="someschema",Item="sometable"]}[Data],
    #"Filtered Rows" = Table.SelectRows(TableData, each [DatetimeUtc] > RangeStart and [DatetimeUtc] <= RangeEnd)
in
    #"Filtered Rows"

     

3 Replies

  • devM's avatar
    devM
    New Member
    4 years ago

    Thanks for your response v-xiaotang , though I did not find any mention why dataset size should grow so fast.

     

    Table is set up like this:

    History is set for 10 years, table actually contains data just for the last two years.

    This is query for this table:

    let
    Source = Sql.Databases(DB_Server),
    Table = Source{[Name=DB]}[Data],
    TableData = Table{[Schema="someschema",Item="sometable"]}[Data],
    #"Inserted Date" = Table.AddColumn(TableData, "Date", each DateTime.Date([DatetimeLocal]), type date),
    #"Inserted Hour" = Table.AddColumn(#"Inserted Date", "Hour", each Time.Hour([DatetimeLocal]), Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Hour", each [DatetimeUtc] > RangeStart and [DatetimeUtc] <= RangeEnd)
in
    #"Filtered Rows"

     

    Why do you think this dataset grows by 5MB each day when it should grow just by 1MB?

  • devM's avatar
    devM
    New Member
    4 years ago

    I mananged to solve the issue with modifying query for the table. Following two steps were removed:

    #"Inserted Date" = Table.AddColumn(TableData, "Date", each DateTime.Date([DatetimeLocal]), type date),
#"Inserted Hour" = Table.AddColumn(#"Inserted Date", "Hour", each Time.Hour([DatetimeLocal]), Int64.Type),

    Instead of power query, these are now calculated in DAX (after data is loaded from database). Hint for this change was warning message (shown on previous screenshot) saying Unable to confirm if the M query can be folded. Now the query is clean and dataset growth is smaller. Final query:

    let
    Source = Sql.Databases(DB_Server),
    Table = Source{[Name=DB]}[Data],
    TableData = Table{[Schema="someschema",Item="sometable"]}[Data],
    #"Filtered Rows" = Table.SelectRows(TableData, each [DatetimeUtc] > RangeStart and [DatetimeUtc] <= RangeEnd)
in
    #"Filtered Rows"