2

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 ?

Sananda Dutta
  • 21
  • 1
  • 4
  • 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? https://learn.microsoft.com/en-us/sql/analysis-services/multidimensional-models/olap-physical/maximum-capacity-specifications-analysis-services?view=sql-server-2017#bkmk_vertipaq – GregGalloway Jan 01 '19 at 01:47
  • 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 Jan 02 '19 at 07:30
  • 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 Jan 02 '19 at 07:38

1 Answers1

0

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?

GregGalloway
  • 11,355
  • 3
  • 16
  • 47
  • Hi Greg,I am using Visual Studio 2017 and compatibility level 1400 (SQL 2017 and Azure Analysis Services), but the datatype from the source column is nvarchar(max) in my case. Since the data may contain some special characters , the datatype is nvarchar(max). But in Tabular , due to the limitation of 4000 characters for nvarchar(Max) datatype, the data processing is failing. – Sananda Dutta Jan 04 '19 at 10:39
  • I tested again with nvarchar instead of varchar and it still works. You may want to perform the test above. Is there some character that's causing problems? Are you able to import that query or make that query into a view and import it? – GregGalloway Jan 04 '19 at 16:01