Usage Statistics

1. Flow:
   1. Start a new advanced data model in Pyramid. Add a relevant data source (PostgreSQL, MS SQL Server, Oracle), depending on which type of repository you have elected to deploy
      1. From the database listing in the property cards, select the Pyramid database repository
      2. From the table selection, choose three views
         • contentview*
         • modelview**
         • transactionview
           • *Available in 2020.24 and later versions
           • **Use the server_log_transaction_columns table instead for versions older than 2020.24
      3. Click Add Selected Nodes, to add these views to the flow
   2. Add a new Convert Columns node after the transactionview select node in the flow from the Column Operations side menu
      1. Change the date column to type "date", with format yyyy-mm-dd
   3. Add a new In-memory target database node and provide a name for the database ("Pyramid Usage")
2. Model:
   1. Change the default measure to "Total Time" on the configuration tab
   2. On the Tables tab:
      1. Rename the modelview to "Elements"
      2. Rename the transactionview table to "Events"
      3. Make sure the Elements table is inner joined to the Events table using the "transaction_id" column
      4. Make sure the contentview table is inner joined to the Events table using the "item_id" column
      5. Change the following measures in the Events table to "Average" aggregations: column_count, row_count, cell_count, connection_time, post_query_time, pre_query_time, other_time, query_time, total_time
      6. Set client as a non-measure and select it
      7. Right-click on transaction_id column and add a count measure
      8. Optional: Right-click on user_name and add a distinct count measure (to count unique users)
      9. Under contentview, set item_type and sub_type as non-measures and select them

      

      Add a time hierarchy based on Events with year, quarter-year, month-year, date, hour ,minute

3. On the Hierarchy tab:

   1. Add a time hierarchy based on Events with year, quarter-year, month-year, date, hour ,minute

      

   2. Add a content tree Parent-child hierarchy with Orphans Handling set to Roots and set the following Attribute Selections:
      • Child Key - item_id
      • Parent Key - folder_id
      • Caption - name

      

   3. Save and then Process your model.

The resulting model will take a snapshot of your transactional activity in Pyramid for further analysis.

Set up a schedule on this model to reprocess the database once a day, typically off-peak.

Metrics

Response Size Metrics

• Column Count: The number of RAW columns in the result set
• Row Count: The number of RAW rows in the result set
• Cell count = columns x rows

The RAW result set is not always indicative of the drawn result in the visual.

For instance, matrix grids can be much larger drawn than their underlying RAW results.

Time Metrics

• Pre-query: Pyramid engine time before the query is submitted.
• Post-query: Pyramid engine time after the query is submitted.
• Other: any time spent on scripting logic and dynamic functions as part of the query
• Server: pre-query + post-query + other + engine - query (not an exact match)
• Connection: the average time taken to connect to the data source
• Query: the time data source takes to respond to the query
• Query Engine: Connection + Queries and Calculations
• Total: server + query engine + network + client

The time aggregates may be slightly off because:

• the measurements are in millisecond integers - so rounding is ignored.
• not every time element is always fully additive - because some items may run in parallel to each other (like 'connection' and 'other' times)

Using these metrics, you should be able to spot performance issues in your query designs vs data source performance vs Pyramid's processing performance.

For further clarification:

• Server time is the entire time that the server handled the request minus the Query Time. That might include queuing time, for example, if the server are receiving more requests than what the services are configured to handle in parallel, or for very big requests there might be an overhead time of creating the payload for the server to send back to the client (browser), and these are not captured by any specific column.
• Connection time is an average time of all the connections that were generated in the request. For simple requests it would probably be an average of 1 item, but there might be multiple. (You can see in the admin view that it is labeled as "average connection"). The connection times are nested inside the Query time, so Connection Time should not be added to Query Time.
• Query Time is only data source related time consumption (connecting, executing, retrieving the data). It is a smart accumulation of all the queries executed against the data source, with consideration of parallel queries.

• Engine Time is the time that the query was executed including internal processing by Pyramid.