Geekstuff (EPMA) - ZAP! that orphaned EPMA application!

Introduction

Probably most of you who worked with EPM Architect before faced the situation where an application in EPMA is in a corrupted deployment state and cannot be deleted properly from EPMA the usual way, or EPMA complains that the application already exists while attempting to recreate the EPMA application you deleted.

In most cases running diagnostic against the application would get you back on-track. If not, as a last resort you can drop all tables from the EPMA database by re-configuring the EPMA data source. However in most cases this is not an option.

If you have manually removed all references of the application in all EPM components (how is out-of-scope of this post) and still you cannot re-create the application in EPMA, consider using my SQL script below to ZAP! that orphaned application from the EPMA database.

Disclaimer

Make sure to create a backup of the EPMA database before using this script. In addition you use this script at you own risk! I have tested this script in 11.1.2.4 on MS SQL 2012 only, however it should work on all version of EPMA.

USE ONLY AS A LAST RESORT!

Zap!

Below the script. Simply replace &ltname&gt with the actual application name you wish to ZAP! from the EPMA database and execute the script. If you want to perform a dry-run, replace "DELETE" with "SELECT *".

/* Author: christian-hoekstra.blogspot.nl Date: 8th June 2016 Version: 1.1 Tested on: MS SQL 2012   Fixed: Similar application names also gets deleted Fixed: XML parsing: line 1, character 10, end tag does not match start tag */   -- Declare required variables DECLARE @c_application_name AS VARCHAR(20); -- Stores application name to DELETE DECLARE @i_application_id AS INT;           -- Stores ID of given application name DECLARE @i_dimension_ids AS VARCHAR(max);   -- Store comma delimited array of dimension id's part of the given application DECLARE @i_view_id AS INT;                  -- Store View ID of given application name DECLARE @c_object_id AS VARCHAR(10);        -- Store Object ID of goven application name DECLARE @xml AS XML;                        -- Used to convert @i_dimension_ids to int values   -- Please set the name of the application to DELETE SET @c_application_name = '<name>';   /* --- Do not edit below --- */ -- Be verbose PRINT 'Application Name: ' + @c_application_name;   -- Get application ID SELECT DISTINCT @i_application_id = i_application_id FROM DS_APPLICATION WHERE c_application_name = @c_application_name;   -- Be verbose PRINT 'Found application ID: ' + CAST(@i_application_id as nvarchar);   -- Collect dimension ID's SELECT @i_dimension_ids = COALESCE(@i_dimension_ids + ', ', '') + CAST(i_dimension_id as nvarchar) FROM (  SELECT DISTINCT i_dimension_id, i_parent_application_id  FROM DS_Dimension  ) AS DIMENSIONID WHERE i_parent_application_id = @i_application_id;   -- Be verbose PRINT 'Found the following dimension IDs: ' + @i_dimension_ids;   -- Get View ID SELECT DISTINCT @i_view_id = i_view_id FROM DS_View WHERE c_view_name = @c_application_name;   -- Be verbose PRINT 'View ID: ' + CAST(@i_view_id as nvarchar);   -- Get Object ID SELECT @c_object_id = c_object_id FROM or_object WHERE x_object_data like '%name="' + @c_application_name + '"%';   -- Be verbose PRINT 'Object ID:' + @c_object_id;   -- Workarround to return value from @i_dimension_ids as INT SET @xml = cast(('<ID>'+replace(@i_dimension_ids,',' ,'</ID><ID>')+'</ID>') as xml);   -- Start deleting stuff DELETE FROM DS_Property_Member_Array WHERE I_APPLICATION_ID=@i_application_id; DELETE FROM ds_property_member_memo WHERE i_application_id =@i_application_id; DELETE FROM ds_property_application WHERE I_APPLICATION_ID=@i_application_id; DELETE FROM ds_property_dimension_memo WHERE I_APPLICATION_ID=@i_application_id; DELETE FROM DS_Property_Member_Dimension WHERE i_application_id=@i_application_id; DELETE FROM ds_property_member WHERE i_application_id=@i_application_id; DELETE FROM ds_property_dimension WHERE I_APPLICATION_ID=@i_application_id; DELETE FROM ds_property_dimension_ref WHERE I_APPLICATION_ID=@i_application_id; DELETE FROM DS_PROPERTY_RELATIONSHIP WHERE I_APPLICATION_ID=@i_application_id; DELETE FROM ds_property_application_array WHERE I_APPLICATION_ID=@i_application_id; DELETE FROM DS_Property_Application_Ref WHERE i_application_id=@i_application_id; DELETE FROM DS_Relationship WHERE i_dimension_id in (SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('ID') as T(N)); DELETE FROM DS_Member WHERE i_dimension_id in (SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('ID') as T(N)); DELETE FROM DS_Property_Dimension_Array WHERE i_dimension_id in (SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('ID') as T(N)); DELETE FROM DS_View_Filter WHERE i_dimension_id in (SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('ID') as T(N)); DELETE FROM DS_Dimension WHERE i_parent_application_id=@i_application_id; DELETE FROM ds_application WHERE I_APPLICATION_ID=@i_application_id; DELETE FROM ds_view WHERE I_View_ID = @i_view_id; DELETE FROM or_object WHERE c_object_ID=@c_object_id;