MicroStrategy ONE
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
DELETE_ALL_OBJECTS_IN_PROJECTS
DELETE_ALL_DELETED_OBJECTS
DELETE_ALL_DELETED_PROJECTS
DELETE_ALL_DELETED_OBJECTS_IN_METADATA
DELETE_ALL_DELETED_PROJECTS_IN_METADATA
DELETE_ALL_DELETED_OBJECTS_IN_PROJECTS
DELETE_ALL_FACTS
DELETE_ALL_FACTS_FROM_METADATA
DELETE_ALL_FACTS_FROM_PROJECTS
DELETE_ALL_FACTS_FROM_DELETED_OBJECTS
DELETE_ALL_FACTS_FROM_DELETED_PROJECTS
DELETE_ALL_FACTS_FROM_DELETED_OBJECTS_IN_METADATA
DELETE_ALL_FACTS_FROM_DELETED_PROJECTS_IN_METADATA
DELETE_All_FACTS_FROM_DELETED_OBJECTS_IN_PROJECTS
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 |
Step 1 - Modify 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.
doTestBeforePurge: true
commandsToExecute:
# - commandName: DELETE_ALL_DELETED_OBJECTS
# - commandName: DELETE_ALL_DELETED_PROJECTS
- commandName: DELETE_ALL_OBJECTS_IN_METADATA
metadataList:
- 7114993896978911232
# - 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
# daysToKeep: 60
# - 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: 60Step 2 - Execute Data Purge
- Open a terminal window and navigate to the Platform Analytics folder.
-
Execute the purge script:
Windows: platform-analytics-purge-warehouse.ps1
Linux: ./platform-analytics-purge-warehouse.sh
-
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.