External Source Model Process

Using SpotlightXL or Model Manager, you can set up and work with external data sources. The following process shows 5 steps in the basic flow to working with external data.

To walk through the basic steps using SpotlightXL, see How to Create and Load External Source Models.

To learn how to transform data using expressions and formulas, see External Source Model Field Types, Expressions, and Formulas.

How to Create an External Source Model 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.

Power and Contributor users can create an External Source Model from the Model Manager Grid or List layouts. Reviewer users cannot create an ESM model.

1. In Spotlight Web, navigate to the Dynamic Planning > Model.
2. Click the + Circle icon from the Grid or List layouts to create a new model.
   
   
   
3. Click the tabular icon to indicate that you want to create an External Source Model.
   
   
   
4. Give the new model a name and an optional description.
   
5. Click the Save icon.
6. Proceed with adding fields. See Creating New ESM Fields Using Model Manager.
7. Click the Save icon again when done.

How to Create Fields in the External Source Model Using Model Manager?

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.

1. Login to Spotlight Web.
2. Select Model.
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.
   
   
   
   1. 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.
   2. Label: Enter the 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.
   3. Format: For Date and ConstantDate types, select the layout that the date or Expression is formatted in.
   4. 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.
   5. DataLoad: 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. Information on the options available in the Create Field box is provided below.
8. When you are finished, click Done.
   Now you see the fields you added to 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.
9. When done making all changes to fields, click the Save icon.
   

How to Create Formulas in the External Source Model Using Model Manager?

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

1. Login to Spotlight Web.
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.

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

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 icon.