Well, if you've been following the blogging and news from Open World you've probably already heard about the Exalytics appliance. This has just gone on-sale and features an in-memory Essbase instance as well as the rest of the BI Foundation. It's an incredible offering and I'm sure this will become a consideration for many company's BI needs. The most interesting thing I heard about the Exalytics system however, was not what it can do right now, but what it will include in the future.
As it was explained to me, Oracle has put a piece of software on the appliance that allows for products like Essbase to be moved to an in-memory solution without having to rewrite the software. This adds a lot of potential flexibility to what will be offered on the Exalytics appliance. John O'Rourke from Oracle showed the Hyperion SIG meeting a roadmap of the future EPM products and one thing he noted was that the next release of Planning was going to be available on the Exalytics platform. This goes a long way to addressing the short comings of the BSO architecture underneath planning in a couple of different ways. First of all, if everything is in-memory you should be able to build bigger Planning models without performance degradation. Secondly, if your Planning model is still too big, the functionality to produce ASO models from within Planning will become more viable because that process will obviously become faster. Either way it should make Hyperion Planning able to handle significantly larger metadata models.
That's the big news from Open World as I see it and don't forget to attend Connection Point in November in Atlanta! You can get details from our website, http://wwww.mindstreamanalytics.com
Friday, October 14, 2011
Friday, August 26, 2011
Hyperion Planning: Performance Settings Dimension order - not all dims move
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.
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.
Monday, January 24, 2011
OAUG Essbase Today
I just finished giving a presentation for the OAUG on Essbase and it was probably way to technical for the audience, but I had a great time putting it together. It had a lot of great examples so if you are interested in downloading it you can find it here, http://www.mindstreamanalytics.com/presentations.html
The most interesting topic in my mind is the capability of Essbase ASO to perform allocations and custom calculations. It's a big leap forward for ASO and definitely sets the stage for ASO to take over more of what BSO is currently used for. However, the big downside right now is that the ASO calculations can't utilize member functions for the allocations and are limited on the custom calculation side. Think about having to have a different formula or allocation for every account instead of being able to group like allocation methodologies together with an @UDA() or @ATTRIBUTE() function, or even an @RELATIVE() function. Once ASO can leverage those types of powerful functions it will really be ready to take over for BSO. While some of the more advanced BSO calculations like @MOD() probably won't be in ASO for some time they aren't heavily used and to some degree I wonder if it's even worth Oracle's time to recreate them in ASO. The people that are using those types of functions probably don't need to move to ASO because the data set size is probably smaller.
Well, the NCOAUG conference is coming up along with the FP&A innovation conference in San Diego. Hope to see everyone at one of them!
Subscribe to:
Posts (Atom)