MENU
    Defining the External Source Model
    • 1 Minute to read
    • Dark
    • PDF

    Defining the External Source Model

    • Dark
    • PDF

    Article summary

    1. To define the source model, go to Model > External Source Model > Source Model.

    2. Give the Source Model a name, such as Opportunity Data.

    3. Fill in the fields and formulas as listed below.

    4. Click Save.

    Here are all the fields to create in the External Source Model. The first 7 fields match the 7 columns in the source data.

    Property

    Value

     

     

     

    Source Model

    Opportunity Data

     

     

     

     

     

     

     

     

    Field

    Type

    Format

    Expression

    Include in Data Load

    Opportunity Name

    Text

     

     

    Yes

    Sales Segment

    Text

     

     

    Yes

    Opportunity Owner

    Text

     

     

    Yes

    Lead Source

    Text

     

     

    Yes

    Term

    Numeric

     

     

    Yes

    ARR

    Numeric

     

     

    Yes

    Service Start

    Date

    MM/DD/YYYY

     

    Yes

    All remaining fields are calculated or preset with constants. In all these fields, Include in Data Load is No. Explanation of the Lookup and Round, If, and DaysFactor functions are provided below.  

    Field

    Type

    Format

    Expression

    Include in Data Load

    Service End

    Formula

    MM/DD/YYYY

    LOOKUP("Opportunity - Daily Calendar" , "Service End" , {Service Start},{Term})

    No

    PlanningStart

    ConstantDate

    MM/DD/YYYY

    01/01/2018

    No

    PlanningEnd

    ConstantDate

    MM/DD/YYYY

    12/31/2019

    No

    Jan-18

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Jan-2018")>0.5,[ARR]/12,0),0)

    No

    Feb-18

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Feb-2018")>0.5,[ARR]/12,0),0)

    No

    Mar-18

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Mar-2018")>0.5,[ARR]/12,0),0)

    No

    Apr-18

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Apr-2018")>0.5,[ARR]/12,0),0)

    No

    May-18

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"May-2018")>0.5,[ARR]/12,0),0)

    No

    Jun-18

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Jun-2018")>0.5,[ARR]/12,0),0)

    No

    Jul-18

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Jul-2018")>0.5,[ARR]/12,0),0)

    No

    Aug-18

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Aug-2018")>0.5,[ARR]/12,0),0)

    No

    Sep-18

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Sep-2018")>0.5,[ARR]/12,0),0)

    No

    Oct-18

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Oct-2018")>0.5,[ARR]/12,0),0)

    No

    Nov-18

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Nov-2018")>0.5,[ARR]/12,0),0)

    No

    Dec-18

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Dec-2018")>0.5,[ARR]/12,0),0)

    No

    Jan-19

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Jan-2019")>0.5,[ARR]/12,0),0)

    No

    Feb-19

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Feb-2019")>0.5,[ARR]/12,0),0)

    No

    Mar-19

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Mar-2019")>0.5,[ARR]/12,0),0)

    No

    Apr-19

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Apr-2019")>0.5,[ARR]/12,0),0)

    No

    May-19

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"May-2019")>0.5,[ARR]/12,0),0)

    No

    Jun-19

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Jun-2019")>0.5,[ARR]/12,0),0)

    No

    Jul-19

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Jul-2019")>0.5,[ARR]/12,0),0)

    No

    Aug-19

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Aug-2019")>0.5,[ARR]/12,0),0)

    No

    Sep-19

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Sep-2019")>0.5,[ARR]/12,0),0)

    No

    Oct-19

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Oct-2019")>0.5,[ARR]/12,0),0)

    No

    Nov-19

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Nov-2019")>0.5,[ARR]/12,0),0)

    No

    Dec-19

    Formula

     

    ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Dec-2019")>0.5,[ARR]/12,0),0)

    No

    Scenario

    ConstantText

     

    Forecast

    No

    Account - Sub Revenue

    ConstantText

     

    Subscription Revenue

    No

    The resulting Source Model appears as follows:
    ModelingImagesEDSMOppLoad31.png

    Note:
    Here is a look at the same Source Model from the Web interface, Model Manager.

    2020uiy.png

    202087k.png


    Was this article helpful?