Symptoms
When deploying and executing the OLAP Server or cubes you may encounter the following error:
The sort order specified for distinct count records is incorrect.
Errors in the OLAP storage engine: An error occurred while processing the 'Inventory Transactions Document Count' partition of the 'Inventory Transactions Document Count' measure group for the 'Inventory' cube from the JetGpOlap database.
Server: The current operation was cancelled because another operation in the transaction failed.
Resolution
There are two possible solutions depending on the environment. Please attempt them in order as the first one may fix the issue.
Solution 1:
Change the collation of the Data Warehouse to match that of SQL Server Analysis Services (SSAS)
-
In SQL Server Management Studio, right-click on the instance name and click Properties.
-
Navigate to the Language/Collation page and determine which collation you are using.
In this example, we are using Latin1_General_CI_AS
-
Open the Jet Data Manager, open the project, right click on your data warehouse, and select Edit Data Warehouse.
-
In the Collation drop-down, select the collation that matches SSAS.
Click OK once the change has been applied.
-
Right click the OLAP database and click Edit OLAP Server.
-
In the Collation drop-down, select the collation that matches SSAS.
Click OK once the change has been applied.
-
Deploy and Execute the project.
Solution 2:
Analysis Services will throw a similar error if one of the records that you are trying to create a DistinctCount against contains a special character (such as a hypen or dash) at the end of the record. If this is the case in your data, please try the solution presented here.
-
Determine the fact table that the issue is occurring in.
-
Add a new field to this table named whatever you'd like.
The field name chosen in this example is DocumentNoFix. This field will need to be present at the Data Warehouse level of your project.
Ensure the Data Type is Text. The recommended length is at least 50 with the Unicode check box checked.
-
Apply a transformation to the new field using the Custom option and use the following code:
BINARY_CHECKSUM([Document No])
Where [Document No] is replaced by the field you are creating the DistinctCount measure for.
Deploy and execute the table once this transformation is in place.
-
Change the measure in the cube to use the new field.
The cube should now deploy and execute successfully.
Comments