Scalable String Storage

This is a new storage method introduced for dimension attributes and distinct count measures in SSAS Multidimensional Online Analytical Processing (MLOAP) engine. In previous versions of SQL Server, the string storage file is not possible to grow over 4 GB. With SQL Server 2012 version you have a slight flexibility with this where you have the luxury of configuring it so that you can have a string storage file of more than 4GB.

 

String Store

In an SSAS multidimensional database, strings are stored separately from numeric data. This architecture is to allow for better performance of characteristics of the data. String data is usually found in dimension attributes which have names or descriptions etc. Apart from those string values, it is also possible to have string data in distinct count measures. As said before, in the previous versions for these String Store files, there is upper limit of 4 GB. Whenever, you hit the limit, file system error will be generated and your CUBE processing will be halted.

 

Resolution in SQL Server 2012

In SQL Server 2012, you have a solution where you can set the storage option for a dimension.

· Using SQL Server Data Tools (new tool as a replacement for SQL Server Business Intelligence Development Studio (SSBIDS)) open the project that contains the dimensions or measure groups which you want to adjust to eliminate the error.

· In Solution Explorer, double-click the dimension.

· In Dimension Designer get the Attributes pane by pressing F4.

· Select the parent node of the dimension. If the dimension is Product, select Product and not one of the child attributes like English Product Name or Class etc.

In the Properties pane, in the Advanced section, set StringStoresCompatibilityLevel to 1100 which is 1050 now.

You can set this option from the Cube portioning option as well.

So this configuration is only for dimension wise and not for the cube level which means you can set your configuration to the only required dimensions or measure groups.

This storage configuration will apply once you process your cube only.

 

StringStoresCompatibilityLevel

So though you can type any value here only meaningful value will be 1050 or 1100. Not sure why it was not given in a drop down so that users will not have to remember these numbers and drop down will make users not to make any mistakes.

As 1050 is the default value, when you have that value string storage has the maximum String Store file limit of 4 GB.

When it is set to 1100 does not mean that you have no limits but the limit changes to 4 billion unique strings per store. Still you have an upper limit.

Important Notes

Storage is a concerning factor in 1100 compatibility level. Following is the comparison of file sizes of String Store files of two SSAS cubes; one cube is with 1050 compatibility level while the other cube has compatibility of 1100.

For the following example, two cubes were created using DimCurrent, DimCustomer, DimDate, DimProduct, DimPromotion, DimSaleTerritory and FactIntetSales in AdventureWorksDWDenali database with different StringStoresCompatibilityLevel.

Storage sizes of each dimension are measured and increment is also calculated.

 

Dimension	StringStoresCompatibilityLevel	Increase
1050	1100
Currency	216 KB	488 KB	126%
Customer	9.71 MB	12.0 MB	23.6%
Date	1.66 MB	3.01 MB	81.3%
Product	2.44 MB	4.64 MB	90.1%
Promotion	1.02 MB	2.15 MB	110%
SaleTerritoty	344 KB	752 KB	118.6%
FactInternetSales	8.10 MB	9.28 MB	14.5%

So you can clearly see that compatibility level 1100 needs more storage. Another point to remember is, you will not be able to revert back to the 1050 compatibility level. So plan carefully before converting it from 1050 to 1100 and make sure you select the only necessary dimensions to have 1100 for the StringStoresCompatibilityLevel.