How to increase search performance -some general tips needed

[abcuser]

Hi,
My system:
- Cognos TM1 v9.5
- Cognos BI Report Studio as reporting tool accessing TM1 cube.

One of my cube is loaded with fresh data each day. Then end-users accesses reports in Cognos BI that gets data from TM1 cube. In the morning when reports are run from the users for the first time, report takes 47 seconds to show the results. Task Manager on Windows shows 100% using tm1sd.exe process for 95% of the time. But when ever report is executed secondly, thirdly etc it runs in 3 seconds. There is probably some cache used to store already executed reports, which is OK.

I am concerted about first execution time of report and I would like to decrease executing time as much as possible - ideally would be sub 3-seconds.

I am a newbie, where should I start to improve performance problems in TM1. What should I check? Are there any general rules or tips? Is there any document with step-by-step instructions what to check to improve performance of cube? Is there any way I could pre-calculate some portion of cube?
Thanks

[lotsaram]

The ViewConstruct TI function should fix things for you. This will pre-calculate and cache the view for your users.

[MarioRubbo]

lotsaram: Currently C8 BI doesn't see any Views or Subsets that are defined in TM1. Will the the cache created by ViewConstruct() benefit a query that is hitting the raw cube - not a View?

[David Usherwood]

ViewConstruct calculates what you ask for, and the TM1 engine caches calculated results until a number changes in the cube or in a cube 'upstream' from that cube (linked via one or more feeder links). This is why your numbers should be faster.
I myself am not that convinced about how worthwhile TM1's building of views is - it's probably just assembling the arrays to push down the client server connection. The cacheing of calculated results, OTOH, is where it shines.
My _impression_ is that TM1 is probably the fastest server for Cognos 8 around at present. It ought to be when compared to relational sources, because of the calculation cache. I've heard some rumours that the interface, being MDX-based, could be faster, but it must depend on how complex the MDX 'query' is. Certainly when I looked some time ago at TM1's MDX engine (trying it out to transfer data and structures between TM1 servers - when, oh when, oh IBM?) it didn't scale.

[abcuser]

The ViewConstruct TI function should fix things for you. This will pre-calculate and cache the view for your users.

I am back to this problem after some months. I have one of the cube that has some reports that have a response time for several minutes. This reports run from Cognos BI 8 Report Studio accessing data to TM1 cube. As I see Windows Task Manager 95% of the time tm1 process is using a CPU. So I am assuming it is a performance problem on TM1 site.

I am looking at the syntax of ViewConstruct TI function and I see I have to specify cubename and viewname. I am just wondering how can I create a view to speed up process? If I understand correctly Cognos Report Studio creates MDX which is executed at TM1 server. Is there a way to know how I must design view at TM1 to bust performance of Cognos Report Studio reports?

How can I know that Cognos Report Studio has benefited from using view (is CPU on Windows the only way to monitor)?

Any help/guide lines are much appreciated?

[David Usherwood]

Right-click on your cube, select 'Export to Text File', then select all the elements in all the dimensions your users may want to view. Make sure 'Skip Consolidated Elements' and 'Skip Rule Calculated Values' are _unchecked_, but 'Skip Zero/Blank Elements' is checked. Save the view, then run the ViewConstruct. Use Performance Monitor to see how large the calc cache has grown, and use the Message Log to see how long the job took. Schedule accordingly.

[abcuser]

Use Performance Monitor to see how large the calc cache has grown

According to "TM1 Operational Guide" Performance Monitor is started in TM1 Architect by running Server | Start Performance Monitor. But this option is grayed out in my sample. How to enable performance monitoring?

[lotsaram]

Use Performance Monitor to see how large the calc cache has grown

According to "TM1 Operational Guide" Performance Monitor is started in TM1 Architect by running Server | Start Performance Monitor. But this option is grayed out in my sample. How to enable performance monitoring?

If this option is greyed out then you do not have full administrator access. Check to see if your tm1p.lic file is installed.

[abcuser]

If this option is greyed out then you do not have full administrator access. Check to see if your tm1p.lic file is installed.

I have now set PerformanceMonitorOn=T in Tm1s.cfg file and restarted TM1 server now I see data in LATEST element of }TimeIntervals dimension, but others are still 0 after several minutes.

Use Performance Monitor to see how large the calc cache has grown

I have searched all }StatsXXX cubes and can't find "calc cache" parameter. Can you please specify actual name of measure and cube name where this info is stored.

[David Usherwood]

Worth reading_the_manual on Performance Monitor. The timeslices are updated every minute for 2 hours, then every hour for 2 days. Look at Memory Used for Calculations in }StatsbyCube.

[abcuser]

David,
I have read a "TM1 Operational Guide" on topic "Performance Monitor", but I am getting "Memory Used for Calculation" = 0 and performance of report from Cognos BI Report Studio hasn't improved.

I did the following:
1. Right click on my cube NAB_PREMIJE and selected "Export as Text Data"
2. Unchecked "Skip Consolidated Values", Skip Rule Calculated View is unchecked and grayed out, Skip Zero/Blank Values is checked
3. From Dimension Element section I have selected some members for dimensions, this changed "All" selection to some names of elements.
4. Saved view as public view with name "test".
5. Created TI process. Left all settings unchecked and went to Advanced | Prolog tab and wrote function ViewConstruct('NAB_PREMIJE', 'test');
and run a process. Process completed successfully.
6. Run some reports from Cognos BI Report Studio (performance hasn't changed)
7. Then have waited for minute or two and checked in TM1 Architect measure "Memory Used for Calculations" for cube }StatsByCube and there is always 0 displayed.

Am I doing something wrong? Why is there 0 displayed for LATEST --> Memory Used for Calculations? And why are there 0 displayed for all of the elements except LATEST for }TimeIntervals? According to documentation The Operational Guide the values should be copied from to 0Mxx every minute. But I see no history data in the }TimeIntevals cube.?
Regards

[David Usherwood]

Your view isn't quite right. Try exporting it to a file and looking through it in Excel.
On Performance Monitor, the history is written by minute. If you start it at 14:20, the first slice will be written against 0M20. Latest is always the latest though.

[Andy Key]

The problem is with your }TimeIntervals dimension. I think it was a version of 9.4 that messed these up. You need to edit the dimension and re-create every element, except for Latest, but without the control character (the square at the start) so you end up with 0M00, 0M01 etc. rather than what you currently have.

[abcuser]

Your view isn't quite right. Try exporting it to a file and looking through it in Excel.

I have exported the file and looked it in Excel and there are some perfectly looking rows. Are there any instructions how should view look like?

On Performance Monitor, the history is written by minute. If you start it at 14:20, the first slice will be written against 0M20. Latest is always the latest though.

I have read the documentation, but all of the columns except for LATEST does not have any data beside zero, which is strange to me.

The problem is with your }TimeIntervals dimension. I think it was a version of 9.4 that messed these up. You need to edit the dimension and re-create every element, except for Latest, but without the control character (the square at the start) so you end up with 0M00, 0M01 etc. rather than what you currently have.

Yes I see there is a square before 0M00 etc, but how to remove this square? I have tried to right click on dimension and "Edit Dimension Structure" is grayed out. Any tip how to remove this squares?

[lotsaram]

Yes I see there is a square before 0M00 etc, but how to remove this square? I have tried to right click on dimension and "Edit Dimension Structure" is grayed out. Any tip how to remove this squares?

You can't edit control dimensions directly in the dimension editor. You have 2 choices, either export the dimension to a cma file then import the dimension to excel and then save as an xdi dimension worksheet as }TimeIntervals. Or recreate the dimension with TI.

[Andy Key]

You can't edit control dimensions directly in the dimension editor.

Where's the <smacks forehead> icon when you need it? I was trying to remember why I bothered to write this TI last time I had to do this...
Bung all this in the prolog. Usual disclaimers apply.

Code: Select all

DimName = '}TimeIntervals';

Counter = DimSiz( DimName);

While (Counter > 0);
   If( Long( DimNm( DimName, Counter)) = 5);
      DimensionElementDelete( DimName, DimNm( DimName, Counter));
   EndIf;
   Counter = Counter - 1;
End;

Counter1 = 0;
While( Counter1 < 2);
   Counter2 = 0;
   While( Counter2 < 60);
      DimensionElementInsert( DimName, '', NumberToString( Counter1) | 'M' | SubSt( '0' | NumberToString( Counter2), Long( NumberToString( Counter2)),99),'N');
      Counter2 = Counter2 + 1;
   End;
   Counter1 = Counter1 + 1;
End;

Counter1 = 0;
While( Counter1 < 2);
   Counter2 = 0;
   While( Counter2 < 24);
      DimensionElementInsert( DimName, '', NumberToString( Counter1) | 'H' | SubSt( '0' | NumberToString( Counter2), Long( NumberToString( Counter2)),99),'N');
      Counter2 = Counter2 + 1;
   End;
   Counter1 = Counter1 + 1;
End;

[abcuser]

Andy, it looks to me that this TI process is not working correctly. According to Operational Guide there should be for minutes of current hour: 0M00, 0M01, ... 0M59, but running above TI process I get: 0M00 to 0M23 (it should go on to 59!)
The same problem for minutes for previous hour 1M01, ... 1M59 (documentation), but process: 1M00 to 1M23 (it should go on to 59). But the hour elements for current date should go from 0H00 to 0H23 and the same for hours for previous day 1H01 to 1H23, but they go to 1H59 and 0H59.

But if I understand correctly this is only solving problem for history snapshot data. If I am interested in the latest data it should be enough to just look at LATEST column. But as seen in picture "Memory Used for Calculations" is still 0 (see red rectangle in attachment). So it looks like ViewConstruct function didn't pre-calculate anything. But I have tried to export view to file and opened file with Excel and it does look like a valid data. Any idea what could be wrong?

[Andy Key]

Ha! Good spot. I must have been using Performance Logging at some point between the top of the hour and 24 minutes past and then turned it off again.

I've swapped the H and M in the previous post.

[abcuser]

Hi,
I have swapped H and M in a process and run it again. But now I am facing several additional problems in dimension }TimeIntervals in cube }StatsByCube:
1. There is still: 0H00 ... 0H59 instead of 0H00 ... 0H23
2. There is still: 1H00 ... 1H59 instead of 1H00 ... 1H23
3. Order of 0Mxx and 1Mxx are not properly sorted it appears:
0M00 ... OM23, 1M00, ... 1M23, 0M24 ... 0M59, 1M24, ... 1M59.
Regards

[David Usherwood]

@abcuser, I think we have two different issues in this thread.

The problems you are having with Performance Monitor seem to relate to the version of TM1 you have. I have used PM over many different versions and, although there are things I would change about it, it has always done what it says on the tin. If it is broken on your version, I would go through your support channel and get that resolved, probably by upgrading.

The original issue you raised was performance of BI reports running over TM1. Does your view include the data you are trying to show in BI? If it does, running ViewConstruct should populate the calculation cache and make your report much faster.