SSAS MOLAP dimension size limit

After the introduction of the StringStoresCompatibilityLevel property in SSAS dimension after SQL Server 2012, SSAS database designer may try to create a MOLAP dimension with more unique values than what is allowed in previous SQL Server versions.

The article Configure String Storage for Dimensions and Partitions has a very good documentation of this property. The limit for a MOLAP dimension becomes the following according to the article.

The maximum limit for larger string storage is 4 billion unique strings or 4 billion records, whichever occurs first.

I want to mention the 4 billion limit here is meant to be 4B, which is 4x1024x1024x1024=4,294,967,296. There are two issues with such limit.

1. After about 4.1 billion rows are processed into the dimension, it becomes very slow to process new rows into the dimension. It is moving at the speed of 2 minutes per 10,000 rows, and the processing speed will become slower and slower before it can reach 4.2 billions rows

 

2. If you wait long enough to process more than 4.2 billion rows, after 4,294,960,049 rows are processed, just 7,247 rows short of 4,294,967,296, SSAS will crash because of an overflow problem.

So in summary, the practical size of a MOLAP dimension is about 4.1 billion rows, and the upper limit is 4,294,960,049 rows.