For those of you using Planning 11.1.2 and have tried to migrate pieces of a Planning application from one environment to another you have probably encountered this bug. What happens is that LCM doesn't keep the dimension order properly and when you open Planning in the target environment post migration you can't re-order the dimensions the way you want them.
The ISSUE: The issue is that after the migration using LCM in a classic Planning app in 11.1.2 we were not able to utilize the "Performance Settings" funtionality in Planning to reorder the dimensions for Essbase. The underlying problem is in the Planning relational repository.
SOLUTION: First, you need to query the Planning application's relational database or schema. This is the one for the Planning application, not the Planning system relational database. (If they are one in the same you need to fix that immediately.)
The query you want is below:
SELECT A. DIM_ID, B.OBJECT_ID, B.OBJECT_NAME, A.POSITION1, A.POSITION2, A.POSITION3, A.POSITION4, A.POSITION5
FROM PRD_HYP_PLANAPP1.HSP_DIMENSION A, PRD_HYP_PLANAPP1.HSP_OBJECT B
WHERE A.DIM_ID=B.OBJECT_ID
ORDER BY POSITION5;
This statement should return something similar to the following results:
DIM_ID OBJECT_ID OBJECT_NAME POSITION1 POSITION2 POSITION3 POSITION4 POSITION5
30 30 HSP_Rates 0 0 0 0 0
34 34 Period 2 2 2 2 1
32 32 Account 1 1 1 1 2
89731 89731 Asset Class 10 10 10 10 3
120689 120689 Project 15 15 15 15 4
38 38 Year 5 3 3 5 4
89730 89730 Line Item 9 9 9 9 5
37 37 HSP_XCRNCY 6 6 6 6 6
33 33 Entity 4 7 5 3 7
31 31 Scenario 12 4 4 7 7
89471 89471 Employee 8 8 8 4 8
35 35 Version 7 5 7 8 9
101836 101836 Product 11 11 11 11 11
91033 91033 Data_Type 3 12 12 12 12
103461 103461 Channel 13 13 13 13 13
103467 103467 Tier 14 14 14 14 14
90128 90128 JobTitle 100 100 100 100 100
120690 120690 Program 101 101 101 101 101
120727 120727 Classification 102 102 102 102 102
This represents your Planning dimensions as well as some of the other things that Planning keeps track of when it interacts with Essbase. In this example we are using all 5 Plantypes, 3 standard, Workforce and CapEx. The dimensions for the standard 3 and Workforce are listed here in POSITION1, POSITION2, POSITION3 and POSITION4. These plan types all work fine and can be reordered within the standard Planning web application with no problems. It's the CAPEX cube that we are having problems with and which is represented here in POSITION5. (In order to figure out what POSITIONX column relates to which cube you can move the dimensions in the Planning application and re-query the relational database to see which values changed.) You will notice that in POSITION5 there are two rows with values of 4 and two rows with values of 7. This is the problem and why Planning cannot move the dimensions.
To fix this particular case we ran the following update statements. These update statements re-ordered the dimensions in the order we wanted them, but also fixed the problem of not being able to use the Planning web "Performance Settings" funtionality.
UPDATE PRD_HYP_PLANAPP1.HSP_DIMENSION A
SET A.POSITION5 = 4
WHERE A.DIM_ID = '89731'
UPDATE PRD_HYP_PLANAPP1.HSP_DIMENSION A
SET A.POSITION5 = 7
WHERE A.DIM_ID = '120689'
UPDATE PRD_HYP_PLANAPP1.HSP_DIMENSION A
SET A.POSITION5 = 3
WHERE A.DIM_ID = '38'
UPDATE PRD_HYP_PLANAPP1.HSP_DIMENSION A
SET A.POSITION5 = 9
WHERE A.DIM_ID = '89730'
UPDATE PRD_HYP_PLANAPP1.HSP_DIMENSION A
SET A.POSITION5 = 5
WHERE A.DIM_ID = '33'
UPDATE PRD_HYP_PLANAPP1.HSP_DIMENSION A
SET A.POSITION5 = 10
WHERE A.DIM_ID = '31'
UPDATE PRD_HYP_PLANAPP1.HSP_DIMENSION A
SET A.POSITION5 = 15
WHERE A.DIM_ID = '35'
Let me know if this works for you. If you get stuck somewhere along the way, give me a shout and I’ll help you get un-stuck.
