How to include columns with length greater than 4000 characters in SSAS Tabular Model?

1
votes

A few of the columns in the source sql view has column length greater than 4000 characters. These are columns which contain some user comments and needs to be inclided in my Tabular SSAS Model. But whenever the character length is greater than 4000 characters, I am getting error while processing the model. I found out that Tabular does not support column length greater than 4000 characters. Is there any way to bypass this issue ?

1
ssas-tabular
Hmm. I don’t see that limitation listed here. What’s the error you are getting? What datatype is your column and what type of database? docs.microsoft.com/en-us/sql/analysis-services/… – GregGalloway
Hi Greg, the datatype of the column in Tabular model is Text and the datatype of the column in the source is NVarchar(Max). The source is SQL database. – Sananda Dutta
This is the error message that I am getting while processing the data: "Failed to save modifications to the server. Error returned: 'The size of a data value in table 'Supplier Profile' column 'PaymentTerm' was too large to fit in that column. The current operation was cancelled because another operation in the transaction failed. '." – Sananda Dutta

1 Answers

0
votes

In Visual Studio 2017 in compatibility level 1400 (SQL 2017 and Azure Analysis Services) I was able to import this query both in legacy data source mode (regular SQL driver) and in the new Power Query mode.

select cast(replicate(cast('A23456789' as varchar(max)),1000) as varchar(max)) as str, 
len(cast(replicate(cast('A23456789' as varchar(max)),1000) as varchar(max))) as len

What are you doing differently? Are you using an older compatibility level or something?

I also would question the analytical value of importing huge text strings into an SSAS Tabular model. Can you explain the value of those columns? Can you possibly parse out the relevant pieces of info from the long strings?