Analysis Services Tabular Memory Usage

[RonBarrettParticipant Started: 13Replies: 12Total: 25]

When your PowerPivot data model workbook exceeds 400mb you know it’s time to move it to an Analysis Services (tabular) server.  Uploading the model schema is very straightforward with Visual Studio Shell.  However, while processing some tables I’m getting the infamous “Memory error: Allocation failure.”

There are a couple of excellent posts on what happens while processing a table:

http://www.jamesserra.com/archive/2012/05/what-happens-when-a-ssas-tabular-model-exceeds-memory/

http://www.powerpivotblog.nl/woops-i-ran-out-of-memory-while-processing-my-tabular-model/

My question is: Aside from memory-expensive columns to what extent do table relationships have upon memory usage while processing tables?

The data model certainly isn’t huge.  There are 18 dimension tables and 5 fact tables.  Two of the tables have about 2 million rows each and another table has about 6 million.  I’ve removed from the model non-relevant columns that have lots of unique values.

The memory error appears to be happening during the calculation of relationships and calc columns.

Any ideas?

Ron

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Ron,

Interesting question and helpful links.

You have probably been to many sites looking for a solution, have you also been to

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/fccfd169-98c2-4804-8e8c-90d2409d1457/ssas-tabular-memory-error-allocation-failure-not-enough-storage-is-available-to-process-this?forum=sqlanalysisservices

—

I do not have an answer to the effect of relationships upon memory usage while processing tables, but in my thoughts I picture relationships being  built using table-like structures, so when you have data models in your size range, they would definitely consume large chunks of memory.

However, between relationships and calculated columns, I think the problem is more likely the calculated columns because of they “can eat a truly staggering amount of RAM” (p. 208, DAX Formulas for Power Pivot by Rob Collie).

Also, when I took Rob’s Comprehensive PowerPivot Course, I believe he mentioned that calculated columns are processed after the Power Pivot engine performs much of its compression algorithms.

Maybe the relationships could be added after importing the model into SSAS and the definition of calculated columns could be captured in Excel using DAX Studio (I believe I have a reliable query using DAX Studio to extract definitions of calculated columns; let me know if you are interested) and also added later.

– Thomas

[Author]

Posts