Forum Discussion

gauravg3's avatar
gauravg3
Icon for Helper I rankHelper I
4 years ago
Solved

Static and Dynamic Data for report

Hi, I have a trend report for incidents reported in last 12 months. The volume per month is >30,000 on an average. As a result the data size is getting huge. So, want to check if its possible to k...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi gauravg3 ,

     

    According to your statement, I know your data source is Servicenow. It is a web data source.  Incremental refresh is a good way to load data in dynamic date range. However web data source doesn't support query folding, so incremental refresh may show bad performance due to large size of data.

    For reference: Sources that support folding

    Here I suggest you to do some transform in Power Query Editor.

    Filter static table in the previous 11 months.

    Then use Group BY to get aggregate values.

    Filter dynamic table by This Month.

    You may refer to this blog to learn more details about how to optmize your report performance.

     

    Best Regards,
    Rico Zhou

     

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

gauravg3's avatar
gauravg3
Icon for Helper I rankHelper I
4 years ago

Hi, sorry should have explained in a more clear way.

Thing is I am extracting data from Service Now and using power automate to get the data in Power BI (via Outlook). Since the data size is getting higher, data is not getting attached to outlook.

As the count for last 11 months would remain unchanged, so was thinking if within Power BI , is there a way to add those values as numbers (may be in table, or something similar) and only apply the query to get the data for last 1 month; this way the data size would reduce to only 1 month data. 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi gauravg3 ,

 

According to your statement, I know your data source is Servicenow. It is a web data source.  Incremental refresh is a good way to load data in dynamic date range. However web data source doesn't support query folding, so incremental refresh may show bad performance due to large size of data.

For reference: Sources that support folding

Here I suggest you to do some transform in Power Query Editor.

Filter static table in the previous 11 months.

Then use Group BY to get aggregate values.

Filter dynamic table by This Month.

You may refer to this blog to learn more details about how to optmize your report performance.

 

Best Regards,
Rico Zhou

 

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