Stupid Programming Trick No. 17 -- Hacking EPMA's Planning Time Distribution

The problem

A Planning 11.1.2.2 application was created with the wrong Time Distribution option. Oops. And no one noticed until the UAT. Double oops. The real oopsie in this is that once created, a Planning application CANNOT change its Time Distribution. For the uninitiated, this is the spread from upper level Time Periods members like YearTotal or quarters to lower level members. It defaults to an even spread or can be optionally set to fiscal calendar 445, 454, or 544 spreads. Again, this is a one-time shot, so whoever creates the application had best mind his p’s and q’s. Which didn’t happen. Like I wrote, oops.

The hunt for the guilty and his inevitable gruesome punishment shall await Cameron’s Star Chamber. I am waiting for HM The Queen to appoint me Privy Councillor so I may begin the joyous prosecution.

 

Putting that happy day aside, oh may it come, and soon, let’s start figuring out how to fix this oopsie without recreating the Planning application.

NB – We are going to go faaaaaaar beyond what Oracle recommends, supports, or will even give you the time of day on if you FUBAR this and then call Oracle Support. It is an interesting hack, and maybe the exigency of your situation calls for it, but know that you do this at your own risk. You Have Been Warned.

 

The beginning of the fix

Dave Farnsworth found Celvin’s Kattookarn’s blog post with the code to change the Even split to 4-5-4 in the Planning application.

 

Here’s the code to transform the Planning application (this is in the Planning app’s schema/database). In this example, it is to change it from Even to 4-5-4. NB – This is in SQL Server but I think it’s identical in PL/SQL except for the COMMIT commands.

 

USE HYP_PLN_ChgSprd

GO

/*

0 = even spread

1 = 4-4-5

2 = 4-5-4

3 = 5-4-4

*/

--Flip from Even to 4-4-5

BEGIN TRANSACTION ;

update hsp_systemcfg set support445='2';

update hsp_account set use_445 ='2' where use_445 ='0';

COMMIT TRANSACTION ;

So problem sorted, yes? 

 

Oops, not entirely

Did I mention this was an EPMA application? Ah, no I did not. And it’s important.

 

After making the above change, and bouncing the Planning service, deploys from EPMA work until there is a change to hierarchy. When that happens, the deploy fails with the below error message:

[May 9, 2013 1:38:35 PM]: Parsing Application Properties...Done

[May 9, 2013 1:38:35 PM]: Parsing Dimensions info...Done

[May 9, 2013 1:38:35 PM]: Registering the application to shared services...Done

[May 9, 2013 1:38:36 PM]: You cannot change the Weeks Distribution after deploying. You must select 454 as the Weeks Distribution before redeploying the application.[May 9, 2013 1:38:36 PM]: An Exception occurred during Application deployment.: You cannot change the Weeks Distribution after deploying. You must select 454 as the Weeks Distribution before redeploying the application.

Btw, here is what EPMA had selected – it’s Even, not 4-5-4. But Planning is 4-5-4. KABOOM.

The research

So a dive into the EPMA tables seems to be in order to change that “Use application distribution” to “Use 454 distribution”.

 

I took a look at the EPM data models to get a feel for what’s going on under the covers. The EPMA schema is pretty sparsely documented, to put it mildly, but after a fair bit of blundering about, I figured out that I needed to look at the DS_Property_Application table and its c_property_value field.

 

Alas, the documentation does not tell you what the property value should be for the time spread or even the property id number. So I created a bunch of different Planning apps, each with a different Time Spread and came up with the following possible settings when I interrogated that field:

• Even
• 445
• 454
• 544

 

Once I knew what to search for wrt the setting, I then needed to figure out the application id and the property member id. With those two (I only want to change one setting, and I only want to do it for the right app) pieces of information, I can write an UPDATE query to fix the spread issue in EPMA. 

 

What application, what property?

I wrote this query to get that information, knowing that the application name is ChgSprd:

 

SELECT

A.c_application_name

,P.*

FROM DS_Property_Application P

INNER JOIN DS_Application A ON

A.i_application_id = P."i_application_id"

AND A.i_library_id = P.i_library_id

WHERE

A.c_application_name = 'ChgSprd'

AND c_property_value = 'Even'

When looking at the below, it looks like that the setting is repeated for each deploy of the application

c_application_name	i_library_id	i_application_id	i_prop_def_dimension_id	i_prop_def_member_id	c_property_value
ChgSprd	1	7	1	346	Even
ChgSprd	87	7	1	346	Even
ChgSprd	88	7	1	346	Even
ChgSprd	89	7	1	346	Even
ChgSprd	90	7	1	346	Even
ChgSprd	91	7	1	346	Even
ChgSprd	92	7	1	346	Even
ChgSprd	93	7	1	346	Even
ChgSprd	94	7	1	346	Even
ChgSprd	95	7	1	346	Even
ChgSprd	96	7	1	346	Even
ChgSprd	97	7	1	346	Even
ChgSprd	98	7	1	346	Even
ChgSprd	99	7	1	346	Even
ChgSprd	100	7	1	346	Even
ChgSprd	101	7	1	346	Even

The important bits are: this is application id 7 and the property id is 346. NB – I have been able to test this on completely different system – 346 is the property that contains the time distribution and the i_application_id varies. You will need to run the above query to figure out the i_application_id.

 

The fix

With an i_application_id firmly in hand, I can write an UPDATE query as below:

/*

Possible values for c_property_value when i_prop_def_member_id = 346

Even

445

454

544

*/

UPDATE P

SET P.c_property_value = '454'

FROM DS_Property_Application P

WHERE

i_prop_def_member_id = '346'

AND i_application_id = '7'

Btw, I tried just changing the last record in the above list of applications (the 101) and it didn’t work (although I got to play with SQL’s MAX function in a subquery so there is that). I had to change ALL of the values from Even to 454. I think maybe I could could have gotten away with changing just the i_library_id setting of 1 but I am not made of free time to test this stuff out. If you try it (on a throwaway instance, please) and it works, send in a comment to this blog.

Anyway, I changed them all, and then I bounced the Hyperion EPMA Server service and (since this was a compact deployment) the Hyperion EPM Server- Web Application service:

NB – In a real environment, I found I had to bounce all of the EPM services. Quite painful across a production environment but such is life. And yes, just restarting EPMA and Planning did not do it – there was a serious amount of relational caching going on.

I then logged back into Workspace, went to the Dimension Library, added CL_test3, and saw the following:

I now have 454 distribution. Success! Boil in bag! Hopefully.

The proof

So the proof of this particular pudding is to run a deploy.

And in Planning:

Fixed on both sides: Planning and EPMA. Whew.

 

A couple of notes

Again, for goodness’ sakes, this is a hack, and I had to do it, but I am pretty sure if you try to use this blog post as evidence that this is okay and, “Cameron said I can do it” Oracle is going to laugh in your face. Do it if you must, but may you never have to.

 

With that enormous caveat, if you are going to do it in your environment:

1. Make a backup of the development EPMA schema. And then in development…
2. Run the query to confirm the application and property ids. Yourappname will replace ChgSprd.
3. Modify the UPDATE statement to change Even/445/454/544 to Even/445/454/544.
4. Don’t forget to change the Planning application’s 454 spread, so maybe a Planning application schema backup is in order too.
5. Restart all Hyperion EPM services (I found that I needed complete restarts of all EPM services for this to work outside of a compact deployment and yes that hurt).
6. See if the deploy works. Prayer to whatever God or gods you worship is recommended at this stage. 

 

This was fun, kind of, and I’m not as scared of the EPMA tables as I once was. And it was a pretty cool hack. So I guess it was worth it. But an Order in Council is still going to go out – vengeance shall be mine as all of the above ate a lot of time I didn’t have.

Be seeing you.