Pyramid Usage Model and Analysis

The following document describes how to build a data model on Pyramid’s database repository for use in content and analytics usage in Pyramid itself. 

The resulting model will give you insights into: 

  1. WHO: Query transactions executed by users and tenants
  2. WHEN: Query transactions by time - down to the hour and minute level
  3. WHAT: Query transactions by data source server and type, database and model, with further details related to content items, the main content folders, specific hierarchies and measures used in each query (from the “elements” hierarchies)
  4. WHERE: Query transactions by processing server
  5. WHICH: Query transactions by which types of content 

And of course, any combination of the above. 


  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 the server_log_transaction_columns table and the transactionview view

      3. Click Add Selected Nodes, to add these tables and 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 server_log_transaction_columns 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. Change all the 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
      5. Right click on transaction_id column and add a count measure
  3. Save and then Process your model.  

The resulting model will take a snapshot of your transactional activity in Pyramid for further analysis. Setup a schedule on this model to reprocess the database once a day, typically off-peak.  

2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Itamar,


    This is very useful feature. But I have one question regarding this. The view is returning only data for last 4 months. Is there any way we can extend this period?




    Reply Like
  • Hi Uroš,

    The Usage Model is based on the transactional data that is stored in the Pyramid database repository.
    This data is stored in the db for a limited time which Administrators can change if needed.

    To configure Pyramid to keep the data for a longer period of time, you can change the "Keep Transactions Logs For These Days" setting in the Admin Console > Settings > Logging.

    Before making this change, please make sure the database is capable of storing that much information.


    Reply Like
Like5 Follow
  • 2 mths agoLast active
  • 2Replies
  • 85Views
  • 3 Following