Page 1 of 1
Slow drill down fixed by dimension save
Posted: Thu Nov 01, 2012 8:47 pm
by Martin Ryan
I've got an issue where the user opens the view, which takes about a minute. If the user then expands a consolidation then it will take the same amount of time to calculate again. Subsequent roll ups and drill downs all take a minute. The usual time saving provded by the pre-calculation doesn't happen.
However if the measures dimension (we haven't tried any other dimensions) is re-saved then the drill up and down becomes near instantaneous, as I'd expect.
Any thoughts on why this might be the case?
Martin
Re: Slow drill down fixed by dimension save
Posted: Thu Nov 01, 2012 9:04 pm
by Duncan P
Hi Martin,
Just to clarify ...
0. Had anything happened to the measures dimension between the server start and the view being opened?
1. If the user then closes and re-opens the view does it revert to 1 minute/gesture, or is it still quick to drill?
2. What happens on a subsequent server restart?
3. Does the underlying cube have any rules?
4. Are you running with any non-default config options?
5. Can you confirm that it is 9.5.1 (as in your profile)?
6. Feeders - what is the situation w.r.t. ...
I know you probably didn't expect the Spanish Inquisition but there you go.
Cheers,
Duncan.
Re: Slow drill down fixed by dimension save
Posted: Thu Nov 01, 2012 9:56 pm
by Martin Ryan
0. No. And it would appear that anything can be saved to have the effect. Looking at the notes a save to another cube's rule file also caused a speed up.
1. Stays fast
2. Will let you know after lunch as the restart takes 45 minutes. However I think the culprit is actually a TI process (import from GL), not a server restart. I'll try both though and see which one does it.
3. Yes and I know they are sub-optimal, however tweaking them caused a TI process to slow right down, so I backed that out. I want to do a re-write (inherited model that's been through about five developers) but it's a major job and not going to happen in the short term
4. Yes:
Code: Select all
[TM1S]
###################################################################################################################################
# General Section
###################################################################################################################################
Language = ENG
ServerName = CXMD
DataBaseDirectory = C:/Program Files (x86)/IBM/Cognos Express/Xcelerator/Custom/TM1Data/CXMD
LoggingDirectory = C:\Program Files (x86)\IBM\Cognos Express\logs\express
###################################################################################################################################
# Client and Server and CAM Section
###################################################################################################################################
ServerCAMURI = http://xxxCETEST:19300/p2pd/servlet/dispatch
CAMPortalVariableFile = portal/variables_TM1.xml
# JW 20120823 Change for SSO
#ClientCAMURI = http://xxxCETEST:19300/p2pd/servlet/dispatch
ClientCAMURI = http://xxxCETEST.int.xxx.cri.nz/IBMCognosExpress/cgi-bin/cognos.cgi
# changed from 900 as per http://www-01.ibm.com/support/docview.wss?uid=swg21450548
ClientPingCAMPassport = 900
DistributedPlanningOutputDir = ./tunit
PortNumber = 19306
ClientMessagePortNumber = 19307
UseSSL = T
# Secuirty for CAM and TM1 mixed secuirty to work
IntegratedSecurityMode = 5
MaximumLoginAttempts=100
#Sets the maximum number of groups that can be created in one ICAS server session.
#JW 20110930 Was 300
GroupsCreationLimit=100
#JW put to 30 on vendor advice
ReceiveProgressResponseTimeoutSecs=30
#memory (in MB) to be allocated when a user accesses a view, the default maximum view is 500 MB on a 64-bit system.
MaximumViewSize=500
## added to fix pulling of data from oracle see http://www.tm1forum.com/viewtopic.php?f=3&t=5448
UseSQLFetch=T
###################################################################################################################################
# TM1 Section
###################################################################################################################################
AllowSeparateNandCRules = T
ParallelInteraction=T
PersistentFeeders=F
# ViewConsolidationOptimizationMethod should be set to TREE in most circumstances. This setting provides the best performance in normal operations and when data is sparse along the view axes.
ViewConsolidationOptimizationMethod=TREE
#New function to run a chore on startup, using this to email
StartupChores=xStartupEmail:zRestart Advisor
#When this parameter is enabled, Windows® trims pages from the Xcelerator process space, but does not page them to disk.
# Disabled due to CE-61
LockPagesInMemory=F
# instruct the TM1 server to automatically define the last dimension in a cube as the measures dimension when a new cube is created on the TM1 server.
DefaultMeasuresDimension=T
#releases the lock on subsets when other requests for the subset are pending, then reacquires the lock after pending requests are processed. This setting improves performance when multiple users attempt to access the same subset, particularly when the subset contain more than 100 elements
UserDefinedCalculations=1
###################################################################################################################################
# Audit Section
###################################################################################################################################
AuditLogOn = F
AuditLogMaxFileSize= 100 MB
AuditLogUpdateInterval=60
5. Actually it's CX10.1. It's a migration from an old TM1 Mid Market edition
6. Again feeders are subobtimal. See 3.
Re: Slow drill down fixed by dimension save
Posted: Fri Nov 02, 2012 12:02 am
by Martin Ryan
After a server restart the expand/collapse was still fast. So I ran the TI process that imported the data. Still fast. Then I ran the process that has nothing but a "SaveDataAll" in it. That makes it dog slow. Re-saving the dimension returns to normal behaviour.
I've replicated that a couple of times. So the culprit is the save data all.
So as a workaround I've put in a bit of code that inserts and deletes an element from the dimension after doing the save data all. That seems to have done the trick. However I'd really like to know why it's doing this.