Change String Stores Compatibility Level

The resolution for this issue requires SQL Server Analysis Services 2012 or higher on your database/cubestore server.

Overview

When executing a large dimension, the following error message is displayed:

File system error: A string store or binary store with a compatibility level of '1050' is at the maximum file size of 4 gigabytes. To store additional strings, you can change the StringStoresCompatibilityLevel property of the associated dimension or distinct count measure to '1100' and reprocess. This option is only available on databases with a compatibility level of '1100' or higher.

Resolution

Since there currently is no way to change the StringStoresCompatibilityLevel property in Jet Data Manager directly, you would need to do this with a script.

1. Create a new script action in JDM

   

2. Copy the following text into the script window

    <Script xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300">
   					     <AddNameSpace Alias="my" AddNameSpace="http://schemas.microsoft.com/analysisservices/2003/engine"/>
   					     <InsertEnd Node="//my:Dimension" SearchType="Path" Operator="All">
   					     <ddl300:StringStoresCompatibilityLevel>1100</ddl300:StringStoresCompatibilityLevel>
   					     </InsertEnd>
   					     </Script>

   

3. Give the script a name, then click OK.

4. On the dimension to which you want to add the script, right-click and select Edit Dimension.

   

5. Modify the setting for Inline Script to Change Storage Settings.

   

6. Deploy and execute the dimension.

Once deployed, the compatibility level has been changed, and the dimension can now handle 4 billion unique string combinations instead of the default 4Gb of data in total.