Data string length

[jim wood]

Should I rephrase my original question....

is the data string length still limited to 256 characters?

Jim.

[Alan Kirk]

Should I rephrase my original question....

is the data string length still limited to 256 characters?

Jim.

You mean with regard to this thread:
http://www.tm1forum.com/viewtopic.php?f ... 283#p20283

No, but there are limits on whether you can get the string into the cell in the first place. (That's what Lotsaram was referring to when he mentioned Excel 2003.)

[jim wood]

I am reffering to that thread. In the past, previous versions of TM1 have only been able to store upto 256 charactaers when using string elements in cubes. I wasn't sure if Lotsaram was reffering to the length of measure names rather the length of the string data stored against them.

[Alan Kirk]

I am reffering to that thread. In the past, previous versions of TM1 have only been able to store upto 256 charactaers when using string elements in cubes. I wasn't sure if Lotsaram was reffering to the length of measure names rather the length of the string data stored against them.

Yes, I can tell you unequivocally that it will store. But if you slice it to Excel 2003, the extra bit won't show in the cell. If you slice it in 2007, it will.

[jim wood]

Thanks Alan. Is their a theoretical limit to how much text ti can store in a cube?

[lotsaram]

Thanks Alan. Is their a theoretical limit to how much text ti can store in a cube?

In theory it's 64,000 characters per cell or string variable for that matter but there have been reports of variable assignment failing at a couple of thousand characters which could be a pain with setting DataSourceQuery or SubsetCreateByMDX.

Whether the 64K limit is for single byte character sets or double byte I don't know, or even whether it would make a difference. For strings in cube cells I can't honestly imagine that it should make any difference at all since it doesn't make sense to be storing masses of text in a cube since there is no formatting or any other text or word processing type capability and functionality that you would then presumably want. I have seen some very very long financial commentary entered in string cells but never anything even approaching 10,000 characters so 64K seems more than enough. (But then again I have heard people complain that 1 million rows in Excel is not enough so who knows.)

[jim wood]

We have successfully stored 750 characters without a problem. We did however encoutner problems when trying to store 13000 characters. It just seemed to store a very large blank space. We did this using the same character repeated to avoid any issues with carridge returns etc...

[Alan Kirk]

We have successfully stored 750 characters without a problem. We did however encoutner problems when trying to store 13000 characters. It just seemed to store a very large blank space. We did this using the same character repeated to avoid any issues with carridge returns etc...

Yabbut... how did you try to get the information into the cell in the first place? That can often be the weak link, the Excel 2003 limitation being one manifestation of that.

But I'm inclined to agree with Lotsaram on this one; I'm not sure that there's much point storing more than relatively brief annotations in a cell. TM1's not really designed to be a string database and without formatting, line wrapping, etc you end up with huge gobs of characters which will become difficult to read.

[Alan Kirk]

We have successfully stored 750 characters without a problem. We did however encoutner problems when trying to store 13000 characters. It just seemed to store a very large blank space. We did this using the same character repeated to avoid any issues with carridge returns etc...

Yabbut... how did you try to get the information into the cell in the first place? That can often be the weak link, the Excel 2003 limitation being one manifestation of that.

Incidentally, if you were doing it through TI you weren't getting hit with this limitation were you? (Page 9 of the 9.5.0 TI Guide)

TurboIntegrator is capable of handling string data in sizes of up to 8000 single-byte characters at a time. This limit applies when your TI process is performing actions such as assigning a value to a variable or importing individual records of data. Any value or record longer than 8000 singlebyte characters is truncated.

[Alan Kirk]

We have successfully stored 750 characters without a problem. We did however encoutner problems when trying to store 13000 characters. It just seemed to store a very large blank space. We did this using the same character repeated to avoid any issues with carridge returns etc...

Yabbut... how did you try to get the information into the cell in the first place? That can often be the weak link, the Excel 2003 limitation being one manifestation of that.

Incidentally, if you were doing it through TI you weren't getting hit with this limitation were you? (Page 9 of the 9.5.0 TI Guide)

TurboIntegrator is capable of handling string data in sizes of up to 8000 single-byte characters at a time. This limit applies when your TI process is performing actions such as assigning a value to a variable or importing individual records of data. Any value or record longer than 8000 singlebyte characters is truncated.

Postscript post: Just FYI, I tested a DBSW formula in Excel 2007 and it wrote a 13,008 character string up correctly.

I created a separate DBRW formula to point to the cell in the cube, and checked that all of the content was there by parsing off the right-most 8 characters. (Which were unique; the first 13,000 were the character "1" generated by the Excel Rept() function.) They returned correctly. The Len() function also correctly returned the length of the cell's contents as 13,008.

Sadly, TM1 Tools' Paste Values function doesn't work to upload such a string; it runs out of memory. Nor does the standard TM1 quirk of pasting a single value into a single DBRW cell; in the case of a string that size, it just overwrites the formula. DBSW seems to be the only reliable way (short of one of the APIs, which I haven't tested) to get a string that size up to the cube.

Of course, once there the thing was sodding near impossible to view in any practical fashion; see also what Lotsa and I are saying above.

SQL Server has an XML data type. TM1 does not.

[jim wood]

We are looking in to this as our current client has defined strategy commentry fields of up to 750 characters. They may never make it that but we wanted to make sure it was posible. Also the front end entry source will either be EV or the web via .net and the API. Even if they use excel as fall back they are using 2007 anyway.

Thanks for the info though Alan. I didn't consider the TM1 copy paste running out of memory for the problem that I noticed. Something to keep a note of going forward. I wonder if there are limitations of entry via EV or the API? We are installing EV and testing it so I'll post on her what we find,

Jim.

[dubs]

(But then again I have heard people complain that 1 million rows in Excel is not enough so who knows.)

when I first started data analysis I thought this, then i realised i should be using a database

[jim wood]

But I'm inclined to agree with Lotsaram on this one; I'm not sure that there's much point storing more than relatively brief annotations in a cell. TM1's not really designed to be a string database and without formatting, line wrapping, etc you end up with huge gobs of characters which will become difficult to read.

I agree but you know what accountants are like for spouting rubbish. Giving them 750 characters is a lot but we are looking to put most of the text formatting on the front end.

[Mike Cowie]

I wonder if there are limitations of entry via EV or the API?

Hi Jim:

I can't speak for EV, but I think it uses the same C/VB API, which I would assume is limited by what length of string TM1 can store in a cube these days. From what I know of the way TM1 stores string values in cube files, it uses 2 bytes to indicate how many bytes long the string value is that it has stored. I think that means you're theoretically able to store a string of 65,536 characters in a single TM1 cube cell. Now, that is assuming each character in the string takes up only 1 byte, which would be true for ASCII characters, but not for Unicode characters. So, if you used all Unicode characters the number of characters you can type in and store to the cube may be half that or less, depending on which Unicode characters and how TM1 encodes Unicode string data. In addition, if you were reading in and storing many very large strings via the TM1 API you could potentially run up against some memory limitations.

Of course, the theoretical limits and the limits of what humans are willing to enter into and read from a single TM1 cube cell are very different things, as many have already mentioned...

Regards,
Mike

[Alan Kirk]

Thanks for the info though Alan. I didn't consider the TM1 copy paste running out of memory for the problem that I noticed. Something to keep a note of going forward.

Just to reiterate, it was TM1 Tools that threw the memory error message, not TM1 itself. It's probably associated with limitations in the black magic/voodoo that I used to get VBA to read the Windows clipboard more than with TM1 as such, although I haven't chased that down to be sure.

TM1 didn't throw an error, it just obliterated the DBRW when I tried to paste onto it. But then, the "pasting data into a single cell sends it up to the cube instead of overwriting the formula" behaviour has never officially happened in the Excel client because it's not documented.

[jim wood]

I wasn't using excel. I was using architect and word for the text if that has anything to do with the price of fish????

[Alan Kirk]

I wasn't using excel. I was using architect and word for the text if that has anything to do with the price of fish????

A very great deal, given that as per the illustrations above the question of how much of the catch (string) gets into the net (cube cell) will depend heavily on which trawler (user interface) you're using in the first place, to stretch the analogy beyond breaking point.

[Trevor MacPherson]

I know this is a very old thread, but I came here by searching with the same question, and didn't get exactly the answer I was hoping for, so I tried it myself with a quick test.
Thought I'd give anyone else who lands here a tested result - ~2.2M characters copied and pasted from a good text editor to cell, saved>closed>opened, cleared clipboard, copy&pasted back out to said text editor, reporting 30,001 characters "caught in the net".
Not enough for War and Peace, but more than enough for what I was looking for, which didn't need formatting anyway.
Also was able to store & retrieve line returns as well (previously - not in this test) to add some readability. Not suggesting anyone else use cells for pages of text, but... it worked for my purposes...