The never ending question about block creation

The never ending question about block creation

Joined: February 9th, 2004, 10:10 am

March 29th, 2017, 11:43 am #1

Dears,

A great consultant said once : "There's lots of ways to skin this cat. And lots of ways to do allocations in Essbase". Source : http://www.network54.com/Forum/58296/thread/1396981685
The kind of consultant who's indeed missing from my RSS feeds lists, simply because his blog is already in everyone's mind...

So when you have to allocate on dense, generic products (sku) by cost centers based on volumes which are on generic cost center and by products, is the only remaining solution to create blocks with a dataload?
This question because:
- datacopy : seems useless in that case (refer to Mike's case, on top link)
- createblockoneq : useless as calculating on dense (sparse would be toooo slow, already tested)
- createnonmissingblk : toooo slow
- createnonmissingblkoneq (11.1.2.4 latest patch) : not tested but I guess it is a mix of the two previous
- @createblock : I have seen some articles (thx Celvin) about still lacking information or real cases on how to set it properly.

Here is some ideas I will be testing, should I test it that way?

FIX(@RELATIVE("Prod", 0),
_Cust1, Cust2,
_RF,
_@RELATIVE("Plant", 0),
_@RELATIVE("CC1", 0),
_M01:M12,
_@RELATIVE("Costs",0))

_"LC" (
_IF(RF->"GEN_Prod"->"GEN_Cust" <> #MISSING AND "CC_RATE"->"Prod"->"GEN_CC"->ACTUAL->"M01" == 1)
_@CREATEBLOCK(@relative(Prod,0));
_@CREATEBLOCK(@relative(Cust,0));
_@CREATEBLOCK(@relative(Plant,0));
_@CREATEBLOCK(@relative(CC,0));
__"LC"->"GEN_CUS" = &CUR_INP_RF_N->"GEN_PDT"->"GEN_CUS" * "EFF_SKU_RATE"->"CC_GEN"->"Plant" / "EFF_SKU_RATE"->"CC_GEN"->"Plant"->"TOT_PDT_SAP";
_);
ENDFIX

or that way?

FIX(@RELATIVE("Prod", 0),
_Cust1,
_Cust2,
_RF,
_@RELATIVE("Plant", 0),
_@RELATIVE("CC1", 0),
_M01:M12,
_@RELATIVE("Costs",0))

_"LC" (
_IF(RF->"GEN_Prod"->"GEN_Cust" <> #MISSING AND "CC_RATE"->"Prod"->"GEN_CC"->ACTUAL->"M01" == 1)
_@CREATEBLOCK(@CURRMBR(PRODUCT)->@CURRMBR(CUSTOMER)->@CURRMBR(PLANT)->@CURRMBR(COSTCENTER));
__"LC"->"GEN_CUS" = &CUR_INP_RF_N->"GEN_PDT"->"GEN_CUS" * "EFF_SKU_RATE"->"CC_GEN"->"Plant" / "EFF_SKU_RATE"->"CC_GEN"->"Plant"->"TOT_PDT_SAP";
_);
ENDFIX
Quote
Like
Share

TimG
TimG

March 29th, 2017, 11:53 am #2

Just a question, but have you dismissed @ALLOCATE (or @MDALLOCATE) from your list of candidates for a reason? I've just been using this function on a project - I like that it takes the block creation headache away as a separate step.
Quote
Share

Seoux
Seoux

March 29th, 2017, 12:50 pm #3

Hi Tim,

You won't believe me but I never used these functions for allocations because IMO they are less readable (at least if no experience with them). However as far as I could research they handle block creation - do you confirm this point?
Quote
Share

Cameron Lackpour
Cameron Lackpour

March 29th, 2017, 2:32 pm #4

Sroux,

The Tech Ref doc for @ALLOCATE does *not* state (or at least I couldn't find) that it creates blocks.

But the section on @CREATEBLOCK does state that:
http://docs.oracle.com/cd/E57185_01/ESB ... block.html

"Whereas the allocation functions (@ALLOCATE and @MDALLOCATE) also create the necessary target blocks, those functions are intended specifically for allocating values. The purpose of @CREATEBLOCK is only to enable rapid block creation, without reading or writing data."

Weird.

Regards,

Cameron Lackpour
Quote
Share

Martin S
Martin S

March 30th, 2017, 10:31 am #5

Several years ago, I was involved in an allocation project, where we hit the block creation issue in a big way.

We investigated all the usual approaches mentioned above, without much success .

As best as I can recall, the approach that worked best for us was

1) to write some sql code that created a data file which would create the possible data intersections (we had a member called "Block", where we entered a data value of 1.)

- it has been a few years, but the sql involved using the data file that held the driver values and joining in with (memory failing me here) another table that held the target intersection. This would create a data file, which basically would create a record for every possible target block in the application.

2) Once the data file was loaded, we then knew that all the possible target blocks would exist at allocation time.

3) Perform the Allocation step

4) After the allocation step was complete, we then changed the value of the member "Block" back to #Missing and then executed a "ClearBlock all". This would then remove any blocks that were not receiving an allocation.

5) Continue with aggregations and whatever else was needed.

Surprisingly, this approach worked pretty well. I acknowledge that it may not be for everyone, but when you are desperate....

Cheers

M

Quote
Share

Joined: March 4th, 2014, 5:57 am

March 30th, 2017, 12:22 pm #6

Dears,

A great consultant said once : "There's lots of ways to skin this cat. And lots of ways to do allocations in Essbase". Source : http://www.network54.com/Forum/58296/thread/1396981685
The kind of consultant who's indeed missing from my RSS feeds lists, simply because his blog is already in everyone's mind...

So when you have to allocate on dense, generic products (sku) by cost centers based on volumes which are on generic cost center and by products, is the only remaining solution to create blocks with a dataload?
This question because:
- datacopy : seems useless in that case (refer to Mike's case, on top link)
- createblockoneq : useless as calculating on dense (sparse would be toooo slow, already tested)
- createnonmissingblk : toooo slow
- createnonmissingblkoneq (11.1.2.4 latest patch) : not tested but I guess it is a mix of the two previous
- @createblock : I have seen some articles (thx Celvin) about still lacking information or real cases on how to set it properly.

Here is some ideas I will be testing, should I test it that way?

FIX(@RELATIVE("Prod", 0),
_Cust1, Cust2,
_RF,
_@RELATIVE("Plant", 0),
_@RELATIVE("CC1", 0),
_M01:M12,
_@RELATIVE("Costs",0))

_"LC" (
_IF(RF->"GEN_Prod"->"GEN_Cust" <> #MISSING AND "CC_RATE"->"Prod"->"GEN_CC"->ACTUAL->"M01" == 1)
_@CREATEBLOCK(@relative(Prod,0));
_@CREATEBLOCK(@relative(Cust,0));
_@CREATEBLOCK(@relative(Plant,0));
_@CREATEBLOCK(@relative(CC,0));
__"LC"->"GEN_CUS" = &CUR_INP_RF_N->"GEN_PDT"->"GEN_CUS" * "EFF_SKU_RATE"->"CC_GEN"->"Plant" / "EFF_SKU_RATE"->"CC_GEN"->"Plant"->"TOT_PDT_SAP";
_);
ENDFIX

or that way?

FIX(@RELATIVE("Prod", 0),
_Cust1,
_Cust2,
_RF,
_@RELATIVE("Plant", 0),
_@RELATIVE("CC1", 0),
_M01:M12,
_@RELATIVE("Costs",0))

_"LC" (
_IF(RF->"GEN_Prod"->"GEN_Cust" <> #MISSING AND "CC_RATE"->"Prod"->"GEN_CC"->ACTUAL->"M01" == 1)
_@CREATEBLOCK(@CURRMBR(PRODUCT)->@CURRMBR(CUSTOMER)->@CURRMBR(PLANT)->@CURRMBR(COSTCENTER));
__"LC"->"GEN_CUS" = &CUR_INP_RF_N->"GEN_PDT"->"GEN_CUS" * "EFF_SKU_RATE"->"CC_GEN"->"Plant" / "EFF_SKU_RATE"->"CC_GEN"->"Plant"->"TOT_PDT_SAP";
_);
ENDFIX
Source_slice (
target_slice->some_member = Source_slice;
)
Quote
Like
Share



Confirmation of reply: