Troubleshooting a Data Type Issue during SSAS Processing


I have built a cube on a series of views in SQL Server that mimicked similar views in Oracle.  The views create a “virtual star schema” over an operational data store that consists of imports from various mainframe sources.  There is no plan at the moment to move to a stored star schema, so views are being used to fill the gap.  During the development cycle issues arose with performance from the Oracle environment.  While those issues were being investigated, the data store tables used to support the SSAS project were moved to a SQL Server instance for the interim.  We retained the views to minimize the impact for delivery once Oracle issues were resolved.

I am currently deploying changes only from Visual Studio (see image below for Project Configuration settings).  After that, I used SSMS with XMLA to process the dimensions and measure groups in a targeted fashion.



The issue was that along the way, the data types had to be adjusted to support the more robust data typing in SQL Server.  As a result of the data type changes, I started to see errors during processing which noted that the data was being truncated.


I tried a number of things to resolve this particular error.  Along the way I fixed other various but unrelated issues.  Here are the activities I tried to resolve the issue which did not work.

  • Rebuild all dimensions
  • Reviewed ALL relationships, keys, and measures for offending data types.  Some of this was reviewed through the BIDSHelper tool – Dimension Data Type Discrepancy Check.
  • Reduced data set.  This returned the error faster, but also allowed for quicker processing during the resolution of the issue.
  • Eliminated relationships and measures.  As this issue was clearly in the measure group, I removed the relationships and the currency measure from the measure group.  This worked.


The last step was the key piece – by eliminating the relationships, I was able to determine which reset all of the relationship values.  While I had done this manually, it appears that I needed to remove all of the relationships and then add them back in as I processed.  This appears to have resolved the issue I was encountering.

The moral of this story is that the BIDSHelper tool showed me where the error was likely occurring, but as is the case with many of the BI tools in Visual Studio, the change was not propagating to all of the locations required within the XMLA until I retouched them all.  Now if I can just get the memory to release properly, but that is a topic for another day.