How to control physical memory usage for TM1

[JimYang]

Recenetly I encounter the folloing issue when i use Excel VBA to update the excel workings dynamicly, Is there anyone who can help me.

I have a Excel working include a formula linked to a Cube with 12 dimensions, this working include nearly 10000 Cells with TM1 DBRW formula. When i run Excel VBA, it will read a record in the existing range of the sheets to dynamicly change some dimension element, refresh the Excel workings and save the result excel file. It takes 16 minutes to read one record and generating one file in some PC and it will show "run time error' when we read the third record and generate the third file. Most of these PC's configuration is RAM 2G and CPU Intel Core(TM) 2 Duo CPU(OS is winXP). Then i use another powerful PC( the OS is Win7) to run this workings(CPU i5 and memory 4G), though it take 8 minutes to generate one file, but it still will show "Run time error" when generate the third file. so i think it is maybe caused by running out of physical memory because i find the physical memory avaiable beome less in the task manager during running. But we find we can run this Excel macro in another PC(a strange PC, i can't find any differnce from other PCs its OS is also Win XP and the memory is 2G , CPU is Intel Core(TM) 2, it can generate all files once. So i watch the performance in task manager when running the VBA, i find the physical memory available change little during runnting, so it will not encounter "run time error" during running. i compared the Tm1P.ini in these PC, i can't find any difference. All these Excel working are running with TM19.4.1/9.5.2 perspective

Could you advise is there any parameter in TM1 used to control physical memory usage so we can generate many files once.

[tomok]

The better question would be why in the world are you using Excel, VBA, and DBRW formulas to dump out all the cube data into Excel files? If you just absolutely have to have this data in Excel you should have a TI process that creates CSV files for you (nothing more than ASCII files using a comma separator). The clients can then open the CSV files in Excel. To answer your question, no, there is no setting in TM1 that can control how much memory Excel is going to use. That is entirely up to Excel.

[JimYang]

Thanks for your reply, As the Excel working are designed as report template which is used for different entities , the entity is one dimension of the cube, we use this Excel VBA to dynamic update the entity and re-calculate the excel workings and save the result file.

My queries is why the PC can generate all Excel files once, whereas other PCs can't. Both of these PCs have same hardware configuration. I can't find any difference between these PCs

[tomok]

My queries is why the PC can generate all Excel files once, whereas other PCs can't. Both of these PCs have same hardware configuration. I can't find any difference between these PCs

How could we possibly know why one PC can run something and another PC not? If they are both doing the exact same thing as regards to TM1 then the problem cannot lie with TM1. TM1 just responds to commands from the Excel add-in, it has no way of knowing what kind of hardware ad/or software configuration is on the PC. If one PC can run and one cannot, I would have to say that the two PC's are not the same and when I say same I mean same hardware, same software, same user logged in, etc.

[JimYang]

How could we possibly know why one PC can run something and another PC not? If they are both doing the exact same thing as regards to TM1 then the problem cannot lie with TM1. TM1 just responds to commands from the Excel add-in, it has no way of knowing what kind of hardware ad/or software configuration is on the PC. If one PC can run and one cannot, I would have to say that the two PC's are not the same and when I say same I mean same hardware, same software, same user logged in, etc.

Actually the VBA in this excel sheet is very easy, only update the dimension value and retrieve value from TM1 Perspective, and i can ensure they are doing the same thing. when update the dimension value and retrieve Value from Tm1 with DBRW, the physical memory available will be decreased in most PCs, so when exceed the physical memory limit, it will show "run time error", but in that PC, the physical memory available will change litte when update the dimension value and retrieve value from TM1, so i think there must be some setting in this pC different from other PCs' setting, so i seek your help to advise which setting can affect the TM1 memory consuming

[tomok]

so i seek your help to advise which setting can affect the TM1 memory consuming

There is no such setting that I am aware of, at least there is no such setting in TM1. If there is a setting in Excel that you can set to contol how much memory it uses that might be an option.

[Alan Kirk]

How could we possibly know why one PC can run something and another PC not? If they are both doing the exact same thing as regards to TM1 then the problem cannot lie with TM1. TM1 just responds to commands from the Excel add-in, it has no way of knowing what kind of hardware ad/or software configuration is on the PC. If one PC can run and one cannot, I would have to say that the two PC's are not the same and when I say same I mean same hardware, same software, same user logged in, etc.

Actually the VBA in this excel sheet is very easy, only update the dimension value and retrieve value from TM1 Perspective, and i can ensure they are doing the same thing. when update the dimension value and retrieve Value from Tm1 with DBRW, the physical memory available will be decreased in most PCs, so when exceed the physical memory limit, it will show "run time error", but in that PC, the physical memory available will change litte when update the dimension value and retrieve value from TM1, so i think there must be some setting in this pC different from other PCs' setting, so i seek your help to advise which setting can affect the TM1 memory consuming

One problem is that you've already jumped to the conclusion that "runtime error = memory usage". You haven't specified exactly what the runtime error you're getting is. (I'm not sure how much clearer the Request For Assistance Guidelines need to be, though I've just modified them to cover this kind of situation. "I'm getting a runtime error" is not helpful. "I'm getting a runtime error 12345 (description) when the code gets to the point where it {does whatever}" is useful.)

One possibility is that It could simply be that there's something wrong in your VBA code. Another is that you're falling foul of this bug in Excel, which Microsloth is too busy creating (ef)fluent user interfaces to fix. The bug described in that article, where you get a runtime error 1004 (either Copy Method of Worksheet Class failed or Application-defined or object-defined error) when you repeatedly copy sheets, is one that I've come across as well and unfortunately there's no way of predicting when it will happen. It may occur after you copy 33 sheets, or it may occur after you copy 10. If you shut down Excel and restart it then it may go back to 33. Or it may not. And that's on a single machine.

However that's a guess since... you haven't specified which runtime error you're getting or the specific circumstances in which it occurs.

The other thing is that while those machines aren't the fastest on the block, mine (at work) is a 7 year old P4 and working with 10,000 cells is not uncommon for me. And it doesn't take minutes. I'd check that the calculation mode in Excel is really manual, not Automatic.

[JimYang]

Alan, What you said is the key point, as i can't find the root cause of the "runtime" error, i have to assume it was caused by memory usage issue according to previous experience.
I run the Excel VBA in my PC again, it takes 15min to generate one file and 30 mins to generate two files, when generating the third file, the system pop up below message
****************************************************************************************************
Microsoft Visual C++ Runtime library
Runtime Error!
Program: C:\Program Files\Cognos\TM1\bin\tm1s.exe
This application has requested the Runtime to terminate it.
Please contact the application's support team for more information
****************************************************************************************************
At the same time , the PF usage has increase from 900K to 2.48G during running through observing Windows task manager. i think this is the root cause to generate "runtime error message!" , but I don't know thy the PF usage will always increase during running. Not only in my PC, but also in other PC, they also have the simular case. Only in one PC, its PF usage will first increased from 900K to 1.06G quickly and then it always maintain 1.06G-1.07G during running, so it can generate all files once.
The number of the foumuals in the worksheet i provided previously is not correct, it should be more than 80000 formulas, most are DBRW formula and some are simple addition /subtration formula and a little is vlookup formula. I can ensure the calculation mode is manual before running.

Our user also run this Excel VBA in win 2003 server, (32G memory), it can generate 20 files once, if generate more than 20 files, it also will pop up "runtime error" message. If we don't encounter the PC which can generate all the files once, we will draw a conclusion, the error is causd by memory issue. But now we think it maybe caused by some setting issue not by running out of memory. Could you give some advise?

[Alan Kirk]

Alan, What you said is the key point, as i can't find the root cause of the "runtime" error, i have to assume it was caused by memory usage issue according to previous experience.
I run the Excel VBA in my PC again, it takes 15min to generate one file and 30 mins to generate two files, when generating the third file, the system pop up below message
****************************************************************************************************
Microsoft Visual C++ Runtime library
Runtime Error!
Program: C:\Program Files\Cognos\TM1\bin\tm1s.exe
This application has requested the Runtime to terminate it.
Please contact the application's support team for more information
****************************************************************************************************

That's why details of the error are important. I assumed that you were talking about a VBA runtime error; you're in fact talking about an executable runtime error. And presumably you're talking about this error showing up on your local PC?

But did you notice which executable?

That's not Excel, that's a TM1 server application. What exactly are you doing here? Are you running TM1 on a Local server, or are you firing up a "remote" server application on the desktop?

At the same time , the PF usage has increase from 900K to 2.48G during running through observing Windows task manager. i think this is the root cause to generate "runtime error message!" , but I don't know thy the PF usage will always increase during running. Not only in my PC, but also in other PC, they also have the simular case. Only in one PC, its PF usage will first increased from 900K to 1.06G quickly and then it always maintain 1.06G-1.07G during running, so it can generate all files once.
The number of the foumuals in the worksheet i provided previously is not correct, it should be more than 80000 formulas, most are DBRW formula and some are simple addition /subtration formula and a little is vlookup formula. I can ensure the calculation mode is manual before running.

Our user also run this Excel VBA in win 2003 server, (32G memory), it can generate 20 files once, if generate more than 20 files, it also will pop up "runtime error" message. If we don't encounter the PC which can generate all the files once, we will draw a conclusion, the error is causd by memory issue. But now we think it maybe caused by some setting issue not by running out of memory. Could you give some advise?

OK, first things first... it's not Excel that's crashing based on that message, it's a TM1 server application. And it's a TM1 application that isn't running on a server box somewhere (other than the 2003 server, which I'll come back to), but rather on the machine that you're running Excel on. If that weren't the case, you wouldn't see the error message on the PC.

You said that the O/S is Windows XP or Windows 7. Safe to say, 32 bit?

If that's the case, any TM1 server application which is running on the PC is going to be limited to a couple of gigs of RAM, regardless of how many gigs are installed on the PC.

Think about what's happening as you run your process. The TM1 Server program that's running on your desktop will probably be generating calculated values each time you loop through a report; rules and consolidations. As it does so, its memory usage will increase because, as we know, a TM1 server application only calculates on demand. When it exceeds the meagre couple of gigs that it has available to it, boom, the tm1s.exe server application will crash. There should be a record of this in the TM1 server application's message log.

The amount of time that it's taking to execute your code will also be a function of this. If the TM1 server application was running on a real server box with Lotsaram and Loadzagrunt (with apologies to two Forum regulars), then you'd be getting decent performance. As it is, if you're running the whole shebang, both server and client, on a geriatric Core 2 PC then yes, I would expect the performance to be sub-optimal.

As for your user who's running it on a Windows Server 2003 box, again it will depend on whether that's 32 bit or 64 bit, and whether you're using a 32 or 64 bit server application.

[JimYang]

What exactly are you doing here? Are you running TM1 on a Local server, or are you firing up a "remote" server application on the desktop?

i am running TM1 on a local server, there are 2 cubes in this TM1 local server, they both have 12 dimensions and one cube is 181M and one is 80M, i think the cube size is not too big.

If that's the case, any TM1 server application which is running on the PC is going to be limited to a couple of gigs of RAM, regardless of how many gigs are installed on the PC.

Think about what's happening as you run your process. The TM1 Server program that's running on your desktop will probably be generating calculated values each time you loop through a report; rules and consolidations. As it does so, its memory usage will increase because, as we know, a TM1 server application only calculates on demand. When it exceeds the meagre couple of gigs that it has available to it, boom, the tm1s.exe server application will crash. There should be a record of this in the TM1 server application's message log.

Yes, our PC is 32 bit and it can only suppport 4G memory, the log in tm1server.log also shows "TM1.Server.Memory AllocateBlockFromOS: alloc(size=8388608) failed: Not enough storage is available to process this demand", base on these information, we can draw conclusion "the error is caused by memory issue" ,

It is strange that there is one PC who is 32bit XP system, it can generate all files once and the PF usage don't increase during running. its PF usage will first increased from 900K to 1.06G quickly and then it always maintain 1.06G-1.07G during running, so it can generate all files once.

Then i think it over, i think it is possible. when generating first file, it may take lots of memory to calculate these DBRW formula and retrieve data from local tm1 server, but in the second time and third time, as it only change the some dimension value, so it should not need so much memory to calculate these DBRW formula and retrieve data from local tm1 server. But i don't know how Windows System control that.

[LoadzaGrunt]

If the TM1 server application was running on a real server box with Lotsaram and Loadzagrunt, then you'd be getting decent performance.

Alan, you always had a way with words Happy Christmas!

[Alan Kirk]

What exactly are you doing here? Are you running TM1 on a Local server, or are you firing up a "remote" server application on the desktop?

i am running TM1 on a local server, there are 2 cubes in this TM1 local server, they both have 12 dimensions and one cube is 181M and one is 80M, i think the cube size is not too big.

I'm assuming that you're citing the size on disk, since cubes in memory rarely have a fixed size. If so, that doesn't have any connection with the amount of memory they consume when loaded since the disk files only store the base level data. The calculations are done as needed so a cube with consolidations may start to take up more memory as the consolidations are queried. A cube with rules and feeders will certainly bear no relation to the size on disk.

If that's the case, any TM1 server application which is running on the PC is going to be limited to a couple of gigs of RAM, regardless of how many gigs are installed on the PC.

Think about what's happening as you run your process. The TM1 Server program that's running on your desktop will probably be generating calculated values each time you loop through a report; rules and consolidations. As it does so, its memory usage will increase because, as we know, a TM1 server application only calculates on demand. When it exceeds the meagre couple of gigs that it has available to it, boom, the tm1s.exe server application will crash. There should be a record of this in the TM1 server application's message log.

Yes, our PC is 32 bit and it can only suppport 4G memory, the log in tm1server.log also shows "TM1.Server.Memory AllocateBlockFromOS: alloc(size=8388608) failed: Not enough storage is available to process this demand", base on these information, we can draw conclusion "the error is caused by memory issue" ,

It is strange that there is one PC who is 32bit XP system, it can generate all files once and the PF usage don't increase during running. its PF usage will first increased from 900K to 1.06G quickly and then it always maintain 1.06G-1.07G during running, so it can generate all files once.

I can't swear that local servers are large address aware, but I can't find anything on the IBM site (actually I could stop typing there and still speak the truth most of the time) or in the documentation that says that they aren't. Consequently my initial thought would be that the PC that can do it has the /3GB switch enabled, while the others don't.

(I will be so glad to see 32 bit software consigned to the dustbin of history over the next few years...)

I still think you'd be better off putting your model on a proper server and working normally. Local servers are really intended for small, personal models or on humongously overpowered PCs.

And happy Saturnalia to you too, Loadza!

[David Usherwood]

Glad to hear you still celebrate the old festivals.
My daughter is currently at the British School in Rome, and they observe Saturnalia there, with the residents serving dinner to the catering staff. But I did see a picture of them serving brussels sprouts - wonder what the (modern) Romans think of that?

[JimYang]

I can't swear that local servers are large address aware, but I can't find anything on the IBM site (actually I could stop typing there and still speak the truth most of the time) or in the documentation that says that they aren't. Consequently my initial thought would be that the PC that can do it has the /3GB switch enabled, while the others don't.

Thanks for your advise. I will try it tomorrow. Though I don't think the PC has the /3GB switch enabled as our company 's PC are ghost from same image, but this is a chance, maybe ITO colleague use the different image to ghost this PC and the image has the /3GB switch enabled.

I still think you'd be better off putting your model on a proper server and working normally. Local servers are really intended for small, personal models or on humongously overpowered PCs.

Your suggestion is right. We have moved this model to windows 2003 server and the server is located in another city, we still need long time to generate the files, due to some reporting deadline(for example, we have this report for 50 entities), if we can generate this report in local PC. it can shorted report generation time.
Anyway, thanks for your help! Happy Saturnalia to you

[JimYang]

I made a mistake from the begining, i excluded the impact of TM1's version. I think higher TM1 version should manager the memory usage better. After today's test, i find the root cause is due to different TM1 version.

The TM1 installed in the PC which can generate all files once is TM1 v9.4.10000.50077_Win86. The PF usage will maintain a fixed level during running
The TM1 installed other PCs are Tm1 v9.4.10200.61478_FP2_Win86 or TM19.5.2. The PF usage will increase during the runing, when running out of memory, it show the error message "Runtime error"

Though i find the cause now, but i don't know how to solve it ? anyone can help? does TM1 have data patch to fix this problem?

[tomok]

Though i find the cause now, but i don't know how to solve it ? anyone can help? does TM1 have data patch to fix this problem?

Yes, I can confirm you've run into the "Oh **[Expletive Removed]**, I don't have enough RAM in my box to run my TM1 model" bug. I've heard rumors of a fix for this bug for years but IBM still hasn't done anything about it. I think if you post a picture of Mark Harmon in your avatar it goes away but I can't confirm that. Perhaps someone else can help us out.

[Alan Kirk]

I made a mistake from the begining, i excluded the impact of TM1's version. I think higher TM1 version should manager the memory usage better. After today's test, i find the root cause is due to different TM1 version.

The TM1 installed in the PC which can generate all files once is TM1 v9.4.10000.50077_Win86. The PF usage will maintain a fixed level during running
The TM1 installed other PCs are Tm1 v9.4.10200.61478_FP2_Win86 or TM19.5.2. The PF usage will increase during the runing, when running out of memory, it show the error message "Runtime error"

Though i find the cause now, but i don't know how to solve it ? anyone can help? does TM1 have data patch to fix this problem?

I believe that memory problems can sometimes be fixed by taking a supercilious, condescending, irritable and intolerant tone with new posters in the forum, but that's only been tested extensively by the occasional expert in the field. (It either fixes memory problems or makes new users less likely to post, I really can't recall which; perhaps someone else can help us out.)

On a more relevant and helpful note, you really do need to ensure that you include all relevant data in questions, and the version is definitely part of that. That having been said, I still think that you need to go back to the drawing board on how you're approaching this. First because you have no control over how different versions will handle memory. It appears that this works for you in an earlier version of 9.4, but not a later version. It could well be that in the later versions Iboglix had to fix locking or other issues which will cause memory usage increases, something that you have no control over, and you can't stay on an earlier version forever. Nor is it a good idea to have a smorgasbord of versions, otherwise you'll run into problems that won't be easy to identify much less resolve.

Some alternative approaches to consider:
- Does this really need to be done in Excel? If so, is there the possibility of running it directly on the remote server box, and merely pulling down the resulting workbooks after it has run?
- Better yet, would it be possible to program the equivalent logic for what the VBA is doing into a TI process which will generate appropriate views of the data, enabling you simply refresh workbooks which point to those views?

The more of this work you can do on the server side, the better the results will be; it'll be faster, and you won't have to worry about trying to shoehorn so much data into machines which really, ultimately, aren't up to working with it.