Using SpotlightXL-Model Dimensions Subtask

Overview

With this task, you can enter dimension data for the dimensions defined on the Model > Setup page. Dimensions entered on the Setup page are populated in the Dimension list box as shown below. You can also add dimension members to a generated model without clearing and regenerating the model. For example, if you have a Revenue Forecast model and you want to add a new sales representative or product, you can do so without clearing or regenerating your model.

Access the Dimension by selecting the Model > Dimension.

Actions Available Described

A description of the actions available for the Dimension subtask is provided below.

Refresh - After a selection is made, click Refresh to view updated data.

Save - Save defined dimension data.

Spreadsheet Fields Described

The spreadsheet fields on the Dimension page are described below.

Model - Select the model you want to define dimension data for.

Status - Indicates whether the model is generated or not. See the Model Setup subtask for more information.

Dimension - Select the dimension you want to provide member details for. New models will have no dimension members defined.

Action

• Select View to see the current dimension members and hierarchy. This option is available for unlocked models.
• Select Update to change the Rollup Operator and Member Type for the dimension members. This option is available for locked models only. See Defining Dimension Members for a Model.
• Select Delete to remove members and their associated data from a dimension. This option is available for locked models only and does not require you to clear and regenerate the model. After you delete members, you need to run calculations to ensure that rollup data is correct. You also must update any views, reports, or calculations that refer to the now-deleted member. See Deleting a Dimension Member from a Generated and Locked Model.
• Select Append to add a dimension member to a generated model. Once you select Append, a blank table is displayed. Enter new dimension members in the table. Click Save to add new dimension member(s) to the model. To view the new dimension member(s) select View from the Action drop-down. See Adding a Dimension Member to a Generated Model.

See Also:

How to Define Multiple Number Formats for Dimension Members in Views

Creating Attributes within Dynamic Planning

Member Code - A unique identifier for the dimension member. You cannot have duplicate member codes.

Member Parent - Represents the name of the parent member in the dimension hierarchy that the member will roll up to. For example, Time might be the dimension, 2020 might be the parent member, and Q12020 a member that rolls up to the 2020 parent member.

Rollup Operator - Based on aggregation and reporting needs. For example, Q12014 rolls up to the 2014 parent member. Select a Rollup Operator for each dimension member.

• The value of the member is not aggregated.
• The value of the member is added to the aggregate value of the preceding sibling member(s).
• The value of the member is subtracted from the value of the preceding sibling member(s).
• The value does not include the child member value in the consolidation to its parent and it does not roll up to any dimensions.

Member Type - Hidden - hides the dimension member from display on the Analyze, Data page. Hidden members will not be removed from the model but they will be removed from the display based on view settings.

How to Define a Dimension Members for a Model?

1. In SpotlightXL, navigate to the Model > Dimension.
2. The model last saved on the Setup page is populated in the Model cell. If you want to work with a different model, select it from the Model List box.
   
3. The first dimension defined on the Setup page is displayed, enter dimension data for that dimension or select another from the Dimension list box.
   
4. Add, edit, or delete dimension data. If you are working with a new model, there will be no members defined.
5. Enter Member Code, Member Parent, and Rollup Operator data. These 3 data items are present in the standard data structure.
6. For Member Type, select Hidden to hide the dimension member from display on the Analyze >Data page. Hidden members will not be removed from the model but they will be removed from the display based on view settings.
   
7. After you have defined dimensions, return to the Setup page and generate your model.

How to Add a Dimension Member to a Generated Model?

This task applies to either locked or unlocked models.

1. In SpotlightXL, navigate to the Model >Dimension.
2. Select a dimension that you want to add a member(s) to.
3. Select Append from the new Action field shown below.
   
   
   
4. Once you select Append, a blank table is displayed as shown below.
   
   
   
5. Enter new dimension members in the table.
6. Click Save to add new dimension member(s) to the model.
7. To view the new dimension member(s) select View from the Action drop-down.

How to Delete a Dimension Member from a Generated and Locked Model?

You can delete dimension members and associated data from a locked model without clearing and regenerating the model. For example, if you have a Revenue Forecast model and you want to remove an old product, you can do so without clearing or regenerating your model.

This option is available only in SpotlightXL.

1. In SpotlightXL, select Model > Setup.
2. Select the model from the Model drop-down.
3. Lock the model.
   Note:

   The model is still generated but is now locked from data changes by users.
4. Select Model > Dimension.
5. Select the dimension that you want to delete the member from. Make a note of the correct spelling of the Member Code and Member Parent.
   
   
   
6. Select Delete from the Action field.     
   • Type the Member Code and Member Parent.
     
     
     
   • Click Save.
   • A message appears and asks you to confirm, Deleting dimension members will clear all the rollup and deleted leaf member's data. Would you like to continue?
   • Click Yes to confirm.
   • A message appears and informs you that the operation has been submitted for execution. Notification will be sent via email once the process is complete.
   • The data for all associated rollup members is deleted with this operation. You need to unlock the model and then run the necessary calculations to obtain the new rollup data.
   • Select Model > Model Administration > Validation.
   • Select the model from the Model drop-down.
   • Review the status of the validation report to determine if any model artifacts need to be updated. You may have reports, views, calculations, formulas, or maps that refer to the deleted member and that need to be updated. Repeat the validation until you have made corrections to all the artifacts.

See Notes about Restructuring.

How to Lock Dimension Members

This task is for Contributors and Power Users.

You can lock specific members or combinations of members in a model to prevent modification. This is useful when you want to allow data input for a specific segment of data only. For example, lock Actual data from modification, but allow input for all Budget data.
Data Locking is a two-step process:

• Set up the data locking definitions.
• Turn on or off all data locking in the application on the Manage, Application Administration, Application Settings page. This step must be done by a Power User.

How to Setup Data Locking?

1. In SpotlightXL, navigate to the Model >Model Administration > Data Locking.
2. Select the model that you want to lock. You will see the dimensions listed as column headings.
3. Use the drop-down under each dimension column to select the member(s) that you want to lock. If you select a parent-level member, all leaf level members under that parent will be locked, but the parent-level members will not be locked.
4. Click Save.

The following example locks all data in member combinations that include any leaf members in the Month-1 - PY-1 hierarchy in the Time dimension.

The following example locks all data in member combinations that include any leaf members in the Month-1 - PY-1 hierarchy in the Time dimension and locks all data in member combinations that include Actual in the Scenario dimension.

The following example locks all data in member combinations that include any leaf members in the Month-1 - PY-1 hierarchy in the Time dimension and Actual in the Scenario dimension.

How to Turn Data Locking On or Off?

1. In SpotlightXL, navigate to the Manage > Application Administration > Application Settings.
2. Under the View Properties section, select Application Level or None for Dimension Based Data Locking for Views.
3. Under the Report Properties section, select Application Level or None for Dimension Based Data Locking for Reports.
4. Under the Model Properties section, select Application Level or None for Dimension Based Data Locking for Models.
5. Click Save.
   

You must logoff and login again for the settings to take effect.

Data that is locked appears with a gray background in views and reports.

How to Define Multiple Number Formats for Dimension Members in Views?

This task is available for Designers and Administrators.

To define multiple number formats for dimension members in Views:

1. Specify the number format for each dimension member by doing the following:
   1. In SpotlightXL, navigate to the Model > Dimension.
   2. Select the model from the Model list box and the dimension that contains the members you want to define number formats for from the Dimension list box.
   3. Select the Set Dimension Based Properties action. 
   4. Select the required Number Format.
      
      
      
2. Click Save after defining number formats for each dimension.
3. Update the properties at the View level so that the number formats defined at the dimension level are displayed as needed.
   
   
   
   1. Navigate to the Analyze >Design View.
   2. Select the view.
   3. Click the Properties action.
   4. Select the dimension from the Dimension based 'Number Format' field you applied number format to on the Model Dimension page.
   5. Click Save.
   6. Select Analyze > Data to see the results of your changes.

Customizing the Display Label

Now you are ready to use the Analyze and Report features to set up views and reports for your users.

Setting a Display Label

You can customize the way that members appear in views and reports using Display Labels. In Structured Planning, Consolidation, and Reporting applications, data is described with a Code and a Name. In Dynamic Planning, the Code becomes the member name by default.

When designing the view or report, using the Properties option, you can use a Display Label instead. By default, Display Label uses the Structured Planning, Consolidation, and Reporting applications definition of Code – Name.

Here is the result:

Model > Dimension

If you want to create a custom label for any member:

1. In SpotlightXL, go to Model > Dimension, and select the dimension you want to customize.
   
   
   
2. Under Action, select Set Display Label.
   
   
   
3. Then look for the member(s) that you want to change.
   
   
   
4. Click Save.

When you next open your View (Analyze > Data), the custom Display Label appears.

How to Customize the Display Label in SpotlightXL?

1. In SpotlightXL, access the Model >Dimension.
2. Select the dimension you want to have a customized display label.
3. In the Action list box, select the Set Display Label option as shown below.
   
   
   
4. The Member Display Label column is enabled. Select the dimension member you want to customize.
5. Enter the new dimension member label/name and click Save. Now, set display settings to Display Label for reports and views.

If you clear the model, the customized display labels are preserved. If you copy a view or report from an existing view or report, the target view or report will inherit the display property of the source view or report.

Display Labels in POV, Drill Through, and Search Functionality

Search functionality supports Display Labels as does drill through and Point of View (POV). In the example below, the report has the Display Label feature enabled, so the POV dropdown contains the display labels.

Display Labels and Lookups

Models sourced from Structured Planning, Consolidation, or Reporting applications with lookups do not need to use the Display Label feature. It is best to use one or the other and not both. If both features are used simultaneously, the default display name will have a lookup in place of the code. For example, let’s assume a dimension member has code “code”, name “name”, and lookup “lookup”. The default display label will be “lookup – name” instead of “code – name”.

Notes about Restructuring

Whenever you modify a hierarchy, including adding, deleting, or moving members or sub-hierarchies, Dynamic Planning performs a restructuring operation. Depending on the model size and configuration, the operation can be a lengthy process (4 or more hours). We have optimized the restructuring process to be significantly faster when modifying the hierarchy of large dimensions and models. It is optimized to be completed in less than 4 hours.

There are no changes to how you update the hierarchy. If the model is generated and you want to update the hierarchy, then simply lock the model and update the hierarchy as you normally would. Performance improvements are applied by default and there is no additional configuration needed.