KB30699: How to measure the Intelligent Cube performance parameters for time and memory in the MicroStrategy Intelligence Server

The Strategy Intelligence Server offers in-memory Intelligent Cubes. The results of the Intelligent Cube reports can be used by multiple users and reports to avoid executing against the data warehouse. Users may find additional information related to managing and using Intelligent Cubes in the System Administration Guide: Chapter 6: "Managing Intelligent Cubes".
 
Administrative users may be interested in measuring different usage and publication metrics related to Intelligent Cubes. In this technical note we attempt to explain how users can measure the performance of two important cube publication metrics: 

1. The time required to publish a cube
2. Peak memory usage for the Intelligence Server during cube publication.

Individual processing components within the Intelligence Server contribute in different ways to the overall values of these metrics. The following image shows the Intelligence Server memory usage during a sample cube publication (using the Intelligence Server Normalization method). The diagram plots the cube publication time against the memory usage (in this case resident set size - RSS) of the MSTRSvr process on a Linux operating system. 

1. Intelligent Cube publication time:
   This metric has 3 major components as shown in the diagram:
   P0 - P1: Query Execution on the data warehouse – This component of the publication time is dependent on the time taken for the data warehouse to execute the Intelligent Cube SQL and generate the result set data.
   P1-P2: Data fetching and normalization – This component of the publication time measures the actual data fetching operation from the data warehouse to Intelligence Server process.
   P2-P3: Analytical Engine Processing Time – This component of the publication time measures the time spent in performing additional processing required to convert the warehouse data and store the cube structure within Strategy.
    
   
2. Intelligent Cube publication memory utilization:
   There are four major memory consuming components of the cube publication process:
   M1: This is the memory used when cube data is being fetched from the data warehouse and processed on the Intelligence Server. This includes the memory required to normalize and persist the Intelligent cube data.
   M2: After the Intelligent Cube data is persisted, some process allocated memory may be released by the Intelligence Server process, however this may not necessarily be observed.
   M3: Once the Analytical Engine begins to convert data to cube structure and normalize this, some additional memory may be used by the Intelligence Server process.
   M4: After the Intelligent Cube is published, some memory may be released by the Intelligence Server process.
    
   

With these individual measurements, users can measure the Intelligent Cube publication performance as:
Intelligent Cube Publication Time:        P3 – P0
Intelligent Cube Peak Memory Usage: M1 – M2 + M3
Intelligent Cube Memory Footprint:        M1 – M2 + M3 – M4
 
Each of the cube publication metric parameters (P0, P1, P2, P3, M1, M2, M3 and M4) are important when measuring performance. Care should be taken to interpret and log this data accurately. There are a number of ways to collect information during the Intelligent Cube publication process. The following discussion explains a few methods and details how users may identify the P0, P1, P2, P3, M1, M2, M3 and M4 parameters:
 
Measuring Intelligent Cube publication times from Strategy Desktop:

• Intelligent Cube Report Execution Page:
  When an Intelligent Cube report is run (and data published), the report execution status will be reported in cube report page in Strategy Desktop. The following screenshot shows the Intelligent Cube report execution page for a sample cube:
   
   

•  
  As shown in this page, publication of this sample cube started at 15:35:38 and took 10 minutes and 18 seconds:
  Start Time: 15:35:38
  Execution Time: 10 minutes 18 seconds
   
  Since the report execution page is only available when a user manually executes a cube report and waits for it to finish, this view will not be used in the discussion to follow.
   
  
• SQL View Statistics:
  When cube report is run, additional information is available in SQL view. This view can also be accessed through History List. Cube report can also be run by adding to History List. And once it is published double clicking on it will open the editor below as well.
   
   

•  
  The following example shows  how the SQL view statistics can be used to determine the processing times of various cube publication steps.
  Query Execution Start Time (T0):    3:35:38 PM.
  Query Execution Finish Time (T3): 3:45:52 PM.
  (T3 – T0) should be less than or equal to “Execution Time” reported in Report Page. The difference between this value and the reported execution time is related to the overhead of the report execution through Strategy Desktop.
   
  

  
  Total Duration in the Query Engine (T4):   10:13.85
  
  
  SQL Duration (T5):                                         10:13.41
  
  Analytical Duration (T6):                                        0:00
  
  Other Processing Duration in QE (T7):         0:00.44
  

  
  
  (T5 + T6 + T7) should be identical to the value for T4 and should be same as (T3 – T0).
   
  Users may also be able to look at the SQL view for the report for a SQL pass called as “Populate Report Data” (shown below as Pass3). This SQL pass indicates time spent in the Analytical Engine for cube publication.
   
  
  
  

  
  NATION a16
  
  where pa11.O_ORDERKEY = pa12.O_ORDERKEY and
  
   pa11.P_PARTKEY = pa12.P_PARTKEY and
  
   pa11.S_SUPPKEY = pa12.S_SUPPKEY and
  
   pa11.O_ORDERKEY = a13.O_ORDERKEY and
  
   pa11.S_SUPPKEY = a14.S_SUPPKEY and
  
   pa11.P_PARTKEY = a15.P_PARTKEY and
  
   a14.S_NATIONKEY = a16.N_NATIONKEY
  
  
  
  Pass3 -  Duration: 0:00:40.36
  
  
  
  
  
  Pass4 -  Duration: 0:00:00.07
  
  drop table ZZTKP010045SP000

  
  
  
   
  If we consider T2 as the start of “Report Data Population” and T3 as Query Engine Execution Finish Time:
  Time to Populate Report Data (T3-T2): 00:40.36
  

Using the Performance Monitor Counters to measure Intelligent Cube memory usage and publication times:
When an Intelligent Cube report is run (published), the Strategy performance counters can be used to gather more information on memory and CPU usage in Intelligence Server. For more information on how to use Diagnostic and Performance Logging tool, users should refer to the System Administration Guide, Section “Configuring what is logged: Diagnostics and Performance Logging tool”. Users should choose a relatively small logging frequency to obtain high resolution of the logged data (at least 1 minute).
 
The performance counters that should be logged for this process are:
 

\Process(MSTRSvr)\Size(MB)

\Process(MSTRSvr)\RSS(MB)

\Process(MSTRSvr)\% CPU time

\PhysicalDisk\Total number of bytes written

\PhysicalDisk\Total number of bytes read

\Network Interface\KBytes sent

\Network Interface\KBytes received

\Strategy Server Jobs(CastorServer)\Total Completed Cube Reports

• Make sure that system time of Desktop client and the Intelligence Server are synchronized. If they are not, they should be adjusted properly. Any time difference can be accounted for,but may may make the process difficult and increase the chances of errors.
• The start time for the Intelligent Cube publication should be noted first. For example, sample cube publication started at 15:35:38. Users should mark the start of the log (marked in light yellow in the screenshot below).
   
  

•  
  The Start Time (P0) is then : 3:35:38 PM. This is T0 from the discussion above.
  
• Once the Intelligent Cube publication start time has been located, users now need to locate the end time. Note that the counter “Total Completed Cube Reports” is 0 at the beginning of publication. When cube is published this counter should increase to 1. In the example below, this counter increased to 1 at 15:45:55. So cube publication ended at 15:45:55.
   
  

•  
  The Intelligent Cube Finish Time (P3): 15:45:55 PM. This should be close to T3 from the discussion above.
   
  Note that in this example P3 and T3 are not exactly the same. There is 3 second difference between the two values because of the finite resolution of the performance logging tool. In such cases, the value of P3 should be adjusted accordingly (using the SQL view statistics or other methods).
  For this example, we could also use the change in the server process RSS reading to approximate the end time as 15:45:53 .  If adjusted properly, the Intelligent Cube publication execution time is: P3 – P0 = 10 minutes 15 seconds. This should be same as (T3 – T0).
   
  
• When cube publication starts, the Intelligence Server will send the SQL requests to the database and will wait until the SQL is executed and results are returned to the server. This time may also be identified from the performance logs as there will be minimal CPU activity, and no RSS increase and or network traffic received on the Intelligence Server network port.
  Once the process starts to receive data from the data warehouse, the counters for Network KBytes received and the Intelligence Server process RSS start to increase. Note that because there will always be some network traffic present, users should identify this period by the sharp increse in the amount of network data received.
  In the  example below, the counter for Kbytes received shows significant network traffic at 15:38:49. Users may also note that a couple seconds later, the counter for the process RSS starts to increase as well (Data received will be processed and stored in I-Server memory)
   
   

•  
  Users can mark this time as P1, and calculate the time spent waiting on the data warehouse as (P1 – P0)= 15:38:49 - 15:35:38 = 3 min 11 seconds.
  
• While data is returned from the database, the counters for  Kbytes received and the process RSS and size will continue to increase. For this example we have chosen the Intelligence Server method for data normalization, and so, during this time the data is also normalized. When the Kbytes received counter goes back to small value, this marks the end of the “data fetch and normalization period”. In this example, the end time is 15:45:12. (P2)
   
  

•  
   
  The total Data Fetch and Normalization time is (P2 – P1) = 15:45:12 – 15:38:49 = 6 min 22 seconds
  Users may also now be able to obtain some of the memory measurements from the performance logs:
  M1: This is RSS increase during this period:  (771 - 148) = 623 MB
  M2:  In this example, the RSS value does not drop so M2 = 0
  During the data fetch time, the MSTRSvr process CPU time should be fairly low. If Intelligence Server normalization is used, there may be usage spikes in the process CPU time when normalization is performed.
  
• Once the data is fetched from data warehouse and normalized, it will be converted to cube structure by the Analytical Engine component of the Intelligence Server. During this time users may observe constant CPU activity on the Intelligence Server, and process memory increase. After the data is converted to cube structure there may also be a reduction in the process memory usage.
   
  

•  
   
  The Intelligence Server Analytical Engine time is (P3 – P2)= 15:45:53 – 15:45:12 = 41 seconds
  M3: 977 MB (1748 – 771). Note that 1748 is the peak memory usage during the Analytical engine processing of the cube data, and is not the value for the memory usage right before end time. The exact formula to determine this value should be {Max(memory) – Start(memory)}
  M4: 480 MB (1748 – 1268). This is the final memory usage after the job is completed.
  Now that all the performance counters are analyzed, users can compare these values to counters collected earlier through desktop and SQL view.
  P3 – P0 = 3:11 + 6:22 + 0:41 = 10: 14. This should be close for (T3 – T0)
  P3 – P2 = 41 seconds. This should be close to the value for (T3 – T2)