TM1 Forum

Hello,
Firs of all i have Cognos Express 10.0 running on Windows server 2008 R2 Enterprise. I have weird issue going on with one of my cube. I have chore scheduled to load financial data through ODBC in "Actual" and "Budget" buckets of the cube at around 4am in the morning. Data loads to "Budget" bucket without any issue. But some time "Actual" bucket has no values in cube even process runs successfully every time. I have seen my log file and server event viewer but there is no error ever recorded at all and all of my process seems to be running successfully however when i open my cube, there is no values available for "Actual" in cube and in the morning when users complain about it then i re-run the process and then cube holds all the Actual values back at that time with no issue at all. since my chore is schedule to run every day so some time i see this issue with cube and some time there is no issue at all. Please let me know if any body has ever faced this type of issue and what would be the solution to avoid it. Thanks in advance for your help!

The process will run successfully if the datasource has no data for whatever reason.
Is it possible that your datasource is on occasion being loaded itself at 4am somedays and therefore appears to have no data in it while your TI runs?

Just stick an asciiouput in your TI and when it has failed you will easily be able to see if there was data in your source at said time.

I am sure the data source has values at that time because there is another cube that has "actual" bucket and has same ODBC process and data loads successfully in that cube with no issue at all. Secondly last week I changed the time to run the chore, just thought it might had to do with other process timing but it does not look like that is the issue. One thing I have noticed from since last week that particular process to load "Actual" data is taking huge amount of time. It used to take about 25 to 30 minutes and now it is almost 55 minutes. I think if there is no values in the out put of the query then it should run with in seconds. Thanks

Can you post your TI script for us to look at?

Just to echo what declanr noted, running something at 4:00 AM, or 5:00 AM, or whatever is meaningless. In a well-designed system, your scheduled processing should include a trigger, or flag-check, to 1) only process when the data is ready, and 2) process again if the previous attempt found no data. This flag can be a record in a relational database, or a single line text file, doesn't really matter which. The first process in the chore checks for the flag, if it exists then process, otherwise quit chore. Last process in chore removes flag.

sh226 wrote:One thing I have noticed from since last week that particular process to load "Actual" data is taking huge amount of time. It used to take about 25 to 30 minutes and now it is almost 55 minutes.

Perhaps there is your clue. If the job that is creating the data source is still writing to it as you are trying to read from it then it could be locking you out. The query is valid, just not executing because the table is locked. I've seen this very situation before, that's why you HAVE to utilize a flagging mechanism where the job that is creating the data source signals TM1 that it is ready. Relying on time alone is just going to lead to heartache for the TM1 admin(s).

Tom, I totally agree with you. Can you please post a line of code that i can use to implement this flag? I have no flag information in my SQL statement. I have attached my TI script for the cube as well. Thanks for your help!

I do want to mention one thing here that yesterday it took the same amount of time 55 minutes but loaded the data successfully so if there is any table lock issue then we would have same issue with cube. As i said earlier, it does not happen every day, it just happens may be couple time a week other wise cube loads values successfully.

tomok wrote:

sh226 wrote:One thing I have noticed from since last week that particular process to load "Actual" data is taking huge amount of time. It used to take about 25 to 30 minutes and now it is almost 55 minutes.

Perhaps there is your clue. If the job that is creating the data source is still writing to it as you are trying to read from it then it could be locking you out. The query is valid, just not executing because the table is locked. I've seen this very situation before, that's why you HAVE to utilize a flagging mechanism where the job that is creating the data source signals TM1 that it is ready. Relying on time alone is just going to lead to heartache for the TM1 admin(s).

sh226 wrote:Tom, I totally agree with you. Can you please post a line of code that i can use to implement this flag?

It's not that simple. I can't just give you a line of code that is going to fix your situation. You have to decide which method you want to use (ODBC table or text file), create the table or flat file, then create the TI process to query for the flag and, if it exists, continue on, or if not, call the ChoreQuit function.

Have you created a wrapper process for these 2 processes? Then use ProcessExecute to run the 2 processes. There is a setting within ProcessExecute that means the wrapper process waits for the first process to finish before executing the second. Then if you add the wrapper to your schedule instead you'll be able to say for certain that the second process shoudl load data. I hope that helps and I'm not covering something that you've already tried,

Jim.

Sorry i am not a developer so please bear with me if i use some non technical language here. First of all i think wrapper is a "call" process that has few processes within one process. is that right? if yes, then I have not created a separate wrapper Process for these two processes however I am using these two processes combined in one chore and my understanding is/was that chore completes the first process in line then starts the second one. If that is not the case then either i will create a wrapper (Call) process or create two separate chores for two processes. Thought? Thanks

Based on your symptoms I think the others are right that the root cause of your problem is your datasource, not your process, but I do have a few comments on the former:

• It looks like you're erasing and reloading all time periods. Is this necessary? You can substantially improve performance by setting a refresh target control variable and wiping/reloading only a single period.
• Why not just set up TIMST to give you the right format instead of pulling an arbitrary format, chopping it up, and pasting it back together in a different format? e.g. (untested) strTimeStamp = TIMST (NOW, '\m/\d/\Y TIMESTAMP: \h:\i' );
• This may be personal preference, but I would would replace the nested IFs with IF/ELSEIF/ELSE in Metadata.
• Minor, but you'll improve performance if you do the SUBST before the IF and save it in a variable (rather than run it as many as twelve times on every row of data).
• Minor, but you'll improve performance if you do one CellGetS, save it to a var, then test var and replace value if blank (rather than do it twice on every dim/element).

If you run your process now, does it also take 55 minutes? I know most of my datasource DBs have batch jobs that run overnight which can lead to locked tables or indices-- they're specifically run overnight for this reason. Either way, that is a very long time-- I would speak to your DBAs to see if they can speed up the view (or your SQL) for you. It may be that they just need to add an index or save your query as a stored procedure. Or it could be as simple as restricting your refresh period.

As far as a wrapper or flag to verify your datasource prior to proceeding, see this thread.

One last thing: are you positive your cube needs 26 dimensions? Def beyond the scope of your question, but I start getting antsy when my cubes get into the 13-14 dim range....

Matt

Thanks all for your suggestions especially Matt. Here is what I have done and so far everything is running smoothly.

I created a one time load process instead of running all period every night. That reduced Process run time significantly (55 minutes to 10 minutes of course).
Secondly, I scheduled all four processes in different time periods so when one process finishes loading data then after few minutes other process starts loading. I do not know if it would make any difference or not. I just thought may be overlap of timing in chore schedule causing table lock.
I have observed closely for few days and everything is working smoothly so far. Thanks again for your help.

Secondly, I scheduled all four processes in different time periods so when one process finishes loading data then after few minutes other process starts loading.

I'm glad this is working for you now, but I think you should refer back to what others had written and finish the job - relying on simply leaving a gap of several minutes between processes doesn't really guarantee safety.

tomok: Just to echo what declanr noted, running something at 4:00 AM, or 5:00 AM, or whatever is meaningless. In a well-designed system, your scheduled processing should include a trigger, or flag-check, to 1) only process when the data is ready, and 2) process again if the previous attempt found no data. This flag can be a record in a relational database, or a single line text file, doesn't really matter which. The first process in the chore checks for the flag, if it exists then process, otherwise quit chore. Last process in chore removes flag.

[bold]jim wood:[/bold]Have you created a wrapper process for these 2 processes? Then use ProcessExecute to run the 2 processes. There is a setting within ProcessExecute that means the wrapper process waits for the first process to finish before executing the second. Then if you add the wrapper to your schedule instead you'll be able to say for certain that the second process shoudl load data. I hope that helps and I'm not covering something that you've already tried,