Version 2021

Purge Platform Analytics Warehouse

It may become necessary to purge some of the data collected and stored in the Platform Analytics warehouse. If a large volume of stored data begins to negatively affect the performance of the Platform Analytics Consumer, or if some metadata or projects have been dropped from an environment, the commands listed in this section allow administrators to remove the associated data from the Platform Analytics warehouse.

The commands used to purge Platform Analytics warehouse data are based on different criteria, including:

  • Metadata: You can purge data from some specific metadata.
  • Projects: You can purge data from specific projects, but all those projects must be in one metadata.
  • Deleted Objects: You can purge the deleted objects and related data.
  • Deleted Projects: You can purge the deleted projects and related data.
  • DaysToKeep: You can purge data and only keep the latest data with the given number of days.

MicroStrategy provides the following valid commands to purge platform analytics warehouse:

DELETE_ALL_OBJECTS_IN_METADATA

This command will purge the whole metadata you have given as well as the related data, including those metadata in lu_metadata. The following tables will be purged:

Metadata Projects Configuration Objects Other Objects Project Objects Fact Tables Postgres Only Fact Tables
lu_metadata lu_project

lu_account

lu_cache

lu_db_connection

lu_db_connection_map

lu_db_instance

lu_db_login

lu_entity

lu_event

lu_mstr_user

lu_recipient

lu_schedule

lu_server_definition

lu_server_instance

lu_subscription_base

lu_subscription_device

lu_user_group

etl_lu_metadata_audit_time

etl_rel_childgroup_usergroup

lu_db_error

lu_grid

rel_account_usergroup

rel_privilege_source_privilege_group

rel_scope_project

rel_sessionid_corrdinate

rel_source_privilege_source_scope

rel_user_entity_source

etl_lu_folder

lu_history_list_message

lu_object

lu_prompt

lu_security_filter

lu_status

access_transaction

access_transaction_reprocess

fact_actiion_security_filter

fact_action_cube_cache

fact_client_executions

fact_machine_configuration

fact_metadata_users

fact_named_user

fact_named_user_license

fact_object_change_journal

fact_object_component

fact_performance_monitor

fact_product_named_users_license

fact_prompt_ansers

fact_report_columns

fact_server_cpu_license

fact_sql_stats

fact_step_sequence

fact_usher_entity_resolved_privilege

fact_usher_inbox_message

fact_usher_inbox_response

lu_client_session

lu_session

historical_access_transactions

historical_fact_action_cube_cache

historical_fact_action_security_filter

historical_fact_object_change_journal

historical_fact_prompt_answers

historical_fact_report_columns

historical_fact_sql_stats

historical_fact_step_sequence

historical_lu_session

DELETE_ALL_OBJECTS_IN_PROJECTS

This command will purge all the projects which you have given and related data, include those projects in lu_project. The following tables will be purged:

Metadata Projects Configuration Objects Other Objects Project Objects Fact Tables Postgres Only Fact Tables
  lu_project

 

lu_db_error

lu_grid

rel_scope_project

etl_lu_folder

lu_history_list_message

lu_object

lu_prompt

lu_security_filter

lu_status

access_transactions

access_transactions_reprocess

fact_action_cube_cache

fact_action_security_filter

fact_client_executions

fact_object_change_journal

fact_object_component

fact_prompt_answers

fact_report_columns

fact_sql_stats

fact_step_sequence

historical_access_transactions

historical_fact_action_cube_cache

historical_fact_action_security_filter

historical_fact_object_change_journal

historical_fact_prompt_answers

historical_fact_report_columns

historical_fact_sql_stats

historical_fact_step_sequence

DELETE_ALL_DELETED_OBJECTS

This command will purge all the deleted objects and related data in the whole pa warehouse. The following tables will be purged:

Metadata Projects Configuration Objects Other Objects Project Objects Fact Tables Postgres Only Fact Table
  lu_project

lu_account

lu_cache

lu_db_connection

lu_db_connection_map

lu_db_error

lu_db_instance

lu_db_login

lu_entity

lu_event

lu_mstr_user

lu_schedule

lu_server_definition

lu_server_instance

lu_subscription_base

lu_subscription_device

lu_user_group

etl_rel_childgroup_usergroup

lu_db_error

lu_grid

rel_account_usergroup

rel_scope_project

rel_user_entity_source

etl_lu_folder

lu_history_list_message

lu_object

lu_prompt

lu_security_filter

lu_status

access_transactions

access_transactions_reprocess

fact_action_cube_cache

fact_action_security_filter

fact_client_executions

fact_object_change_journal

fact_object_change_journal

fact_object_component

fact_prompt_answers

fact_report_columns

fact_sql_stats

fact_step_sequence

fact_user_entity_resolved_privilege

fact_latest_cube_cache

historical_access_transactions

historical_fact_action_cube_cache

historical_fact_action_security_filter

historical_fact_object_change_journal

historical_fact_prompt_answers

historical_fact_report_columns

historical_fact_sql_stats

historical_fact_step_sequence

DELETE_ALL_DELETED_PROJECTS

This command will purge all the deleted projects and related data in the whole pa warehouse. The following tables will be purged:

Metadata Projects Configuration Objects Other Objects Project Objects Fact Tables Postgres Only Fact Tables
  lu_project

 

lu_db_error

lu_grid

rel_scope_project

lu_object

lu_security_filter

lu_prompt

lu_history_list_message

etl_lu_folder

lu_status

access_transactions

access_transactions_reprocess

fact_client_executions

fact_action_cube_cache

fact_action_security_filter

fact_object_change_journal

fact_object_component

fact_prompt_answers

fact_report_columns

fact_sql_stats

fact_step_sequence

historical_access_transactions

historical_fact_action_cube_cache

historical_fact_action_security_filter

historical_fact_object_change_journal

historical_fact_prompt_answers

historical_fact_report_columns

historical_fact_sql_stats

 

DELETE_ALL_DELETED_OBJECTS_IN_METADATA

This command will purge all the deleted objects under you given metadata and related data. The following tables will be purged:

Metadata Projects Configuration Objects Other Objects Project Objects Fact Tables Postgres Only Fact Tables
  lu_project

lu_account

lu_cache

lu_db_connection

lu_db_connection_map

lu_db_instance

lu_db_login

lu_entity

lu_event

lu_mstr_user

lu_schedule

lu_server_definition

lu_server_instance

lu_subscription_base

lu_subscription_device

lu_subscription_device

lu_user_group

etl_rel_childgroup_usergroup

lu_db_error

lu_grid

rel_account_usergroup

rel_scope_project

rel_user_entity_source

etl_lu_folder

lu_history_list_message

lu_object

lu_prompt

lu_security_filter

lu_status

access_transactions

access_transactions_reprocess

fact_client_executions

fact_action_cube_cache

fact_action_security_filter

fact_latest_cube_cache

fact_object_change_journal

fact_object_change_journal

fact_object_component

fact_prompt_answers

fact_report_columns

fact_sql_stats

fact_step_sequence

fact_user_entity_resolved_privilege

historical_access_transactions

historical_fact_action_cube_cache

historical_fact_action_security_filter

historical_fact_object_change_journal

historical_fact_prompt_answers

historical_fact_report_columns

historical_fact_sql_stats

historical_fact_step_sequence

DELETE_ALL_DELETED_PROJECTS_IN_METADATA

This command will purge all the deleted projects under you given metadata and related data. The following tables will be purged:

Metadata Projects Configuration Objects Other Objects Project Objects Fact Tables Postgres Only Fact Tables
  lu_project

 

lu_db_error

lu_grid

rel_scope_project

etl_lu_folder

lu_history_list_message

lu_object

lu_prompt

lu_security_filter

lu_status

access_transactions

access_transactions_reprocess

fact_client_executions

fact_action_cube_cache

fact_action_security_filter

fact_object_change_journal

fact_object_component

fact_prompt_answers

fact_report_columns

fact_sql_stats

fact_step_sequence

historical_access_transactions

historical_fact_action_cube_cache

historical_fact_action_security_filter

historical_fact_object_change_journal

historical_fact_prompt_answers

historical_fact_report_columns

historical_fact_sql_stats

historical_fact_step_sequence

DELETE_ALL_DELETED_OBJECTS_IN_PROJECTS

This command will purge all the deleted objects under you given projects and related data. The following tables will be purged:

Metadata Projects Configuration Objects Other Objects Project Objects Fact Tables Postgres Only Fact Tables
  lu_project

 

lu_db_error

lu_grid

rel_scope_project

etl_lu_folder

lu_history_list_message

lu_object

lu_prompt

lu_security_filter

lu_status

access_transactions

access_transactions_reprocess

fact_client_executions

fact_action_cube_cache

fact_action_security_filter

fact_object_change_journal

fact_object_component

fact_prompt_answers

fact_report_columns

fact_sql_stats

fact_step_sequence

historical_access_transactions

historical_fact_action_cube_cache

historical_fact_action_security_filter

historical_fact_object_change_journal

historical_fact_prompt_answers

historical_fact_report_columns

historical_fact_sql_stats

historical_fact_step_sequence

DELETE_ALL_FACTS

This command will purge all the fact tables in the whole pa warehouse. The following tables will be purged:

Metadata Projects Configuration Objects Other Objects Project Objects Fact Tables Postgres Only Fact Tables
   

 

lu_db_error

rel_sessionid_coordinate

lu_status

access_transactions

access_transactions_reprocess

fact_client_executions

fact_action_cube_cache

fact_action_security_filter

fact_object_change_journal

fact_prompt_answers

fact_report_columns

fact_sql_stats

fact_step_sequence

lu_session

historical_access_transactions

historical_fact_action_cube_cache

historical_fact_action_security_filter

historical_fact_object_change_journal

historical_fact_prompt_answers

historical_fact_report_columns

historical_fact_sql_stats

historical_fact_step_sequence

historical_lu_session

DELETE_ALL_FACTS_FROM_METADATA

This command will purge all the fact tables in you given metadata lists. The following tables will be purged:

Metadata Projects Configuration Objects Other Objects Project Objects Fact Tables Postgres Only Fact Tables
   

 

lu_db_error

rel_sessionid_coordinate

lu_status

access_transactions

access_transactions_reprocess

fact_client_executions

fact_action_cube_cache

fact_action_security_filter

fact_object_change_journal

fact_prompt_answers

fact_report_columns

fact_sql_stats

fact_step_sequence

lu_client_session

lu_session

historical_access_transactions

historical_fact_action_cube_cache

historical_fact_action_security_filter

historical_fact_object_change_journal

historical_fact_prompt_answers

historical_fact_report_columns

historical_fact_sql_stats

historical_fact_step_sequence

historical_lu_session

DELETE_ALL_FACTS_FROM_PROJECTS

This command will purge all the fact tables in you given project lists. The following tables will be purged:

Metadata Projects Configuration Objects Other Objects Project Objects Fact Tables Postgres Only Fact Tables
   

 

lu_db_error

lu_status

access_transactions

access_transactions_reprocess

fact_action_cube_cache

fact_action_security_filter

fact_client_executions

fact_object_change_journal

fact_prompt_answers

fact_report_columns

fact_sql_stats

fact_step_sequence

historical_access_transactions

historical_fact_action_cube_cache

historical_fact_action_security_filter

historical_fact_object_change_journal

historical_fact_prompt_answers

historical_fact_report_columns

historical_fact_sql_stats

historical_fact_step_sequence

DELETE_ALL_FACTS_FROM_DELETED_OBJECTS

This command will purge all the fact tables generated by deleted objects in the whole pa warehouse. The following tables will be purged:

Metadata Projects Configuration Objects Other Objects Project Objects Fact Tables Postgres Only Fact Tables
   

 

lu_db_error

lu_status

access_transactions

access_transactions_reprocess

fact_action_cube_cache

fact_action_security_filter

fact_client_executions

fact_object_change_journal

fact_prompt_answers

fact_report_columns

fact_sql_stats

fact_step_sequence

historical_access_transactions

historical_fact_action_cube_cache

historical_fact_action_security_filter

historical_fact_object_change_journal

historical_fact_prompt_answers

historical_fact_report_columns

historical_fact_sql_stats

historical_fact_step_sequence

DELETE_ALL_FACTS_FROM_DELETED_PROJECTS

This command will purge all the fact tables which generated by deleted projects in whole pa warehouse. The following tables will be purged:

Metadata Projects Configuration Objects Other Objects Project Objects Fact Tables Postgres Only Fact Tables
   

 

lu_db_error

lu_status

access_transactions

access_transactions_reprocess

fact_action_cube_cache

fact_action_security_filter

fact_client_executions

fact_object_change_journal

fact_prompt_answers

fact_report_columns

fact_sql_stats

fact_step_sequence

historical_access_transactions

historical_fact_action_cube_cache

historical_fact_action_security_filter

historical_fact_object_change_journal

historical_fact_prompt_answers

historical_fact_report_columns

historical_fact_sql_stats

historical_fact_step_sequence

DELETE_ALL_FACTS_FROM_DELETED_OBJECTS_IN_METADATA

This command will purge all the fact tables generated by deleted objects in given metadata. The following tables will be purged:

Metadata Projects Configuration Objects Other Objects Project Objects Fact Tables Postgres Only Fact Tables
   

 

lu_db_error

lu_status

access_transactions

access_transactions_reprocess

fact_action_cube_cache

fact_client_executions

fact_action_security_filter

fact_object_change_journal

fact_prompt_answers

fact_report_columns

fact_sql_stats

fact_step_sequence

historical_access_transactions

historical_fact_action_cube_cache

historical_fact_action_security_filter

historical_fact_object_change_journal

historical_fact_prompt_answers

historical_fact_report_columns

historical_fact_sql_stats

historical_fact_step_sequence

DELETE_ALL_FACTS_FROM_DELETED_PROJECTS_IN_METADATA

This command will purge all the fact tables generated by deleted projects in given metadata. The following will be purged:

Metadata Projects Configuration Objects Other Objects Project Objects Fact Tables Postgres Only Fact Tables
   

 

lu_db_error

lu_status

access_transactions

access_transactions_reprocess

fact_action_cube_cache

fact_client_executions

fact_action_security_filter

fact_object_change_journal

fact_prompt_answers

fact_report_columns

fact_sql_stats

fact_step_sequence

historical_access_transactions

historical_fact_action_cube_cache

historical_fact_action_security_filter

historical_fact_object_change_journal

historical_fact_prompt_answers

historical_fact_report_columns

historical_fact_sql_stats

historical_fact_step_sequence

DELETE_All_FACTS_FROM_DELETED_OBJECTS_IN_PROJECTS

This command will purge all the fact tables generated by deleted objects in given project lists. The following will be purged:

Metadata Projects Configuration Objects Other Objects Project Objects Fact Tables Postgres Only Fact Tables
   

 

lu_db_error

lu_status

access_transactions

access_transactions_reprocess

fact_action_cube_cache

fact_client_executions

fact_action_security_filter

fact_object_change_journal

fact_prompt_answers

fact_report_columns

fact_sql_stats

fact_step_sequence

historical_access_transactions

historical_fact_action_cube_cache

historical_fact_action_security_filter

historical_fact_object_change_journal

historical_fact_prompt_answers

historical_fact_report_columns

historical_fact_sql_stats

historical_fact_step_sequence

Purge Configuration File

The purgeConfig.yaml file is located in the Platform Analytics conf directory. Simply uncomment each command you would like to execute by removing the # in front of each line. There are six parameters that control the various purge commands:

  • doTestBeforePurge: The default value is true. Set to false to skip testing before command execution.

  • commandName: The name of the command to execute.

  • onlyDeletedProjects: Set to true to purge only deleted projects.

  • onlyDeletedObjects: Set to true to purge only deleted objects.

  • metadataList: By default data will only be purged from the Platform Analytics warehosue. Provide a list of metadata ids to apply the purge actions to specific metadata only.

  • projectList: By default data will only be purged from the Platform Analytics warehosue. Provide a list of project guid values to apply the purge actions to specific projects only.

  • daysToKeep: If this value is 0, it will purge all the fact table. Assume this value is a, then we will keep fabs(a) days data.

The following is a sample file:

Copy
#doTestBeforePurge: true
#commandsToExecute:
#  - commandName: DELETE_ALL_DELETED_OBJECTS

#  - commandName: DELETE_ALL_DELETED_PROJECTS

#  - commandName: DELETE_ALL_OBJECTS_IN_METADATA
#    metadataList:
#      - metadataId_1
#      - metadataId_2

#  - commandName: DELETE_ALL_DELETED_OBJECTS_IN_METADATA
#    metadataList:
#      - metadataId_1
#      - metadataId_2

#  - commandName: DELETE_ALL_DELETED_PROJECTS_IN_METADATA
#    metadataList:
#      - metadataId_1
#      - metadataId_2

#  - commandName: DELETE_ALL_OBJECTS_IN_PROJECTS
#    metadataList:
#      - metadataId
#    projectList:
#      - projectGuid_1
#      - projectGuid_2

#  - commandName: DELETE_ALL_DELETED_OBJECTS_IN_PROJECTS
#    metadataList:
#      - metadataId
#    projectList:
#      - projectGuid_1
#      - projectGuid_2
 
#  - commandName: DELETE_ALL_FACTS_FROM_DELETED_OBJECTS
#    daysToKeep: 60

#  - commandName: DELETE_ALL_FACTS_FROM_DELETED_PROJECTS
#    daysToKeep: 60

#  - commandName: DELETE_ALL_FACTS_FROM_METADATA
#    metadataList:
#      - metadataId_1
#      - metadataId_2
#    daysToKeep: 60

#  - commandName: DELETE_ALL_FACTS_FROM_DELETED_OBJECTS_IN_METADATA
#    metadataList:
#      - metadataId_1
#      - metadataId_2
#    daysToKeep: 60

#  - commandName: DELETE_ALL_FACTS_FROM_DELETED_PROJECTS_IN_METADATA
#    metadataList:
#      - metadataId_1
#      - metadataId_2
#    daysToKeep: 60

#  - commandName: DELETE_ALL_FACTS_FROM_PROJECTS
#    metadataList:
#      - metadataId
#    projectList:
#      - projectGuid_1
#      - projectGuid_2
#    daysToKeep: 60

#  - commandName: DELETE_All_FACTS_FROM_DELETED_OBJECTS_IN_PROJECTS
#    metadataList:
#      - metadatatId
#    projectList:
#      - projectGuid_1
#      - projectGuid_2
#    daysToKeep: 60

Perform a Data Purge

  1. Open a terminal window and navigate to the Platform Analytics folder.
  2. Execute the purge script:

    Windows: platform-analytics-purge-warehouse.ps1

    Linux: ./platform-analytics-purge-warehouse.sh

  3. If you have enabled doTestBeforePurge the purge information will be displayed. Enter Y or N to confirm or abort the purge of the listed data.

View Purge Statistics

The Platform Analytics warehouse contains the purge_statistic table to track the purge operations that have been executed. Each record contains the following important information:

  • id: This column is just the Id to identify a record.
  • execute_time: This column is the time in milliseconds to execute a populate or delete SQL query.
  • insert_ts: The timestamp of when the query finished executing.
  • purge_command_id: For each purge command, it will generate a purge_command_id to identify a purge command.
  • purge_command_name: The current purge command's name.
  • rows: How many rows this query effects.
  • table_name: What table this query has effected.