BPC Script logic for Dummies? (Final)   |
|
As a last post of this script logic series, I would like to explain some special functions that only exist in BPC MS.
Even though the user can implement below functions in BPC NW using BADI, in my personal opinion, I think we really need these functions in the BPC NW version.
1. FLD function (for using property value with *REC statement)
Sometimes customer may want to use a property value for calculating with *REC statement.
The syntax is:
FLD(Dimensionname.Propertyname)
and here is an example.
*WHEN ENTITY.SCALE
*IS <>””
*REC(EXPRESSION=%VALUE%*FLD(ENTITY.SCALE))
*ENDWHEN
It means the Records value will be calculated as a product of current record value and scale property value of entity
member in the current record.
Let's assume fact table has below current records.
ACCOUNT, ENTITY, CURRENCY, SIGNEDDATA
Revenue, CT, USD, 100
Revenue, CA, USD, 200
Revenue, NJ, USD, 300
Revenue, NY, USD, 400
Here is an example logic script.
*WHEN ENTITY.SCALE
*IS <>””
*REC(EXPRESSION=%VALUE%*FLD(ENTITY.TAXRATE)/100, ACCOUNT="TAX")
*ENDWHEN
and let's say ENTITY dimension member worksheet looks like below.
ID, PARENTH1, ..., TAXRATE
CT, USA, ..., 6.35
CA, USA, ..., 7.75
NJ, USA, ..., 7.00
NY, USA, ..., 8.875
When you run the script logic, 4 records will be generated as below.
ACCOUNT, ENTITY, CURRENCY, SIGNEDDATA
Revenue, CT, USD, 100
Revenue, CA, USD, 200
Revenue, NJ, USD, 300
Revenue, NY, USD, 400
TAX, CT, USD, 6.35
TAX, CA, USD, 15.5
TAX, NJ, USD, 21.00
TAX, NY, USD, 35.5
2. GET function
When we use *REC statement, we can use Lookup function to get a value from the other application. For example, FX
translation needs rate value. I explained it in the previous post.
In addition to above, users may want to get the value from other record within SELECTED REGION.
The syntax is:
GET({dimension}={member name(ID)}[, {dimension}={member name(ID)}]…)
and here are some examples.
GET(ACCOUNT= ”EXTSALES”)
GET(ACCOUNT= ACCOUNT.MYPROPERTY)
GET(ACCOUNT= ACCOUNT.MYPROPERTY + ".TOTAL" ) // support the concatenation using '+' operator.
Note: if ACCOUNT.MYPROPERTY has "TAX" value, it will get the value of TAX account record value.
GET(ACCOUNT=ACCOUNT.ELIMACC,ENTITY=INTCO.ENTITY)
Note: User can specify multiple dimension names to select record.
All missing dimension will be same as source record.
Let's use the same example that we used above.
We will calculate NetRevenue using Revenue and Tax.
*XDIM_MEMBERSET ACCOUNT = REVENUE, TAX // I will explain why we need it.
*WHEN ACCOUNT
*IS “REVENUE”
*REC(EXPRESSION=%VALUE% - GET(ACCOUNT=”TAX”), ACCOUNT=”NETREVENUE”)
*ENDWHEN
If we run above script, 4 records will be created.
ACCOUNT, ENTITY, CURRENCY, SIGNEDDATA
Revenue, CT, USD, 100
Revenue, CA, USD, 200
Revenue, NJ, USD, 300
Revenue, NY, USD, 400
TAX, CT, USD, 6.35
TAX, CA, USD, 15.5
TAX, NJ, USD, 21.00
TAX, NY, USD, 35.5
NetRevenue, CT, USD, 93.65 // 100 - 6.35
NetRevenue, CA, USD, 184.5 // 200 - 15.5
NetRevenue, NJ, USD, 279 // 300 - 21
NetRevenue, NY, USD, 364.5 // 400 - 35.5
There are two things you keep in mind to use this GET function.
First, GET function will not search from database but search from 'SELECTED REGION'.
Therefore, you should specify members in the *XDIM_MEMBERSET before use GET function.
That's why we specified *XDIM_MEMBERSET ACCOUNT = REVENUE, TAX in the example.
Second, the Script engine can't use the results of a previously calculated value without *COMMIT statement.
This example will not work.
*WHEN ACCOUNT
*IS “UNITS”
*REC(ACCOUNT=”REVENUE”, FACTOR=GET(ACCOUNT=”PRICE”))
*IS “REVENUE”
*REC(ACCOUNT=”TAXES”, FACTOR=.5)
*ENDWHEN
So we need to separate two calculations using Commit statement to make it work.
*WHEN ACCOUNT
*IS “UNITS”
*REC(ACCOUNT=”REVENUE”, FACTOR=GET(ACCOUNT=”PRICE”))
*ENDWHEN
*COMMIT
*WHEN ACCOUNT
*IS “REVENUE”
*REC(ACCOUNT=”TAXES”, FACTOR=.5)
*ENDWHEN
Usually, *COMMIT statement will post records into the database but what if the customer wants to calculate without
posting data?
For that purpose, BPC MS has a special statement which is *GO.
*GO statement will not post data but can use the result value of the previous script.
3. Dummy memory variable
User can save intermediate result and assign them to dummy members like dummy account members or any other dimension.
This member can be used as temporary records to store intermediate results that can be used as inputs for subsequent
calculations. These temporary records will be automatically skipped during the commit time.
Dummy members must be identified with a leading pound (#) sign.
For example:
*REC(ACCOUNT=#TEMP_ACC)
Even though #TEMP_ACC member does not exist in the account dimension, the generated record can be used anywhere in the logic
as below example, but its value will not be stored in the database.
*WHEN ACCOUNT.FLAG
*IS = Y
*REC(ACCOUNT=#TEMP_ACC)
*ENDWHEN
*GO
*WHEN ACCOUNT
*IS #TEMP_ACC
*REC(FACTOR=GET(ACCOUNT=MULTIPLIER),ACCOUNT=FINAL)
*ENDWHEN
This is useful to calculate a complex calculation like allocation.
Let's assume you want to allocate some expense based on the each store's area.
You must have a total area value of each store because the formula will be
Each Store's Expense = Total Expense amount * Each Store's area / Total area
Even though each store's area is saved in the database as an account member, you need to have the total value. Of
course, it is easy to get the total value using MDX script statement but we need to do it using the SQL script statement
for better performance.
Finally, I finished the BPC Script logic for dummies(?) series.
Well, it might not be enough for using it right now but as I addressed in the first post,
I hope you get the basic concepts of BPC script logic so that you can understand and read what it means.
Thank you for reading this series.
James Lim
James Lim SAP RIG America - BPC MS platform architect & Customer Care
| Showing messages 1 through 3 of 3. | ||
|
| ||
| Titles Only | Main Topics | Oldest First |
-
Intermediate # values
2011-11-07 22:38:14 Pankaj Patil Business Card [Reply]
Hi James,
Can the intermediate # values generated before GO be used after the commit as well ?
Example:
Logic...(#A1 is generated)
GO
Logic...(#A1 can be used)
COMMIT (Write to DB)
Logic... (Can #A1 values be used in this block ?)
COMMIT
Regards,
Pankaj-
Intermediate # values
2011-11-08 06:30:13 James Lim Business Card [Reply]
Hi PanKaj,
I have never tried it that case before :)
Please try it but I guess it might not WORK because all temp variable will be cleared after COMMIT.
Why do you want to use COMMIT between them?
If the number of records are not too big, it will be ok to use GO.
James.
-
-
Thanks
2011-10-21 02:54:02 Ethan JEWETT Business Card [Reply]
Thanks again James, for doing these blogs. We need more like them.
While BPC NW does have an equivalent to the GET() statement in the form of an MDX tuple, the rest (FLD, *GO, and dummy variables) are very difficult to implement unless you put your complete logic in a BADI. I could not agree more with your statement that we need these in the NW version.
Cheers,
Ethan
| http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/27016 |
'Technique > SAP BPC' 카테고리의 다른 글
| When the performance of BPC data manager package is not consisent. (EPM BPC Microsoft platform) (0) | 2011.11.25 |
|---|---|
| Comparision list of BPC Script Logic command between NW version and MS version (0) | 2011.11.25 |
| BPC Script logic for Dummies? (Part 5) (0) | 2011.11.25 |
| BPC Script logic for Dummies? (Part 4) (0) | 2011.11.25 |
| BPC Script logic for Dummies? (Part 3) (0) | 2011.11.25 |
