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. Refer to the Table of Contents for details.
Start a New Cube Sheet
- Navigate to: Modeling > Model Management.
- Choose one of the following options.
- Level Assigned Sheets: Create a level-assigned sheet.
- User Assigned Sheets: Create a user-assigned sheet. The users you assign will have access to all levels available in the sheet, including access to sensitive information they might not typically see (for example, personnel data).
- Click New Sheet.
- Enter a Sheet name and Account code prefix. The sheet name must be unique for your installation,
- Select Create a new sheet > Cube, then click Next.
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 screen for a cube sheet, click Cube Accounts.
- Click one of the following from the toolbar:
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 <New Account> > Account Details (on the right), enter a Code and Name for the account. Short Name and Description are optional.
Select a Type. Refer to Account Details Field Descriptions.
In Account Attributes, select attributes, if needed.
In Data Type > Display As select number, currency, or percent.
Set Data Privacy: private, public at top level only, or public at all levels.
click Save .
Tips for Cube Accounts
- To create an account group (identical to account groups in the Custom Accounts area), click Create new group.
- To include an existing cube-enabled GL Short for General Ledger. or custom account on the cube sheet, click 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 check box: Read only on sheet. If checked, this account cannot be edited from the cube sheet. This check box is not available for cube calculated accounts, since those accounts are read only on a sheet.
Add Custom Dimensions and Attributes to the Sheet
Cube sheets must contain a set of dimensions.
Time, Account, and Level dimensions: These are systems 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 7 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 screen for a cube sheet, click Dimensions, Attributes and Levels.
Use the left pane to browse or search for Dimensions and Attributes.
To include a dimension or attribute in a sheet, drag it 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 click Trash or press Delete.
When you've finished adding dimensions and attributes, click Save .
Set the Initial View
You can control how the cube sheet appears to users when they first open it in the sheet viewer.
Click 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:
- From the Sheet Summary screen for a cube sheet, click Restrictions.
- Click 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 click 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, click 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).