IMPORTANT: Allways perform a FULL Backup of the databases before doing anything to it !!!

ALSO: It is advised that you open a case at Microsoft before doing this - directly editing the database is not supported and you may find yourself in an unsupported state if anything goes wrong.

If by any chance you run into the issue (very unlikely ...) that you remove some Management Pack in OM 2007 R2 and something happens and you end up with leftovers of a dataset in the Data Warehouse database belonging to that Management Pack, here is what you should do: delete it manually.

This happened with the Exchange2010 dataset from the Exchange 2010 Management Pack. What this does is that there may be some errors because of workflows that still try to perform actions on that dataset because it is still found in the database and/or not being able to reimport the Management Pack - well the MP itself will get imported but there will be errors when it tries to create the dataset in the Data Warehouse database.

So, this SQL Query will permanently delete a dataset. You need the Management Group name and the dataset name.

To get the dataset name, just run this SQL Query:

SELECT *
 FROM DataSet

After you know the name, just edit the SQL Query below and enter the Management Group name for @MGName and the dataset name for @DataSetLike:

DECLARE
    @DataSetLike NVARCHAR(255),
    @MGName NVARCHAR(255),
    @DataSetId UNIQUEIDENTIFIER,
    @MGid INT,
    @FetchStatus INT,
    @AgId INT
 
 SET @MGName = N'MG_NAME'
 SET @DataSetLike = N'DATASET_NAME'
 
 DECLARE Cur CURSOR LOCAL FOR
    SELECT DataSetID
    FROM DataSet
    WHERE DatasetDefaultName LIKE ('%' + @DataSetLike + '%')
 
 OPEN Cur
 
 FETCH NEXT FROM Cur INTO @DataSetId
 SET @FetchStatus = @@FETCH_STATUS
 
 SET @MGid = (
    SELECT ManagementGroupRowId
    FROM ManagementGroup
    WHERE ManagementGroupDefaultName = @MGName
 )
 WHILE (@FetchStatus = 0) BEGIN
    SELECT @AgId = min(StandardDatasetAggregationStorageRowId)
    FROM StandardDatasetAggregationStorage
    WHERE DatasetId = @DataSetId
    PRINT N'Agid = ' + Cast(@Agid AS VARCHAR)
 
    WHILE (@AgId IS NOT NULL) BEGIN
       DELETE
       FROM StandardDatasetAggregationStorageIndex
       WHERE StandardDatasetAggregationStorageRowId = @AgId
       PRINT N'Deleting from StandardDataSetAggregationStorageIndex: ' + Cast(@DataSetId AS NVARCHAR(50))
 
       DELETE
       FROM StandardDatasetAggregationStorage
       WHERE StandardDatasetAggregationStorageRowId = @AgId
       PRINT N'Deleting from StandardDataSetAggregationStorage: ' + Cast(@DataSetId AS NVARCHAR(50))
 
       SELECT @AgId = min(StandardDatasetAggregationStorageRowId)
       FROM StandardDatasetAggregationStorage WHERE DatasetId = @DataSetId
 
       PRINT N'Agid = ' + Cast(@Agid AS VARCHAR)
    END
 
    DELETE
    FROM ManagementGroupDataset
    WHERE
       DatasetId = @DataSetId AND
       ManagementGroupRowId = @MGid
    PRINT N'Deleting from ManagementGroupDataset: ' + Cast(@DataSetId AS NVARCHAR(50))
 
    DELETE
    FROM StandardDataset
    WHERE DatasetId = @DataSetId
    PRINT N'Deleting from StandardDataSet: ' + Cast(@DataSetId AS NVARCHAR(50))
 
    DELETE
    FROM StandardDatasetAggregationStorage
    WHERE DatasetId = @DataSetId
    PRINT N'Deleting from StandardDataSetAggregationStorage: ' + Cast(@DataSetId AS NVARCHAR(50))
 
    DELETE
    FROM StandardDatasetAggregationHistory
    WHERE DatasetId = @DataSetId
    PRINT N'Deleting from StandardDataSetAggregationHistory: ' + Cast(@DataSetId AS NVARCHAR(50))
 
    DELETE
    FROM StandardDatasetAggregation
    WHERE DatasetId = @DataSetId
    PRINT N'Deleting from StandardDataSetAggregation: ' + Cast(@DataSetId AS NVARCHAR(50))
 
    DELETE
    FROM StandardDatasetTableMap
    WHERE DatasetId = @DataSetId
    PRINT N'Deleting from StandardDataSetTableMap: ' + Cast(@DataSetId AS NVARCHAR(50))
 
    DELETE
    FROM Dataset
    WHERE DatasetId = @DataSetId
     N'Deleting from DataSet: ' + Cast(@DataSetId AS NVARCHAR(50))
 
    FETCH NEXT FROM Cur INTO @DataSetId
    SET @FetchStatus = @@FETCH_STATUS
 
 END
 
 CLOSE Cur
 DEALLOCATE Cur

Have fun data-settin' !