아주가끔은제정신

Even though BPC 7.5 was released, some customers are still using BPC 5.X. Some of them said "BPC is great product, we saved a lot of time!" but others said "it is too slow!”

What is the truth?

Here are some facts based on my experience.

First, some customers are still using same input schedule or template that they used. It means they might not get the benefit of new report function, which is EVDRE. Usually, EVDRE will process worksheet by worksheet therefore; it will show much better performance than EVGTS, EVGET and EVSND.

Second, Report structure might be wrong. One of customer reported that system can't process any query and send request due to CPU pecked 100%. After I investigated it, I found that Report structure was wrong. They wanted to get end of year value so they used 2008.TOTAL instead of 2008.DEC (YTD appset). Therefore, Analysis Services need to calculate all year value instead of calculating one month. After changed time dimension member from 2008.Total to 2008.DEC, CPU usage came back to normal and performance issue was solved.

Third, BPC 5.X version is based on Microsoft Analysis Services as OLAP Database. Therefore, most of query performance is depends on Microsoft Analysis Service's power. Unfortunately, SQL2005 has some performance issue when user defines COMPLEX dimension formula (Custom Rollup formula). In addition, there is some cache handling issue when MDX uses dimension property. These issues didn't exist in SQL2000 because Microsoft completely redesign Analysis Service engine in SQL2005. These performance issues improved a lot with SQL2005 Service Pack2 and Cumulative hot fixes. On top of that, SQL2008 improved more with 'block computation' and global cache handling even though MDX refers dimension property. As a result, if customer uses a lot of complex dimension formula in 4.2 with SQL2000 and upgraded it to 5.X with SQL2005, they used to complain its performance. Unfortunately, BPC 5.X doesn't support SQL2008. Therefore, if customer wants to use complex dimension formula and have a performance issue with it.

Here are the reasons why I strongly recommend upgrading it BPC 7 or 7.5.

1. SQL2008 MDX performance is much better than SQL2005 (Based on my test result, it is 4 times faster than SQL2005)

2. BPC Shared query engine has improved. If user has a big report, performance will improve a lot.

3. Fixed and enhanced a lot of functional features.

In my opinion, BPC is too flexible product so if our partners and consultants do not guide customer well, it will make the problem. 

We can't use laundry machine to wash and peel potato. As a matter of fact, some customer used laundry machine for washing and peeling potato before. That's why you can see this kind of warning sticker now :)

If you know BPC and had a chance to meet some customers, you might hear about a request or question that "Can we do Drill-Through BW data from BPC?"

Unfortunately, current BPC 7 NW and MS version doesn't support. In addition, even though user uses 7.5 version, BPC 7.5 MS version will not support it. (BPC 7.5 NW support it and BPC 7.5 MS supports URL based drill through in SP3.)

When I visit a customer site during last week, I heard a BIG complaint about this so I tried to develop a simple excel macro to mimic similar function as BPC 7.5 NW Drill-Through.

Here is the features of my prototype.

1. This is excel workbook macro so user should add this macro into each workbook that needs BW drill-through.

2. User can maintain URL and its parameter like EVDRE

Note : It is open source so anyone can modify and use it

Let's try it step by step. 

1. Create a EVDRE Report. ROW : Account, Column : Time 

2. After you create it, Press Alt + F11 key then it will open EXCEL macro as below screen shot.

3. Find workbook name that has EVDRE report and Double-click 'ThisWorkbook' then it will open macro edit window.

4. Paste macro code and save it

5. close macro edit window  (Alt + F4)

6. Make Drill Through Control panel as below screen shot. Under EVDRE control panel will be good location. Screen shot will open Yahoo website based on the selected account member.  parameter name is p and we need to get account dimension member in the same row so specify column name

7. Save  and close workbook.

8. Open workbook again and make enable macro

9. Select any value cell and right click then popup menu will be shown. Select NW_DrillThrough menu under original Drill Through menu.

10. It will open Yahoo web page with selected Account name query result.

You can extend it with changing control panel as below to execute NW query. 

In conclusion, this can be a workaround to achieve drill-through NW data from BPC 7 MS and NW.

If you have any comment, please let me know.

=========== Macro code ========================

Const APP_SHORTNAME = "NW_DRILLTHROUGH"
Private iRow As Integer
Private iCol As Integer

Private Sub Workbook_Open()

Call setmenu

End Sub

Private Sub Workbook_Activate()

Call setmenu

End Sub

Private Sub Workbook_Deactivate()

'when workbook is deactivated, remove menu
On Error Resume Next
Application.CommandBars("Cell").Controls(APP_SHORTNAME).Delete

End Sub

Public Sub setmenu()

'Developed by James Lim SAP RIG America  2010. Jan. 8'
'This macro should put it in the thisworkbook
'When workbook is opened or activated, macro will be called and menu will set.

Dim ctlNewMenu As CommandBarControl
Dim ctlNewGroup As CommandBarControl
Dim ctlNewItem As CommandBarControl
Dim iBPCDrillThrough As Integer

On Error GoTo Err_Trap

On Error Resume Next

Application.CommandBars("Cell").Controls(APP_SHORTNAME).Delete

On Error GoTo 0

'Get position of default Drill Through menu
iBPCDrillThrough = Application.CommandBars("Cell").Controls("Drill Through...").Index

'Put New Drill Through menu under it
Set ctlNewItem = Application.CommandBars("Cell").Controls.Add(, , , Before:=iBPCDrillThrough + 1)

'Set menu caption
ctlNewItem.Caption = APP_SHORTNAME

'Assign macro to open browser
ctlNewItem.OnAction = "ThisWorkbook.ProcessData"

Err_Trap:

If Err <> 0 Then

Err.Clear

Resume Next

End If

End Sub

Public Sub ProcessData()
'get URL information from Control panel

Dim lcol As Long
Dim lrow As Long
Dim sParamName As String
Dim sParamValue As String
Dim sURL As String
Dim sURLHeader As String
Dim sURLTail As String

lrow = pFindPosRow("URLHeader")
If lrow = 0 Then Exit Sub
lcol = pFindPosCol("URLHeader")

sURLHeader = Range(numToAddress(lcol + 1) & CStr(lrow)).Value
sURLTail = Range(numToAddress(lcol + 1) & CStr(lrow + 1)).Value

sURL = sURLHeader

i = lrow + 2

Do While Trim(Range(numToAddress(lcol) & CStr(i)).Value) <> ""

    sParamName = Range(numToAddress(lcol) & CStr(i)).Value
    sParamValue = Range(numToAddress(lcol + 1) & CStr(i)).Value

    If IsNumeric(sParamValue) Then
       sParamValue = Range(numToAddress(Application.ActiveCell.Column) & sParamValue).Value
    Else
       sParamValue = Range(sParamValue & Application.ActiveCell.Row).Value
    End If

    sURL = sURL & sParamName & "=" & sParamValue & "&"
    i = i + 1
Loop

sURL = Mid(sURL, 1, Len(sURL) - 1)
sURL = sURL & sURLTail

'open browser
ActiveWorkbook.FollowHyperlink Address:=sURL

End Sub

Private Function pFindPosRow(sText As Variant, _
                             Optional SearchDirection As XlSearchDirection = xlNext, _
                             Optional SearchOrder As XlSearchOrder = xlByRows) As Long

Dim sResult As String, oRg As Range
Set oRg = Cells.Find(What:=sText, LookIn:=xlValues, _
                     LookAt:=xlPart, SearchOrder:=SearchOrder, _
                     SearchDirection:=SearchDirection, _
                     MatchCase:=False, SearchFormat:=False)

If Not oRg Is Nothing Then
  sResult = oRg.Row
Else
  MsgBox "Can't find " & sText, vbCritical + vbOKOnly, "Error"
  GoTo Exit_sub
End If

pFindPosRow = sResult

Exit_sub:
Set oRg = Nothing
 

End Function

Private Function pFindPosCol(sText As Variant, _
                             Optional SearchDirection As XlSearchDirection = xlNext, _
                             Optional SearchOrder As XlSearchOrder = xlByColumns) As Long

Dim sResult As Long, oRg As Range
Set oRg = Cells.Find(What:=sText, LookIn:=xlValues, _
                     LookAt:=xlPart, SearchOrder:=SearchOrder, _
                     SearchDirection:=SearchDirection, _
                     MatchCase:=False, SearchFormat:=False)

If Not oRg Is Nothing Then sResult = oRg.Column

pFindPosCol = sResult
Set oRg = Nothing

End Function

Function numToAddress(lAddress As Long) As String
   Dim iCol As Long
    Dim sColAddress As String
   
    iCol = lAddress
       
    While (iCol > 0)
        iCol = iCol - 1
        sColAddress = Chr(Asc("A") + (iCol Mod 26)) + sColAddress
        iCol = iCol \ 26
    Wend

    numToAddress = sColAddress
   
End Function

Recently I had a chance to visit a customer site. They said they had a performance issue with currency conversion. I found that they were using script logic for currency conversion so I converted thier script logic to business rule. The performance improved more than 10 times even though new business rule processed more records. Please check below graph. 

As you can see, it took under 150 seconds to convert 1.14 million records. 

Then you may ask a question why the performance is SO MUCH different.

The answer is... 'Business rule is the logic module that is based on the stored procedure of data base server but script logic is based on the component of application server'.

Script logic reads data from the Database server and calculates it. After it finishes calculations, Logic components will post result data through BPC sending engine.

Most of customer has multi server environment so Application server and Database server is separated physically. It means script logic will make communication traffic between Application server and database server. Usually it doesn't matter when data size is small but like this customer case, if logic creates a lot of records, it will make a performance issue. In addition, sending engine performance also will be affected its amount of data.

In conclusion, we should use business rule when logic needs to process a lot of data. Of course business rule can't handle all cases but if we use it properly, we can get best result.

I attached two scripts for comparing.

<Script logic for currency conversion>

*CLEAR_DESTINATION
*DESTINATION RPTCURRENCY=USD,EUR
*LOOKUP RATE
*DIM RATESRC="RATECALC"
*DIM RATE=ACCOUNTHC.RATETYPE
*DIM SOURCECURR:INPUTCURRENCY=ENTITY.CURRENCY
*DIM USD:INPUTCURRENCY="USD"
*DIM EUR:INPUTCURRENCY="EUR"
*ENDLOOKUP
*WHEN ACCOUNTHC.RATETYPE
*IS "NOTRANS"
*IS AVG,END,BOY,HISTIIS,HISTCS,HISTDIV,HISTADJ
*REC(FACTOR=LOOKUP(SOURCECURR)/LOOKUP(USD),RPTCURRENCY="USD")
*REC(FACTOR=LOOKUP(SOURCECURR)/LOOKUP(EUR),RPTCURRENCY="EUR")
*ELSE
*REC(RPTCURRENCY="USD")
*REC(RPTCURRENCY="EUR")
*ENDWHEN
[RPTCURRENCY].[#USD]=NULL
[RPTCURRENCY].[#EUR]=NULL

*COMMIT

<Script logic that will execute business rule>

*CLEAR_DESTINATION
*DESTINATION RPTCURRENCY=USD,EUR

*RUN_STORED_PROCEDURE=SPRUNCONVERSION('%APP%','%CATEGORY_SET%','','RATECALC','%SCOPETABLE%','%LOGTABLE%')

 Note: Please refer below URL. You can find a document how to convert currency using business rule.

http://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/library/bpx-community/enterprise-performance-management/How%20to%20do%20Currency%20Translation%20for%20Financial%20Application%20using%20SAP%20BPC%207.0%20version%20for%20Microsoft%20SQL%20Server

'Dimension Formula' is one of the BPC term that is for calulated members.

For example of calculated member, ROA is one of famous KPI for measuring business peformance.

This dimension formula is using 'Custom Rollup' feature in the Microsoft Analysis services.

Due to change of architecture of Microsoft Analysis Services from SQL2000 to SQL2005, BPC customers could not use simple dimension formula like
Account A = Account B X Account C (or X constant value like 1.5).

It was working in SQL2000 but dimension formula didn't work with SQL2005 if dimension has multiple hierarchies.

Therefore, SAP released ‘HOW TO GUIDE' for creating dimension formula in SQL2005 but there were some problems as below.

a. Formula should have complex IIF statement
b. If length of formula is too long, user should make cascade account and it will make slower performance.
c. It is hard to maintain because can't read it easily.

Note : URL of Dimension formula with SQL2005.
http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/008d665b-94bf-2a10-78b2-b32ffe04ba73;jsessionid=(J2EE3414900)ID0541202550DB10246661973771485801End

In SQL2008, Microsoft seems to fix that internal calculation issue with multiple hierarchies.

I will explain how to use dimension formula with SQL2008.  (Note : Based on the test result, some complex formula still needs same IIF function as SQL2005 so you need to test it after change formula based on this article.)

Here is a test scenario that developer has used it for testing SQL2005 dimension formula.

Dimension formula exists in the entity dimension as below.

Entity hierarchy structure will be same as below screenshot.

Here is EVDRE result. As you can see, there is no IIF in the dimension formula but all calculation result is correct.

If we are using it with SQL2005, it should have IIF statement to get correct calculation result like iif(Acct.H2.CurrentMember is [Acct].[H2].[All Acct.H2],Acct.H1.Cost1+ Acct.H1.Cost2, Null)

BPC Microsoft platform with SQL2008 is the best combination for better performance and easy maintenance than using SQL2005.

Especially, for the dimension formula, we can use it like SQL2000 so we don't need to use IIF statement for the MDX dimension formula.

I hope it will help all consultant who is struggling with BPC dimension formula.

A few weeks ago, I visited one of our customer sites and did some technical consulting.

While I was working with them, one of customer told me that Business Rule didn't work suddenly. That business rule was working properly before I visited. :(.

So I checked business rule (It was for currency conversion) and found nothing that needed to be fixed.  .

As we know, it is really hard to find out the root cause because Business Rule just returned 'FX-XXX' error message. Even though it gives some clues but sometimes it is not the root cause then user will be lost what they need to fix..

So I tried to find how to debug easily for escaping this situation because customer started to suspect me that I had ruined that logic. (Of course I didn’t!!!) .

As we know, All Business rule stored procedure is encrypted so customer and partners could not debug it in the source code level of stored procedure..

BUT... (There is always BUT. :) .

We can get more detail error message from the management studio using my method that I will explain..

First, we need to create temporary scope table.Usually Scope table will be created automatically based on the passing parameters but we need to create it to run it in management studio..

Here is scope table structure. You can give any name for it because we will use it when we call stored procedure..



Second, fill some values in the table.

This value should be same value when you run that Business Rule..



Third, Execute SPRUNXXXX stored procedure in the management studio..

You need to specify correct one because there are several stored procedures in BPC. For example, Currency conversion is SPRUNCONVERSION, Account Transformation is SPRUNCALCACCOUNT etc. Now you can see the result as below screenshot.

Please remember when you run SPRUNXXXX stored procedure, you should use scope table name that we created. Below example shows that scope table name is 'Test_Scope'



As you can see, you will find more detail information here.In this example, it says Currency_type is invalid column. It means mbrrptcurrency table should have currency_type column (property) but it doesn't have.

Therefore, if we create Currency_type property for RPTCURRENCY dimension and fill in proper value, we can solve issue.

By the way, the customer that I addressed earlier had wrong property value in their time dimension but it was processed properly because it was a property value. I also solved that case using this way.This is not the solution for all cases but I am pretty sure it will give more hints.

If you have any questions. Please comment it.

Sometimes BPC system needs to be restarted whatever the reason is.

Here is what we should restart. (Note: it should be done sequentially.)

1. Stop 'Send Governor' service

2. Stop BPC component services (Please see below screen shot how to stop it manually)

3. Stop and Start IIS (Internet Information Services

4. Start 'Send Governor' service

As we know step1, 3 and 4 can be done command.

Here is command for executing above steps.

1. net stop "Outlooksoft Sendgovernor service"

3. IISRESET

4. net start "Outlooksoft Sendgovernor service"

But there is no command for stopping BPC component in the component services.

One of BPC customer wanted to do this so I created simple script that can stop all component service's component. (Note: it will stop all component so if customer is using other component, it needs to be modified to avoid stopping.)

Here is source code.

===================================================== 

Set adminCatalog = CreateObject("COMAdmin.COMAdminCatalog")

'getting all the Applications in the server

Set objAppCollection = adminCatalog.GetCollection("Applications")

    objAppCollection.Populate
  
    'loop for all application
    For i = 0 To objAppCollection.Count - 1

        Wscript.echo objAppCollection.Item(i).Name
 
        'Shutdown app
         adminCatalog.ShutDownApplication objAppCollection.Item(i).Name
  
    next 

set objAppcollection = Nothing
set adminCatalog = Nothing
 

=====================================================

Let's assume user saved above source code as 'RestartCom.vbs'.

Now we can create a batch file - let's say it is ResetBPC.bat -  that can be scheduled using windows scheduler.

Below four lines of source code is content of ResetBPC.bat file.

----------------------------------------------------------------------

net stop "Outlooksoft Sendgovernor service"

Cscript c:\restartcom.vbs

IISRESET

net start "Outlooksoft Sendgovernor service"

----------------------------------------------------------------------

The customer who asked it to me satisfied its result using this script.

They could restart BPC service during night time without manual procedure.

I think it also will help other customers who have same concern and save time all SAP colleagues and partner consultants.

If you have any questions, please leave comments.

SAP BPC has Workstatus Function that user can or can’t submit their data from client according to workstatus state value but some users complain below things.

a.     If one of the child entity wants to adjust value after all entity’s workstatus set as ‘locked’,  there is no way to allow submitting data from only that entity. Admin has to unlock all entity so Admin can’t make sure others do not submit data during workstatus is unlocked.

b.     Even though there is TOPDOWN application parameter exists, it needs to unlock from top member to based member.

c.     In addition, if user wants to change status, it can’t change it from ‘Locked(Approved)’ to ‘Unlocked’. It means if user has 4 states in their workstatus, they have to change 3 times using current UI.

Tbl<app>Lock table has all workstatus information so user can solve above issues using Data Manager custom package. This blog article will help to create it.

Note: Create custom package should be done by customers and partners. This document is just helping to create package and doesn’t guarantee its result. SAP strongly recommends testing custom package after it is created by users or partners.  

Package will work based on the selected base members.

For example, if user selects Actual, Budget and 2010.JAN, 2010.FEB, SalesUSNeast, SalesWest it will change below combinations.

Actual, 2010.JAN, SalesUSNeast

Actual, 2010.JAN, SalesWest

Actual, 2010.FEB, SalesUSNeast

Actual, 2010.FEB, SalesWest

Budget, 2010.JAN, SalesUSNeast

Budget, 2010.JAN, SalesWest

Budget, 2010.FEB, SalesUSNeast

Budget, 2010.FEB, SalesWest

Here is detail design of SSIS package.

   a. Package Screen shot

-. Package will create SQL statement in the script task

-. ExecuteSQL task will execute that SQL statement

-. ForeachLoopContainter will execute stored procedure as many as combination of selected members.

   b. SQL statement for creating all combination of selected base members

For example, if user selects below members, query and result set will be same as below. 

Category Dimension: 'Actual','Budget'

Time Dimension: '2007.JAN','2007.FEB'

Entity Dimension: 'SalesUSNeast','SalesSouth'

<Query>
select a.*, b.* , c.* from ( select 'actual' as category union select 'budget' ) as a, (select '2007.JAN' as [time]union select '2007.FEB' ) as b, (select 'salesusNeast' as [entity]union select 'salesSouth' ) as c order by category,[time],entity 

<Resultset> 

actual   2007.FEB    salesSouth

actual   2007.FEB    salesusNeast           

actual   2007.JAN    salesSouth

actual   2007.JAN    salesusNeast

budget   2007.FEB    salesSouth

budget   2007.FEB    salesusNeast

budget   2007.JAN    salesSouth

budget   2007.JAN    salesusNeast

c.  Stored procedures that needs to be executed in the loop container

EXEC up_SetWorkStatusFinance @StatusCode=2,@UpdateBy=N'<Execution user>,@Category=N'ACTUAL',@Entity=N'Worldwide1',@Time=N'2006.TOTAL'

EXEC up_SetWorkStatusIncludeChildrenFinance @StatusCode=2,@UpdateBy=N'<Execution User',@Category=N'ACTUAL',@Entity=N'Worldwide1',@Time=N'2006.TOTAL'

d. Data Manager Script for receiving selected dimension members

You can define Global variable and use this value for creating SQL statement in the First script task

GLOBAL(A1,%CATEGORY_SET%)

       Note: Global variable is case sensitive so please define it as UPPERCASE.

One of my customers created this package with SAP Partner based on this article and used it in the production environment now. Of course, this is not for usual case but might be useful for controlling complex workstaus without affecting workstatus of other entity.

If you have any question, please leave a comment.

When we say the performance issue of BPC, we need to make clear which performance issue.
Data retrieving performance issue in the report?
The performance of Logic calculation? Data posting performance in the input template?
Or performance of data manager package?

The reason why we need to make clear is the bottom reason of performance issue is quite different. (I will post about each root cause of performance issue later.)


When I visited a customer site 3 weeks ago, I heard the complaint about the inconsistent performance of their custom data manager package.
So we monitored SQL process and found there was some CXPACKET wait type.

CXPACKET wait type comes from Microsoft SQL engine for waiting worker threads on the CPU.
SQL server usually split a SQL query into multiple internal queries by query engine and multiple CPU threads are performing those partial queries and sync its result afterwards. Most of case, it works perfectly without any problem but sometimes, especially when insert a lot of data, CXPACKET wait type happens. It means query engine is waiting one of other partial query.

The problem is, when it happens, its performance becomes REALLY SLOW. (I saw a worst case that took several hours to finish. Usually, it can be finished within 30 minutes.)

That's why customer complains the performance of their custom data manager package is not consistent.

Some Microsoft experts says if user maintains indexes and statistics very well, this CXPACKET WAIT should not happen but as I addressed, when user tries to insert a lot of records in the table, sometimes it happens.

To resolve this issue, there is one workaround which is disable parallelism option (MAXDOP: Max degree of parallelism option.)

Of course, when we disable MAXDOP, it means we gave up parallel execution feature of SQL server. Therefore, its performance will be decreased but it will guarantee consistent execution time. (Actually, performance difference is not that big.)

In addition, we will not set this option forever because this is an option of SQL server, it will affect all other query performance, Therefore, if a custom data manager package has this issue, we can disable this option as a first step of package and enable it when package finishes.

Here is a screen shot of the package that has two additional SQL execute Tasks.

The first SQL execute task should have below statement
sp_configure 'show advanced options', 1; --Should show advanced option to set parallelism
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 1;   --Turn off parallelism
GO
RECONFIGURE WITH OVERRIDE;
GO

The last SQL execute task should have below statement.
sp_configure 'max degree of parallelism', 0;   --Turn on parallelism
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'show advanced options', 0;  --will not show advanced options.
GO
RECONFIGURE WITH OVERRIDE;
GO

In conclusion, when user executes this package, parallelism option will be turned off during package is running and roll back parallelism option when it finishes.

Of course, this option should be added when user finds CXPACKET wait type in the process monitor while their package is running and user should not run same package at the same time because parallelism option might be turned on/off unitentionally. (User can avoid this through set the flag while package is running.)

The two customers solved inconsistent performance issue of their cusom package using this solution. Of course, user should check the environment and other condition like running lite-optimization at the same time before apply this solution. 

Performance is important but sometimes consistent performance is more important when it is scheduled job.
 

I guess many consultants already know the difference between BPC NW version and MS version.

Even though you know the difference, I think this chart will help us as a reference.

If you find anything wrong in the list, please leave a comment so that I can update it continuously.

Thank you.

*Index of Color

     YELLOW : Supported by both version.

     GREEN : Supported by NW version only

     WHITE : Supported by MS version only

     ORANGE : It was supported by MS version before 7.0 but not suppported any more.

Here is a link of Google docs. If you want to add more, please use it and edit, I will convert it as image and will publish it later

https://spreadsheets.google.com/spreadsheet/ccc?key=0AlNX_-VUdSnudFhZSlpibzJ1NmpTX0N1Ty1WcUZUbFE&hl=en_US&authkey=CM3FoJkB

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