I got an email from a friend mine asking, “How can I delete year members from Hyperion Planning?”
I was thinking, “Isn’t there a delete icon to do so?” Then I suddenly realized that the feature was only available in the Cloud version.
I thought of trying this on the new 11.2 Vision application, and here is the year dimension.
FY28, FY29, and FY30 were added accidentally, and now I want to remove them.
The first thing that came to my mind was, how about using the Operation column in a metadata file and try deleting members.
Well, I couldn’t trick Planning, below is the error message I got after running the metadata import.
Note: Below given is not a recommended approach. I will advise you to delete and re-create the application with a previous LCM file.
How to delete year members
There are a few tables that we are doing to look at to remove these members.
- HSP_ALIAS
- HSP_MEMBER
- HSP_MEMBER_FORMULA
- HSP_MEMBER_TO_UDA
- HSP_MEMBER_TO_ATTRIBUTE
- HSP_UNIQUE_NAMES
- HSP_OBJECT
- HSP_CALENDAR
First, we need to get the OBJECT_ID from the HSP_OBJECT table for the 3 years. Once we get that information, we are going to delete it from all the tables except HSP_CALENDAR.
If you are deleting a year member that is a start or end year of a scenario member, you need to change that from the Scenario dimension. If you don’t change that, then you are going to get FK_HSP_SCEN_EYRID constraint violation error.
What about the HSP_CALENDAR table? This table which holds information on the FIRST YEAR, numbers of years are there in the application and so on.
We must update this table with the reduced number; in this case, it is going to 22-3, 19 years.
Now that we know what needs to be done, here is the SQL I ran on my Oracle Database to delete the members.
DEFINE deleteYears = "'FY28','FY29', 'FY30'";
DELETE FROM HSP_MEMBER WHERE MEMBER_ID IN( SELECT OBJECT_ID FROM HSP_OBJECT WHERE OBJECT_NAME IN (&deleteYears));
DELETE FROM HSP_MEMBER_FORMULA WHERE MEMBER_ID IN( SELECT OBJECT_ID FROM HSP_OBJECT WHERE OBJECT_NAME IN (&deleteYears));
DELETE FROM HSP_MEMBER_TO_UDA WHERE MEMBER_ID IN( SELECT OBJECT_ID FROM HSP_OBJECT WHERE OBJECT_NAME IN (&deleteYears));
DELETE FROM HSP_MEMBER_TO_ATTRIBUTE WHERE MEMBER_ID IN( SELECT OBJECT_ID FROM HSP_OBJECT WHERE OBJECT_NAME IN (&deleteYears));
DELETE FROM HSP_ALIAS WHERE MEMBER_ID IN( SELECT OBJECT_ID FROM HSP_OBJECT WHERE OBJECT_NAME IN (&deleteYears));
DELETE FROM HSP_UNIQUE_NAMES WHERE OBJECT_ID IN( SELECT OBJECT_ID FROM HSP_OBJECT WHERE OBJECT_NAME IN (&deleteYears));
DELETE FROM HSP_OBJECT WHERE OBJECT_NAME IN (&deleteYears);
UPDATE HSP_CALENDAR SET NUM_YEARS=19;
COMMIT;
Restarted Planning service, and viola those 3 members are gone.
