I'm working for a business lender, and trying to come up with a basic dimensional model around the subject area of loan approvals.

We use a CRM, (Salesforce) and one of the core objects is a (Sales) "Opportunity". Sales usually start off as a lead, if we get traction on a lead, we will create an account, at least one contact, and an "Opportunity" which is a potential loan (our business calls it a "Deal"). All of the loan approval process happens under a particular "Opportunity". I know that Account and Contact would be good dimension candidates, I am unsure of Lead and Opportunity though.

I'm interested in opinions on which category Leads and Opportunities belong in: Fact or Dimension?

Here is an example of some opportunity fields that may be relevant to a data warehouse:

            OpportunitySK                       BIGINT        NOT NULL
                                                PRIMARY KEY CLUSTERED      
          , AccountId                           NCHAR(18)      NULL                              
          , ACTUAL_AMT_COLLECTED                DECIMAL(18,2)  NULL
          , ACTUAL_AMT_OFFERED                  DECIMAL(18,2)  NULL
          , ActualDailyPayment                  DECIMAL(18,2)  NULL
          , ACTUAL_MCA_TERM                     NVARCHAR(10)   NULL
          , ActualPaymentFrequency              NVARCHAR(255)  NULL
          , ApprovalAmount                      DECIMAL(18,2)  NULL
          , APPROVAL_DATE                       DATETIME       NULL
          , ApprovalExpirationDate              DATETIME       NULL
          , BrokerAccount                       NCHAR(18)      NULL
          , CreatedDate                         DATETIME       NOT NULL
          , CreditAnalyst                       NCHAR(18)      NULL
          , CreditProcessor                     NCHAR(18)      NULL
          , CreditSubmittalDate                 DATETIME       NULL
          , DocsInDate                          DATETIME       NULL
          , DocsOutDate                         DATETIME       NULL
          , eNoahCompleteDate                   DATETIME       NULL
          , ExternalId                          NVARCHAR(30)   NULL
          , FundDate                            DATETIME       NULL
          , FundedAmount                        DECIMAL(18,2)  NULL
          , Funder                              NCHAR(18)      NULL
          , Id                                  NCHAR(18)      NOT NULL
          , InternalCreditAnalyst               NCHAR(18)      NULL
          , LenderAccount                       NCHAR(18)      NULL
          , MerchantNumber                      NVARCHAR(32)   NULL
          , OfferInDate                         DATETIME       NULL
          , OfferOutDate                        DATETIME       NULL
          , OwnerId                             NCHAR(18)      NOT NULL
          , PhysicalAddress1                    NVARCHAR(1300) NULL
          , PhysicalAddress2                    NVARCHAR(1300) NULL
          , PhysicalAddressCity                 NVARCHAR(1300) NULL
          , PhysicalAddressState                NVARCHAR(1300) NULL
          , PhysicalAddressZip                  NVARCHAR(1300) NULL
          , PreQualDate                         DATETIME       NULL
          , RecordTypeId                        NCHAR(18)      NULL
          , RequestedAmount                     DECIMAL(18,2)  NULL
          , StageName                           NVARCHAR(40)   NOT NULL
          , StatementsInDate                    DATETIME       NULL
          , Type                                NVARCHAR(40)   NULL
          , UpForFundingDate                    DATETIME       NULL
          , WC_CreditDecisionRecord             NCHAR(18)      NULL
          , YearsInBusiness                     DECIMAL(18,0)  NULL
          , SystemModstamp                      DATETIME       NOT NULL
          , AccountExternalId                   NVARCHAR(100)  NULL
          , ACH                                 VARCHAR(5)     NULL
          , ACH_Debit                           VARCHAR(5)     NULL
          , ACH_DebitAmount                     DECIMAL(18,2)  NULL
          , ACH_QUALIFIED                       VARCHAR(5)     NULL
          , ACH_StartDate                       DATETIME       NULL
          , Amount                              DECIMAL(18,2)  NULL
          , AmountOffered                       DECIMAL(18,2)  NULL
          , Approved                            VARCHAR(5)     NULL
          , BalancePaid100PercentDate           DATETIME       NULL
          , BalancePaid50PercentDate            DATETIME       NULL
          , BalancePaid60PercentDate            DATETIME       NULL
          , BalancePaidatRenewal                DECIMAL(18,2)  NULL
          , BalancePaidPercent                  DECIMAL(18,2)  NULL
          , BalanceRemaining                    DECIMAL(18,2)  NULL
          , BalanceRemainingPercent             DECIMAL(18,2)  NULL
          , BOFI_Deal                           NVARCHAR(255)  NULL
          , BOFI_RestrictedState                NVARCHAR(1300) NULL
          , BookingStarted                      VARCHAR(5)     NULL
          , BrokerTier                          NVARCHAR(255)  NULL
          , CampaignID                          NCHAR(18)      NULL
          , ClosedLostDate                      DATETIME       NULL
          , CloseDate                           DATETIME       NOT NULL
          , CompanyDBA                          NVARCHAR(1300) NULL
          , ContractNumber                      NVARCHAR(32)   NULL
          , DateEstablished                     DATETIME       NULL
          , DateonDocs                          DATETIME       NULL
          , DaysbwAccountActivationDatePrequal  DECIMAL(18,0)  NULL
          , DaysbwActivationDateFundDate        DECIMAL(18,0)  NULL
          , DaysbwApprovalDateFundDate          DECIMAL(18,0)  NULL
          , DaysbwDocsinDateFundDate            DECIMAL(18,0)  NULL
          , DaysInApprovalStage                 DECIMAL(18,0)  NULL
          , DaysInOfferOutStage                 DECIMAL(18,0)  NULL
          , DeclinedDate                        DATETIME       NULL
          , Deposit                             DECIMAL(18,2)  NULL
          , EqAmount                            DECIMAL(18,0)  NULL
          , EqAmtLeased                         DECIMAL(18,2)  NULL
          , EqExperience                        NVARCHAR(32)   NULL
          , EqHasQuote                          VARCHAR(5)     NULL
          , EqLeasedBefore                      VARCHAR(5)     NULL
          , EqNew                               VARCHAR(5)     NULL
          , EqNumVendors                        NVARCHAR(255)  NULL
          , EqTerm                              DECIMAL(18,0)  NULL
          , EqUsed                              VARCHAR(5)     NULL
          , EqWhat                              NVARCHAR(64)   NULL
          , EqWhen                              NVARCHAR(128)  NULL
          , EqWhy                               NVARCHAR(64)   NULL
          , EqWithWhom                          NVARCHAR(64)   NULL
          , EquipCostForActual                  DECIMAL(18,2)  NULL
          , EquipCostPaidByLenderForActual      DECIMAL(18,2)  NULL
          , EquipCostPerDocRequest              DECIMAL(18,2)  NULL
          , EquipmentCondition                  NVARCHAR(255)  NULL
          , EquipmentGrandTotalSummary          DECIMAL(18,2)  NULL
          , EquipmentSeller                     NVARCHAR(255)  NULL
          , EquipmentStory                      NVARCHAR(255)  NULL
          , EquipmentYear                       NVARCHAR(255)  NULL
          , Fax                                 NVARCHAR(1300) NULL
          , FinalFundDate                       DATETIME       NULL
          , FiscalQuarter                       INT            NULL
          , FiscalYear                          INT            NULL
          , IsClosed                            VARCHAR(5)     NOT NULL
          , IsSplit                             VARCHAR(5)     NOT NULL
          , IsWon                               VARCHAR(5)     NOT NULL
          , LeadSource                          NVARCHAR(40)   NULL
          , LeaseNumber                         NVARCHAR(32)   NULL
          , LegalName                           NVARCHAR(1300) NULL
          , LegalStatus                         NVARCHAR(1300) NULL
          , LendersLeaseNumber                  NVARCHAR(32)   NULL
          , LendingTreeId                       NVARCHAR(9)    NULL
          , LendVantageCommission               DECIMAL(18,2)  NULL
          , LendVantageId                       NVARCHAR(255)  NULL
          , LoanStartDate                       DATETIME       NULL
          , LoanStartDateVerified               VARCHAR(5)     NULL
          , Locked                              VARCHAR(5)     NULL
          , MailingAddrDisplay                  NVARCHAR(1300) NULL
          , MailingAddress1                     NVARCHAR(1300) NULL
          , MailingAddressCity                  NVARCHAR(1300) NULL
          , MailingAddressState                 NVARCHAR(1300) NULL
          , MailingAddressZip                   NVARCHAR(1300) NULL
          , MarketingSubmissionDate             DATETIME       NULL
          , MaturityDate                        DATETIME       NULL
          , MerchantID                          NVARCHAR(32)   NULL
          , MonthlyGrossCC_Volume               DECIMAL(18,2)  NULL
          , MonthlySales                        DECIMAL(18,2)  NULL
          , MonthlyVisaMC_Volume                DECIMAL(18,2)  NULL
          , MonthsInBusiness                    DECIMAL(18,0)  NULL
          , Name                                NVARCHAR(120)  NOT NULL
          , NF_DeclineDate                      DATETIME       NULL
          , OpportunityID18                     NVARCHAR(18)   NULL
          , PartnerAccountId                    NCHAR(18)      NULL
          , PastDueBalance                      DECIMAL(18,2)  NULL
          , PaymentsBehind                      DECIMAL(18,0)  NULL
          , PaymentsBounced                     DECIMAL(18,0)  NULL
          , PaymentsCollected                   DECIMAL(18,0)  NULL
          , Phone                               NVARCHAR(1300) NULL
          , Probability                         DECIMAL(18,0)  NULL
          , ProcessorUserID                     NVARCHAR(32)   NULL
          , RateReviewCompleted                 VARCHAR(5)     NULL
          , Ratio                               NVARCHAR(32)   NULL
          , RenewalNumber                       DECIMAL(18,0)  NULL
          , ResubmitDate                        DATETIME       NULL
          , SplitFunding                        NVARCHAR(255)  NULL
          , SplitOpportunity                    VARCHAR(5)     NULL
          , SplitOpportunitywith                NCHAR(18)      NULL
          , Status                              NVARCHAR(255)  NULL
          , SubmissionDate                      DATETIME       NULL
          , SyndicationDate                     DATETIME       NULL              
  • It's likely both. Can you add the attributes of both lead and opportunity to your question so we can sketch a schema? May 18 '19 at 16:42
  • Dub, I am also working on similar kind of project. Please share what you end up having with leads and opportunities dim or fact table. thanks, Anu. Apr 6 '20 at 3:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.