How to manually delete a data set from the Data Warehouse in OM 2007 R2

  • Article

IMPORTANT: Always 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 *<br> 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<br>    @DataSetLike NVARCHAR(255),<br>    @MGName NVARCHAR(255),<br>    @DataSetId UNIQUEIDENTIFIER,<br>    @MGid INT,<br>    @FetchStatus INT,<br>    @AgId INT  SET @MGName = N' 
MG_NAME '<br> SET @DataSetLike = N' DATASET_NAME

















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

 

 

Have fun data-settin' !