Forum Discussion
46GB PPU Semantic Model: "Memory error: maximum allowable memory allocation for your tier"
I've finally received a different error message, which I assume is closely related to the ones I was receiving earlier. This makes me think that there may be some user settings I could adjust to get around this, though I haven't confirmed this.
Failed to save modifications to the server. Error returned: 'Resource Governing: This operation was canceled because there wasn't enough memory to finish running it. Either reduce the memory footprint of your dataset by doing things such as limiting the amount of imported data, or if using Power BI Premium, increase the memory of the Premium capacity where this dataset is hosted. More details: consumed memory 53639 MB, memory limit 49315 MB, database size before command execution 53084 MB. See https://go.microsoft.com/fwlink/?linkid=2159753 to learn more. Technical Details: RootActivityId: 344ed8dd-99a4-4539-acbb-392119ea557e Date (UTC): 3/23/2024 11:06:16 AM
In the end I've avoided this issue entirely by redesigning my data model to incorporate a second fact table at a different grain. This enabled me to drop an expensive ID column from my original fact table, thereby cutting my memory usage by ~85%.
Hi,
Could you please explain a little bit more on how you incorporated a second fact table at a different grain?
I'm facing kind of the same issue and would like to hear how you resolved it.
Thanks!
Hey amir_mm, rather than explain my situation, let me point you to the article that initially I found confusing but helped in the long run.
It's an article by Alberto Ferrari at SQL BI about Header/Detail models: https://www.sqlbi.com/articles/header-detail-vs-star-schema-models-in-tabular-and-power-bi/. It's a good article, but the first few times I skimmed the article I assumed the best solution for my case was to only use the "detail" table, then use DAX to calculate the measures at the "header" grain. This DAX is simple, but the problem is that it calculated too slowly for the size of my detail table. And the DAX also required to include the header_id column within my Detail table. This high-cardinality column took up a lot of space even after I converted it to an integer. Finally I stumbled on this really helpful comment at the bottom of the article:
As Alberto points out in this comment, the key thing to avoid in a header/detail model is the relationship between the two fact tables. In my case I found that using both a header and a detail table -- without any relationships -- enabled me to remove all my high-cardinality Fact ID columns, which wound up reducing my memory consumption by 90%, despite adding a second large fact table! The model is also faster, and both tables share the same dimensions. (There was one challenge with a many-many relationship between dim_product and my header table, but I handled that with a junction table.)
Hope this background helps you a bit. Good luck!🙂