Using the Model Artifacts Browser

Overview

The Model Artifacts Browser is a dynamic viewer window where you can see all of the artifacts associated with a model in one place. It is easy to click between different artifacts to quickly open and view them, or to compare to the artifacts associated with other models.

Opening the Model Artifacts Browser

To open the Model Artifacts Browser, do one of the following:

• Select Model Artifacts from the More menu, or

• Click on a Map, Calculation, Formula, or Scope artifact inside a Model box

When you click on an artifact, the last 5 items used are shown in a list box. Click one to open it, or click More... to see all artifacts of that type.

The Model Artifacts Browser has the same look and feel whether the model is Generated and Unlocked, Generated and Locked, or Ungenerated. However, some operations, like a calculation, cannot be performed on a Locked or Ungenerated model.

The left side of the Model Artifacts Browser has the list of artifacts. The right side contains details of the artifact selected.

Icons are available to perform actions on the artifact, such as Refresh, Filter, Breakback, Variable Manager and Run.

You can click on a column title to sort the list of artifacts by that column.

Ways to View Model Artifacts

Model Artifacts are available for Power and Contributor users. Reviewer users can see only Views (Analyze) and Reports.

You can view a model's artifacts several different ways.

From the Grid layout, inside a Model box:

From the List layout:

From the More menu, click to open the Model Artifacts Browser:

Running and Filtering Views

This option is available for all users.

In Practice: Running a View from the Model Manager

1. From a Model Box, click the Views artifact.

2. Select the view you want to open.

   

In Practice: Running a View from the Model Artifacts Browser:

1. From a Model Box or Model List layout, select the More menu, then select Model Artifacts.

2. Click Views.

3. Select the view you want to open, then click the Run icon.

   

In Practice: Filter or Search for a View:

1. From a Model Box or Model List layout, select the More menu, then select Model Artifacts.

2. Click Views.

3. Click the Filter icon.

4. Type a few characters in the view name. The Model Artifacts Browser dynamically refreshes to show you which views have those characters in their name.

   

Running and Filtering Reports

This option is available for all users.

In Practice: Running a Report from the Model Manager

1. From a Model Box, click the Reports artifact.

2. Select the report you want to open.

   

In Practice: Running a Report from the Model Artifacts Browser

1. From a Model Box or Model List layout, select the More menu, then select Model Artifacts.

2. Click Reports.

3. Select the report you want to open, then click the Run icon.

   

   

In Practice: Filter or Search for a Report:

1. From a Model Box or Model List layout, select the More menu, then select Model Artifacts.

2. Click Reports.

3. Click the Filter icon.

4. Type a few characters in the report name. The Model Artifacts Browser dynamically refreshes to show you which reports have those characters in their name.

   

Using the Dimension Browser for Hierarchy Management

This option is available for Power and Contributor users. Reviewer users cannot view or edit dimensions.

Dimension Browser for a Generated and Unlocked Model

Dimension Browser for a Generated and Locked Model

Dimension Browser for an Ungenerated Model

Dimension Locking Compared to Dimension Security

Click the dimension from the Model box or from the Model Artifacts Browser to open the Dimension Browser. The dimensions are listed on the left and the members appear on the right in the Member Pane.

Click the dimension name. The hierarchies and members in the Members Pane can be expanded and collapsed with an icon. The selected member's properties appear in the Member Properties panel.

There are three situations in which you might want to edit the dimensions in your model:

• Ungenerated New Model, No Data : you can add, edit, delete, or move members in the dimension hierarchies freely.

• Generated Existing Model, Loaded with Data, Model Unlocked : you can add new members but cannot edit or delete existing dimension members. If you append a new member, when you save, all rollup data for the new member's hierarchy will be deleted, so you must run an aggregation afterward.

• Generated Existing Model, Loaded with Data, Model Locked : you can add, edit, delete, or move members in the dimension hierarchies. When you save, all rollup data for the members changed will be deleted, so you must run an aggregation afterward.

The Dimension Browser has different options depending on whether the model is Generated and Unlocked, Generated and Locked, or Ungenerated.

Dimension Browser for a Generated and Unlocked Model

Icons that are available to perform actions include Refresh, Add Member, Save, and New Attribute.

On existing members, you can update the Display Label, Number Format, and Locked status.

You can add new members and set member properties. If you add a new member, when you save, all rollup data for the new member's hierarchy will be deleted, so you must run an aggregation afterward.

Dimension Browser for a Generated and Locked Model

Icons that are available to perform actions include Refresh, Add Member, Delete Member, Save, and New Attribute.

On existing members, you can update the Display Label, Number Format, and Rollup Operator. You can also move the member to a different parent by dragging and dropping it.

You can add new members and set member properties.

You can select a member and delete it.

If you have changed rollup operators, added, deleted, or moved members, then when you save, all rollup data for the members changed will be deleted, so you must run an aggregation afterward.

When you click Save, a message tells you that your changes have been submitted for execution. You will receive an email once the process is completed. While the change is being processed, you cannot make other changes.

Dimension Browser for an Ungenerated Model

Icons that are available to perform actions include Refresh, Add Member, Delete Member, Save, and New Attribute.

On existing members, you can update the Display Label, Number Format, and Rollup Operator. You can also move the member to a different parent by dragging and dropping it.

You can add new members and set member properties.

You can select a member and delete it.

When you click Save, your changes are saved immediately. Because there is no data in the model, there is no delay in saving the changes.

Dimension Locking Compared to Dimension Security

The Dimension Browser offers the ability to lock dimension members. This option prevents the user from making changes to data for that member in Views (or applications) that have enabled Dimension-Based Locking. The user can see the data for that member but cannot change it.

The Dimension Browser does not offer the ability to prevent users from seeing data for a dimension member. To restrict user access, a Power user must define security access to certain dimensions, hierarchies, or members using dimension security. This is available in SpotlightXL. See Manage, Application Administration, Model Permissions.

Using the Dimension Attribute Browser

This option is available for Power and Contributor users. Reviewer users cannot view or edit attributes.

Dimension Attribute Browser for a Generated and Unlocked Model

Dimension Attribute Browser for a Generated and Locked Model

Dimension Attribute Browser for an Ungenerated Model

Adding Attributes

Mapping Attributes to Dimension Members

The Dimension Attribute Browser uses the same interface as the Dimension Browser and has the same icons. Attributes are indented under the dimension that they are associated with.

Click the attribute from the Model box or from the Model Artifacts Browser to open the Dimension Browser. The dimensions are listed on the left, attributes indented under them, and the members appear on the right in the Member Pane.

Click the attribute name. The hierarchies and members in the Members Pane can be expanded and collapsed with an icon. The selected member's properties appear in the Member Properties panel.

Dimension Attribute Browser for a Generated and Unlocked Model

Icons that are available to perform actions include Refresh, Add Member, Delete Member, Save, and New Attribute.

On existing attribute members, you can update the Display Label. You can also move the attribute member to a different parent by dragging and dropping it.

You can add new members to an attribute hierarchy.

You can select an attribute member and delete it.

Dimension Attribute Browser for a Generated and Locked Model

Icons that are available to perform actions include Refresh, Add Member, Delete Member, Save, and New Attribute.

On existing attribute members, you can update the Display Label. You can also move the attribute member to a different parent by dragging and dropping it.

You can add new members to an attribute hierarchy.

You can select an attribute member and delete it.

If you have made changes only to attribute members, then when you click Save, a message tells you that your changes have been saved.

Dimension Attribute Browser for an Ungenerated Model

Icons that are available to perform actions include Refresh, Add Member, Delete Member, Save, and New Attribute.

On existing attribute members, you can update the Display Label. You can also move the attribute member to a different parent by dragging and dropping it.

You can add new attribute members to an attribute hierarchy.

You can select an attribute member and delete it.

When you click Save, your changes are saved immediately. Because there is no data in the model, there is no delay in saving the changes.

Adding Attributes

Power and Contributor users can add new attributes and attribute members to any model.

In Practice: Adding a New Attribute to Your Model:

1. From a Model Box, click the Dimensions or Attributes artifact. Select a dimension to open the Dimension Attribute Browser.

2. Select the dimension that the attribute will be associated with.

3. Click +New Attribute.

4. Type the name of the new attribute.

5. If the attribute you added will have children, then toggle Enable Dimension Leaf Members on. This makes it possible for the attribute and its members to be visible in views and reports.

   

6. Click the Save icon.

7. Select the Attribute that now appears under the dimension. You see that the attribute was added but it has no members yet.

8. Click the + icon to add members.

9. Type the name of the new attribute member.

10. Click Add.

    

11. Continue until you have added all the attribute members.

12. Optionally click each attribute member and customize the Display Label for each.

13. Click Save.

    

    The attributes are now ready to be mapped to dimension members.

Mapping Attributes to Dimension Members

Once you have created a new attribute and added members to it, you are ready to map the attributes to your dimensions.

In Practice: Mapping Attributes to Dimension Members

1. From a Model Box, click the Dimensions or Attributes artifact. Select a dimension to open the Dimension Attribute Browser.

2. Select the dimension that the attribute appears under.

3. Click a dimension member to map to an attribute.

4. In the Member Properties panel, see the Attributes Mapping section.

5. Click the down arrow icon to display a list box of attribute members.

6. Click the attribute member to map to the dimension member.

   

7. Continue until you have mapped the desired dimension members to attributes.

8. Click Save.

Running and Filtering Calculations

This option is available for Power and Contributor users. Reviewer users cannot view calculations.

In Practice: Running a Calculation from the Model Manager

1. From a Model Box, click the Calculations artifact.

2. Select the calculation you want to open.

   

In Practice: Running a Calculation from the Model Artifacts Browser

1. From a Model Box or Model List layout, select the More menu, then select Model Artifacts.

2. Click Calculations.

3. Select the calculation you want to open, then click the Run icon.

   

In Practice: Filter or Search for a Calculation

1. From a Model Box or Model List layout, select the More menu, then select Model Artifacts.

2. Click Calculations.

3. Click the Filter icon.

4. Type a few characters in the calculation name. The Model Artifacts Browser dynamically refreshes to show you which calculations have those characters in their name.

   

Viewing or Changing the Value of a Variable in a Calculation

The Model Manager provides the ability to view and set variables in calculations using the Variable Manager. This functionality is also available in SpotlightXL.

You can view the current value for the variable and optionally specify a new value. This is useful for organizations that want to set up a calculation and run it regularly, using a variable to specify what is different each time you run it. For example, the calculation may run an aggregation for a department, or for just a particular year, where the department or year are variables.

Consider the following example:

• Everest Cycles is a model that contains financial data for an international company that sells bicycles.

• The model needs to be aggregated quarterly.

• Using SpotlightXL, you create a Scope for the model that defines a variable called @LatestQuarter@, based on the Time dimension.

• You create a Calculation that runs an aggregation for the scope of time specified in the variable @LatestQuarter@. The initial value of the variable is Q1 2014.

In Model Manager, the variable used in the calculation "Agg Latest Quarter" can be modified at runtime so that the calculation aggregates whichever quarter the user choose.

In Practice

1. Login to Spotlight.

2. Select Model from the task menu.

3. Select the Calculations artifact from the Model box and pick one from the list to open the Model Artifacts Browser.

   

4. The Model Artifacts Browser lists the calculations. Included is an icon, which opens the Variable Manager.

5. Click the Variable Manager icon to open the Variable Manager screen.

   The icons along the top are:

   • Return to Model Artifacts.

   • Refresh to the last saved version.

   • Save changes to the calculation.

   • Save changes to the calculation and Run it.

   • Run the calculation.

   • The Calculation Name is provided along with a drop-down if you would like to switch to viewing a different calculation.

   
   

   A Calculation Step is provided so that you can step through complex calculations with multiple variables that need to be set. The current step and its variable(s) are listed. For information on how calculation steps correspond to the rows in a calculation, see Calculation Steps in Model Manager.

6. Click the open box icon to select a new value for the variable.

7. In the dimension browser pane on the right, search or click through to find the quarter that you want to calculate.

   

8. Click Select Member. The selected member then appears under the Value column.
   
   

9. Click Save or Save and Run. A message appears to confirm your selection.

10. Click to Return to Model Artifacts when done.

Calculation Steps in Model Manager

In Model Manager, you can set the value of variables used in calculations.

Select the calculation and click the Variables Manager icon to open the Variable Manager screen.

Under Calculation Step, you see the list of All variables used in the calculation. These steps correspond to rows in the calculation in SpotlightXL that include variables. By default, you see All variables that are used in all steps of the calculation. You can set them all at once, or set them step by step.

The Variable Manager screen does not show you all the rows in the calculation, only those that include variables. For example, the full calculation for the Detailed Revenue calculation above includes several rows that do not use variables. Only the 6 rows that include variables are included in the Variable Manager.

You can use the drop-down to select different steps in the calculation and set variable values for each. Or you can select All and set all variable values at once.

In Practice: Making Changes to Variables

1. Select a step in the calculation.

2. Select a variable.

3. Select the dimension member you want to use in place of the variable.

4. Click Select Member.

   

5. Continue by changing variables in as many steps as you want.

6. Click Save or Save and Run when done.

   To assign a substitution variable to a variable :

   You can also use a substitution variable to change the value for a variable instead of using Variable Manager. You can assign the substitution variable to the variable once in the calculation, and then you only need to change the value of the substitution variable going forward.

In Practice

1. Select a step in the calculation.

2. Select a variable.

3. Select the substitution variable you want to use in place of the variable. (The substitution variable must be predefined.)

4. Click Select Member.

   

5. Click Save or Save and Run when done.

Editing a Breakback in a Calculation

You can review and update Breakback steps in a calculation using Model Manager in Spotlight. We recommend that you use SpotlightXL to create the Breakback step in the calculation, but after that, you can edit the Breakback on the web.

In Practice: Viewing the Breakback Steps in a Calculation in Spotlight

1. Login to Spotlight and select Model from the task menu.

2. Select the model that has the Breakback operation in a calculation, then click on that calculation. If you are not sure which calculations include Breakback, then open the Artifacts Browser.

3. In the Model Artifacts Browser, as you select different calculations in the list, those that include a Breakback operation will show a Breakback icon on the icon bar.

   

4. Click the Breakback icon. The Model Artifacts Browser expands to display the Breakback steps under "Edit Breakback for Calculation Step."

   

   If the calculation includes more than one Breakback operation, you can toggle between them by selecting the step using the drop-down.

   

   As a reminder, Breakback operations include several pieces of information, all of which you can view or edit in the Breakback pane. The list below shows the different elements of Breakback.

• Model name

• Parent-level data intersection from which to allocate data to children

• Spread method

• One or more parent-level dimension members, if using the Reference Spread method

• Type of change to make: amount or percentage increase/decrease

• Value of the change to make: an inputted value or by looking up a reference to a number in a member intersection

• Hold members (child members that will be skipped in the Breakback)

After you make changes to the Breakback operation:

• Click Save

• Or click Save and Run

Using Substitution Variables in Breakback Operations from a Calculation

You can use substitution variables in place of member names in Breakback operations within a calculation.

This feature is supported both in SpotlightXL and Spotlight.

When defining Breakback operations in a calculation, you can use the member select icon to select a Substitution Variable instead of a fixed member name in the Breakback Cell:

You can use Substitution Variables in the Reference Spread also:

You can use Substitution Variables in the Hold section also:

Using the Calculation Variables Manager #SKIP# Option

Power and Contributor can use the SKIP option for variables in a calculation. This functionality is available from both Model Manager and SpotlightXL.

Use the SKIP option when you have a Map or Formula that uses variables (usually multiple variables). You can build one master map or formula to accommodate data loading or formulas for a whole series of data intersections. Then when the calculation is run that executes the formula or map, you can skip those data intersections that do not need to be loaded or calculated.

Consider the following formula with two variables and the calculation that runs it. We will set the calculation to skip the @NextQtr@ variable.

In Practice

1. Login to Model Manager.

2. Select the model and calculation that uses the formula or map variable(s).

3. Click the pencil icon to open the Variables Manager.

4. Select the variable that you want to skip, and click the Skip option under the member selection box.

   

   After you click Skip, you see #SKIP# as the Value of the variable. Select a value for other variables at this time also.

   

5. Click Save when you are done with your changes.

   This calculation will now calculate Sales as ASP * Units for the member intersections defined in the formula. In the Time dimension, only Q3 2018 will be calculated.

Filtering Formulas

This option is available for Power and Contributor users. Reviewer users cannot view formulas.

In Practice: Filter or Search for a Formula

1. From a Model Box or Model List layout, select the More menu, then select Model Artifacts.

2. Click Formulas.

3. Click the Filter icon.

4. Type a few characters in the formula name. The Model Artifacts Browser dynamically refreshes to show you which formulas have those characters in their name.

   

Filtering Scopes

This option is available for Power and Contributor users. Reviewer users cannot view scopes.

In Practice: Filter or Search for a Scope

1. From a Model Box or Model List layout, select the More menu, then select Model Artifacts.

2. Click Scopes.

3. Click the Filter icon.

4. Type a few characters in the scope name. The Model Artifacts Browser dynamically refreshes to show you which scopes have those characters in their name.

   

Creating, Editing, and Filtering Maps

This option is available for Power and Contributor users. Reviewer users cannot view maps.

In Practice: Filter or Search for a Map

1. From a Model Box or Model List layout, select the More menu, then select Model Artifacts.

2. Click Maps.

3. Click the Filter icon.

4. Type a few characters in the map name. The Model Artifacts Browser dynamically refreshes to show you which maps have those characters in their name.

   

   The Model Manager lets you view and edit existing Maps. The Map Manager also includes helpful Member Selection boxes so you do not have to remember or re-key member names.

   For details about creating a Map from scratch, see Creating a Model Map.

Viewing an Existing Map

In Practice

1. Login to Spotlight.

2. Select the Model task.

3. Select the Model Relationship layout.

4. Scroll to find the model and map you want to view.

5. Select Maps, then pick a map from the list, to open the Artifacts Browser.

6. Click the pencil icon to open the map.

Editing an Existing Map

In Practice

1. Open the map using the pencil icon. The Mappings screen appears.

2. The left pane of the screen displays the following basic information about the map:

   • Map name

   • Map description

   • Target model

   • Source model

   • Map type (Data, Metadata, or Both)

   • Transfer type (All data, or Leaf data)

   • Write Back ID (used only when data is flowing back to the original data source)

   • Access Token (used only when automating data integration with APIs)

     

3. The right pane of the screen displays the mapping details (along with their equivalents in SpotlightXL):

   • Source Dimension

   • Member (Source Value)

   • Filter (Source Filter)

   • Target Dimension

   • Member (Target Value)

   • Filter (Target Filter)

   • Match Criteria

4. Use the drop-down arrows to select different options.

5. Use the open icon to view the Member Selection box.

6. To delete a row, click the checkbox next to the row and click the trashcan icon.

7. To discard your changes, click the Refresh icon.

8. To add a new row, click the + icon.

   Note:

   You can expand the Mappings pane by clicking the left arrow between the two panes. It looks slightly different depending on your browser.

   

9. When done with your changes, click Save.

   

   For details about using maps to load data from Planful Structured Planning, Consolidation, or Reporting applications, see Setting up the Target/Master Model using a Map.

Creating a New Map Using Model Manager

Model Maps provide the ability to move metadata and data from one place to another:

• To transfer data or metadata from Planful Structured Planning, Consolidation, or Reporting applications to a SpotlightXL model

• To transfer data from one SpotlightXL model to another

• To transfer data within a model

• To transfer data from a SpotlightXL model to Planful Structured Planning, Consolidation, or Reporting applications

In Practice

1. Open the Model Manager layout screen of your choice.

2. Click the More menu, then select Add, then Map.

   The Mappings screen appears.

3. The left pane of the screen lets you specify the following basic information about the map:

   • Map name (required)

   • Map description

   • Target model, populated with the name of the model you started from in the Model Manager layout (required)

   • Source model, select the drop-down to pick the model you are copying from (required)

   • Map type, default is Data (Data, Metadata, or Both) (required)

   • Transfer type, default is Leaf (All data, or Leaf data) (required)

   • Write Back ID (used only when data is flowing back to the original data source)

   • Access Token (used only when automating data integration with APIs)

4. The right pane of the screen is where you specify the mapping details:

   • Source Dimension

   • Member (Source Value)

   • Filter (Source Filter)

   • Target Dimension

   • Member (Target Value)

   • Filter (Target Filter)

   • Match Criteria

5. Use the drop-down arrows to select different options.

6. Use the open icon to view the Member Selection box.

7. To delete a row, click the checkbox next to the row and click the trashcan icon.

8. To discard your changes, click the Refresh icon.

9. To add a new row, click the + icon.

   Note:

   You can expand the Mappings pane by clicking the left arrow between the two panes. It looks slightly different depending on your browser.

10. When done with your changes, click Save.

How to Edit a Metadata Map for Direct Access to PCR

When a model with Direct Access to PCR is created or saved for the first time, a map is automatically created and has the same name as the model. The map defines which metadata will be contained in the model, and it is defined by default to download All Members for all dimensions. The map type is Metadata. The map can be viewed or edited by Power or Contributor users. Edit the map if your business use case requires a subset of the dimensionality available in PCR.

In Practice

1. Login to Spotlight.

2. Select the Model task.

3. Locate the model with direct access to PCR. The model will have a Locked icon next to it.

4. Locate the metadata map. It is in the Map list and has the same name as the model. Click the Map name.

   Tip

   If the metadata map does not exist, then check to be sure you did not rename any of the dimensions from their default names. When you first create the model and specify that it comes from Direct Access to PCR, the dimensions are populated automatically. As a best practice, do not rename any dimensions or else the metadata map is not created for you when you click Save.

5. Open the map using the pencil icon. The Mappings screen appears.

   The left pane of the screen displays basic information about the map.

   Note:

   You cannot modify the Map Type. The Map Type is Metadata. The Source Model is HACPM_Financial. The Transfer Type is Leaf.

   The right pane of the screen displays the mapping details.

   

6. Use the drop-down arrows to select different options.

7. Use the open icon to view the Member Selection box.

   

8. If you make changes on the left side for the Source dimension, make the same changes on the right side for the Target dimension

   

9. To discard your changes, click the Refresh icon.

10. When done with your changes, click Save.

    

Creating, Editing, and Filtering External Source Model Fields

This option is available for Power and Contributor users. Reviewer users cannot view fields.

In Practice: Filter or Search for a Field

1. From a Model Box or Model List layout, select the More menu, then select Model Artifacts.

2. Click Fields.

3. Click the Filter icon.

4. Type a few characters in the field name. The Model Artifacts Browser dynamically refreshes to show you which fields have those characters in their name.

Viewing Existing ESM Fields

In Practice

1. Login to Spotlight.

2. Select the Model task.

3. Scroll to find the External Source Model you want to view.

4. Click the gray text that says External Source Model.

   

   The list of existing fields appears.

5. Click a field to view its definition.

   

6. Click Cancel when done.

Editing Existing ESM Fields

Before you can edit existing Source Model Fields:

• You cannot modify existing fields if a Source Model Map exists because an existing Source Model Map contains mappings from the current list of fields.

• You cannot modify existing fields if the Source Model contains data. You must clear the model first.

In Practice

1. Login to Spotlight.

2. Select the Model task.

3. Scroll to find the External Source Model you want to view.

4. Click the gray text that says External Source Model.

   

   The list of existing fields appears.

5. Click a field to view its definition.

   

6. You can change the field name, its data type, its format (for dates), add an expression, and specify whether or not the field is coming from the data load file.

   

7. When done, click Done. The changes are not saved yet, but you can make changes to other fields before doing a final save on the list of fields.

8. When done making all changes to fields, click the Save button.

   For more details on expressions and formulas, see Creating New ESM Formulas Using Model Manager.

Creating New ESM Fields Using Model Manager

Define a Source Model with fields and types reflecting the structure of the external data source you are copying data from.

• Your external data source is assumed to be a two-dimensional list of transaction-style records where each row provides a piece of data along with fields that describe that data.

• Your external data source may contain a lot of fields that you do not need and you can indicate that they be ignored.

• Your external data source may contain information that is in a different format than you want, so you can transform that data using formulas and expressions.

Before creating new Source Model Fields:

• You cannot create new fields if a Source Model Map exists because an existing Source Model Map contains mappings from the existing list of fields.

• You cannot create new fields if the Source Model contains data. You must clear the model first.

In Practice

1. Login to Spotlight.

2. Select the Model task.

3. Scroll to find the External Source Model you want to view.

4. Click the gray text that says External Source Model.

   

   The list of existing fields, if any, appears.

   

5. Click ADD. The Create Fields box appears.

6. Click Add Fields. A new field is added with a default type Text. Under Label, enter a name for the field. Continue by adding additional fields.

   

   The table below provides information on the options available in the Create Field box.

   Type	Select Text, Numeric, Date, Formula, Constant Text, Constant Numeric, or Constant Date, based on the content and purpose of the column in your external data source.
   Label	Enter a name of the field. Fields will be used in the Source Map to identify data from the external data source (CSV, TXT, or other file) that will become dimensions and dimension members, or data. Best Practice: List the fields in the order that they appear in your external data source to make it easier to load the data.
   Format	For Date and ConstantDate types, select the layout that the date or Expression is formatted in.
   Expression For more details on Data Types, Expressions, and Formulas, see External Source Model Field Types, Expressions, and Formulas.	For ConstantText types, enter the text to place into this field. For ConstantDate types, enter the date to place into this field. For ConstantNumeric types, enter the value to place into this field.
   Data Load	Yes indicates that this field is coming directly from the data source, and No indicates that this field is derived from a formula or constant.

   If you make a mistake you can click the trashcan icon to delete a field.

   

   Note:

   When you add a Formula field, you are adding a placeholder. You will fill in the formula separately.

7. When you are finished, click Done.

   Now you see the fields you added in the list of fields. You can click each item to make changes to the fields.

   

   If you have added any fields of type Formula, you must fill in the formula before you can save all the fields. See Creating New ESM Formulas below.

8. When done making all changes to fields, click the Save button.

Creating, Editing, and Filtering External Source Model Formulas

This option is available for Power and Contributor users. Reviewer users cannot modify ESM formulas.

For a complete list of formulas available, see External Source Model Field Types, Expressions, and Formulas.

Viewing Existing ESM Formulas

In Practice

1. Login to Spotlight.

2. Select the Model task.

3. Scroll to find the External Source Model you want to view.

4. Click the gray text that says External Source Model.

   

   The list of existing fields appears. Scroll to find the fields with a formula associated with them.

5. Click a field to view its definition. The right pane refreshes to show the formula.

   

Editing Existing ESM Formulas

Before you can edit existing Source Model Fields with Formulas:

• You cannot modify existing fields if a Source Model Map exists because an existing Source Model Map contains mappings from the current list of fields.

• You cannot modify existing fields if the Source Model contains data. You must clear the model first.

In Practice

1. Login to Spotlight.

2. Select the Model task.

3. Scroll to find the External Source Model you want to view.

4. Click the gray text that says External Source Model.

   

   The list of existing fields appears. Scroll to find the fields with a formula associated with them.

5. Click a field to view its definition. The right pane refreshes to show the formula.

   

6. You can edit the existing formula by clicking inside of it and making changes. You can also select all the text, press Del, and then use the Functions and Operators drop-downs to create a new formula.

   

7. When done, click Set Formula. You can make changes to other fields and formulas before doing a final save on the list of fields.

8. When done making all changes to fields, click the Save button.

Creating New ESM Formulas using Model Manager

If your Source Model contains fields of type Formula, you can create the formulas using Model Manager's interactive formula builder.

In Practice

1. Login to Spotlight.

2. Select the Model task.

3. Scroll to find the External Source Model you want to view.

4. Click the gray text that says External Source Model.

   

   The list of existing fields appears.

   The right pane displays the formula builder options and editor. You can type in a formula where it says "Enter formula here..." or you can select from the drop-down list of functions and operators.

Example

In this example, we will create a unique Customer # by combining other fields together:

Customer # Preface - State - Phone Number

1. From the Functions list, select Concatenate. The basic syntax for this function is inserted into the editor.

   

2. To ensure that the formula will be assigned to the correct field, enter the field name before "Enter Formula."

   

3. Select the text Field 1 and press Del. Type @ to see a list of field names to insert. Select Customer # Preface.

   

4. Select the text Field 2 and replace it with the second field name to use: State.

5. Select the ... after Field 2 and replace it with the third field name to use: Phone Number. Clean up excess brackets as needed.

   Now the formula looks like this:

   

6. To add the dashes between each field in the formula, add a comma and " - " as follows:

   

7. Click Set Formula.

8. Click Saved Formulas to see the list of formulas created and saved so far.

   

9. When done making all changes to fields and formulas, click the Save button.

Filtering External Source Model Maps

This option is available for Power and Contributor users. Reviewer users cannot view maps.

In Practice: Filter or Search for a Map

1. From a Model Box or Model List layout, select the More menu, then select Model Artifacts.

2. Click Maps.

3. Click the Filter icon.

4. Type a few characters in the field name. The Model Artifacts Browser dynamically refreshes to show you which maps have those characters in their name.

Dynamic Planning: Variable Management

The Variable Management screen allows you to view and update multiple variables across multiple models. In the variable management screen, you can view and update Variables. In the Variable Management Landing screen, which is also the view screen, you can view all the variables defined in the application across different artifacts. You can navigate to specific artifacts by using the hyperlinks. You can view and update variables based on model and calculation combination in the update variable screen. You can update variables for one or more models on one or more calculations at a time.
You can either update each variable by adding the new value or perform a Bulk Variable update across one model for multiple calculations. You will have the ability to choose dimension member/substitution variables. The variable list will be generated based on the model and calculations selected. You can select a new value for the selected variable from the drop-down list. You cannot perform bulk operations on a variable that does not have any value assigned to it.
Variable Management screens would be accessible to all the power users,and contributors can view or update the variables from only those models that they can access.

In Practice: To view variables in the Variable Management Screen

1. Open SpotlightXL, go to your profile, and click Manage Your Account.
2. Select Subtask and scroll down to select Model Administration.
3. Select Variable Management in the Model Administration drop-down list.
4. In the Variable Management screen, the default value for the Action drop-down list is View. You can view all the variables on this screen.

In Practice: To update variables in the Variable Management Screen

1. Open SpotlightXL, go to your profile, and click Manage Your Account.
2. Select Subtask and scroll down to select Model Administration.
3. Select Variable Management in the Model Administration drop-down list.
4. Select a Model from the drop-down list.
5. Select a Calculation from the drop-down list.
6. Select a New value from the drop-down list to update variables individually.
7. Or Select the Bulk Update option from Actions in the toolbar to add a new value for selected variables across multiple calculations for a model.

To view Audit Log:

• Select Task > Manage > Subtask > Application Administration > Audit Log

The power users can see the audit log for the below user activities:

• Users navigating to variable management landing page (view)
• Users updating the variable value individually
• Users updating the variable values via bulk update
• Variable update failure

Best Practices 

Common Guidelines for Dimensions

Key type dimensions :

• Growing dimensions, such as Products, Projects, Customer, Companies

• Dimensions that need to be scoped, such as Scenario, Time, Measures

• Dimensions that might be used in a formula reference

Value type dimensions :

• Static dimensions in terms of size; they are not growing rapidly

• Dimensions with a large number of levels (if possible, it could be defined as Attribute)

• Departments, Regions dimensions

Size Guidelines

Key type dimensions :

• Growing dimensions, such as Products, Projects, Customer, Companies

• Dimensions that need to be scoped, such as Scenario, Time, Measures

• Dimensions that might be used in a formula reference

Value type dimensions :

• Static dimensions in terms of size; they are not growing rapidly

• Dimensions with a large number of levels (if possible, it could be defined as Attribute)

• Departments, Regions dimensions

Model Building: Tips

• If dimension names or types are incorrect after saving, you need to use "Modify Model" to make changes. You can either delete or modify the dimension type using this option.

• You must have at least one Key and one Value dimension defined

• The order of your dimensions in Model Setup will dictate how your dimensions are displayed in the Analyze task.

Full Refresh

• Adjust rollup operators to ‘’ in dimensions like Scenario, Time, Measures, Reporting as needed (one-time setup).

• Run refresh by each Scenario and Year combination rather than running all scenarios in one step.

• If you have QTD or YTD measures in your model, make sure you run a formula to get the expected results at the quarter level and year level after the Aggregation step.

Incremental Refresh

• Identify the data set that has changed, such as Actual for the current period and prior period.

• Only the changed data will be cleared and refreshed.

• Add any new chart-of-account members to your model as part of your steps, such as adding new accounts. System defined dimensions like Scenario and Time would require different steps.

• If you have QTD or YTD measures in your model, make sure you run a formula to get the expected results at the quarter level and year level after the Aggregation step.

Incremental Refresh – System-Defined Dimensions Process

In Practice: Adding a New Scenario to the Model:

1. Add the Scenario under Model, Dimension.

2. Run Metadata Download under Manage, Application Administration if you are missing the new scenario in the HACPM_Financial model under Model, Dimension.

3. Update the Data Map to include the new scenario and then run the incremental refresh process for the new scenario.

In Practice: Adding a New Fiscal Year to the Model:

1. Add the time member(s) for the new fiscal year under Model, Dimension. Use Append mode by copying the hierarchy from the HACPM_Financial model.

2. Update the Calculation to use the new periods and then run the incremental refresh process.

Incremental Refresh: Tips

• Set up the model refresh through the Scheduler to run overnight automatically.

• Run test model refreshes and adjust the model for optimal performance before moving the model to Production for users.

Model Restructure

Whenever you modify a hierarchy, including adding or moving members or sub-hierarchies, Dynamic Planning performs a restructure operation. Depending on the model size and configuration, the operation can be a lengthy process (4 or more hours). We have improved the restructuring operation 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 is no additional configuration needed. 

If the model is generated and you want to update the hierarchy, then simply lock the model and update the hierarchy. 

You can optionally select to enable a Data Storage Partitioning enhancement. It is an opt-in feature and will be enabled only for selective customers based on the use case. This data storage partitioning enhancement splits the internal model storage and stores leaf-level and rollup-level data separately, thus improving the performance of some operations, such as running a hierarchy update or running a map. Contact Planful Support if you want to opt-in to this feature.