In this exercise you will learn how to change member names and aliases, add members to existing dimensions, add Account members and understand Account Types, add Custom Dimensions and add Attribute Dimension.
Update Period names and aliases…
1. From menu bar on AutoPlan page, select Administration >
Dimensions D. On Dimensions tab and from Dimensions drop down list select Period. Note: Begbalance is primarily used for Balance Sheet accounts. Expand YearTotal and Q1, select Jan and click Edit button. Change Name: to M1 and Alias: to Jan and click Save button. An error message displays - An object with the name Jan already exists. In order to change Alias to an existing Name, you need to Edit and Save the member twice. Close the error message and with just Name: of M1 click Save button. Now for M1, click Edit button. Add Alias: Jan and click Save button.
2. Repeat step 1 for remaining months M2/Feb through M12/Dec
3. From menu bar on AutoPlan page, select Administration > Alias Tables A. On Alias Tables page click Add button.
Explorer User Prompt dialog box displays:
Enter name for new Alias Table – French
4. Repeat step 3 for M2/Fevrier and M3/Mars.
5. From menu bar on AutoPlan page, select Administration >
Dimensions D. On Dimensions tab and from Dimensions drop down list select Period. Expand YearTotal and Q1, select M1 and click Edit button. Change value for Aliases drop down list from Default to French and enter Alias: of Janvier and click Save button.
6. From menu bar on AutoPlan page, select Administration >
Manage Database B. On Manage Database page, Select Database checkbox and click Refresh button. When Refresh Complete message displayed, click Finish button.
7. Open Essbase Administration Services Console and Edit
AutoPlan:AutoFin Outline to confirm changes to Period member Names and Aliases. To change Alias table, go to Properties tab, under Alias tables right-click on French and select Set as Active.
Return to Outline tab and confirm French aliases for M1, M2 and M3.
Return to Properties tab and reset Default as the Active alias table.
Close Outline Editor.
Add Scenario members…
8. From menu bar on AutoPlan page, select Administration >
Dimensions D. On Dimensions tab and from Dimensions drop down list select Scenario. Select Current and click Edit button.
Change to Name: Actuals, Start Yr.: FY06, Alias: Act, unselect checkbox next to Enabled for Process Management and click Save button.
Click Add Sibling button and enter following values:
Scenario: Budget
Enabled for Process Management: checked Click Save button.
Click Add Sibling button and enter following values:
Enabled for Process Management: checked
Click Save button.
Click Add Sibling button and enter following values:
Scenario: Act vs Bud Start Yr.: FY06
Start Period: M1 End Yr.: FY06 End Period: M12
Data Storage: Dynamic Calc
Enabled for Process Management: unchecked Click on Member Formula tab and
Enter Member Formula: @VAR(“Actuals”,”Budget”);
Click Save button.
Validate Member Formula does not provide accurate validation.
Click Add Sibling button and enter following values:
Scenario: Act vs Fcst Start Yr.: FY06
Start Period: M1 End Yr.: FY06 End Period: M12
Data Storage: Dynamic Calc
Enabled for Process Management: unchecked Click on Member Formula tab and
Enter Member Formula: @VAR(“Actuals”,”Forecast”);
Click Save button.
Add Version members…
9. From menu bar on AutoPlan page, select Administration >
Dimensions D. On Dimensions tab and from Dimensions drop down list select Version. Select BU Version_1 and click Edit button. Change Name: to Current and Alias: to Working and click Save button.
Click Add Sibling button and enter Version: Final and click Save button.
Click Add Sibling button and enter Version: R1, Alias:
Round1 and click Save button.
Click Add Sibling button and enter Version: R2, Alias:
Round2 and click Save button.
Click Add Sibling button and enter Version: R3, Alias:
Round3 and click Save button.
Click Add Sibling button and enter Version: WhatIf1 and click Save button.
Click Add Sibling button and enter Version: WhatIf2 and click Save button.
Add Account members…
10. From menu bar on AutoPlan page, select Administration >
Dimensions D. On Dimensions tab and from Dimensions drop down list select Account. Create the Account hierarchy based on worksheet below from C:\BootCamp\Model\123Model.xls (you may want to open worksheet in order to cut and paste Account member names).
Use Multiplication for both price members after Plan Types: for both AutoFin and AutoHR
For appropriate higher level Account members (e.g. “Total Cost of Sales”, “Allocated Costs”, “Operating Expenses” and Taxes) change Account Type: to Expense (Variance Reporting: will also change to Expense) and use Subtraction after Plan Types: for both AutoFin and AutoHR.
When you add descendants of these members, they will default to Expense.
Leave default of Source Plan Type: AutoFin
11. Also add Metric accounts:
Select “Net Sales”, click Add Sibling button and enter:
Name: Metrics
Plan Types: Ignore (for both AutoFin and AutoHR) Click Save button.
Click Add Child button and enter:
Name: Headcount
Account Type: Saved Assumption
Plan Types: Ignore (for both AutoFin and AutoHR) Click Save button.
Click Add Sibling button and enter:
Name: Average Inventory Account Type: Asset
Plan Types: Ignore (for both AutoFin and AutoHR) Click Save button.
Click Add Sibling button and enter:
Name: Rebate % Sales Account Type: Expense
(Variance Reporting: will also change to Expense) Plan Types: Ignore (for both AutoFin and AutoHR) Select the Member Formula tab and Enter member Formula:
"Rebates" % "Auto Sales";
Click Save button.
12. From menu bar on AutoPlan page, select Administration >
Manage Database B. On Manage Database page, Select Database checkbox and click Refresh button. When Refresh Complete message displayed, click Finish button.
13. Open Essbase Administration Services Console and View
AutoPlan:AutoFin Outline to confirm Account hierarchy Member Names, Consolidations and Expense Reporting properties. Close Outline Editor.
14. Right-click on AutoPlan:AutoFin and select Set > Default calculation… , add SET UPDATECALC OFF;
Click OK button.
15. Open C:\BootCamp\Model\123Model.xls and Copy the LOAD worksheet to worksheet Before sheet: LOAD (remember to select Create a copy checkbox).
16. Select Data Source Manager and connect to AutoPlan:AutoFin database using right-click and selecting Ad-hoc Analysis.
A Smart View dialog box displays:
Select No button so that “the contents of the sheet” are NOT cleared.
17. Copy the data values from original LOAD worksheet to the LOAD (2) worksheet and click on Submit Data button.
18. Right-click on cell and select Hyperion > Calculation Options >
Calculate, select the Default Calculation Script for AutoFin Cube and click Launch button.
Calculation Script has been processed dialog box displays.
Click OK button and click Close button to close Calculation Scripts dialog box.
19. Select Validate worksheet and connect to AutoPlan:AutoFin database using right-click and selecting Ad-hoc Analysis. Select No button so that “the contents of the sheet” are NOT cleared.
Confirm that value for “Net Sales” in cell B45 is 130,000,000.
If there are differences, compare with MeasuresModel worksheet to identify differences, make corrections to Account members, Refresh Database and revalidate.
20. Copy the ScenVariance worksheet to worksheet Before sheet:
ScenVariance (remember to select Create a copy checkbox).
21. Select Data Source Manager and connect to AutoPlan:AutoFin database using right-click and selecting Ad-hoc Analysis. Select No button so that “the contents of the sheet” are NOT cleared.
22. Copy the Actuals and Budget values from original ScenVariance worksheet to the ScenVariance (2) worksheet and click on Submit Data button.
23. Click Refresh button, both variances should be 100 indicating that Expense Reporting for Rebates and @VAR formula is set correctly.
24. Copy the TimeBalanceInput worksheet to worksheet Before sheet:
TimeBalanceInput (remember to select Create a copy checkbox).
25. Connect to AutoPlan:AutoFin database using right-click and selecting Ad-hoc Analysis. Select No button so that “the contents of the sheet”
are NOT cleared.
26. Copy the data values from original TimeBalanceInput worksheet to the TimeBalanceInput (2) worksheet and click on Submit Data button.
27. Right-click on cell and select Hyperion > Calculation Options >
Calculate, select the Default Calculation Script for AutoFin Cube and click Launch button.
28. Select TimeBalanceBefore worksheet, connect to AutoPlan:AutoFin database using right-click and selecting Ad-hoc Analysis. Select No button so that “the contents of the sheet” are NOT cleared. Data should roll up across time in standard manner.
29. From menu bar on AutoPlan page, select Administration >
Dimensions D. On Dimensions tab and from Dimensions drop down list select Account.
30. Select Headcount, click Edit button, set Time Balance: Balance and Skip: Missing and click Save button.
Select “Average Inventory”, click Edit button, set Account Type:
Asset, Time Balance: Average (default for Asset accounts is Balance) and Skip: Missing and click Save button.
31. From menu bar on AutoPlan page, select Administration >
Manage Database B. On Manage Database page, Select Database checkbox and click Refresh button. When Refresh Complete message displayed, click Finish button.
32. Select TimeBalanceAfter worksheet, connect to AutoPlan:AutoFin database using right-click and selecting Ad-hoc Analysis. Select No button so that “the contents of the sheet” are NOT cleared. Data should now reflect appropriate Time Balance calculations across time.
33. Review TimeBalanceSkipMiss worksheet.
Create Custom Dimensions…
34. From menu bar on AutoPlan page, select Administration >
Dimensions D. On Dimensions tab click Add Dimension button.
Add Dimension window displays:
Enter Dimension: Total Dealers, check Apply Security: and click Save button.
Confirmation dialog box displays:
Click OK button.
35. With “Total Dealers” selected, click Custom Dimensions button.
The Manage Attributes and Value window displays:
Click Create button at top of left-hand pane to create new Attribute dimension related to “Total Dealers”.
Enter Name: US Regions, leave Data Type: Text (default), click Save button and then click on in upper right-hand corner to close Create Attribute dialog box.
36. Click Create button at top of right-hand pane to create new Attribute dimension Values.
Enter Name: East US and click Save button. Also add Central US and West US and then click on in upper right-hand corner to close Create Attribute Value dialog box and click Close button to close the Manage Attributes and Value window.
37. On Dimensions tab click Add Child button and enter Name: AW 001.
Plan Types which were left checked (Valid) for AutoFin and AutoHR now have to be valid for every member of Total Dealers – All or Nothing.
Click on Attribute Values tab.
Select West US on left and click on Add button to assign Attribute Value to member. Click Save button. We will be adding members later using Outline Load Utility.
38. On Dimensions tab click Add Dimension button and enter Dimension: Products, check Apply Security: and click Save button. We will be adding members later using Outline Load Utility.
39. Click on the Performance Setting tab. The default settings appear to be reasonable for this application.
40. From menu bar on AutoPlan page, select Administration >
Manage Database B. On Manage Database page, Select Database checkbox and click Refresh button. When Refresh Complete message displayed, click Finish button.
41. Open Essbase Administration Services Console and View AutoPlan:AutoFin Outline to confirm Custom Dimensions and Attribute Dimension. Right-click on “AW 001” member, select View member properties and confirm Attribute Value assigned.
Plan Types…
42. View AutoPlan:AutoHR Outline and expand Accounts dimension. For Accounts, AutoFin was set as Source Plan Type. @XREF formulas then bring values from the AutoFin cube to the AutoHR cube.
Load Data…
43. Right-click on AutoFin, select Load data… and the Data Load dialog box displays. Click on Find Data File button, select File System tab, select C:\BootCamp\Load\LoadACT.txt file and click OK button.
Click on Find Rules File button and select Essbase Server tab.
Look in: demodrive:AutoDemo:AutoPlan, select LoadAct.rul file and click OK button.
Data Load dialog box should look as shown below:
Click the OK button to initiate the data load.
Data Load Results dialog box should be displayed indicating Status of Success:
Click Close button.
44. Right-click on AutoFin database, select Execute Calculation… and select the (default) calculation.