Change History Years in Planning on an EPMA application – Understanding EPMA tables Part II


Second post in understanding EPMA tables, if you missed the first one here is the link.
When I wrote the first post on EPMA, my intention was to convert all Classic Planning hacks (like history year addition, change weekly distribution, change start month) into EPMA hacks.
I started with adding history years and was not sure whether I can achieve it. I shelved this post for 7 months!!!!
I couldn’t accept that I cannot do it. There are those days!!!!!
After 7 months, it is a feel good factor that you were able to crack it.
What we are going to discuss is not a supported/recommended way, do at your own risk.

I’ve an EPMA application with start year FY11
before change
I decided to share Year dimension, in 11.1.2.3.500, it’ll warn you about it. It won’t allow you to share Year dimension.
Error copying dimension Year: Member "Year" failed the following validation(s): 
Validation: PlanningCanRenameMember. 
Reason: Members of Year dimensions cannot be renamed if the Planning application to which they belong has been deployed.

Nice Smile. I had to delete Year dimension and then add it from Shared Library.
Since we are talking about dimension, let’s look at a table called DS_DIMENSION

year dimension
Don’t think that this table only holds “dimension” information, you’ll see that even Application Class (System, Planning, Essbase (BSO), Essbase (ASO), Consolidation) is a dimension.
E_DIMENSION_TYPE determines where it is EPMA internal dimension (MetaData) or a Normal dimension.
E_DIMENSION_STATE will tell you whether it is a Shared/Local dimension.
Back to where we left, I tried to deploy the application and got this error, which was expected.
Validation Log
==============

Job Id: 76
Created : 4/29/2014 3:37:21 PM GMT
Application : EPMAPLN

Validation Summary
==================

Result : Failure
Summary : There were 1 errors and 0 warnings during the validation process. For details refer to the error and warning section of this log.


Error Details
=============
Error : You can only add years that are greater than the current year range defined for the application.

The start year of the application now started showing 2010.

Now where is the problem, this is where the admiration towards EPMA started, it validates against Planning application and against itself. When you start a deployment it starts keeping a record of that entry. Don’t believe me look at DS_APPLICATION table.

library id
1 is the current application, rest all are previous, so now you’ve to change the member names from each libraries.
update ds_member
Get the library_id of your application and change FY11 (which was the earlier start year) to FY10, FY12 to FY11 and so on, commit the changes.
As I said, it validates against Planning too, so you’ll have to change HSP_OBJECT and HSP_CALENDAR tables.
planning application members
Update objects in similar way, FY11 to FY10 and so on.
update planning application
Update the FIRST_YEAR in HSP_CALENDAR. Now one change which I didn’t perform 7 months back and which kept me hunting for the reason was because of a column!!!!!
I did update the tables, restarted EPMA and Planning, however when I try to deploy, it started giving me this error message

[Apr 24, 2014 12:12:22 PM]: Parsing Application Properties...Done
[Apr 24, 2014 12:12:22 PM]: Parsing Dimensions info...Done
[Apr 24, 2014 12:12:36 PM]: Registering the application to shared services...Done
[Apr 24, 2014 12:12:37 PM]: You cannot change the Start Year after deploying. You must select 2011 as the Start Year before redeploying the application.
[Apr 24, 2014 12:12:37 PM]: An Exception occurred during Application deployment.: You cannot change the Start Year after deploying. You must select 2011 as the Start Year before redeploying the application.

I kept wondering where is it still fetching 2011, and the answer was OLD_NAME in HSP_OBJECT, so before you repeat the same mistake, change that column in similar fashion (FY11 to FY10 and so on)
I was paranoid and went one more step to delete the entries from DS_TRANSACTION_HISTORY. (I don’t think this is needed). However if you need it here is the sql for that.

delete from ds_transaction_history where c_application_name ='EPMAPLN';
Tables to be updated
DS_MEMBER columns C_MEMBER_NAME and C_MEMBER_DESCR
HSP_CALENDAR column FIRST_YEAR
HSP_OBJECT columns OLD_NAME and OBJECT_NAME
Restart EPMA and Planning services and deploy the application.

[Apr 29, 2014 2:58:53 PM]: Parsing Application Properties...Done
[Apr 29, 2014 2:58:53 PM]: Parsing Dimensions info...Done
[Apr 29, 2014 2:58:58 PM]: Registering the application to shared services...Done
[Apr 29, 2014 2:58:59 PM]: Checking for rates properties...Done
[Apr 29, 2014 2:58:59 PM]: Loading Smart Lists...Done
[Apr 29, 2014 2:58:59 PM]: Loading Alias Tables...Done
[Apr 29, 2014 2:58:59 PM]: Updating the default user preferences...Done
[Apr 29, 2014 2:58:59 PM]: Loading Dimensions...Done
[Apr 29, 2014 2:59:00 PM]: Loading Attribute Dimensions...Done
[Apr 29, 2014 2:59:00 PM]: Loading Attribute Members...Done
[Apr 29, 2014 2:59:00 PM]: Loading members for dimension Year...Done
[Apr 29, 2014 2:59:01 PM]: Loading members for dimension Entity...Done
[Apr 29, 2014 2:59:01 PM]: Loading members for dimension Version...Done
[Apr 29, 2014 2:59:01 PM]: Loading members for dimension Account...Done
[Apr 29, 2014 2:59:01 PM]: Loading members for dimension Period...Done
[Apr 29, 2014 2:59:02 PM]: Loading members for dimension Asset Class...Done
[Apr 29, 2014 2:59:02 PM]: Loading members for dimension Employee...Done
[Apr 29, 2014 2:59:02 PM]: Loading members for dimension Asset Detail...Done
[Apr 29, 2014 2:59:02 PM]: Loading members for dimension Job...Done
[Apr 29, 2014 2:59:02 PM]: Loading Scenario Members...Done
[Apr 29, 2014 2:59:03 PM]: Loading Base Currency Members...Done
[Apr 29, 2014 2:59:03 PM]: Starting Cube Create/Refresh...Done
[Apr 29, 2014 2:59:23 PM]: Creating Security Filters...Done
[Apr 29, 2014 2:59:23 PM]: Application deploy/redeploy is complete.


About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.