Handling Large Volume of Non-Summarised Data in Power BI Dashboard

[Pragati11]

Hello Experts,

I have a scenario in Power BI on which I need some suggestions.

We have a Power BI dashboard which is built from a high volumes of multiple data files. In this dashboard, as per requirement, we can't summarise our data as we need to show information at a customer level and all the activities related to the customer.

Therefore, we are bringing in everything on Power BI Desktop, creating dashboard and then publishing it online to users.

When the dashboard is shared in Power BI Service, it takes some time to load the report elements. Then we have got some slicers for users to use, along with some search capability to search for an individual.

Whenever this search is used it takes few minutes (~5 minutes), to get the search results as the search is happening on millions of rows. So, from the user point of view, the performance of the dashboard seems to be slow.

Some high-level metrics:

1. 4 files used on the dashboard - excel files
2. Each file is nearly 8 GB
3. Number of columns in every file is between 80 to 100

I understand that we are missing something around modelling our data and using excel files rather than using any database solution.

I appreciate if I can have some suggestions around how should I approach to this issue.

Kind Regards,

Pragati

[v-xicai · Accepted solution]

Hi @Pragati11 ,

If the columns of each files have the similar structure , for example the columns' name are Jan, Feb, Mar,,,etc, you may select on all columns at the same time and choose "Unpivot column" feature to decrease the number of columns.

If the table of each files have the similar structure , you may use Append or Merge feature to summarize these tables, see Append vs. Merge in Power BI and Power Query . Or you may use combine file to combine them into a single logical table

You may improve the performance of the large dataset, see the articles Power BI performance best practices , Power BI Performance Tips and Techniques.

And you also use Incremental refresh for your data source, the Incremental refreshes only data that has changed.  Since incremental refresh is Premium only feature, so you may need to purchase Power BI Premium capacity for your organization, see How to purchase Power BI Premium. Incremental refresh policies are defined in Power BI Desktop and applied when published to the Power BI service.  See more detail steps: Incremental refresh in Power BI Premium .

Best Regards,

Amy 

Community Support Team _ Amy

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

[Greg_Deckler]

Can you use Aggregation Tables?

[Pragati11]

Hi @Greg_Deckler ,

If I undesratnd Aggregation is Summarising tables. But in my scenario, we are shwing details at an individual level on the dashboard. So, aggregation is not an option.

Regards,

Pragati