Exciting ways to run powerBI out of memory

I’ve been working on a project that has a rather large model, composed of a couple larger datasets (260,000 rows x 10 columns) and (270,000 rows x 25 columns).  So not large at all, nothing the tabular model in powerBI can’t handle.  However, the model contains a lot of calculated tables, used to aggregate data in more complicated ways and then relate that data to the appropriate dimensions.  Here’s what my model looks like:

model

Every shaded table is a calculated table.  I would run into this problem where the PBIX file would only let me load smaller datasets, as it would run out of memory when I tried to load anything larger.  Eventually I found out the issue was a relationship on a derived table to a dimension that was causing problems.

create-relationship

Amazingly, that error in the model would consume 7 gig working memory on my local machine, at which point the process would bail.

workingmem

The point of this post is to bring to light the relationships on calculated tables.  You’ll know that the calculation is really running your local machine out of memory when the menu box lets you know that it’s loaded all of the rows, and then fails.  When you load data in the tool, there’s nothing to let you know that the relationship/calculation is actually the problem.  The only way to identify where the problem lies is to remove the relationships or calculated table.

Far better an approximate answer to the right question, which is often vague, than the exact answer to the wrong question, which can always be made precise. -John Tukey
The plural of anecdote is not data. - John Myles White

Recent Posts

RSS PowerBI blog

  • New Power BI Premium summary and workload metrics available in the admin portal April 22, 2019
    We have rolled out a new high-level summary metrics experience in the Power BI Admin Portal . This new experience replaces the four summary tiles you previously saw : CPU, Memory thrashing, Memory usage and DirectQuery with wide range of metrics measuring the summary of usage in the last 7 days to better portray the health […]
  • Live now, full session catalog for Microsoft Business Applications Summit 2019 April 22, 2019
    Time to get excited – the full session catalog for Microsoft Business Applications Summit is here. Explore every breakout session and workshop coming to the conference, taking place in Atlanta, Georgia June 10 – 11, 2019. Get ready to flex your skills – and build new ones – with all things Power BI and beyond. […]
  • E-Mail Subscriptions for Paginated Reports is Now Available April 19, 2019
    E-mail subscriptions is one of the key features used by millions of SQL Server Reporting Services customers today. Our latest release for “New Feature Friday” is the support of Paginated Reports in e-mail subscriptions in Power BI. Now, for the first time, you can schedule an e-mail subscription with a PDF attachment of your full […]