How do you delete dimension values en masse?

Question

I accidentally loaded thousands of dimensions values by accident. Trying to delete them through the UI is proving to be difficult, affecting system performance for other users. Is there a quicker way to delete the dimension values directly in the database?

Solution

Before loading thousands of dimension values, really think about why they are needed and for what purpose they will serve. Often times, the need to load thousands of dimension values can be mitigated by utilizing an Exception Report instead (with no dimensions!).

In this scenario where you've already loaded the dimension values by mistake, you can run the following SQL statement to purge the dimension values from the database. Create mysqldumps of the following tables beforehand just in case. Note, you'll need root access to the Metric Insights server and the MySQL database to run the following statement:

SET @BULK_OPERATION_DISABLE_TRIGGERS=1;

DELETE FROM metric_measured_value
WHERE metric_id = <element id/s here>;

DELETE FROM last_dashboard_element_segment_value
WHERE element_id = <element id/s here>;

DELETE FROM dashboard_element
WHERE element_id = <element id/s here>;

DELETE FROM segment_value
WHERE segment_id = <dimension id/s here>;

DELETE FROM segment
WHERE segment_id =<dimension id/s here>;

CALL bulk_operation_affected_rows_process(NULL,NULL);

SET @BULK_OPERATION_DISABLE_TRIGGERS=NULL;

Note, the IDs can be found by going to the Element Editor and Dimension Editor in the UI and locating the ID number in the URL. For additional assistance, please contact [email protected]