Suggestion on Large Data Model

[krixtsup3r]

Hi All, just wanna ask for your suggestion with large data models as currently we are hitting up the Capacity Limit which really slows down our entire space. We have these data that is around 20 - 50M rows that we needed to load as the stakeholders wanted to see this per user data. We have already tried to optimize these datasets as much possible and we are already at limit of it. 

With that being said what is the best way on doing this? Currently what we do is we create the source from a Dataflow, then connect the PowerBI Desktop for that. The dataflow only uses a 100K CPU usage but when it was transitioned to Desktop it goes up to 4M+ for the last 14-days, note as well that we have a 100+ users and can be more. 

Does creating a direct query access to the dataflow will help? or directly accessing the datasource from the warehouse then direct query it will be much better? 

[krixtsup3r]

Thanks @lbendlin, that is already in consideration but the cost is too high for the upgrade. 

Is there any best way on reducing this? does removing some measures, functionalities (like dropdown switch of measure), visuals, calculated columns and reducing data modelling within power query will help it?

I have looked all over the place but can't seem to fit to what I am experiencing at the moment. 

[lbendlin]

Measures play no role in dataset size.

Consider using partitions. Basically incremental refresh but controlled manually. That way you can refresh smaller parts of the dataset one at a time.

[krixtsup3r]

Calculated column does right?

Do you have any documentation or similar case/topic that shows how partitions work? Thank you very much.

[lbendlin]

yes, Calculated Columns do.  Use DAX Studio to identify the columns that contribute most.

Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Learn

Advanced incremental refresh and real-time data with the XMLA endpoint in Power BI - Power BI | Micr...

[lbendlin]

Please provide more information. What is your Capacity SKU. What is the uncompressed size of the dataset. What is the eviction count. Is the dataset stored in large format. are you considering incremental refresh. etc .

[krixtsup3r]

Our capacity SKU is P1/A4. 

Uncompressed size 5,505MB.

Not sure where to find the evicted count but here are the details:

We can do incremental refresh but majority of our data changes.

[lbendlin]

Yes, looks like your P1 is becoming too small for the dataset size. Capacity size must be 2x+ of the largest dataset size, otherwise refresh won't have enough space. Consider moving to P2 or higher.