BPC Script Logic

[Former Member]

Hi BPC Experts,

I am facing some distributions from the budgeting point of view of the BPC tool. I am trying to do these distributions using script logic but I really don't know how it works. Looking in the forum i have found some documentation but I haven't found code examples.

Where can I find examples of script logic for distribution purposes? Is there any other way of doing it without using VB macros?

Thanks in advance

[Former Member · Accepted solution]

Dear Lauro Bombin,

I am mailing you a PDF document to you gmailID. It will help you in script logic.

I am also working on Budgeting application in SAP BPC.

Regards,

Satish.

[Former Member · Accepted solution]

Thank you Satish

I already have this document, it is useful, but I am still confused about how to develop a distribution script. I am also having some problems with some instructions.

[Former Member · Accepted solution]

Hi Lauro,

What you call distribution, is that the same thing as allocation? If so, you should look carefully at the BPC SQL logic syntax for *RUN_ALLOCATION. This is a very powerful, flexible syntax that doesn't require much coding to access many different types of allocation drivers and cross-dimensional intersections of data.

I would definitely avoid using VB code. VB (if you're talking about VBA macros in Excel) will only calculate the data that's in the spreadsheet in front of the end user. The key benefit of SQL logic is that it can act directly on the database, allowing for greater performance, automation, security, etc.

The current version of the admin guide has a very (very very very) limited description of *RUN_ALLOCATION. It does much more than is documented there, but I don't know if any documentation has been made available publicly or to customers. Please check with SAP Support on this.

In the meantime, if you have a specific scernario that you're trying to figure out, explain the setup, and perhaps we can help you out.

[Former Member · Accepted solution]

Hi Tim,

I have tryed to use the ALLOCATION function, but I am not able to make it work, even in a really simple case.

For instance, in this example:

*RUNALLOCATION

*FACTOR=USING/100

*DIM ENTITY WHAT=ADMIN; WHERE<>ADMIN; USING<>ADMIN

*DIM ACCOUNT WHAT=RENTAL; WHERE=RENTAL; USING=PERCENTAGE

*ENDALLOCATION

Where would be the value of PERCENTAGE stored, in a table, in another LGF file inside the folder SYSTEM LIBRARY? What can we put in the USING tag, values, which kind of expressions?

What happen with the other dimensions of the application that are not specified in the RUNALLOCATION instruction?

Who can we run the script logic? Please correct me if I am not right.

I think there are three options:

1.- in the default.lgf file, writing directly the code or calling other lgl file with it

2.- in other LGF file that we could run using a package

3.- in other LGF file that we could run through an event of VBA for Excel

Thanks again Tim

[Former Member · Accepted solution]

You have a few syntax errors, particularly the missing = sign. This is correct syntax:

*RUNALLOCATION
*FACTOR=USING/100
*DIM ENTITY WHAT=ADMIN; WHERE=ADMIN; USING=ADMIN
*DIM ACCOUNT WHAT=RENTAL; WHERE=RENTAL; USING=PERCENTAGE
*ENDALLOCATION

But the logic doesn't make sense -- this says to allocate the rental expense from the admin entity to the admin entity.

Let's say the scenario is, allocate the rental expense from Admin to all the operating entities, based on a percentage of total space utilization, that is keyed in manually into an account called RentalAllocDriver. (This needs to be an account in the account dimension, not in an LGF file or elsewhere -- the RUN_ALLOCATION engine looks in the fact tables for all the data it needs.)

In the entity dimension, there's a node called Operating, with base members beneath it which are all operating entities. When entering the RentalAllocDriver, do this for each of Operating's base members.

(Let's assume Admin is outside this Operating node, for the time being. If it were inside of Operating, this would allocate some portion of the total back into Admin, which sometimes is desireable, but it can be confusing to think about this at first.)

This would look like:

*RUNALLOCATION
*FACTOR=USING/100
*DIM ENTITY WHAT=ADMIN; WHERE=BAS(Operating); USING=BAS(Operating)
*DIM ACCOUNT WHAT=RENTAL; WHERE=RENTAL; USING=RentalAllocDriver
*ENDALLOCATION

Note the factor of USING/100. This means that the values should total to 100, when looking at the total of Operating. If one entity is to get 15% of the total rental expense, the RentalAllocDriver=15 (and not 0.15). If you prefer to enter percentages that add to 100%, then your FACTOR=USING, without the /100.

You will probably want to run this logic from a package (not default logic). As for the other dimensions not specified here, that kind of "all depends" -- you wouldn't want this to run on the actual category of data, and you really need to think through every dimension, one by one, to understand if there's any dimensional relevance to this equation.

For instance, if you want to input your allocation driver only in one month of the year, then you'd need for your USING to reference that time period, rather than each of the 12 months that have the WHAT and WHERE.

[Former Member · Accepted solution]

Hi Tim,

I think that I understand what you mean, Let's see this simple example:

*RUNALLOCATION

*FACTOR=USING/100

*DIM CATEGORY WHAT=ACTUAL; WHERE=BUDGET; USING=DIVEXP

*DIM ACCOUNT WHAT=RENTAL; WHERE=SUPPLIES; USING=DIVEXP

*ENDALLOCATION

What I want to do is:

[category= ACTUAL, Account=RENTAL] = [category= BUDGET, Account=ACTUAL] * DIVEXP/100

The other dimensions will be taken from the current view of the user, and the value of DIVEXP will be taken from a stored table (if possible)

What do you suggest?

[Former Member · Accepted solution]

If DivExp is in the cube itself, this will be much easier. If this is an account, then let's assume the values for it are stored in the Actual category.

Note that the USING of category needs to be a member of the Category dimension, which DivExp is not (I assume?)

*RUNALLOCATION 
*FACTOR=USING/100 
*DIM CATEGORY WHAT=ACTUAL; WHERE=BUDGET; USING=Actual
*DIM ACCOUNT WHAT=RENTAL; WHERE=SUPPLIES; USING=DIVEXP 
*ENDALLOCATION 

The problem with the script above is that it still only has one member in each of the WHEREs, so it's taking Actual Rentals, and posting them to Budget Supplies. (It will do this for every entity, every currency, every time, every profit center, etc.....) If this is what you want, then I think the logic will do what it's instructed to do, but it doesn't make practical sense. An allocation usually takes a value from one place, and spreading it out to a number of other places.

I always find it useful to sketch out on paper, in words and arrows and a diagram, what I want the logic to do -- I turn off the computer for a minute (ok, at least I close the monitor), and list out every dimension in the application -- is a shift from one member to another group of members required, in getting from the what to the where? What is the dimension that has one what (source) and many wheres (destinations)?

Is the allocation driver ("using") in the same member of this dimension as the what?

And then move on to the next dimension.

I sometimes surprise myself, that after working on logic for an hour or two, that I've totally forgotten about one of the dimensions, which is critical to getting things to work properly.

[Former Member · Accepted solution]

Hello Tim,

Now it works. I have more questions:

- Is it usual to work with ALLOCATIONs an variables (using a SELECT statement)? Could you please provide me a simple example?

- The values of the USING are stored (let's say) in an Account or an Entity. Is there any possibility to read them from another place?

- In script logic, can we read from an excel cell or only from the data base (in this case, should we use SQL Syntax)?

Thanks again

[Former Member · Accepted solution]

Script logic runs against the database, so all the values need to be stored in a BPC application (and for SQL logic, this basically means they need to be in the fact table).

You can get your USING from wherever you need it. Here's an example that uses a *SELECT statement to allocates some accounts from a TotalCompanyBase dummy profit center, out to all the profit centers.

It runs the calculation in one entity (the one that the user selects when running the logic in batch-mode), based on the sales figures in a different entity.

Because the SELECT needs to be evaluated at run-time (not logic validation time), this file must be called from the data manager package using the Filename.LGF* and not Filename.LGX

This means that logic written like this can't be included in default logic, without using some very fancy workarounds that Arnaud discussed a few weeks ago in this forum.

// NOTE
// If you don't know what to do...
// Save this file, but don't validate!


// The logic validation will fail with an error: "subscript out of range"
// this is due to the fact that the runtime parameter of entity_set is not passing a value, during the validation.
// to test the validation, replace the entity_set clause below with a valid member,
// but you can't leave it like that, or else users will only ever allocate the member you hard-code here, and not their runtime selection.


// I keep these here for easy copy-and-paste when validating the logic.
// "[ID] = 'USEast'")
// "[ID] = '%ENTITY_SET%'")
*SELECT (%ThisEntity%, [ID], "Entity", "[ID] = '%ENTITY_SET%'")
*SELECT (%SalesEntity%, [SalesEntity], "Entity", "[ID] = '%ENTITY_SET%'")


*RUNALLOCATION	
*FACTOR USING/TOTAL	
*DIM ACCOUNT WHAT=DIMLISTALLOC='ProfitCtrAllocAccts'; Where=<<<; Using=Sales; Total=<<<	
*DIM ProfitCenter What=TotalCompanyBase;WHERE=[ProfitCenterLevel]='5'; Using=<<<; Total=<<<	
*DIM Entity What=%ThisEntity%;Where=<<<;Using=%SalesEntity%;Total=<<<
*ENDALLOCATION	

[Former Member · Accepted solution]

I have managed to make this script that correspond to this formula for all the accounts

(category=BUDGET, time=2007.JAN) = (category=ACTUAL, time=2005.JAN)* (Value in account TAXES/100).

//*RUNALLOCATION

// *FACTOR=USING/100

// *DIM CATEGORY WHAT=ACTUAL; WHERE=BUDGET; USING=ACTUAL

// *DIM TIME WHAT=2005.JAN; WHERE=2007.JAN; USING=2005.JAN

// *DIM ACCOUNT WHAT=; USING=TAXES

//*ENDALLOCATION

//*COMMIT

Let's give another turn to the screw. Now I would like to using the same structure copy the values from one whole year to another. And if possible, taking the value of the year as a variable from the current view.

I have tried with the %YEAR% keyword to give offset to the second date but I haven´t been able to make it work.

[Former Member · Accepted solution]

Assuming you're in a batch logic (not default) and can use *select, try something like this, which should let you run all 12 months of the year in one go. I believe it will treat each of the 12 months separately, but if it doesn't there's a special EACH syntax that you may need to consider for the USING and/or TOTAL.

// this time_set means the user must select only one month when they are prompted for the time. 
// If they select months from multiple years... not sure what could happen.

*SELECT (%ThisYear%, "[YEAR]", "TIME", "[ID] = '%TIME_SET%'")

// this requires you add another property to your time dimension, NextYear. 
// There may be more elegant ways to achieve the same purpose -- this is just the first one to occur to me. 
// (Actually the second to occur to me -- first I tried to do some integer math in the SELECT statement, 
// to take the YEAR + 1, or CAST(YEAR and INT) + 1, but I didn't get it to work 
// so I opted for this instead. If a member property solves your problem, add a member property.)
*SELECT (%NextYear%, "[NextYear]", "TIME", "[ID] = '%TIME_SET%'")

*RUNALLOCATION
   *FACTOR=USING/100
   *DIM CATEGORY WHAT=ACTUAL; WHERE=BUDGET; USING=ACTUAL
   *DIM TIME WHAT=BAS(%ThisYear%.TOTAL); WHERE=BAS(%NextYear%.TOTAL); USING=BAS(%ThisYear%.TOTAL)
   *DIM ACCOUNT WHAT=<all>; WHERE=<all>; USING=TAXES
*ENDALLOCATION

[Former Member · Accepted solution]

Hello again,

It is not working propertly, it seems that the system it is not recognizing all the months in the year, even if I put the TOTAL extension.

Is there any other way of handling the time variables? May be the WHERE or the FOR instructions?

I have seen from your code that there are some variables %CATEGORY_SET%, %ENTITY_SET%, and %TIME_SET%, that read the value of the current view. Are these standard variables? If we create new dimensions, would we have similar variables for them (Ex: NewDimension, %NewDimension_SET%)?

Thanks

[Former Member · Accepted solution]

For the problem with months, try first to write the allocation as %ThisYear%.JAN and %NextYear%.JAN -- see if that works.

Worst case, you can write the *RUN_ALLOCATION 12 times, once for each month, in the same script logic. But there should be a way to get it to work on each month separately, within one *RUN_ALLOCATION, and the performance will be much better that way.

I am not really sure how the %TIME_SET% etc. variables all work. Trial & error (& success I hope?) are your best bet. They aren't documented meaningfully anywhere, and they seem to work in some contexts but not in others. I have tried using them for user-defined dimensions, but haven't had much success. I think it may relate to whether this dimension is set up as a dimension that is passed in the SELECTION from the DTS package run-time. If you look at the variables set up in the package itself (eData -> organize package list) you'll see the dynamic constants (sic) such as %ENTITY_DIM%. You can't add new ones to this list, as far as I know, and not every dimension is provided. I would guess that the %ENTITY_SET% types of variables are somehow related to these, but there's no documentation on precisely how these work.

One thing I learned from experience: in an application where I renamed my category-type dimension as "Version", the variable %VERSION_SET% automatically became available, and %CATEGORY_SET% was no longer available. The dynamic constant, however, remains as %CATEGORY_DIM%, not %VERSION_DIM%.

[Former Member · Accepted solution]

If you need to loop though an allocation process that has variables defined for time, you could try using a FOR and NEXT processes, and embedding the Allocation inside the LOOP. External to the loop you will need variables defined and select statements:

This is an example, but the example DOES NOT loop through time.

*SELECT(%ENTITYLIST%,"[ID]","ENTITY","[PARID]='Y'")

*FOR %BULoop%=%ENTITYLIST%

*RUNALLOCATION

*Factor=Using

*Dim Account What=NEWCALC1; WHERE=NEWBASE1; USING=NEWCALC1; TOTAL=<<<

*Dim Time WHAT=varBUDYR.INP; WHERE=BAS(varBUDYR.TOTAL); USING=varBUDYR.INP; TOTAL=<<<

*Dim Category WHAT=BUDGET; WHERE=<<<; USING=<<<; TOTAL=<<<

*Dim DataSRC WHAT=ADJUSTMENT; WHERE=<<<; USING=<<<; TOTAL=<<<

*Dim Doctor WHAT=No_Doctor; WHERE=<<<; USING=<<<; TOTAL=<<<

*DIM CHARGECODE WHAT=No_Chargecode; WHERE=<<<; USING=<<<; TOTAL=<<<

*Dim ENTITY WHAT=%BULOOP%_IN; WHERE=BAS(%BULoop%); USING=%BULOOP%_IN; TOTAL=<<<

*ENDALLOCATION

*NEXT

Hope this helps.