When deploying and executing the OLAP Server or cubes you may encounter the following error:
There are two possible solutions depending on the environment. Please attempt them in order as the first one may fix the issue.
Change the collation of the Data Warehouse to match that of SQL Server Analysis Services (SSAS)
1. In SQL Server Management Studio right click on the instance name and click properties.
2. Navigate to the Language/Collation page and determine which collation you are using. In this example we are using Latin1_General_CI_AS
3. Open the Jet Data Manager, open the project, and edit the data warehouse.
4. In the Collation drop-down select the collation that matches SSAS. Click OK once the change has been applied.
5. Right click the OLAP database and click Edit OLAP Server.
6. In the Collation drop-down select the collation that matches SSAS. Click OK once the change has been applied.
7. Deploy and Execute the project.
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.
1. Determine the fact table that the issue is occurring in.
2. Add a new field to this table named whatever you'd like. The field name chosen here 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.
3. Apply a transformation to the new field using the Custom option and use the following code:
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.
4. Change the measure in the cube to use the new field.
The cube should now deploy and execute successfully.