Cube sheets can contain multi-dimensional data input across a specified set of dimensions. Although the cube sheet was designed for modeling complex (and multi-dimensional) sales forecasting, you can use it for planning almost any multi-dimensional model. To create a cube sheet, you need to perform several tasks.
Before You Begin
Required permission: Model Management Access > Model
How You Get There
From the nav menu, select Modeling. In the Sheet menu, select User Assigned Sheets or Level Assigned Sheets to view the list of sheets in your model.
Start a New Cube Sheet
- From the Sheets list, select the New Sheet button.
- Enter a Sheet Name and Account Code Prefix. The sheet name must be unique for your installation,
- Select the Create a new sheet radio button, and choose Cube from the drop-down.
- Select Next.
The Sheet Summary for the new sheet appears with the links to:
- Cube Accounts
- Dimensions, Attributes, and Levels
Create Cube Accounts
Cube sheets must contain at least one standard cube account. Although General Ledger accounts are the most common type of account, there are other types of accounts available.
- From the Sheet Summary, select Cube Accounts.
- From the toolbar, select one of the following buttons:
Create new cube standard account: Standard cube accounts behave identically to custom accounts on a standard sheet. Users can enter numbers or formulas into these accounts and they roll up any account tree created within the cube in the same way that custom accounts roll up. Standard cube accounts are all accessible from within and from outside the cube sheet.
Create new cube calculated account: Cube calculated accounts behave identically to modeled calculated accounts. They have a formula defined on them at the global account level, and they roll up in the same way as modeled calculated accounts: they sum up the value of the formula at each location. Cube calculated accounts must have formulas that evaluate to zero if the standard cube accounts at the same location are empty.
Create new cube assumption account: Cube Assumptions behave identically to assumptions, except that they are only accessible from within the cube. References to cube assumptions from outside the cube are not allowed.
Create new cube metric account: Metric cube accounts behave like metric accounts on a standard sheet. Users can enter numbers or formulas into these accounts, and rollup is performed by evaluating the formula on the rollups of its terms.
- In the Account Details (on the right), enter a Code and Name for the account. Short Name and Description are optional.
Select a Type. See Account Details Field Descriptions.
In Account Attributes, select attributes, if needed.
In Data Type > Display As select number, currency, or percent.
Set Data Privacy.
Tips for Cube Accounts
- To create an account group (identical to account groups in the Custom Accounts area), select Create new group.
- To include an existing cube-enabled GL Short for General Ledger, or custom account on the cube sheet, select Include GL or Custom Account. Users can drill into transactions of cube-entered GL accounts from the cube sheet.
- Cube accounts have nearly all the same attributes as custom accounts. They also have an additional checkbox: Read only on sheet. If checked, this account cannot be edited from the cube sheet. This checkbox is not available for cube calculated accounts, since those accounts are read only on a sheet.
Add Custom Dimensions and Attributes
Cube sheets must contain a set of dimensions.
Time, Account, and Level dimensions: These are system dimensions that must be present in every cube sheet. You cannot remove them, but can specify which of their elements are present in the cube. The asterisk (*) next to these dimensions indicate that they cannot be removed from the cube sheet.
Custom dimensions: You can include up to 10 custom dimensions on each sheet. You must create custom dimensions first before you can include them in a cube sheet. Navigate to Modeling > Levels and Dimensions to create custom dimensions and populate them.
Attributes: These are account attributes, level attributes, dimension attributes. If you add a dimension attribute, the associated dimension is added automatically if it isn't already present in the cube. If you exclude attribute values from the sheet, you don't exclude the associated dimensions automatically.
The total maximum number of system dimensions, custom dimensions, and attributes allowed in a cube is 50.
To add dimensions or attributes to the cube sheet:
- From the Sheet Summary, select Dimensions, Attributes and Levels.
Use the left pane to browse or search for Dimensions and Attributes.
Drag the dimension or attribute to the center panel.
To view dimension properties, select a dimension in the center panel; its properties appear in the right panel.
(Custom dimensions only) Select Edit dimension on sheet to allow users to add dimension values when they use the sheet.
To rearrange dimensions and attributes, use any of the following methods. The order you set determines the order in which the drop-down selectors for the dimensions and attributes appear on the sheet.
Select a dimension or attribute and use the Up/Down arrows to move it.
Drag and drop dimensions or attributes to reorder them.
Right-click and select Move to top or Move to bottom from the context menu.
To delete a dimension or attribute, select the item you want to delete and select the Trash or press Delete.
When you've finished adding dimensions and attributes, select Save .
Set the Initial View
You can control how the cube sheet appears to users when they first open it in the sheet viewer.
Select the gear icon (above the center pane) to set the sheet properties.
In Initial Settings, select the dimensions and attributes that are initially placed on the two axes of the sheet viewer.
(Optional) To ensure that dimension columns are not too wide, specify the Maximum column width.
(Optional) To automatically hide any row with zero or blank values in the sheet viewer, select Suppress rows if all zeros or blank.
You can further set the default view of the sheet with the display options on the sheet itself.
Set Cube Restrictions
Restrictions let you specify that certain combinations of dimensions should not be available in your cube. For example, you might want to specify that Sales West level is only allowed to see City Dimension members for San Francisco, Sacramento and Los Angeles. If you do not add cube restrictions, all dimension combinations exist in the cube sheet.
To set a new restriction:
- Select Restrictions.
- Select Add New. A page appears with a list of dimensions. The left pane represents the dimension that determines the restriction (the primary dimension). The right pane represents the dimension that gets restricted (the secondary dimension). For example, to set up a restriction for Organization Level and Cities, select the organization level dimension in the left pane and the cities dimension in the right pane.
- Select a primary and secondary dimension, then select Create and Continue.
The Edit Restriction page appears.
- Set the restrictions for each element on the edit restrictions page.
For the Organization Level and Cities example, navigate to the Sales West organization level in the left pane and make sure San Francisco, Sacramento, and Los Angeles are allowed in the right pane.
When you've finished defining the restriction, select Save .
Review the cube sheet to confirm your restrictions. For example, when you select the Sales West organization level, you should only see the cities dimension members set by your restrictions (San Francisco, Sacramento, and Los Angeles).