Building Cubes locks out users

[SteveMellare]

Hi and apologies up front for asking such a basic/ignorant question!

As an E2E solution designer I have inherited a project thats been using TM1 for several years with a fair amount of staff turnover and I am new to the space (and hence have had little opportunity to undertand the application architecture).

I was recently informed that we needed to do some VB scripting to create a copy of closed cube submissions to a new TM1 instance every time we wanted to finalise the cube building process in order to allow our analysts to continue analysing other open cubes - this is apparently because TM1 will lock out users from accessing the application whilst it is building cubes. IT also means we are only using 1 or a multi core machine on windows.

I understand that TM1 is an in-memory application, so I guess this is possibly part of the way the application works but obviously allowing analysts to read open cubes whilst closed cubes are building would be ideal if we could do this natively in the application itself.

Can anyone confirm that we need to resort to VB scripting to create additional instances to allow us analyse and build cubes at the same time or is there are smarter way?

Thx!

[Martin Ryan]

Interesting set up you've got there!

On your specific question, building a cube is instantaneous and if it had to be automated I'd do it via Turbo Integrator not VBA.

But on your wider situation, I can't think of any time when creating new cubes would be part of the usual business process. Closing and opening scenarios within a given cube would be the usual way of handling the kind of problem that having lots of cubes would solve.

What is the business process you've got there? What problem are you trying to solve by having multiple cubes? Someone here might be able to suggest a better approach.

Martin

[PlanningDev]

Not sure if this is related but we are experiencing an issue where we attempt to update dimensions and then load cube data through a chore. The issue is that if the datawarehouse is overloaded and Im stuck in a queue waiting for my sql to execute the chore starts the lock at the beginning of the chore even though I have to wait 15 minutes just to start receiving the fetch from the datawarehouse. This creates longer than usual lockout times and during this period we are also experiencing an overall performance impact.

The data loads easily within seconds into TM1 (once the warehouse provides that data back) but we have issues while the chore is stuck waiting. Very strange on this one so far.

[SteveMellare]

Thanks for the replies guys. Am going to check with the developers to work out responses.

[mutyada]

Finally I can give back to the forum.

I had this same problem where oracle was overloaded and the process is stuck in a queue and users would be locked out. I couldn't find any workarounds on this forum, but did use a previous post on TM1ProcessExecute.exe and TM1ChoreExecute.exe

http://forums.olapforums.com/viewtopic.php?f=21&t=548

To overcome this I created another TM1 instance called Datasources, which would send the SQL to Oracle and then produce a CSV file. Once the CSV file is available the main TM1 instance then imports the CSV file. The process in the Datasources instance is kicked of by the main instance using a *.bat file which calls TM1ProcessExecute.exe and passes the necessary parameters.
This is my Prolog:

#Declare Variables
#Note spaces at the end of the strings so that concatenation produces the correct command line call
#No spaces in BatchFile otherwise ExecuteCommand cannot find file
BatchFile ='Oracle_Update_Ascii.bat ' ;
DatasourceASCIIQuoteCharacter='' ;
AdminServer = 'TM1Server ' ;
TM1Server = 'Datasources_Instance ' ;
UserID = 'DatasourcesUser ' ;
Password = 'DatasourcesUserPassword ' ;
ProcessName = '"Oracle Update Ascii" ' ;
OraclePeriod = CellGetS('System', 'CurrentOraclePeriod', 'String') ;
P0 = '"' | OraclePeriod | '" ' ;

#Create the batch file in the data directory
ASCIIOutput (BatchFile,'"C:\Program Files (x86)\Cognos\TM1\bin\TM1ProcessExecute" %1 %2 %3 %4 %5 %6') ;

This is my Epilog:

#Execute the batch file
ExecuteCommand ( BatchFile | AdminServer | TM1Server | UserID | Password | ProcessName | P0, 1) ;

#Delete the batch file
ASCIIDelete(BatchFile) ;

The next process in the chore simply loads the data from an Ascii file instead of ODBC. You can then always delete the Ascii file afterwards too.
I know it may be a little more difficult to troubleshoot but I haven't had any issues so far, and it's documented.

[SteveMellare]

Thanks mutyada,

The guys are going through and testing this hypothesis against our system.

Appreciate the feedback!

Cheers