Varchar(MAX) field being truncated in SSAS cube

Question

In SQL Server 2016 I have a relational dimension table that has a field set to varchar(MAX). Some of the data in that field is over 2k characters. When this data is processed by SSAS the field is truncated. It seems to be truncating at 2,050. I have searched the XML for the whole cube to see if I can find 2050 (or 2,050) but it doesn't show up.

In the Data Source View the field length is -1. My understanding is that this means unlimited. In the dimension definition the field is WChar and the DataSize is 50,000.

I can't for the life of me find why this field is being truncated. Where else can I look?

UPDATE: The issue was with Excel. When we view this data using PowerBI the field is not truncated. So the data in SSAS is fine.

Sounds like you may have read this already? milambda.blogspot.com/2007/09/… — GregGalloway
The WChar length was 50000 for the KeyColumn or NameColumn or both? I would explicitly define a key and name with length — GregGalloway
Try deleting the SSAS database on the server then deploying and reprocessing. I just want to be 100% sure the deployment did actually deploy the WChar length successfully. — GregGalloway
@GregGalloway, Yes, I did read that blog post, thanks. There is a KeyColumn for this field that's an integer DataSize 0. The field in question has WChar 50,000. — Jake Munson
@GregGalloway, I just deployed a new copy of the SSAS database and processed it. Unfortunately the field is still truncated. — Jake Munson

Unknown Unknown · Accepted Answer · 2020-08-12T03:01:38

I have faced this issue while importing an excel file with a field containing more than 255 characters. I solved the issue using Python.

Simply, import the excel in a pandas data frame and then calculate the length of each of those string values per row.

Then, sort the dataframe in descending order. This will enable SSIS to allocate maximum space for that field as it scans the first 8 rows to allocate storage:

df = pd.read_excel(f,sheet_name=0,skiprows = 1)
df = df.drop(df.columns[[0]], axis = 1)
df['length'] = df['Item Description'].str.len()
df.sort_values('length', ascending=False, inplace=True)
writer = ExcelWriter('Clean/Cleaned_'+f[5:])
df.to_excel(writer,sheet_name='Billing',index=False)
writer.save()

Varchar(MAX) field being truncated in SSAS cube

1 Answers