BPC310_EN_Col91_FV_Part_A4 (第5页)

第 255 页 / 共 473 页

BPC310 Lesson: Labs

Figure 276: Process Dimensions

13. Whenprompted,“Thetaskhasbeensuccessfullycompleted”,selectOK.

14. Press [Alt]+[Tab] to switch back to Excel.

15. From the Excel File menu, select Exit and do not save any changes, if

prompted.

16. Return to the Admin Console.

17. ClosetheAdminConsoletoreturntotheBPCAdministrationpage.

18. Whenprompted“CurrentAppSetstatusisnotavailable. Wouldyoulike

to change AppSet status?” select Yes.

19. ClosetheBPCAdministrationpageandreturntoyourWindowsDesktop.

20. Younowwanttoseethemasterdatatables. Navigateasfollows:

Start → Programs → Microsoft SQL Server 2005 → SQL Server

ManagementStudio. ClickatConnectinthenextpop-up.

21. NavigateasfollowsforyourSTUDENT_##applicationset:

235

第 256 页 / 共 473 页

Unit7: Labs BPC310

Figure 277: Application Set: Tables

22. Open the Tables folder and look for data table dbo.mbrAccount. Right

mouseclickatthedatatableandselectOpenTable.

23. YoushouldnowseeallAccountdimensionmembersespeciallythoseyou

created in Lab 2.

Figure 278: Account Dimension Members

24. ClosethetableafterwardsandreturntotheBPCDesktop.

Lab 9 – Creating a New Application

Objective: UsingtheAdminConsole,createanewapplicationnamedSalesby

copyingtheFinanceapplicationfromyourSTUDENT_##applicationset. Using

theActionPane,selecttheobjectstoincludeintheapplicationcopy,modifythe

newapplicationandprocessallapplicationsintheappset.

236

第 257 页 / 共 473 页

BPC310 Lesson: Labs

Description: You may have multiple applications in an application set.

ApplicationsinthesameApplicationSetsharecommondimensions,andtherefore

processingcycles,aswellasuserids. Youcanalsoretrievedatafrommultiple

applicationsinthesameapplicationsetintothesamereport.

ThislabisperformedusingyourSTUDENT_##applicationset.

1.

FromyourWindowsDesktop,double-clickonthe BusinessPlanning

and Consolidation shortcut icon. When asked for your password enter

training.

2.

FromtheLaunchPage, clickonthe BPCAdministrationshortcut

icon.

3. MakesuretologontoyourSTUDENT_##applicationset. ClickatManage

ApplicationSetsandintheconnectionwizardpopupclickOK.Ifasked

foryourpassword,enterTrainingandselectSTUDENT_##asyourdefault

application set.

ThenclickatApplicationonthelefthandsideofthehierarchy.

4. Verifythatyourscreenlookssimilartothefollowing:

Figure 279: Example 1

5. FromtheActionPane,selectAddanewapplication.

6. Enter Sales for the application name and Sales Application for the

description.

7. FromtheActionPane,clickonGotoNextStep2of4.

8. SelecttheapplicationtypeReportingType→Financialandthenfromthe

ActionPane, clickonGotoNextStep3of4.

9. Onstep3,leavethedefaultsasisandthenfromtheActionPane,clickonGo

to Next Step 4 of 4.

237

第 258 页 / 共 473 页

Unit7: Labs BPC310

10. On step 4, leave the defaults as is and then from the Action Pane, click

on Add a New Application.

11. Whenprompted,“Thetaskhasbeensuccessfullycompleted”,selectOK.

12. SelecttheSalesapplicationfromthetreeview.

13. FromtheActionpane, selectModifyapplication.

14. Verifythatyourscreenlookssimilartothefollowing:

Figure 280: Example 2

15. FromtheSharedDimensionslist,selecttheProductdimension,thenclick

the rightarrowbuttontoaddittotheApplicationDimensionslist.

16. FromtheApplicationDimensionslist,selecttheIntCodimension,thenclick

the leftarrowbuttontoremovetothedimensionfromtheApplication

Dimension list.

17. IntheActionPane,selecttheReassignSQLIndexandProcessApplication

checkboxes.

18. Verifythatyourscreenlookssimilartothefollowing:

238

第 259 页 / 共 473 页

BPC310 Lesson: Labs

Figure 281: Example 3

19. FromtheActionPane,clickonModifyApplication.

If you receive the following message:

Figure 282: Message

Select YES.

20. Whenprompted,“Thetaskhasbeensuccessfullycompleted”,selectOK.

21. SelecttheSalesapplicationfromthetreeview.

22. FromtheActionPane,clickonSelectOptimizeapplication.

23. Select the checkboxes for all applications and select the Full Optimize

option.

24. Verifythatyourscreenlookssimilartothefollowing:

239

第 260 页 / 共 473 页

Unit7: Labs BPC310

Figure 283: Applications

25. ScrolldownandfromtheActionPane,clickonOptimizeApplications.

26. Whenprompted,“Thetaskhasbeensuccessfullycompleted”,selectOK.

Lab 10 – Grant Access to the Sales Application and to

all Tasks and Allow Loading of transaction data for

certain file types

Objective: UsingtheAdminConsole,grantaccesstodimensionmembersofthe

newly created Sales application.

Description: Whenanewapplicationiscreated,youmustdefinememberaccess

tothesecureddimensionsthatresideintheapplication. Thisisrequiredinorder

touseBPCWeb, BPCforExcel, BPCforWordandBPCforPowerPointwith

anynewlycreatedapplicationthatcontainssecureddimensions. Securitywillbe

discussedinmoredetailinasubsequentsectionofthecourse.

ThislabisperformedusingyourSTUDENT_##applicationset.

240

第 261 页 / 共 473 页

BPC310 Lesson: Labs

1. FromtheAdminConsole,expandtheSecurityfolderandthenexpandthe

Member Access Profiles folder.

2. Select the AdminMbrAccPrf profile.

Note: ThisprofilewascopiedoverfromtheApShellapplicationsetwhen

yourSTUDENT_##applicationsetwascreated.

3. Verifythatyourscreenlookssimilartothefollowing:

Figure 284: Example 1

4. FromtheActionPane,selectModifymemberaccessprofile.

TheModifyMemberAccessProfileassistanceisdisplayed.

241

第 262 页 / 共 473 页

Unit7: Labs BPC310

Figure 285: Modify Member Access Profile 1

5. SelectNexttomovetotheBPCAccessstep.

6. Select the Sales Application tab on the upper right and verify that your

screen looks similar to the following:

Figure 286: Modify Member Access Profile 2

7. Onthefirstrow,selectReadOnlyfromtheAccessdropdownlistboxand

selectEntityfromtheDimensiondropdownlistbox.

8.

IntheMemberfield, clickonthe ellipsebuttontoopenthemember

selector dialog box.

9. SelecttheAllmembersindimensioncheckboxasshownbelow,thenselect

OK.

242

第 263 页 / 共 473 页

BPC310 Lesson: Labs

Figure 287: Example 2

10. Onthesecondrow,selectRead&WritefromtheAccessdropdownlistbox

andselectCategoryfromtheDimensiondropdownlistbox.

11.

IntheMemberfield, clickonthe ellipsebuttontoopenthemember

selector dialog box.

12. SelecttheAllmembersindimensioncheckboxthenselectOK.

13. Verifythatyourscreenlookssimilartothefollowing:

Figure 288: Modify Member Access Profile 3

243

第 264 页 / 共 473 页

Unit7: Labs BPC310

14. Click Next to continue to step 3.

15. LeavethedefaultsasisandthenclickNexttocontinuetostep4.

16. ClickontheApplybuttontoprocessthechanges.

17. Whenprompted“Thistaskhassuccessfullycompleted”,selectOK.

18. NowclickatTaskProfilePrimaryAdminintheSecuritysectionandselect

“Modify Task Profile”.

Nowclickattaskprovielprimaryadmininthesecuritysectionandselect

modify task profile.

Justclickthroughitanddońtchangeanything.

Nowyouareauthorizedforallactionsinthesystem.

19. Foralaterdataloadoftransactiondatait'salsonecessarytomaintaintheso

called Application Set Parameters. For example you have to allow the

uploadoftransactiondatafrom.txtfilesandothers.

Therefore you must navigate to the mode which contains the Business

PlanningandConsolidationWebPageforBPCAdministration.

Youwillseeascreensimilartothefollowing:

Figure 289: BPC Administration 1

ClickatSetAppSetParametersActionPanesideandifnecessaryenterthe

followingparameterandaddthewishedextension:

244

第 265 页 / 共 473 页

BPC310 Lesson: Labs

Parameter: ALLOW_EXTENSIONS

Extension: ALL

Select UPDATE.

You should see the following screen:

Figure 290: BPC Administration 2

ClickBACKseveraltimesuntilyoureachtheBPCAdministrationWebpage.

21. MakesurethatyouareloggedontoapplicationSALESofSTUDENT_##by

clickingatApplicationNotSetintheActionPaneandbyselectingSALES.

Figure 291: Example 5

Figure 292: Example 6

Lab 11 – BPC Logic

Objective: Createadimensionlogiccalculationthenvalidateandsavethescript

logic in your STUDENT_## application set.

245

第 266 页 / 共 473 页

Unit7: Labs BPC310

Description: Youcandefineformulastoperformcalculationsonmembers. The

GrossMarginPercentagecalculationisadimensionformula,andthereforeyou

willupdatetheAccountdimensionmembersheettoaddthecalculation.

Inaddition, whenanewapplicationsetiscreatedanddimensionmembersare

changed,youneedtovalidateandsavetheapplicablescriptlogicfiles.

ThislabisperformedusingyourSTUDENT_##applicationset.

Creating a Dimension Formula

1. At the Action Pane select “Manage Application Sets” and if lead to the

Connection Wizard fill in your password.

2. Fromthetreeview,expandtheDimensionLibraryfolderandthenselectthe

Account dimension.

3. FromtheActionPane,selectMaintaindimensionmembers.

The Account.xls workbook is opened.

4. SelecttheMembersheetandthencopythelastrowinthenextrow.

Note: Youcanusetherightmousebuttontoaccesstheeditingshortcut

menu.

5. Changethenewrowtoreflectthefollowinginformation:

Row 189

ID EVDE- PAR- GROUPFORUMLA ACC- SCAL-

SCRIP- ENTH1 TYPE ING

TION

GM_Per- Gross Stats Stats GrossMar- AST N

cent Margin% gin/TotRev

6. FromtheActionPane, selectSavetoServer.

7. FromtheActionPane, selectProcessdimension.

8. TheProcessdimensionsdialogboxisdisplayed.

9. Leavethedefaultsasis,comparetotheexamplebelowandthenselectOKto

start the processing.

246

ID	EVDE- SCRIP- TION	PAR- ENTH1	GROU	PFORUMLA	ACC- TYPE	SCAL- ING
GM_Per- cent	Gross Margin%	Stats	Stats	GrossMar- gin/TotRev	AST	N

第 267 页 / 共 473 页

BPC310 Lesson: Labs

Figure 293: Process Dimensions

10. Whenprompted,“Thetaskhasbeensuccessfullycompleted”,selectOK.

Validating the Script Logic

1. Fromthetreeview,expandtheApplicationfolder.

2. Expand the Finance folder.

3. Expand the Script Logic folder.

4. Verifythatyourscreenlookssimilartothefollowing:

Figure 294: Example 1

5. Fromthetreeview,selectthescriptlogicfileFXTrans.lgf.

6. Verifythatyourscreenlookssimilartothefollowing:

247

第 268 页 / 共 473 页

Unit7: Labs BPC310

Figure 295: Example 2

7. FromtheActionPane,selectValidateandSavetocompilethecodesince

this is a new App Set.

8. Whenprompted“App:FinanceThelogicpassedthevalidationtestandwas

saved successfully”, select OK.

9. Fromthetreeview,selectthescriptlogicfileICElim.LGF.

10. Verifythatyourscreenlookssimilartothefollowing:

Figure 296: Example 3

248

第 269 页 / 共 473 页

BPC310 Lesson: Labs

11. FromtheActionPane, selectValidateandSave.

12. Whenprompted“App:FinanceThelogicpassedthevalidationtestandwas

saved successfully”, select OK.

13. Fromthetreeview,selectthescriptlogicfileDefault.LGF.

14. Verifythatyourscreenlookssimilartothefollowing:

Figure 297: Example 4

15. FromtheActionPane, selectValidateandSave.

16. Whenprompted“App:FinanceThelogicpassedthevalidationtestandwas

saved successfully”, select OK.

17. Fromthetreeview,expandtheapplicationRatefolderandthenexpandthe

Script Logic folder.

18. Fromthetreeview,selectthescriptlogicfileDefault.LGF.

19. Verifythatyourscreenlookssimilartothefollowing:

Figure 298: Example 5

249

第 270 页 / 共 473 页

Unit7: Labs BPC310

20. FromtheActionPane, selectValidateandSave.

21. When prompted “App:Rate The logic passed the validation test and was

saved successfully”, select OK.

22. Fromthetreeview,expandtheapplicationSalesfolderandthenexpandthe

Script Logic folder.

23. Fromthetreeview,selectthescriptlogicfileFXTrans.lgf.

24. Verifythatyourscreenlookssimilartothefollowing:

Figure 299: Example 6

25. FromtheActionPane, selectValidateandSave.

26. When prompted “App:Sales The logic passed the validation test and was

saved successfully”, select OK.

27. Fromthetreeview,selectthescriptlogicfileDefault.LGF.

28. Verifythatyourscreenlookssimilartothefollowing:

Lookfor'Example7'amongthescreenshotslistedbelow.

250

第 271 页 / 共 473 页

BPC310 Lesson: Labs

Figure 300: Example 7

29. FromtheActionPane, selectValidateandSave.

30. When prompted “App:Sales The logic passed the validation test and was

saved successfully”, select OK.

Note: There is no need to validate and save the ICElim.LGF script

logic file in the Sales application due to the fact that intercompany

eliminationsarenotrequiredandthatyouhavepreviouslyremovedthe

IntCodimensionfromtheSalesapplication. Inaddition, thereisnoon

theneedtovalidateandsavescriptlogicfilesinapplicationsotherthan

Finance, RateandSalesforthesubsequentlabs.

31. Sincewedon’tneedthescriptlogicintheSalesAppforEliminations,select

ICElins.LGF>Deletelogicintheactionpane>Ok.

Figure 301: Example 8

32. Fromthetreeview,selectyourSTUDENT_##applicationset.

33. FromtheActionPane,selectSetapplicationsetstatus.

34. SelecttheAvailableoptionthenclickonUpdateapplicationsetstatus.

251

第 272 页 / 共 473 页

Unit7: Labs BPC310

Lab 12 – Solve Order

Objective: Using Dimension Formulas and the Solve Order Feature to show

Dimension Formulas work and how the order of dimension formulas can be

influenced.

Variant1: Account3shallbecalculatedbeforeProduct3

1. Creating new Members

GotoBPCAdministrationandmakesureyoudidthefollowinglogon:

ApplicationSet: STUDENT_##

Application: SALES

1. Create the members Account1, Account2, Account3, Total of the

ACCOUNTdimensionaccordingtothefollowing:

Figure 302: Members 1

2. FortheProperty“Formula”andmember“Account3”enter:

Figure 303: Formula 1

Note: TheMDXformulawritesIIFinsteadofIF.

3. Save to Server.

ProcessDimensionanddoafullprocesshereforallapplications.

Don'tforgettodeselect“Takethesystemoffline”.

NowincludetheformulapropertyforthePRODUCTdimensionasfollows

andselectModifyDimensionPropertyafterwards.

252

第 273 页 / 共 473 页

BPC310 Lesson: Labs

Figure 304: Dimension Information

4. CreatethemembersProduct1,Product2,Product3asdescribedbelow:

Figure 305: Members 2

5. FortheProperty“Formula”andmember“Product3”enter:

Figure 306: Formula 2

6. Save to Server.

7. ProcessDimensionanddoafullprocesshere.

Don'tforgettodeselect“Takethesystemoffline”.

8. ClickatapplicationSetSTUDENT_##andrefreshclient-sidedimension

files.

9. Then logoff the system and logon again to BPC Administration for

STUDENT_##withSALESviatheConnectionWizard.

Creating an Input Schedule

1. Go to BPC for Excel.

2. Again make sure you logged in to:

ApplicationSet: STUDENT_##

Application: SALES

253

第 274 页 / 共 473 页

Unit7: Labs BPC310

3. Ifaskedenteryourpasswordintheconnectionwizard.

4. Select E-Tools → Client Options and here select “Refresh Dimension

Members”. AfterExecutionselectCLOSE.

5. Set the Current View as follows:

Dimension Member

ENTITY STORE1

CATEGORY BUDGET

MEASURES PERIODIC

TIME JAN.2008

ACCOUNT TOTAL

RPTCURRENCY LC

DATASRC INPUT

PRODUCT CON_PET_TYPE

Caution: Ifyoucan'tselectamemberviathedrop-downboxclickatthe

dimensionnamelefttothedrop-downboxandyouareinthesocalled

memberlookup. Hereyoucanselectaswishedintheuppertable.

6. NowcreateyourfirstInputScheduleasfollows:

7. FromtheDataInputselectBuildaScheduleusingadynamictemplate.

8. SelecttheAccount–TrendtemplateandthenselectOpen.

9. Whenprompted,“Aschedulehasbeenbuiltforyou”,selectOK.

10. YoushouldnowseeanInputScheduleinwhichProductismarkedredabout

theInputSchedulebecauseProductisinthe“header”oftheScheduleand

wouldhavetobeasinglevaluefortheinputofdata.

11. OpentheControlPanelusingthe“+”signsintheleftcornerandconfigure

asfollowsandafterthatselect“ExpandAll”:

254

Dimension	Member
ENTITY	STORE1
CATEGORY	BUDGET
MEASURES	PERIODIC
TIME	JAN.2008
ACCOUNT	TOTAL
RPTCURRENCY	LC
DATASRC	INPUT
PRODUCT	CON_PET_TYPE

第 275 页 / 共 473 页

BPC310 Lesson: Labs

Figure 307: Control Panel

12. IfyougetthemessagetoclearInputselectOK.

13. You will receive similar results:

Figure 308: Account Trend Schedule 1

14. DeleteallproductcolumnsexceptProduct1,Product2,Product3andyouwill

receive a sheet similar to the following:

Figure 309: Account Trend Schedule 2

15. GotoSetworkbookoptionsintheActionPaneandverifythat“Refresh

after Data Send” is selected.

16. FromtheeToolsmenu,selectSaveDynamicTemplate.

17.

Click on the Company button on the left side of the dialog box to

switchtotheeEXCELfolderundertheapplicationfolderontheserver.

18. ExpandtheInputSchedulesfolderandthenexpandtheManagerfolder.

19. Herecreateanewfolder“CPM310_##”andthensaveyourInputSchedule

as M300 Solve_Order and select Save.

PlanningtheInputDataandcontrollingtheresultsofthesolveorder

1. Now fill in the following plan data:

255

第 276 页 / 共 473 页

Unit7: Labs BPC310

Figure 310: Example 1

2. SaveyourdataselectingSENDDATA→SendactiveworksheetintheAction

Pane. Ifyougetanyinformationconcerninghowmanydatasetsaresaved,

confirm with YES.

3. You should get the following results:

Figure 311: Example 2

Caution: Sinceintheworkbookoptionstherefreshoptionitselectedthe

refreshisautomaticallyexecutedaftersendingdata.

Concerningthesolveordertheaccount3memberiscalculatedbeforethe

product3memberwitharesultof50forthecrossproduct.

4. NowchangeyourdataaccordingtothefollowingInputScheduleandselect

SENDDATA→Sendactiveworksheet. Youshouldreceivethefollowing

results:

Figure 312: Example 3

5. Leave BPC for Excel open.

Variant2: Product3shallbecalculatedbeforeAccount3

AdaptingtheMasterDataConfigurationtothenewSolveOrder

InyourBPCAdministrationmodegotoMaintainDimensionMembersforthe

AccountDimensionandfortheProductDimension.

256

第 277 页 / 共 473 页

BPC310 Lesson: Labs

ForAccount3changetheformulatosolve_order=10. SelectSavetoServer.

ForProduct3changetheformulatosolver_order=1. SelectSavetoServer.

NowselectProcessDimensionfortheAccountandProductDimensionanddoa

fullprocesshere. Don'tforgettodeselect“Takethesystemoffline”.

ClickatapplicationSetSTUDENT_##andrefreshclient-sidedimensionfiles.

Controlling the Changes in the Formula

Go to BPC for Excel mode which was kept open with Input Schedule

M300_Solve Order.

First you should see the old data:

Figure 313: Example 4

NowopentheconnectionwizardandgothroughapplicationSetStudent_##and

Application Sales. After that click at the icon “Refresh Workbook” and you

should see:

Figure 314: Example 5

The result of 5 for (Account3,Product3) is a consequence of formatting the

numbers. With 4 decimal places you would have had 4,7619 which would be

5000: 1050:

Figure 315: Example 6

Here you can see that Product3 is now calculated before Account3 according

to the new solve order.

257

第 278 页 / 共 473 页

Unit7: Labs BPC310

Ifyouwantyoucanchangethedataasfollowsandsenddatatotheserver:

Figure 316: Example 7

CloseyourInputSchedulebutleaveBPCforExcelopen. Whenpromptedto

save, select NO.

Lab 13 – Introduction into Script Logic

Objective: Youwanttogetanintroductionintoscriptlogicandwanttoexecute

it while sending data.

MakesureyouareloggedontoBPCforAdminstration.

ApplicationSet: STUDENT_##

Application: SALES

CREATION OF NEW ACCOUNTS

1. Create new member UPSIDE FACTOR for the Account Dimension as

follows:

Figure 317: Member 1

2. Select Save to Server.

3. CreatethememberMYACCOUNTasacopyfrommemberExternal

Sales and adapt the description:

Figure 318: Member 2

258

第 279 页 / 共 473 页

BPC310 Lesson: Labs

4. Select Save to Server.

5. CreateafurthernewmemberbycopyingtheUPSIDEFACTORlineandby

adapting it as follows:

Figure 319: Member 3

6. Select Save to Server.

7. CreatetwofurthermembersTOTVALandSALAFTERTAXasacopyof

External Sales and name them as follows:

Figure 320: Member 4

8. Select Save to Server.

9. Now select Process Dimension and mark the Account Dimension. Dońt

forgettodeselect“TakeSystemoffline”. Andselectbothapplicationsand

“Full Process”.

10. ClickattheApplicationSetandexecuteRefreshclient-sidedimensionfiles.

WORKING WITH FACTORS

Caution: FACTORSstandforaMultiplication! Neverforaddition.

Logic 1:

ExtSalesshallbemarkedup50%andtheresultshallbewrittenbacktothenew

accountUpsideFactorwhichisastatisticalaccount.

TocreatethislogicwithScriptLogicnavigatetotheScriptLogicnodebeneath

theSalesApplicationonthelefthandsideandselectCreateNewLogic.

EnterthetechnicalnameoftheIncludeyouwanttocreate. Write“LOGIC_1”.

259

第 280 页 / 共 473 页

Unit7: Labs BPC310

Figure 321: Logic_1 1

Now fill in the following script logic:

Figure 322: Logic_1 2

Validate and Save your script logic file!

Navigatetothelogicfilesonthefileserver(openWindowsExplorer)toseethat

a second file “LOGIC_1.LGX” is automatically created by the system when

validating and saving:

MyComputer→Database(G:)→BPC→Data→Webfolders→STUDENT_##

→ AdminApp → Sales

Seeasimilarmenupathinthenextscreenshot:

260

第 281 页 / 共 473 页

BPC310 Lesson: Labs

Figure 323: Logic_1 3

NowincludethetechnicalnameofthenewscriptlogicintotheDefaultscriptlogic

forapplicationSalessinceonlythedefaultlogicisexecutedwhilesendingdata

AlsoinactivatethecurrencytranslationInclude“FXTRANS”writingtwotimes

“/” in front of the include.

You should get the following results:

Figure 324: Logic_1 4

Validate and Save the Default Logic.

Test the Script Formula

MakesurethatyouareloggedontoBPCforExcelfor

ApplicationSet: STUDENT_##

Application: SALES

261

第 282 页 / 共 473 页

Unit7: Labs BPC310

Select ETools → Client Options → Refresh Dimension Members. After that

select Close.

Nowgothroughtheconnectionwizardandenteryourpasswordifasked.

Set the Current View as follows:

Dimension Member

ENTITY STORE1

CATEGORY BUDGET

MEASURES PERIODIC

TIME JAN.2008

ACCOUNT TOTREV

RPTCURRENCY LC

DATASRC INPUT

PRODUCT DOGBALL

CreateaninputschedulebyselectingDataInput→BuildaScheduleusinga

dynamictemplateandusetheACCOUNT–TRENDtemplate.

Open the Control Panel and select as follows:

Figure 325: Expansion Settings

ClickEXPANDALLafterwardsandifprompted“DoyouwanttoClearInput

Data” select “YES”.

Save the Input Schedule to the server to folder Manager → CPM310_## as

M900_Script_Logic.xlt.

You should receive the following result:

262

Dimension	Member
ENTITY	STORE1
CATEGORY	BUDGET
MEASURES	PERIODIC
TIME	JAN.2008
ACCOUNT	TOTREV
RPTCURRENCY	LC
DATASRC	INPUT
PRODUCT	DOGBALL

第 283 页 / 共 473 页

BPC310 Lesson: Labs

Figure 326: Account Trend Schedule 1

Enter500forExtSalesandsenddata. Youshouldreceive750asupsidefactor.

Figure 327: Account Trend Schedule 2

Calculation: 500 * 1,5 = 750 (upside factor)

Leave BPC for Excel open.

Logic 2:

SumupExtSalesandICSalesandthenmarkupthesumby50%andwriteresult

tothenewaccountUpsideFactorwhichisastatisticalaccount.

CreateanewIncludefortheSalesApplication:

Figure 328: Logic_2 1

Fillinthefollowingcodingintothescriptlogiceditor:

263

第 284 页 / 共 473 页

Unit7: Labs BPC310

Figure 329: Logic_2 2

Caution: *ISEXTSALES,ICSALESmeansADDITION.

Hint: If you only want the sum of both accounts then

the *REC line would have to be configured as follows:

*REC(ACCOUNT=”UPSIDEFACTOR”)

Validate and Save your new include.

IncludeyournewscriptlogicintoDefaultscriptlogicasfollows:

Figure 330: Logic_2 3

Validate and Save the Default Logic.

Test the Script Formula

Enter 400 for ExtSales and 600 for ICSales and Send Data. You will receive

1500 as upside factor.

264

第 285 页 / 共 473 页

BPC310 Lesson: Labs

Figure 331: Account Trend Schedule 3

Calculation: 400 + 600 = 1000 * 1,5 = 1500

Leave BPC for Excel open.

Logic 3:

Seven accounts have the property PARENTH1 = TOTREV. Let the system find

theseaccountsbythepropertymentionedandsumuptheirdataandthenmultiply

thesumwith1,5(e. i. 50%)andwritetheresulttothenewaccountUpsideFactor

which is a statistical account.

Create a new logic for the Sales Application called “LOGIC_3” and fill in as

follows:

Figure 332: Logic_3 1

Validate and Save the new logic.

GointotheDefaultLogicandenterasfollows:

Figure 333: Logic_3 2

265

第 286 页 / 共 473 页

Unit7: Labs BPC310

Validate and Save the Default Logic.

Test the Script Formula

WithinBPCforExcelandhereinyourInputSchedulefirstsetalldatatozero

including the UpsideFactor and send data.

You do this because the Upside Factor remains by its value from the last

calculationwhensavinganeweveniftheotherfiguresarezero.

Result:

Figure 334: Account Trend Schedule 4

Fillin100forExtSales,200forICSalesand1forMyAccount. SelectSenddata.

You will receive 452 as UpsideFactor.

Figure 335: Account Trend Schedule 5

Calculation: 100+200+1=301*1,5=451,50=452

WORKING WITH EXPRESSIONS

Logic 4:

SamelogicasinLogic3butprogrammeddifferentlyusingEXPRESSIONSinstead

of FACTOR.

CreateanewIncludefortheSalesApplicationandcallit“Logic_4”.

266

第 287 页 / 共 473 页

BPC310 Lesson: Labs

Figure 336: Logic_4 1

Caution: %VALUE% means=key word for original value before

calculation

Select Validate and Save.

IntegrateLogic_4asincludeintotheDefaultLogicasgivenbelow

Figure 337: Logic_4 2

Select Validate and Save.

Test the Script Formula

FirstsetalldatatozeroincludingtheUpsideFactorandsenddata.

You do this because the Upside Factor remains by its value from the last

calculationwhensavinganeweveniftheotherfiguresarezero.

Result:

Figure 338: Account Trend Schedule 6

Set20forExtSales,30forICSalesand1forMyAccountandsenddataandyou

will receive the following data:

267

第 288 页 / 共 473 页

Unit7: Labs BPC310

Figure 339: Account Trend Schedule 7

Calculation: 20+30+1 = 51 * 1,5 = 76,5

WORKINGWITHMEMBERSETANDGET

Logic 5:

youwanttocalculatetherelationbetweenICSALESandEXTSALESin%and

writeresultintoaccountEXTSALREL–onlyforBudgetdata

CreateanewLogicfortheSalesApplicationandcallit“Logic_5”. Fillinthe

following coding:

Figure 340: Logic_5 1

Select Validate and Save.

IntegrateLogic_5asIncludeintotheDefaultLogicasgivenbelow

Figure 341: Logic_5 2

Select Validate and Save.

Test the Script Formula

268

第 289 页 / 共 473 页

BPC310 Lesson: Labs

Within your Input Schedule in BPC for Excel first set all values to zero and

send data.

Result:

Figure 342: Account Trend Schedule 8

Fillin30forExtSalesand10forICSales,selectsenddataandyouwillreceive

the following data:

Figure 343: Account Trend Schedule 9

Calculation: 10/30 *100 = 33,33 % (33 % as in input schedule is due to

formatting of numbers).

WORKING WITH GO

Logic 6:

Two values shall be calculated whereby the second depends on the result of

the first one. The calculations are performantly done by the *GO statement.

Calculation shall only be done for Budget

Create a new Logic for Application Sales and call it “Logic_6”. Fill in the

following coding:

269

第 290 页 / 共 473 页

Unit7: Labs BPC310

Figure 344: Logic_6 1

Select Validate and Save.

IntegrateLogic_6asincludeintotheDefaultLogicasgivenbelow

Figure 345: Logic_6 2

Select Validate and Save.

Test the Script Formula.

FirstsetalldatatozeroincludingtheUpsideFactorandsenddata.

Thenset200forExtSalesand200forICSales. SelectSendDataandyouwill

receive the following data:

Figure 346: Account Trend Schedule 10

Calculation:

1. 200+200 = 400 =TOTVAL

2. 400 * 0,08 = 32 = SALAFTERTAX

270

第 291 页 / 共 473 页

BPC310 Lesson: Labs

LeaveopenyourInputScheduleforthenextLab.

Go back to BPC for Administration and Validate and Save LOGIC_1 and

integrateLogic_1asIncludeintotheDefaultLogicasgivenbelow.

Figure 347: Script Logic

Validate and Save the Default Logic.

Lab 14 – Script Logic and Data Management Package

Objective: YouwanttoexecutetheScriptLogiccentrally(aftersendingdata)and

soneedtocreateadatamanagementpackagewhichallowstostartthecalculation

according to the script logic. For our example we are going to take LOGIC_1

which calculated the first upside factor.

MakesureyouareloggedontoBPCforAdminstration.

ApplicationSet: STUDENT_##

Application: SALES

Create a new Include

1. NavigatetothescriptlogicnodefortheSalesApplicationandcreateanew

include called “EXAMPLE01”.

2. ThenopentheDefaultLogicfileandcopyitscontent. Thenpastethecontent

intoyournewInclude. SelectValidateandSave.

3. You should receive the following result:

271

第 292 页 / 共 473 页

Unit7: Labs BPC310

Figure 348: Example 1

4. MakesurethattheDefaultLogicisnolongerexecutedwhilesendingdata.

ThereforegototheDefaultlogicandputacommentbeforetheInclude.

5. You should receive the following results:

Figure 349: Example 2

6. Select Validate and Save.

Create a Data Management Package

1. Now create a data management package with which you can execute the

logicasanadministrator. ThecreationisdoneviacopyingtheexistingFX

Restatement.dtsx package!

2. Navigate as follows to create the new package: Start → Programs →

MicrosoftSQLServer2005→SQLServerBusinessIntelligenceDevelopment

Studio

3. Select File → open File.

4. For “Look in” select as follows:

272

第 293 页 / 共 473 页

BPC310 Lesson: Labs

Figure 350: Example 3

Note: YoucouldalsonavigateviaWindowsExplorer→MyComputer→

Database(G:)→BPC→Data→Webfolders→Student_##→Sales→

DataManager→PackageFiles→SystemFiles

5. Mark FX Restatement.dtsx:

Figure 351: Example 4

6. Copy it into the folder “MYFILES”:

Figure 352: Example 5

7. Renamethecopiedfileas: “Example01.dtsx”. AfterthatselectCancel.

8. You should receive the following result:

273

第 294 页 / 共 473 页

Unit7: Labs BPC310

Figure 353: Example 6

9. Close MS Visual Studio.

IntegrateaDataManagementPackageintotheDataManager

1. Navigate to BPC for Excel and make sure that you are logged on to

ApplicationSetSTUDENT_##andApplicationSALES.

2. Navigate as follows: Manage Data → Maintain Data Management →

Manage Packages (organize List)

3. FirstcreateapackagegroupCPM310_##fortheSalesApplicationbeneath

which the new package will be addressed.

4. Markthenode“Sales”,rightmouseclick“AddPackageGroup”andenter

the name “CPM310_##”.

5. You should receive the following results:

Figure 354: Example 7

6. SelectOKandselectOKinthenextpop-up.

7. Nowclickatfolder“CPM310_##”andinthe“yellowarea”called“Package

Name”rightmouseclickandselect“AddPackage”.

8. Fill in the next pop-up as follows:

274

第 295 页 / 共 473 页

BPC310 Lesson: Labs

Figure 355: Example 8

9. Select Add. Don't select Save.

Link Script Logic and Package

1. Clickatyourpackageandrightmouseclick. Thenselect“ModifyPackage”.

Figure 356: Example 9

2. Clickatthebutton“ViewPackage”inthe“File”line.

Figure 357: Example 10

3. You will receive the following pop-up:

275

第 296 页 / 共 473 页

Unit7: Labs BPC310

Figure 358: Example 11

4. Selectthebutton“Advanced”intheupperrightcornerandyouareledto

the following editor:

Figure 359: Example 12

5. In the line “Task Execute formulas,Logicfile%Apppath%\.Admi-

nApp\%App%\FXTRANS.lgx”youhavetoexchangeFXTRANS.LGX

withExample01.LGXtogetthelinkbetweenthelogicandthepackage.

6. You should receive the following result:

Figure 360: Example 13

7. Andwheneveryouwanttoexecutethispackageasanadministratoryouwant

tohavethepossibilitytoselectCategory,Entity,TimeandAccount.

8. AndsinceAccountisnotyetavailableforthepop-upyouhavetoadditin

thesecondlinebeginningwithprompt. Herejustadd%Account_Dim%.

9. You will receive the following results:

Figure 361: Example 14

276

第 297 页 / 共 473 页

BPC310 Lesson: Labs

10. SelectOKandthenthreetimesSaveandthenOK.

Test Data Management Package

1. YouareinBPCforExcelandwiththesameselectionsfromthelastLaband

with Input Schedule M900_Script_Logic.xlt open delete all data in the

sheet and select Send Data.

2. Then enter 300 for ExtSales and 300 for IcSales and send data. Confirm

with YES, after that click CLOSE.

3. TheUpsideFactorwhichiscalculatedbyLogic_1withinExample01should

not be calculated:

Figure 362: Example 15

4. Now execute the new package.

5. Firstclickat“HOME”. Thennavigateasfollows:

ManageData→Runadatamanagementpackage.

6. SelectPackageGroup“CPM310_##”andthenPackage“Example01”.

7. Select Run.

8. Select“No, donotcheckworkstatussettings”.

9. Select Next.

YouwillreceivethefollowingresultandcanseethatyoucanselectAccount

in the next pop-up:

277

第 298 页 / 共 473 页

Unit7: Labs BPC310

Figure 363: Example 16

10. Select:

Category: Budget

Entity: Store 1

Time: 2008.Jan

Account: leave open

11. SelectNext,thenFINISH,thenOK.Afterthatclick“ViewStatus”.

12. Thecalculationshouldbefinishedandyoushouldseeasimilarscreen:

Figure 364: Example 17

278

第 299 页 / 共 473 页

BPC310 Lesson: Labs

13. CloseyourscreenbyselectingCLOSE,thenCANCELandreturntoyour

Input Schedule M900_Script_Logic.xlt. Click at the button “Refresh

Workbook”.

14. You should receive the following results:

Figure 365: Example 18

15. Calculation: 300 * 1,5 = 450 = Logic_1

NowreturntotheOnlineExecutionofLogic1. Doingsoyouhavetodelete

the“//”before“IncludeLogic_1”intheDefaultInclude. ValidateandSave

your Default Settings.

16. You should receive the following results:

Figure 366: Example 19

17. CloseyourInputSchedulewithoutsavingbutleaveBPCforExcelopen.

Lab 15 – Loading Currency Exchange Rates into the

Rate Application

Objective: Transferaload-readyimportfilefromyourlocalmachinetotheserver

using the Data Upload option.

Launch a Data Manager Import package to load Actual, Budget and Forecast

currency exchange rates into the Rate application of your STUDENT_##

application set.

279

第 300 页 / 共 473 页

Unit7: Labs BPC310

Description: Priortoloadingfinancialdata,exchangeratescanbeloadedintothe

RateapplicationsoforeigncurrencytranslationfromLocalCurrencyintoUSD&

EURwilltakeplacein“realtime”duringsubsequentloads.

ThislabisperformedusingyourSTUDENT_##applicationset.

Loading the Exchange Rates

1. In the BPC for Excel mode select Application Set “Student_##” and

Application RATE, then click OK.

2. FromtheActionPane, selectManageData.

3. FromtheActionPane, selectUploaddatafile.

4.

FortheSourceFilefield,clickontheonthe Selectbuttontoselectthe

PetDepotRateData.txtloadfilelocatedinyourCPM310–##folder. Select

Open.

5.

FortheDestinationFilefield,clickontheonthe SelectbuttonandSave

thefilewiththesamenameintotheCompany→DataFiles→MYFiles

folderontheBPCserver. ClickattheSavebutton. Whenprompted,“File

transferred successfully”, select OK.

This uploads the file from the G Drive location to the folder on the BPC

ApplicationServer. ItdoesnotloadtheratesintotheFacttables. Thiswill

be next.

Note: IfyouhadnotenteredtheAppSetParametersfromLab10intothe

systemyouwouldn’thavebeenabletosavethe.txtfiletotheserver.

6. FromtheActionPane, selectPreviewdatafile.

7. Select the Pet Depot Rate Data.txt and then select Open. This displays

thefirst200recordsofthedatafile. Inthisexample,theseratesareallper

the Group Currency which is USD.

8. SelectCanceltoclosetheDataPreviewdialogbox.

9. FromtheActionPane,selectRunadatamanagementpackage.

10. SelecttheImportpackageandthenselectRun.

11.

IntheImportfilefield,clickonthe SelectbuttontoselectthePetDepot

RateData.txtloadfileandthenselectOpen.

12. Verify that your screen looks similar to the following then click Next to

continue. Thescriptlogicisusedduringthisload,soleavethedefaultsetto

Yes for default logic.

280

第 301 页 / 共 473 页

BPC310 Lesson: Labs

Figure 367: Data Management Package 1

13. Select the option No, do not check work status settings (we haven’t

configuredthatyet)andthenclickonFinishtostarttheload.

Figure 368: Data Management Package 2

281

第 302 页 / 共 473 页

Unit7: Labs BPC310

14. Whenprompted“Therequesttorunthepackageontheserverwassuccessful.

The package is now running” select OK.

15. From the Action Pane, select View Status.

16. ClickontheRefreshstatuseverycheckbox. Thiswillrefreshthedisplay

every 5 seconds, by default.

17. Afterafewmoments,verifythatyourPackageStatuswindowlookssimilar

to the following:

Figure 369: Package Status

18. DoubleclickontheImporttoseethePackageLog(justincaseyouneed

to debug a dataload):

Figure 370: Package Log

282

第 303 页 / 共 473 页

BPC310 Lesson: Labs

19. Then double click on the formula log and scroll to the bottom to see the

load time and data:

Figure 371: Formula Log

20. Close all the pop-ups.

Lab 16 – Loading Actual & Budget Data into the

Finance Application

Objective: Transferaload-readyimportfilefromyourlocalmachinetotheserver

using the Data Upload option.

LaunchaDataManagerimportpackagetoloadActualandBudgetdataintothe

FinanceapplicationofyourSTUDENT_##applicationset.

ViewthepackagestatusandverifytheimportbycreatingaTrendreportinBPC

for Excel.

Description: TheBPCDataManagerisapowerfulmodulethatletsyoumove

data into and out of an Application, as well as copy or move data within an

Application. It supports mapping and complex transformations of data and is

basedonSQLServerIntegrationServices(SSIS).

ThislabisperformedusingyourSTUDENT_##applicationset.

1. Loading the Finance Data

283

第 304 页 / 共 473 页

Unit7: Labs BPC310

1. FromtheActionPane,clickonRATEintheCVsection.

2. The BPC Logon screen is displayed.

3. FromtheApplicationdropdownlistbox,selecttheFINANCEapplication

as shown below and then click OK.

Figure 372: BPC Logon

4. FromtheActionPane, selectManageData.

5. FromtheActionPane, selectUploaddatafile.

6.

FortheSourceFilefield,clickontheonthe Selectbuttontoselectthe

PetDepotFinanceData.txtloadfilelocatedinyourCPM310–##folder.

Select Open.

7.

FortheDestinationFilefield,clickontheonthe SelectbuttonandSave

the file with the same name into the Company → DataFiles → MY Files

folder on the BPC server.

8. Select OK to upload the file.

9. Whenprompted,“Filetransferredsuccessfully”,selectOK.

10. FromtheActionPane, selectPreviewdatafile.

11. Select the Pet Depot Finance Data.txt file and then select Open. This

displaysthefirst200recordsofthedatafile.

12. SelectCanceltoclosetheDataPreviewdialogbox.

13. FromtheActionPane,selectRunadatamanagementpackage.

14. SelecttheImportpackageandthenselectRun.

15.

IntheImportfilefield,clickonthe SelectbuttontoselectthePetDepot

Finance Data.txt load file.

16. LeaveallotherfieldswiththeirdefaultsandthenclickonNext.

17. SelecttheoptionNo,donotcheckworkstatussettingsandthenclickon

Finish to start the load.

284

第 305 页 / 共 473 页

BPC310 Lesson: Labs

18. Whenprompted“Therequesttorunthepackageontheserverwassuccessful.

The package is now running” select OK.

19. From the Action Pane, select View Status.

20. ClickontheRefreshstatuseverycheckbox. Thiswillrefreshthedisplay

every 5 seconds, by default.

Afteraboutoneminute,verifythatyourPackageStatuswindowlookssimilar

to the following:

Figure 373: Package Status

21. Select Close to close the status window.

22. SelectCanceltoclosetheDataManagerRunPackagewindow.

2. Verifying the Data Load

1. Using the hyperlinks in the CV section of the Action Pane, change your

currentviewsettingstomatchthefollowing:

Figure 374: Example

285

第 306 页 / 共 473 页

Unit7: Labs BPC310

2.

From the top of the Action Pane, click on the Home icon to return to

the Home Action Pane.

3. FromtheActionPane,selectReporting&Analysis.

4. FromtheActionPane,selectBuildareportusingadynamictemplate.

5. SelectthetemplateTrend, andthenselectOpen.

6. Whenprompted“Areporthasbeenbuiltforyou”,selectOK.

7. Verifythatyourreportlookssimilartothefollowing:

Figure 375: Trend Report 1

8. ChangethecurrentviewcategorytoBUDGETandverifythatyourreport

looks similar to the following:

Figure 376: Trend Report 2

9. From the Excel File menu, select Close.

10. Whenprompted,“Doyouwanttosavethechanges…?”selectNo.

286

第 307 页 / 共 473 页

BPC310 Lesson: Labs

Lab 17 – Creating Forecast Data for the Finance

Application

Objective: Copy Actual and Budget data to create the full year Forecast data

for 2008.

Description: YouwillusetheDataManagercopypackagetocopyActualdata

forJanuary–MarchandBudgetdataforApril–Decembertopopulatethefull

year Forecast.

ThislabisperformedusingyourSTUDENT_##applicationset.

1. CreatingForecastdatausingtheCopypackage

1. FromtheActionPane,selectHomethenManageData.

2. FromtheActionPane,selectRunadatamanagementpackage.

3. SelecttheCopypackageandthenselectRun.

4. ChangetheCopysettingstomatchthefollowing:

Figure 377: Data Management Package 1

5. Click Next to continue.

6.

UndertheSourceheading,clickonthe Selectbuttontotherightof

the Category drop-down list box.

7. SelecttheACTUALcategoryandthenclickthe'CopySelected'button.

287