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
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 
. I had to delete Year dimension and then add it from Shared Library.
. 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
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.
1 is the current application, rest all are previous, so now you’ve to change the member names from each libraries.
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.
Update objects in similar way, FY11 to FY10 and so on.
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.
