QUESTION:
Is there a Metric which can be used for collecting the Data Transfer Statistics in Strategy Enterprise Manager 9.x? which can tell how much time is getting consumed for transferring of data from Database to I-Server for a particular Intelligent Cube.
ANSWER:
The time of Data Transferring from Database to I-Server equals to "Sum of Data Transfer from Datasource(s) Time" which can be found in SQL view when publishing the Intelligent Cube. Currently, there is only a Metric to display of "Total Elapsed Time in Query Engine ", which is named "RP Query Engine Duration (hh:mm:ss)" under: Enterprise Manager\Public Objects\Metrics\Report Processing Analysis.
For the relationship between "Sum of Data Transfer from Datasource(s) Time" & "Total Elapsed Time in Query Engine", please see detailed information as below:
1. With Enterprise Manager set up, in the monitored Project, publish the Intelligent Cube and check the SQL view.
Note: Total Elapsed Time in Query Engine = Sum of Query Execution Time + Sum of Data Fetching and Processing Time + Sum of Analytical Processing Time + Sum of Other Processing Time, while the "Sum of Data Transfer from Datasource(s) Time" is included in "Sum of Data Fetching and Processing Time".
2. Manually run data load.
3. Go to Enterprise Manager project, create a new report with Attribute "Intelligent Cube" , "Report Job" and Metric "RP Query Engine Duration (hh:mm:ss)" and execute it. Please be noticed the "RP Query Engine Duration (hh:mm:ss)"="Total Elapsed Time in Query Engine".
4. If looking into the Metric - RP Query Engine Duration (hh:mm:ss), it is calculate from "SQL Execution Step",which is related to the Enterprise Manager lookup table IS_REP_STEP_TYPE, restoring each step for a Report Job as shown below.
Since there is only the whole step "SQL EXECUTION", but no more detailed information for each stage (Query Execution / Data Fetching and Processing /Analytical Processing / Other Processing), so there is no statistics only for "Data Transfer from Datasource(s) Time".
