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 :)
