On rare occasion, I encounter an EPMA application that will not offer an option to delete because the process that created it did not complete properly or the application was deleted from the Shared Service registry prior to removing it in the Application Library. This short article describes the process to remove an application from the Application Library in EPM 11.1.2.1 using SQL.
When I use this process, I am very careful to ensure that I have a solid backup of the EPMA relational repository as well as the relevant HFM or Planning relational databases. For example, if I was about to delete an HFM application using this method, I would first back up both the EPMA and HFM relational database repositories. If this was a Planning application, I would back up the EPMA, Planning System, and Planning Application repositories.
The Future of Big Data
With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.
The example SQL below will permanently delete an EPMA application from the database. This process does not remove the related Essbase, Planning, or HFM application.
1. Query the DS_APPLICATION table and determine the i_application_id of the application you wish to destroy and replace below with the value.
2. Query the DS_DIMENSION table and determine the i_dimension_id for each local dimension with the i_parent_application_id of the application you wish to destroy and replace in the query with a comma separated list of the i_dimension_id values.
3. Query the DS_VIEW table for the view id of the application you wish to destroy and replace below with the i_view_id.
4. Query the OR_OBJECT table for the c_object_id of the application you wish to destroy and replace with the value. Include single quotes around the value in the query.
After execution, additional steps may be necessary to delete related Essbase, HFM, or Planning applications.
/******************************************************/
/*EPM 11.1.2.x */
/*SQL to remove an EPMA application that will not
Delete from the application library/*
/******************************************************/
use EPMA
go
delete from DS_Property_Member_Array where I_APPLICATION_ID=<APPID>
delete from ds_property_member_memo where i_application_id =<APPID>
delete from ds_property_application where I_APPLICATION_ID=<APPID>
delete from ds_property_dimension_memo where I_APPLICATION_ID=<APPID>
delete from DS_Property_Member_Dimension where i_application_id=<APPID>
delete from ds_property_member where i_application_id=<APPID>
delete from ds_property_dimension where I_APPLICATION_ID=<APPID>
delete from ds_property_dimension_ref where I_APPLICATION_ID=<APPID>
delete from DS_PROPERTY_RELATIONSHIP where I_APPLICATION_ID=<APPID>
delete from ds_property_application_array where I_APPLICATION_ID=<APPID>
delete from DS_Property_Application_Ref where i_application_id=<APPID>
delete from DS_Relationship where i_dimension_id in (<DIMID>)
delete from DS_Member where i_dimension_id in (<DIMID>)
delete from DS_Property_Dimension_Array where i_dimension_id in (<DIMID>)
delete from DS_View_Filter where i_dimension_id in (<DIMID>)
delete from DS_Dimension where i_parent_application_id=<APPID>
delete from ds_application where I_APPLICATION_ID=<APPID>
delete from ds_view where I_View_ID =<VIEWID>
delete from or_object where c_object_ID=<OBJID>
