How to Create and Load External Source Models

Data can be loaded into SpotlightXL from an Excel worksheet, CSV file, TXT file using copy-paste actions (Ctrl+C or Ctrl+V), or integration tools like Boomi.

Define a Master Model with Dimensions: 

1. In SpotlightXL, go to the Model > Setup to define a Master model with required dimensions.
   
   
   
2. Go to Model > Dimension subtask to load metadata for all dimensions.
   
   
3. Once dimensions are defined, click Save and Generate Model. For details on the field types, expressions, and formulas, see Source Model Field Types, Expressions, and Formulas.

Define a Source Model:

1. Create a Source Model to facilitate data transfer from external sources to the Master Model.
2. Navigate to the Model task and External Source Model > Source Model.
3. Enter fields and specify their types based on the order and type in the external data source. 
4. Click Save.
   
   
   

Define the Source Map:

1. Connect the Source Model to the external Source Data using the Model task and External Source Model > Source Map.
2. Select Data in the Type field.
3. Map each field from the Source Model to a Dimension Member or Value in the Master Model.
4. Click Save.
   

Load Data into the Source Model:

1. Use the Model task and External Source Model > Source Data.
2. Specify the Source Model name and ensure the page displays current status and column headings.
   
   
   
3. Select Load Data drop-down and select Load File or copy-paste rows directly from an excel worksheet.
   
   
   
4. After loading, click Load Data and wait for the process to complete. Click Refresh to update derived fields.
   
   

Create Calculation for Data Transfer:

1. In the Model task, navigate to the Calculation subtask.
2. Specify ExternalSourceMap type and the Source Map name defined earlier.
3. Add Aggregation to ensure rollups are calculated correctly.
4. Click Run to execute the calculation and transfer data from the Source Model to the Master Model.
   
   

Review Data in Master Model:

1. Create views or reports using the Analyze > Data to visualize data now present in your Master Model.
2. Optionally use Drill Through on any data cell to access original transaction details.