1. Understand 3 logic parts.
Logic is a special script engine and it consists of 3 parts. Scoping, calculation(create/Record) and Writing.
2. Scoping
BPC is based on NW BI or MSAS which has a lot of data. Therefore, if you don't specify scope, it will take a lot of time to read data.
Let's say you need to calculate 2011.January, actual data and only one account like 'Discounted External sales' based on the External Sales.
How can we scope this from a big database?
The answer is.... *XDIM_MEMBERSET
*XDIM_MEMBERSET is using for scope data by each dimension.
Here is the grammar of XDIM_MEMBERSET.
*XDIM_MEMBERSET <DIMENSIONNAME> = <MEMBERNAME 1>,<MEMBERNAME 2>...<MEMBERNAME n>
Now, let't scope above exampe.
for scoping 2011.January, *XDIM_MEMBERSET TIMEDIM=2011.JAN
for scoping actual, *XDIM_MEMBERSET CATEGORYDIM=ACTUAL
for scoping external sales, *XDIM_MEMBERSET ACCOUNTDIM=EXTSALES
(Note: we need to scope External sales because discounted External sales will be calculated based on the External Sales.)
3. Now, we just finished scoping so it is time to calculate(create) data.
Unlike other script engine, there is no temporary variable in the logic script engine so it will create a record that has same as fact table structure.
and it will replace or change its value uaing '*REC' command. (Note: *REC means 'Record'.)
Here is the grammar of *REC statement
*REC[([FACTOR|EXPRESSION={Expression}[,{dim1}={member},{dim2}=?)]
Using that grammar, We can make our script as below.
*REC (FACTOR = 0.9,ACCOUNT="DISCOUNT_EXTSALES")
Which means multiply by 0.9 to current scoped record and replace account member with DiSCOUNT_EXTSALES
Here is an example what happens with above statement.
<Scoped record>
EXTSALES,2011.JAN,ACTUAL,10000
<Generated record>
DISCOUNT_EXTSALES,2011.JAN,ACTUAL,9000
What if you want to put generated record into BUDGET category?
Then statement should be
*REC (FACTOR = 0.9,ACCOUNT="DISCOUNT_EXTSALES",CATEGORY="BUDGET")
Now you want to put 80% value into FORECAST at the same time. what should we do?
We can use another *REC statement at the same time.
*REC (FACTOR = 0.9,ACCOUNT="DISCOUNT_EXTSALES",CATEGORY="BUDGET")
*REC (FACTOR = 0.8,ACCOUNT="DISCOUNT_EXTSALES",CATEGORY="FORECAST")
<Scoped record>
EXTSALES,2011.JAN,ACTUAL,10000
<Generated record>
DISCOUNT_EXTSALES,2011.JAN,BUDGET,9000
DISCOUNT_EXTSALES,2011.JAN,FORECAS,8000
Getting easier? I hope so :smile:
4. As a final step, we need to write data into database.
script statement is really simple one.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
5 | |
5 | |
4 | |
3 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 |