Deleting Data from Project

by ZD (@zsvoboda)

The new data deletion API has been introduced in GoodData Release 51. The API is located at the following URL.

https://secure.gooddata.com/gdc/md/<your-project-id>/dml/manage

You can also use the CL tool ExecuteDml(maql="...") command instead of invoking the API directly.

Both ExecuteDml and the API require a MAQL DELETE statement that performs the data deletion. Lets start with a few examples:

DELETE FROM {attr.opportunity.id} WHERE {fact.oppoprtunity.probability}<0 OR {fact.oppoprtunity.amount}<100;
DELETE FROM {attr.opportunity.id} WHERE {created.date.yyyymmdd}<"2009-01-01";
DELETE FROM {attr.opportunity.id} WHERE {created.date.yyyymmdd} BETWEEN "2010-01-01" AND "2010-12-31";
DELETE FROM {attr.opportunity.id} WHERE {label.opportunity.salesrep} IN ("SMITH","CLOONEY");

So the DELETE syntax is

DELETE FROM <attribute> WHERE <condition>;

where

• <attribute> - determines the data that will be deleted. If you want to delete whole record from a dataset, use the dataset’s CONNECTION_POINT attribute.
• <condition> - identifies the records that will be deleted.

Lets explain the <attribute> in more detail on an example. The attribute {attr.opportunity.id} is the CONNECTION_POINT of the Opportunity dataset. The dataset also contains the {attr.opportunity.status} attribute. The {attr.opportunity.status} has the {label.opportunity.status} label. Then the statement

DELETE FROM {attr.opportunity.status} WHERE {label.opportunity.status}="Open";

deletes the single Open value from the {attr.opportunity.status} attribute. The dataset’s records are preserved. This statement most probably breaks the referential integrity of the project as the records with the Open status no longer reference any value in the {attr.opportunity.status} attribute.

The statement

DELETE FROM {attr.opportunity.id} WHERE {label.opportunity.status}="Open";

deletes all records with the attribute {attr.opportunity.status} equal to Open .

WARNING: The MAQL DELETE command is translated to a corresponding SQL command. This might have certain unwanted side effects. For example, the following statement:

DELETE FROM {attr.opportunity.id} WHERE {created.date.yyyymmdd}>"John Doe";

deletes all records from the Opportunity dataset because the value “John Doe” gets converted to a NULL date. Similarly, the statement:

DELETE FROM {attr.opportunity.id} WHERE {label.opportunity.salesrep}>"CLOONEY";

deletes all opportunities that are associated with sales reps that come after “CLOONEY” in the alphabet.

And the last one. You need to be the admin to invoke this API.