Cube processing fails due to string store compatibility level

Products

• IT Analytics

Issue/Introduction

Cube processing fails and returns the following error in the SQL Server Agent log:

Error ErrorCode="3238133842" Description="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."

Environment

Release : 2.1.3

Component : Job

Cause

The compatibility level of the ITAnalytics database and dimensions is lower than 1100.

Per Microsoft:

When you upgrade a site database from an earlier version of SQL Server, the database keeps its existing cardinality estimation level, if it's at the minimum allowed for that instance of SQL Server. When you upgrade SQL Server with a database at a compatibility level lower than the allowed level, it automatically sets the database to the lowest compatibility level allowed by SQL Server.

Resolution

To increase the StringStoreLength, follow this procedure:

1. Change the database compatibility level to 1100
2. Set the StringStoresCompatibilityLevel property of the affected dimension
3. Process (Full) the affected dimension and dependent objects
   1. Using SQL Server Management Studio (SSMS), connect to the Analysis Services server hosting the ITAnalytics cubes
   2. In Object Explorer, navigate to Databases > ITAnalytics > Dimensions
   3. Locate the dimension referenced in the error message
   4. Right-click the dimension and select Process
   5. In the Process Dimension window's Object list, right-click the dimension name and select Process Full
   6. Click the Change Settings button
   7. Select the box labelled Process affected objects
   8. Click the OK button
   9. Click the OK button to start processing the dimension and its affected objects
   10. When processing has completed, click the Close button