Hyperion Planning change first year & delete year members

In this case, I was told to customize the Year dimension of hyperion planning application that should be changed the first year and deleted some year members which the end users didn't need.

Refer to the screenshot, following are the details requirements of end users: 

1. The first year member of Year dimension should be changed from 'FY13' to 'FY16';

2. The year members of Year dimension should be keept between 'FY16' to 'FY25', deleted the others year members;

3. Add the year member named 'FY22' of Year dimension .

Before the customize process, we need to backup the following relations database tables witch is used by the planning application for safety: 

1. HSP_ALIAS(Alias that is tied to an object)

2. HSP_CALENDAR(Internal System Calendar)

3. HSP_MEMBER(Base table for members)

4. HSP_OBJECT(Base table for all objects)

5. HSP_SCENARIO(Scenario members)

6. HSP_UNIQUE_NAMES(Internal table to track member name uniqueness)

Step1: Logon the schema(I used PL/SQL Developer) that was configured in application datasource and run the following query: 

    select t.object_id, t.object_name, t.old_name from HSP_OBJECT t

    where t.object_name like 'FY%'; 

Cause by the index PARENT_NAME(PARENT_ID, OBJECT_NAME) of HSP_OBJECT, I update the OBJECT_NAME from 'FY%' to 'UPDATE%':

    update HSP_OBJECT t set t.object_name = replace(t.object_name, 'FY','UPDATE')

    where t.object_name like 'FY%';

Copy the column strings from 'FY16' to 'FY25'

Paste the strings to OBJECT_NAME as OBJECT_ID(From small to large)

Paste the strings to OLD_NAME as OBJECT_NAME(Keep same as OBJECT_NAME)

Step2: Add 'FY22' row into the OBJECT_NAME and OLD_NAME , then move down sequentially 'FY23'&'FY24'&'FY25' as OBJECT_ID,  commit the update

Step3: Try to delete the one row include 'UPDATE%' in HSP_OBJECT, then got the error :
    ORA-02292: integrity constraint(schemaname.FK_HSP_MBR_MBRID) violated - child     record found

That Cause by the foreign key FK_HSP_MBR_MBRID of HSP_MEMBER

Query the member rows need to be deleted in HSP_MEMBER:

    select * from HSP_MEMBER t

    where t.member_id in

    (select t.object_id from HSP_OBJECT

    where t.object_name like 'UPDATE%';

Delete the query rows:

    delete from HSP_MEMBER t

    where t.member_id in

    (select t.object_id from HSP_OBJECT

    where t.object_name like 'UPDATE%';

Step4: Try to delete the one row include 'UPDATE%' in HSP_OBJECT again, got the other error :
    ORA-02292: integrity constraint(schemaname.FK_HSP_ALIAS_MBR) violated - child       record found

That Cause by the foreign key FK_HSP_ALIAS_MBR of HSP_ALIAS

Query the member rows need to be deleted in HSP_MEMBER:

    select * from HSP_ALIAS t

    where t.member_id in

    (select t.object_id from HSP_OBJECT

    where t.object_name like 'UPDATE%';

The query rows number(7) in HSP_ALIAS is not same as the rows number in HSP_OBJECT(12) include the strings 'UPDATE%'

That Cause by the end users didn't define the alias of others years members

Delete the query rows:

    delete from HSP_MEMBER t

    where t.member_id in

    (select t.object_id from HSP_OBJECT

    where t.object_name like 'UPDATE%';

Query the rows witch OBJECT_NAME include '20%' in HSP_OBJECT

Delete the others query rows which the end users didn't need (Foreign Key FK_HSP_ALIAS_ALS in HSP_OBJECT: HSP_OBJECT.OBJECT_ID = HSP_ALIAS.ALIAS_ID)

Step 5: Delete the rows include 'UPDATE%' in HSP_OBJECT

Step 6: Query the HSP_CALENDAR:

    select * from HSP_CALENDAR t

Update the FIRST_YEAR from 2013 to 2016 in HSP_CALENDAR

Step 6: Query/update/delete the rows witch include the 'FY%' in HSP_UNIQUE_NAMES as HSP_OBJECT (Keep same as OBJECT_ID&OBJECT_NAME)

Step 7: Restart Planning service and check the Year dimension

The customize process is completed  :)