Source Model

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 and fields describing that data.
• Your external data source may contain many 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.

The Source Model is used as an intermediary step to get data from your external data source into a Master Model.

Options Available on the Source Model Page

Information on the options available on the Source Model page is provided below.

Source Model - Enter a name for the source model.

Field - Enter the names of the fields. 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.

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.

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 Formula types, enter the formula to apply to the data. This can be a simple calculation or a function. See the Formulas below. 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.

Include in Data Load - This column is filled in by the system. 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. 

You can update an ESM by adding new fields and deleting unwanted fields AFTER the data is loaded and the map(s) and view(s) are defined. 

How to Add a Field to an ESM in SpotlightXL?

1. Let’s say you set up an ESM definition in SpotlightXL (on the Source Model page accessed by navigating to Model > External Source Model > Source Model.
   
   
   
2. Now, let’s say you want to add a field called Status to the ESM Source definition as shown below.
3. Navigate to Model > External Source Model > Source Model and select the ESM you want to add the field.
4. Enter the new field and select the Type as well as whether or not it will be included in the data load. There is no need to delete maps and views to perform this step. The map and views associated remain unchanged.
   
   
   
5. Save and navigate to the Source Map page (Model > External Source Model > Source Map).
6. Select the additional field and complete the Maps To, Target Dimension, and Target Member fields. Then, Save.
7. Navigate to the Model > External Source Model > Source Data page and the field will be available to load data against.

How to Delete an ESM Field in SpotlightXL?

1. In SpotlightXL, go to the Source Map page (Model > External Source Model > Source Map) and delete the field from the map.
2. In SpotlightXL, go to the Source Model page (Model > External Source Model > Source Model) delete the field from the source model definition and save.

In addition to Refresh, Save, and Delete, this page also provides the option to Clear Model. Clear Model removes data from the Source Model. This option is typically used after you have created the Source Map and loaded data using the Source Data subtask, while you are testing your expressions and formulas, or after you have loaded data, verified it, and are ready to move on to the next batch of data.