KB441999: Metric join type is not applied when there is a link attribute and filters on the visualization

• Strategy

This article notes a scenario where metric join type is not applied when there is a link attribute and filters on the visualization.

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.
SYMPTOM
When adding a link attribute and filters to a visualization, the metric join type (Outer) is not applied and hence there are fewer rows on the visualization and subtotal reflects the same.
STEPS TO REPRODUCE
1. Create a Data Import In-Memory Cube with few tables.

2. Create a Dossier with above created cube as dataset.

3. Campaign attribute is defined as link attribute with following expression:

4. Go to visualization menu and select More Options and set both the metric join types to be Outer.

5. Visualization also has following filters defined:

6. Go to Query Details and note that "Lead Qualification Score" metric is being calculated in TempTable143 (highlighted in blue below) and "Total Responses" metric is being calculated in TempTable144 (highlighted in green below). When THESE 2 CSI passes are joined together, they are joined as inner metric even if the metrics are specified to be outer joined (highlighted in red below).


*********   Visualization Summary Start  **********
Time Spent: 0.615 sec(s)
Query Execution Start Time: 2/4/2019 2:26:17 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

Note: The total 'Time Spent' above may be greater than the summation of the individual step execution times below.
There are preparation tasks for each step that are not individually measured.
*********   Visualization Summary End    **********



************   Individual Step Start   ************
Number of Rows Returned: 8726
Time Spent: 0.003 sec(s)
Query Execution Start Time: 2/4/2019 2:26:17 PM
Query Execution End Time: 2/4/2019 2:26:17 PM

Tables Accessed:
Table19    [L_UNIT]:    Unit,         LOOKUP_TABLE    
Table79    [L_CAMPAIGN]:    Campaign,         LOOKUP_TABLE    
Table114    [REL_UNIT_CAMPAIGN]:    Unit, Campaign,         RELATIONSHIP_TABLE,    ONE_TO_MANY

select    [Campaign]@[sfc_campaign_id],
    [Campaign]@[sfc_campaign_desc],
    [Unit]@[marketing_budget_directory_id],
    [Unit]@[marketing_budget_directory_desc]
from    AQ.Campaigns_Marketing
with Table Join Tree:     [REL_UNIT_CAMPAIGN]
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 3114
Time Spent: 0.002 sec(s)
Query Execution Start Time: 2/4/2019 2:26:17 PM
Query Execution End Time: 2/4/2019 2:26:17 PM

select    [Campaign]@[sfc_campaign_id]
from    RELATION11-<Unit, Campaign>
where    [Unit]@[marketing_budget_directory_id] in (220)
to    Ex11_tempcube10
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 8726
Time Spent: 0.002 sec(s)
Query Execution Start Time: 2/4/2019 2:26:17 PM
Query Execution End Time: 2/4/2019 2:26:17 PM

Tables Accessed:
Table35    [L_TYPE]:    Type,         LOOKUP_TABLE    
Table79    [L_CAMPAIGN]:    Campaign,         LOOKUP_TABLE    
Table103    [REL_TYPE_CAMPAIGN]:    Type, Campaign,         RELATIONSHIP_TABLE,    ONE_TO_MANY

select    [Campaign]@[sfc_campaign_id],
    [Campaign]@[sfc_campaign_desc],
    [Type]@[sfc_campaign_type_id],
    [Type]@[sfc_campaign_type_desc]
from    AQ.Campaigns_Marketing
with Table Join Tree:     [REL_TYPE_CAMPAIGN]
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 102
Time Spent: 0.002 sec(s)
Query Execution Start Time: 2/4/2019 2:26:17 PM
Query Execution End Time: 2/4/2019 2:26:17 PM

select    [Campaign]@[sfc_campaign_id]
from    RELATION12-<Type, Campaign>
where    [Type]@[sfc_campaign_type_id] in (-46)
to    Ex11_tempcube11
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 8726
Time Spent: 0.001 sec(s)
Query Execution Start Time: 2/4/2019 2:26:17 PM
Query Execution End Time: 2/4/2019 2:26:17 PM

Tables Accessed:
Table46    [L_SUBTYPE]:    Subtype,         LOOKUP_TABLE    
Table79    [L_CAMPAIGN]:    Campaign,         LOOKUP_TABLE    
Table105    [REL_SUBTYPE_CAMPAIGN]:    Subtype, Campaign,         RELATIONSHIP_TABLE,    ONE_TO_MANY

select    [Campaign]@[sfc_campaign_id],
    [Campaign]@[sfc_campaign_desc],
    [Subtype]@[sfc_campaign_subtype_id],
    [Subtype]@[sfc_campaign_subtype_desc]
from    AQ.Campaigns_Marketing
with Table Join Tree:     [REL_SUBTYPE_CAMPAIGN]
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 922
Time Spent: 0.002 sec(s)
Query Execution Start Time: 2/4/2019 2:26:17 PM
Query Execution End Time: 2/4/2019 2:26:17 PM

select    [Campaign]@[sfc_campaign_id]
from    RELATION13-<Subtype, Campaign>
where    [Subtype]@[sfc_campaign_subtype_id] in (-1965, -1716, -1711, -1706, -1703, -1691, -723)
to    Ex11_tempcube12
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 3216
Time Spent: 0.002 sec(s)
Query Execution Start Time: 2/4/2019 2:26:17 PM
Query Execution End Time: 2/4/2019 2:26:17 PM

select    [Campaign]@[sfc_campaign_id]
from    RELATION12-<Type, Campaign>
where    [Type]@[sfc_campaign_type_id] in (-46, -44, -42, -41, -22, -17, -14)
to    Ex11_tempcube13
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 5123
Time Spent: 0.002 sec(s)
Query Execution Start Time: 2/4/2019 2:26:17 PM
Query Execution End Time: 2/4/2019 2:26:17 PM

select    [Campaign]@[sfc_campaign_id]
from    RELATION11-<Unit, Campaign>
where    [Unit]@[marketing_budget_directory_id] in (-1, 220)
to    Ex11_tempcube14
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 32
Time Spent: 0.454 sec(s)
Query Execution Start Time: 2/4/2019 2:26:17 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

Tables Accessed:
Table79    [L_CAMPAIGN]:    Campaign,         LOOKUP_TABLE    
Table103    [REL_TYPE_CAMPAIGN]:    Type, Campaign,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table105    [REL_SUBTYPE_CAMPAIGN]:    Subtype, Campaign,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table108    [REL_CAMPAIGN_SF_ID_CAMPAIGN]:    Campaign SF ID, Campaign,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table114    [REL_UNIT_CAMPAIGN]:    Unit, Campaign,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table127    [REL_SP_DATE]:    SP, Date,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table129    [REL_YEAR_DATE]:    Year, Date,         RELATIONSHIP_TABLE,    ONE_TO_MANY
Table136    [F_FACT_SFC_CAMPAIGN_MEMBERC061DEA243C7E687F335F7AE48463B0A]:    Cm Hot Warm Indicator, Vip, Campaign Member Account Status Group2, Sfc Campaign Member Lead Status Group, Campaign Member Status Group, Campaign Member Account Owner Division, Campaign Member Lead Status, Campaign Member Account Owner Group, Campaign Member Create SP, Campaign Member Status, Campaign Member Account Owner Unit, Campaign Member District, Campaign Member Account Owner, Campaign Member Create Date, Campaign Member First Responded Date, Campaign Member Most Recent Offer, Date, Campaign, Campaign Member Account, Campaign Member Account SFID, Company Domain, Campaign Member Company, Campaign Member Title, Campaign Member Phone, Campaign Member, Campaign Member Email, Email Domain, Sfc Campaign Member Name Tag2, Campaign Member SF ID,     Num Total Attended2, Cm Hot Warm Count, Num Prosp Registered2, # Lead Responses, Num Total Registered2, Total Responses, L4, L2, L1, Row Count - FACT_SFC_CAMPAIGN_MEMBER, L3, Num Prosp Attended2,     FACT_TABLE    
Table137    [F_LU_SFC_LEADCC24106242A6DDCC967F6BAB3F3F2345]:    MQL, Lead Manually Qualified Flag, Lead Rating, Lead Create Date, Date, Campaign, Lead,     Net New Lead Count, Lead Manually Qualified, Qualified Lead Count, Row Count - LU_SFC_LEAD, # L4 Leads Created, # L3 Leads Created, Lead Qualification Score, Lead Interest Score, Inquiries Per Lead, # L5 Leads Created, # L0 Leads Created, # L2 Leads Created, Sum Mql, # L1 Leads Created, # L6 Leads Created,     FACT_TABLE    

select    [Campaign]@[sfc_campaign_id],
    sum([[F_LU_SFC_LEADCC24106242A6DDCC967F6BAB3F3F2345].Lead Qualification Score])@{[Campaign]} as [Lead Qualification Score]
from    AQ.Campaigns_Marketing
with Table Join Tree:     [F_LU_SFC_LEADCC24106242A6DDCC967F6BAB3F3F2345]
     Join [REL_TYPE_CAMPAIGN]<[Type]@[sfc_campaign_type_id] in (-46, -44, -42, -41, -22, -17, -14)> with output level Tuple([Campaign]@[sfc_campaign_id], [Type]@[sfc_campaign_type_id], [Date]@[date_id], [Lead]@[sfc_lead_id])
     Join [REL_SUBTYPE_CAMPAIGN]<[Subtype]@[sfc_campaign_subtype_id] in (-1965, -1716, -1711, -1706, -1703, -1691, -723)> with output level Tuple([Campaign]@[sfc_campaign_id], [Type]@[sfc_campaign_type_id], [Date]@[date_id], [Lead]@[sfc_lead_id])
     Join [REL_UNIT_CAMPAIGN]<[Unit]@[marketing_budget_directory_id] in (-1, 220)> with output level Tuple([Campaign]@[sfc_campaign_id], [Unit]@[marketing_budget_directory_id], [Type]@[sfc_campaign_type_id], [Date]@[date_id], [Lead]@[sfc_lead_id])
     Join [REL_SP_DATE]<[SP]@[sp_id] in (201806, 201807)> with output level Tuple([Campaign]@[sfc_campaign_id], [Unit]@[marketing_budget_directory_id], [Type]@[sfc_campaign_type_id], [Date]@[date_id], [Lead]@[sfc_lead_id])
     Join [REL_YEAR_DATE]<[Year]@[year_id] in (2018)> with output level Tuple([Campaign]@[sfc_campaign_id], [Unit]@[marketing_budget_directory_id], [Type]@[sfc_campaign_type_id], [Date]@[date_id], [Lead]@[sfc_lead_id])
     Join (Set of distinct Tuple([Campaign]@[sfc_campaign_id], [Date]@[date_id]) where Tuple([Campaign]@[sfc_campaign_id], [Date]@[date_id]) in [F_FACT_SFC_CAMPAIGN_MEMBERC061DEA243C7E687F335F7AE48463B0A]<([Cm Hot Warm Indicator]@[cm_hot_warm_indicator] in (Yes)
 and [Campaign Member Account Status Group2]@[Campaign_Member_Account_Status_Group2] in (C2, C3, P3))>) with output level Tuple([Campaign]@[sfc_campaign_id], [Unit]@[marketing_budget_directory_id], [Type]@[sfc_campaign_type_id], [Date]@[date_id], [Lead]@[sfc_lead_id]) <([Unit]@[marketing_budget_directory_id] in (220)
 or [Type]@[sfc_campaign_type_id] in (-46))>
Save As TempTable143    

select    [Campaign]@[sfc_campaign_id],
    sum([[F_FACT_SFC_CAMPAIGN_MEMBERC061DEA243C7E687F335F7AE48463B0A].Total Responses])@{[Campaign]} as [Total Responses]
from    AQ.Campaigns_Marketing
with Table Join Tree:     [F_FACT_SFC_CAMPAIGN_MEMBERC061DEA243C7E687F335F7AE48463B0A]<([Cm Hot Warm Indicator]@[cm_hot_warm_indicator] in (Yes)
 and [Campaign Member Account Status Group2]@[Campaign_Member_Account_Status_Group2] in (C2, C3, P3))>
     Join [REL_TYPE_CAMPAIGN]<[Type]@[sfc_campaign_type_id] in (-46, -44, -42, -41, -22, -17, -14)> with output level Tuple([Campaign Member]@[sfc_campaign_member_id], [Campaign Member SF ID]@[campaign_member_sfcid], [Campaign]@[sfc_campaign_id], [Type]@[sfc_campaign_type_id], [Sfc Campaign Member Name Tag2]@[sfc_campaign_member_name_tag2], [Campaign Member Status]@[sfc_campaign_member_status_id], [Campaign Member Status Group]@[campaign_member_status_Group], [Campaign Member Create Date]@[sfc_campaign_member_created_date_id], [Date]@[date_id], [Campaign Member First Responded Date]@[sfc_campaign_member_first_responded_date_id], [Campaign Member Company]@[sfc_campaign_member_company], [Campaign Member Lead Status]@[sfc_campaign_member_lead_status_id], [Sfc Campaign Member Lead Status Group]@[sfc_campaign_member_lead_status_Group], [Campaign Member Title]@[sfc_campaign_member_title], [Campaign Member Account Status Group2]@[Campaign_Member_Account_Status_Group2], [Campaign Member Phone]@[sfc_campaign_member_phone], [Campaign Member Email]@[sfc_campaign_member_email], [Email Domain]@[email_domain], [Company Domain]@[company_domain], [Campaign Member District]@[sfc_campaign_member_district_id], [Campaign Member Create SP]@[sp_id], [Campaign Member Account]@[sfc_campaign_member_account_id], [Campaign Member Account SFID]@[sfc_campaign_member_account_sfcid], [Campaign Member Most Recent Offer]@[sfc_campaign_member_most_recent_offer_id], [Campaign Member Account Owner]@[acct_owner_id], [Campaign Member Account Owner Unit]@[acct_owner_unit_id], [Campaign Member Account Owner Group]@[acct_owner_Group_id], [Campaign Member Account Owner Division]@[acct_owner_Division_id], [Cm Hot Warm Indicator]@[cm_hot_warm_indicator], [Vip]@[VIP])
     Join [REL_SUBTYPE_CAMPAIGN]<[Subtype]@[sfc_campaign_subtype_id] in (-1965, -1716, -1711, -1706, -1703, -1691, -723)> with output level Tuple([Campaign Member]@[sfc_campaign_member_id], [Campaign Member SF ID]@[campaign_member_sfcid], [Campaign]@[sfc_campaign_id], [Type]@[sfc_campaign_type_id], [Sfc Campaign Member Name Tag2]@[sfc_campaign_member_name_tag2], [Campaign Member Status]@[sfc_campaign_member_status_id], [Campaign Member Status Group]@[campaign_member_status_Group], [Campaign Member Create Date]@[sfc_campaign_member_created_date_id], [Date]@[date_id], [Campaign Member First Responded Date]@[sfc_campaign_member_first_responded_date_id], [Campaign Member Company]@[sfc_campaign_member_company], [Campaign Member Lead Status]@[sfc_campaign_member_lead_status_id], [Sfc Campaign Member Lead Status Group]@[sfc_campaign_member_lead_status_Group], [Campaign Member Title]@[sfc_campaign_member_title], [Campaign Member Account Status Group2]@[Campaign_Member_Account_Status_Group2], [Campaign Member Phone]@[sfc_campaign_member_phone], [Campaign Member Email]@[sfc_campaign_member_email], [Email Domain]@[email_domain], [Company Domain]@[company_domain], [Campaign Member District]@[sfc_campaign_member_district_id], [Campaign Member Create SP]@[sp_id], [Campaign Member Account]@[sfc_campaign_member_account_id], [Campaign Member Account SFID]@[sfc_campaign_member_account_sfcid], [Campaign Member Most Recent Offer]@[sfc_campaign_member_most_recent_offer_id], [Campaign Member Account Owner]@[acct_owner_id], [Campaign Member Account Owner Unit]@[acct_owner_unit_id], [Campaign Member Account Owner Group]@[acct_owner_Group_id], [Campaign Member Account Owner Division]@[acct_owner_Division_id], [Cm Hot Warm Indicator]@[cm_hot_warm_indicator], [Vip]@[VIP])
     Join [REL_UNIT_CAMPAIGN]<[Unit]@[marketing_budget_directory_id] in (-1, 220)> with output level Tuple([Campaign Member]@[sfc_campaign_member_id], [Campaign Member SF ID]@[campaign_member_sfcid], [Campaign]@[sfc_campaign_id], [Unit]@[marketing_budget_directory_id], [Type]@[sfc_campaign_type_id], [Sfc Campaign Member Name Tag2]@[sfc_campaign_member_name_tag2], [Campaign Member Status]@[sfc_campaign_member_status_id], [Campaign Member Status Group]@[campaign_member_status_Group], [Campaign Member Create Date]@[sfc_campaign_member_created_date_id], [Date]@[date_id], [Campaign Member First Responded Date]@[sfc_campaign_member_first_responded_date_id], [Campaign Member Company]@[sfc_campaign_member_company], [Campaign Member Lead Status]@[sfc_campaign_member_lead_status_id], [Sfc Campaign Member Lead Status Group]@[sfc_campaign_member_lead_status_Group], [Campaign Member Title]@[sfc_campaign_member_title], [Campaign Member Account Status Group2]@[Campaign_Member_Account_Status_Group2], [Campaign Member Phone]@[sfc_campaign_member_phone], [Campaign Member Email]@[sfc_campaign_member_email], [Email Domain]@[email_domain], [Company Domain]@[company_domain], [Campaign Member District]@[sfc_campaign_member_district_id], [Campaign Member Create SP]@[sp_id], [Campaign Member Account]@[sfc_campaign_member_account_id], [Campaign Member Account SFID]@[sfc_campaign_member_account_sfcid], [Campaign Member Most Recent Offer]@[sfc_campaign_member_most_recent_offer_id], [Campaign Member Account Owner]@[acct_owner_id], [Campaign Member Account Owner Unit]@[acct_owner_unit_id], [Campaign Member Account Owner Group]@[acct_owner_Group_id], [Campaign Member Account Owner Division]@[acct_owner_Division_id], [Cm Hot Warm Indicator]@[cm_hot_warm_indicator], [Vip]@[VIP])
     Join [REL_SP_DATE]<[SP]@[sp_id] in (201806, 201807)> with output level Tuple([Campaign Member]@[sfc_campaign_member_id], [Campaign Member SF ID]@[campaign_member_sfcid], [Campaign]@[sfc_campaign_id], [Unit]@[marketing_budget_directory_id], [Type]@[sfc_campaign_type_id], [Sfc Campaign Member Name Tag2]@[sfc_campaign_member_name_tag2], [Campaign Member Status]@[sfc_campaign_member_status_id], [Campaign Member Status Group]@[campaign_member_status_Group], [Campaign Member Create Date]@[sfc_campaign_member_created_date_id], [Date]@[date_id], [Campaign Member First Responded Date]@[sfc_campaign_member_first_responded_date_id], [Campaign Member Company]@[sfc_campaign_member_company], [Campaign Member Lead Status]@[sfc_campaign_member_lead_status_id], [Sfc Campaign Member Lead Status Group]@[sfc_campaign_member_lead_status_Group], [Campaign Member Title]@[sfc_campaign_member_title], [Campaign Member Account Status Group2]@[Campaign_Member_Account_Status_Group2], [Campaign Member Phone]@[sfc_campaign_member_phone], [Campaign Member Email]@[sfc_campaign_member_email], [Email Domain]@[email_domain], [Company Domain]@[company_domain], [Campaign Member District]@[sfc_campaign_member_district_id], [Campaign Member Create SP]@[sp_id], [Campaign Member Account]@[sfc_campaign_member_account_id], [Campaign Member Account SFID]@[sfc_campaign_member_account_sfcid], [Campaign Member Most Recent Offer]@[sfc_campaign_member_most_recent_offer_id], [Campaign Member Account Owner]@[acct_owner_id], [Campaign Member Account Owner Unit]@[acct_owner_unit_id], [Campaign Member Account Owner Group]@[acct_owner_Group_id], [Campaign Member Account Owner Division]@[acct_owner_Division_id], [Cm Hot Warm Indicator]@[cm_hot_warm_indicator], [Vip]@[VIP])
     Join [REL_YEAR_DATE]<[Year]@[year_id] in (2018)> with output level Tuple([Campaign Member]@[sfc_campaign_member_id], [Campaign Member SF ID]@[campaign_member_sfcid], [Campaign]@[sfc_campaign_id], [Unit]@[marketing_budget_directory_id], [Type]@[sfc_campaign_type_id], [Sfc Campaign Member Name Tag2]@[sfc_campaign_member_name_tag2], [Campaign Member Status]@[sfc_campaign_member_status_id], [Campaign Member Status Group]@[campaign_member_status_Group], [Campaign Member Create Date]@[sfc_campaign_member_created_date_id], [Date]@[date_id], [Campaign Member First Responded Date]@[sfc_campaign_member_first_responded_date_id], [Campaign Member Company]@[sfc_campaign_member_company], [Campaign Member Lead Status]@[sfc_campaign_member_lead_status_id], [Sfc Campaign Member Lead Status Group]@[sfc_campaign_member_lead_status_Group], [Campaign Member Title]@[sfc_campaign_member_title], [Campaign Member Account Status Group2]@[Campaign_Member_Account_Status_Group2], [Campaign Member Phone]@[sfc_campaign_member_phone], [Campaign Member Email]@[sfc_campaign_member_email], [Email Domain]@[email_domain], [Company Domain]@[company_domain], [Campaign Member District]@[sfc_campaign_member_district_id], [Campaign Member Create SP]@[sp_id], [Campaign Member Account]@[sfc_campaign_member_account_id], [Campaign Member Account SFID]@[sfc_campaign_member_account_sfcid], [Campaign Member Most Recent Offer]@[sfc_campaign_member_most_recent_offer_id], [Campaign Member Account Owner]@[acct_owner_id], [Campaign Member Account Owner Unit]@[acct_owner_unit_id], [Campaign Member Account Owner Group]@[acct_owner_Group_id], [Campaign Member Account Owner Division]@[acct_owner_Division_id], [Cm Hot Warm Indicator]@[cm_hot_warm_indicator], [Vip]@[VIP]) <([Unit]@[marketing_budget_directory_id] in (220)
 or [Type]@[sfc_campaign_type_id] in (-46))>
Save As TempTable144    

select    [Campaign]@[sfc_campaign_id],
    [Campaign]@[sfc_campaign_desc],
    [Campaign SF ID]@[campaign_sfcid],
    [TempTable143.Lead Qualification Score] as [Lead Qualification Score],
    [TempTable144.Total Responses] as [Total Responses]
from    AQ.Campaigns_Marketing
with Table Join Tree:     TempTable143
     Join TempTable144 with output level Tuple([Campaign]@[sfc_campaign_id])
     Join [REL_CAMPAIGN_SF_ID_CAMPAIGN] with output level Tuple([Campaign]@[sfc_campaign_id], [Campaign SF ID]@[campaign_sfcid])
to    Ex11_tempcube15
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 3114
Time Spent: 0.002 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

select    [Campaign]@[sfc_campaign_id]
from    RELATION11-<Unit, Campaign>
where    [Unit]@[marketing_budget_directory_id] in (220)
to    Ex11_tempcube0
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 102
Time Spent: 0.002 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

select    [Campaign]@[sfc_campaign_id]
from    RELATION12-<Type, Campaign>
where    [Type]@[sfc_campaign_type_id] in (-46)
to    Ex11_tempcube1
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 922
Time Spent: 0.001 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

select    [Campaign]@[sfc_campaign_id]
from    RELATION13-<Subtype, Campaign>
where    [Subtype]@[sfc_campaign_subtype_id] in (-1965, -1716, -1711, -1706, -1703, -1691, -723)
to    Ex11_tempcube2
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 3216
Time Spent: 0.002 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

select    [Campaign]@[sfc_campaign_id]
from    RELATION12-<Type, Campaign>
where    [Type]@[sfc_campaign_type_id] in (-46, -44, -42, -41, -22, -17, -14)
to    Ex11_tempcube3
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 5123
Time Spent: 0.001 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

select    [Campaign]@[sfc_campaign_id]
from    RELATION11-<Unit, Campaign>
where    [Unit]@[marketing_budget_directory_id] in (-1, 220)
to    Ex11_tempcube4
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 8728
Time Spent: 0.001 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

select    [Campaign]@[sfc_campaign_id],
    [Campaign]@[sfc_campaign_desc],
    [Campaign SF ID]@[campaign_sfcid],
    [Campaign(Link)]@[ID],
    [Campaign(Link)]@[FORM_1]
from    RELATION5-<Campaign, Campaign SF ID, Campaign(Link)>
to    Ex11_tempcube16
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 922
Time Spent: 0.034 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

select    [Campaign]@[sfc_campaign_id],
    [Campaign]@[sfc_campaign_desc],
    [Campaign SF ID]@[campaign_sfcid],
    [Campaign(Link)]@[ID],
    [Campaign(Link)]@[FORM_1]
from    Ex11_tempcube16
where    (<Campaign> in (Ex11_tempcube2)
 and <Campaign> in (Ex11_tempcube3)
 and <Campaign> in (Ex11_tempcube4)
 and (<Campaign> in (Ex11_tempcube0) or <Campaign> in (Ex11_tempcube1)))
to    Ex11_tempcube17
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

Drop    Ex11_tempcube16
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 32
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

select    *    
from    Left Outer Join(
                    Ex11_tempcube15, 
                    Ex11_tempcube17
                   ) on Campaign, Campaign SF ID
to    Ex11_tempcube18
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

Drop    Ex11_tempcube15
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

Drop    Ex11_tempcube0
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

Drop    Ex11_tempcube1
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

Drop    Ex11_tempcube2
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

Drop    Ex11_tempcube3
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

Drop    Ex11_tempcube4
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

Drop    Ex11_tempcube17
************   Individual Step End     ************



************   Individual Step Start   ************
Number of Rows Returned: 32
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

select    [Campaign(Link)]@[ID],
    [Campaign(Link)]@[FORM_1],
    sum([Lead Qualification Score])@{[Campaign(Link)]},
    sum([Total Responses])@{[Campaign(Link)]}
from    Ex11_tempcube18
where    (<Campaign> in (Ex11_tempcube12)
 and <Campaign> in (Ex11_tempcube13)
 and <Campaign> in (Ex11_tempcube14)
 and (<Campaign> in (Ex11_tempcube10) or <Campaign> in (Ex11_tempcube11)))
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

Drop    Ex11_tempcube18
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

Drop    Ex11_tempcube10
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

Drop    Ex11_tempcube11
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

Drop    Ex11_tempcube12
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

Drop    Ex11_tempcube13
************   Individual Step End     ************



************   Individual Step Start   ************
Time Spent: 0.000 sec(s)
Query Execution Start Time: 2/4/2019 2:26:18 PM
Query Execution End Time: 2/4/2019 2:26:18 PM

Drop    Ex11_tempcube14
************   Individual Step End     ************

CAUSE
This is a known defect in Strategy 10.11 or earlier releases where the metric outer join setting is lost for link attribute and default (inner) is applied.
ACTION
This defect has been addressed in Strategy 11.0.
If you are upgrading to Strategy 11.0 or above, to pick up the fix, enable project level setting "Data Engine Version" to 11. For more details on enabling the setting, refer to Change Data Engine Version.
Now, the metric join type setting is honored and applied correctly and expected metric rows and subtotals are observed.


...
select    [Campaign]@[sfc_campaign_id],
    [Campaign]@[sfc_campaign_desc],
    [Campaign SF ID]@[campaign_sfcid],
    [TempTable143.Lead Qualification Score] as [Lead Qualification Score],
    [TempTable144.Total Responses] as [Total Responses]
from    AQ.Campaigns_Marketing
with Table Join Tree:     TempTable143
     OuterJoin TempTable144 with output level Tuple([Campaign]@[sfc_campaign_id])
     Join [REL_CAMPAIGN_SF_ID_CAMPAIGN] with output level Tuple([Campaign]@[sfc_campaign_id], [Campaign SF ID]@[campaign_sfcid])
to    Ex11_tempcube15
...

The Strategy Internal Reference Number for the issue discussed in this technical note is KB441999 and DE98724.

Comment

0 comments

Details

Knowledge Article

Published:

September 27, 2018

Last Updated:

March 21, 2024

********* Visualization Summary Start ********** Time Spent: 0.615 sec(s) Query Execution Start Time: 2/4/2019 2:26:17 PM Query Execution End Time: 2/4/2019 2:26:18 PM Note: The total 'Time Spent' above may be greater than the summation of the individual step execution times below. There are preparation tasks for each step that are not individually measured. ********* Visualization Summary End ********** ************ Individual Step Start ************ Number of Rows Returned: 8726 Time Spent: 0.003 sec(s) Query Execution Start Time: 2/4/2019 2:26:17 PM Query Execution End Time: 2/4/2019 2:26:17 PM Tables Accessed: Table19 [L_UNIT]: Unit, LOOKUP_TABLE Table79 [L_CAMPAIGN]: Campaign, LOOKUP_TABLE Table114 [REL_UNIT_CAMPAIGN]: Unit, Campaign, RELATIONSHIP_TABLE, ONE_TO_MANY select [Campaign]@[sfc_campaign_id], [Campaign]@[sfc_campaign_desc], [Unit]@[marketing_budget_directory_id], [Unit]@[marketing_budget_directory_desc] from AQ.Campaigns_Marketing with Table Join Tree: [REL_UNIT_CAMPAIGN] ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 3114 Time Spent: 0.002 sec(s) Query Execution Start Time: 2/4/2019 2:26:17 PM Query Execution End Time: 2/4/2019 2:26:17 PM select [Campaign]@[sfc_campaign_id] from RELATION11-<Unit, Campaign> where [Unit]@[marketing_budget_directory_id] in (220) to Ex11_tempcube10 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 8726 Time Spent: 0.002 sec(s) Query Execution Start Time: 2/4/2019 2:26:17 PM Query Execution End Time: 2/4/2019 2:26:17 PM Tables Accessed: Table35 [L_TYPE]: Type, LOOKUP_TABLE Table79 [L_CAMPAIGN]: Campaign, LOOKUP_TABLE Table103 [REL_TYPE_CAMPAIGN]: Type, Campaign, RELATIONSHIP_TABLE, ONE_TO_MANY select [Campaign]@[sfc_campaign_id], [Campaign]@[sfc_campaign_desc], [Type]@[sfc_campaign_type_id], [Type]@[sfc_campaign_type_desc] from AQ.Campaigns_Marketing with Table Join Tree: [REL_TYPE_CAMPAIGN] ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 102 Time Spent: 0.002 sec(s) Query Execution Start Time: 2/4/2019 2:26:17 PM Query Execution End Time: 2/4/2019 2:26:17 PM select [Campaign]@[sfc_campaign_id] from RELATION12-<Type, Campaign> where [Type]@[sfc_campaign_type_id] in (-46) to Ex11_tempcube11 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 8726 Time Spent: 0.001 sec(s) Query Execution Start Time: 2/4/2019 2:26:17 PM Query Execution End Time: 2/4/2019 2:26:17 PM Tables Accessed: Table46 [L_SUBTYPE]: Subtype, LOOKUP_TABLE Table79 [L_CAMPAIGN]: Campaign, LOOKUP_TABLE Table105 [REL_SUBTYPE_CAMPAIGN]: Subtype, Campaign, RELATIONSHIP_TABLE, ONE_TO_MANY select [Campaign]@[sfc_campaign_id], [Campaign]@[sfc_campaign_desc], [Subtype]@[sfc_campaign_subtype_id], [Subtype]@[sfc_campaign_subtype_desc] from AQ.Campaigns_Marketing with Table Join Tree: [REL_SUBTYPE_CAMPAIGN] ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 922 Time Spent: 0.002 sec(s) Query Execution Start Time: 2/4/2019 2:26:17 PM Query Execution End Time: 2/4/2019 2:26:17 PM select [Campaign]@[sfc_campaign_id] from RELATION13-<Subtype, Campaign> where [Subtype]@[sfc_campaign_subtype_id] in (-1965, -1716, -1711, -1706, -1703, -1691, -723) to Ex11_tempcube12 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 3216 Time Spent: 0.002 sec(s) Query Execution Start Time: 2/4/2019 2:26:17 PM Query Execution End Time: 2/4/2019 2:26:17 PM select [Campaign]@[sfc_campaign_id] from RELATION12-<Type, Campaign> where [Type]@[sfc_campaign_type_id] in (-46, -44, -42, -41, -22, -17, -14) to Ex11_tempcube13 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 5123 Time Spent: 0.002 sec(s) Query Execution Start Time: 2/4/2019 2:26:17 PM Query Execution End Time: 2/4/2019 2:26:17 PM select [Campaign]@[sfc_campaign_id] from RELATION11-<Unit, Campaign> where [Unit]@[marketing_budget_directory_id] in (-1, 220) to Ex11_tempcube14 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 32 Time Spent: 0.454 sec(s) Query Execution Start Time: 2/4/2019 2:26:17 PM Query Execution End Time: 2/4/2019 2:26:18 PM Tables Accessed: Table79 [L_CAMPAIGN]: Campaign, LOOKUP_TABLE Table103 [REL_TYPE_CAMPAIGN]: Type, Campaign, RELATIONSHIP_TABLE, ONE_TO_MANY Table105 [REL_SUBTYPE_CAMPAIGN]: Subtype, Campaign, RELATIONSHIP_TABLE, ONE_TO_MANY Table108 [REL_CAMPAIGN_SF_ID_CAMPAIGN]: Campaign SF ID, Campaign, RELATIONSHIP_TABLE, ONE_TO_MANY Table114 [REL_UNIT_CAMPAIGN]: Unit, Campaign, RELATIONSHIP_TABLE, ONE_TO_MANY Table127 [REL_SP_DATE]: SP, Date, RELATIONSHIP_TABLE, ONE_TO_MANY Table129 [REL_YEAR_DATE]: Year, Date, RELATIONSHIP_TABLE, ONE_TO_MANY Table136 [F_FACT_SFC_CAMPAIGN_MEMBERC061DEA243C7E687F335F7AE48463B0A]: Cm Hot Warm Indicator, Vip, Campaign Member Account Status Group2, Sfc Campaign Member Lead Status Group, Campaign Member Status Group, Campaign Member Account Owner Division, Campaign Member Lead Status, Campaign Member Account Owner Group, Campaign Member Create SP, Campaign Member Status, Campaign Member Account Owner Unit, Campaign Member District, Campaign Member Account Owner, Campaign Member Create Date, Campaign Member First Responded Date, Campaign Member Most Recent Offer, Date, Campaign, Campaign Member Account, Campaign Member Account SFID, Company Domain, Campaign Member Company, Campaign Member Title, Campaign Member Phone, Campaign Member, Campaign Member Email, Email Domain, Sfc Campaign Member Name Tag2, Campaign Member SF ID, Num Total Attended2, Cm Hot Warm Count, Num Prosp Registered2, # Lead Responses, Num Total Registered2, Total Responses, L4, L2, L1, Row Count - FACT_SFC_CAMPAIGN_MEMBER, L3, Num Prosp Attended2, FACT_TABLE Table137 [F_LU_SFC_LEADCC24106242A6DDCC967F6BAB3F3F2345]: MQL, Lead Manually Qualified Flag, Lead Rating, Lead Create Date, Date, Campaign, Lead, Net New Lead Count, Lead Manually Qualified, Qualified Lead Count, Row Count - LU_SFC_LEAD, # L4 Leads Created, # L3 Leads Created, Lead Qualification Score, Lead Interest Score, Inquiries Per Lead, # L5 Leads Created, # L0 Leads Created, # L2 Leads Created, Sum Mql, # L1 Leads Created, # L6 Leads Created, FACT_TABLE select [Campaign]@[sfc_campaign_id], sum([[F_LU_SFC_LEADCC24106242A6DDCC967F6BAB3F3F2345].Lead Qualification Score])@{[Campaign]} as [Lead Qualification Score] from AQ.Campaigns_Marketing with Table Join Tree: [F_LU_SFC_LEADCC24106242A6DDCC967F6BAB3F3F2345] Join [REL_TYPE_CAMPAIGN]<[Type]@[sfc_campaign_type_id] in (-46, -44, -42, -41, -22, -17, -14)> with output level Tuple([Campaign]@[sfc_campaign_id], [Type]@[sfc_campaign_type_id], [Date]@[date_id], [Lead]@[sfc_lead_id]) Join [REL_SUBTYPE_CAMPAIGN]<[Subtype]@[sfc_campaign_subtype_id] in (-1965, -1716, -1711, -1706, -1703, -1691, -723)> with output level Tuple([Campaign]@[sfc_campaign_id], [Type]@[sfc_campaign_type_id], [Date]@[date_id], [Lead]@[sfc_lead_id]) Join [REL_UNIT_CAMPAIGN]<[Unit]@[marketing_budget_directory_id] in (-1, 220)> with output level Tuple([Campaign]@[sfc_campaign_id], [Unit]@[marketing_budget_directory_id], [Type]@[sfc_campaign_type_id], [Date]@[date_id], [Lead]@[sfc_lead_id]) Join [REL_SP_DATE]<[SP]@[sp_id] in (201806, 201807)> with output level Tuple([Campaign]@[sfc_campaign_id], [Unit]@[marketing_budget_directory_id], [Type]@[sfc_campaign_type_id], [Date]@[date_id], [Lead]@[sfc_lead_id]) Join [REL_YEAR_DATE]<[Year]@[year_id] in (2018)> with output level Tuple([Campaign]@[sfc_campaign_id], [Unit]@[marketing_budget_directory_id], [Type]@[sfc_campaign_type_id], [Date]@[date_id], [Lead]@[sfc_lead_id]) Join (Set of distinct Tuple([Campaign]@[sfc_campaign_id], [Date]@[date_id]) where Tuple([Campaign]@[sfc_campaign_id], [Date]@[date_id]) in [F_FACT_SFC_CAMPAIGN_MEMBERC061DEA243C7E687F335F7AE48463B0A]<([Cm Hot Warm Indicator]@[cm_hot_warm_indicator] in (Yes) and [Campaign Member Account Status Group2]@[Campaign_Member_Account_Status_Group2] in (C2, C3, P3))>) with output level Tuple([Campaign]@[sfc_campaign_id], [Unit]@[marketing_budget_directory_id], [Type]@[sfc_campaign_type_id], [Date]@[date_id], [Lead]@[sfc_lead_id]) <([Unit]@[marketing_budget_directory_id] in (220) or [Type]@[sfc_campaign_type_id] in (-46))> Save As TempTable143 select [Campaign]@[sfc_campaign_id], sum([[F_FACT_SFC_CAMPAIGN_MEMBERC061DEA243C7E687F335F7AE48463B0A].Total Responses])@{[Campaign]} as [Total Responses] from AQ.Campaigns_Marketing with Table Join Tree: [F_FACT_SFC_CAMPAIGN_MEMBERC061DEA243C7E687F335F7AE48463B0A]<([Cm Hot Warm Indicator]@[cm_hot_warm_indicator] in (Yes) and [Campaign Member Account Status Group2]@[Campaign_Member_Account_Status_Group2] in (C2, C3, P3))> Join [REL_TYPE_CAMPAIGN]<[Type]@[sfc_campaign_type_id] in (-46, -44, -42, -41, -22, -17, -14)> with output level Tuple([Campaign Member]@[sfc_campaign_member_id], [Campaign Member SF ID]@[campaign_member_sfcid], [Campaign]@[sfc_campaign_id], [Type]@[sfc_campaign_type_id], [Sfc Campaign Member Name Tag2]@[sfc_campaign_member_name_tag2], [Campaign Member Status]@[sfc_campaign_member_status_id], [Campaign Member Status Group]@[campaign_member_status_Group], [Campaign Member Create Date]@[sfc_campaign_member_created_date_id], [Date]@[date_id], [Campaign Member First Responded Date]@[sfc_campaign_member_first_responded_date_id], [Campaign Member Company]@[sfc_campaign_member_company], [Campaign Member Lead Status]@[sfc_campaign_member_lead_status_id], [Sfc Campaign Member Lead Status Group]@[sfc_campaign_member_lead_status_Group], [Campaign Member Title]@[sfc_campaign_member_title], [Campaign Member Account Status Group2]@[Campaign_Member_Account_Status_Group2], [Campaign Member Phone]@[sfc_campaign_member_phone], [Campaign Member Email]@[sfc_campaign_member_email], [Email Domain]@[email_domain], [Company Domain]@[company_domain], [Campaign Member District]@[sfc_campaign_member_district_id], [Campaign Member Create SP]@[sp_id], [Campaign Member Account]@[sfc_campaign_member_account_id], [Campaign Member Account SFID]@[sfc_campaign_member_account_sfcid], [Campaign Member Most Recent Offer]@[sfc_campaign_member_most_recent_offer_id], [Campaign Member Account Owner]@[acct_owner_id], [Campaign Member Account Owner Unit]@[acct_owner_unit_id], [Campaign Member Account Owner Group]@[acct_owner_Group_id], [Campaign Member Account Owner Division]@[acct_owner_Division_id], [Cm Hot Warm Indicator]@[cm_hot_warm_indicator], [Vip]@[VIP]) Join [REL_SUBTYPE_CAMPAIGN]<[Subtype]@[sfc_campaign_subtype_id] in (-1965, -1716, -1711, -1706, -1703, -1691, -723)> with output level Tuple([Campaign Member]@[sfc_campaign_member_id], [Campaign Member SF ID]@[campaign_member_sfcid], [Campaign]@[sfc_campaign_id], [Type]@[sfc_campaign_type_id], [Sfc Campaign Member Name Tag2]@[sfc_campaign_member_name_tag2], [Campaign Member Status]@[sfc_campaign_member_status_id], [Campaign Member Status Group]@[campaign_member_status_Group], [Campaign Member Create Date]@[sfc_campaign_member_created_date_id], [Date]@[date_id], [Campaign Member First Responded Date]@[sfc_campaign_member_first_responded_date_id], [Campaign Member Company]@[sfc_campaign_member_company], [Campaign Member Lead Status]@[sfc_campaign_member_lead_status_id], [Sfc Campaign Member Lead Status Group]@[sfc_campaign_member_lead_status_Group], [Campaign Member Title]@[sfc_campaign_member_title], [Campaign Member Account Status Group2]@[Campaign_Member_Account_Status_Group2], [Campaign Member Phone]@[sfc_campaign_member_phone], [Campaign Member Email]@[sfc_campaign_member_email], [Email Domain]@[email_domain], [Company Domain]@[company_domain], [Campaign Member District]@[sfc_campaign_member_district_id], [Campaign Member Create SP]@[sp_id], [Campaign Member Account]@[sfc_campaign_member_account_id], [Campaign Member Account SFID]@[sfc_campaign_member_account_sfcid], [Campaign Member Most Recent Offer]@[sfc_campaign_member_most_recent_offer_id], [Campaign Member Account Owner]@[acct_owner_id], [Campaign Member Account Owner Unit]@[acct_owner_unit_id], [Campaign Member Account Owner Group]@[acct_owner_Group_id], [Campaign Member Account Owner Division]@[acct_owner_Division_id], [Cm Hot Warm Indicator]@[cm_hot_warm_indicator], [Vip]@[VIP]) Join [REL_UNIT_CAMPAIGN]<[Unit]@[marketing_budget_directory_id] in (-1, 220)> with output level Tuple([Campaign Member]@[sfc_campaign_member_id], [Campaign Member SF ID]@[campaign_member_sfcid], [Campaign]@[sfc_campaign_id], [Unit]@[marketing_budget_directory_id], [Type]@[sfc_campaign_type_id], [Sfc Campaign Member Name Tag2]@[sfc_campaign_member_name_tag2], [Campaign Member Status]@[sfc_campaign_member_status_id], [Campaign Member Status Group]@[campaign_member_status_Group], [Campaign Member Create Date]@[sfc_campaign_member_created_date_id], [Date]@[date_id], [Campaign Member First Responded Date]@[sfc_campaign_member_first_responded_date_id], [Campaign Member Company]@[sfc_campaign_member_company], [Campaign Member Lead Status]@[sfc_campaign_member_lead_status_id], [Sfc Campaign Member Lead Status Group]@[sfc_campaign_member_lead_status_Group], [Campaign Member Title]@[sfc_campaign_member_title], [Campaign Member Account Status Group2]@[Campaign_Member_Account_Status_Group2], [Campaign Member Phone]@[sfc_campaign_member_phone], [Campaign Member Email]@[sfc_campaign_member_email], [Email Domain]@[email_domain], [Company Domain]@[company_domain], [Campaign Member District]@[sfc_campaign_member_district_id], [Campaign Member Create SP]@[sp_id], [Campaign Member Account]@[sfc_campaign_member_account_id], [Campaign Member Account SFID]@[sfc_campaign_member_account_sfcid], [Campaign Member Most Recent Offer]@[sfc_campaign_member_most_recent_offer_id], [Campaign Member Account Owner]@[acct_owner_id], [Campaign Member Account Owner Unit]@[acct_owner_unit_id], [Campaign Member Account Owner Group]@[acct_owner_Group_id], [Campaign Member Account Owner Division]@[acct_owner_Division_id], [Cm Hot Warm Indicator]@[cm_hot_warm_indicator], [Vip]@[VIP]) Join [REL_SP_DATE]<[SP]@[sp_id] in (201806, 201807)> with output level Tuple([Campaign Member]@[sfc_campaign_member_id], [Campaign Member SF ID]@[campaign_member_sfcid], [Campaign]@[sfc_campaign_id], [Unit]@[marketing_budget_directory_id], [Type]@[sfc_campaign_type_id], [Sfc Campaign Member Name Tag2]@[sfc_campaign_member_name_tag2], [Campaign Member Status]@[sfc_campaign_member_status_id], [Campaign Member Status Group]@[campaign_member_status_Group], [Campaign Member Create Date]@[sfc_campaign_member_created_date_id], [Date]@[date_id], [Campaign Member First Responded Date]@[sfc_campaign_member_first_responded_date_id], [Campaign Member Company]@[sfc_campaign_member_company], [Campaign Member Lead Status]@[sfc_campaign_member_lead_status_id], [Sfc Campaign Member Lead Status Group]@[sfc_campaign_member_lead_status_Group], [Campaign Member Title]@[sfc_campaign_member_title], [Campaign Member Account Status Group2]@[Campaign_Member_Account_Status_Group2], [Campaign Member Phone]@[sfc_campaign_member_phone], [Campaign Member Email]@[sfc_campaign_member_email], [Email Domain]@[email_domain], [Company Domain]@[company_domain], [Campaign Member District]@[sfc_campaign_member_district_id], [Campaign Member Create SP]@[sp_id], [Campaign Member Account]@[sfc_campaign_member_account_id], [Campaign Member Account SFID]@[sfc_campaign_member_account_sfcid], [Campaign Member Most Recent Offer]@[sfc_campaign_member_most_recent_offer_id], [Campaign Member Account Owner]@[acct_owner_id], [Campaign Member Account Owner Unit]@[acct_owner_unit_id], [Campaign Member Account Owner Group]@[acct_owner_Group_id], [Campaign Member Account Owner Division]@[acct_owner_Division_id], [Cm Hot Warm Indicator]@[cm_hot_warm_indicator], [Vip]@[VIP]) Join [REL_YEAR_DATE]<[Year]@[year_id] in (2018)> with output level Tuple([Campaign Member]@[sfc_campaign_member_id], [Campaign Member SF ID]@[campaign_member_sfcid], [Campaign]@[sfc_campaign_id], [Unit]@[marketing_budget_directory_id], [Type]@[sfc_campaign_type_id], [Sfc Campaign Member Name Tag2]@[sfc_campaign_member_name_tag2], [Campaign Member Status]@[sfc_campaign_member_status_id], [Campaign Member Status Group]@[campaign_member_status_Group], [Campaign Member Create Date]@[sfc_campaign_member_created_date_id], [Date]@[date_id], [Campaign Member First Responded Date]@[sfc_campaign_member_first_responded_date_id], [Campaign Member Company]@[sfc_campaign_member_company], [Campaign Member Lead Status]@[sfc_campaign_member_lead_status_id], [Sfc Campaign Member Lead Status Group]@[sfc_campaign_member_lead_status_Group], [Campaign Member Title]@[sfc_campaign_member_title], [Campaign Member Account Status Group2]@[Campaign_Member_Account_Status_Group2], [Campaign Member Phone]@[sfc_campaign_member_phone], [Campaign Member Email]@[sfc_campaign_member_email], [Email Domain]@[email_domain], [Company Domain]@[company_domain], [Campaign Member District]@[sfc_campaign_member_district_id], [Campaign Member Create SP]@[sp_id], [Campaign Member Account]@[sfc_campaign_member_account_id], [Campaign Member Account SFID]@[sfc_campaign_member_account_sfcid], [Campaign Member Most Recent Offer]@[sfc_campaign_member_most_recent_offer_id], [Campaign Member Account Owner]@[acct_owner_id], [Campaign Member Account Owner Unit]@[acct_owner_unit_id], [Campaign Member Account Owner Group]@[acct_owner_Group_id], [Campaign Member Account Owner Division]@[acct_owner_Division_id], [Cm Hot Warm Indicator]@[cm_hot_warm_indicator], [Vip]@[VIP]) <([Unit]@[marketing_budget_directory_id] in (220) or [Type]@[sfc_campaign_type_id] in (-46))> Save As TempTable144 select [Campaign]@[sfc_campaign_id], [Campaign]@[sfc_campaign_desc], [Campaign SF ID]@[campaign_sfcid], [TempTable143.Lead Qualification Score] as [Lead Qualification Score], [TempTable144.Total Responses] as [Total Responses] from AQ.Campaigns_Marketing with Table Join Tree: TempTable143 Join TempTable144 with output level Tuple([Campaign]@[sfc_campaign_id]) Join [REL_CAMPAIGN_SF_ID_CAMPAIGN] with output level Tuple([Campaign]@[sfc_campaign_id], [Campaign SF ID]@[campaign_sfcid]) to Ex11_tempcube15 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 3114 Time Spent: 0.002 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM select [Campaign]@[sfc_campaign_id] from RELATION11-<Unit, Campaign> where [Unit]@[marketing_budget_directory_id] in (220) to Ex11_tempcube0 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 102 Time Spent: 0.002 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM select [Campaign]@[sfc_campaign_id] from RELATION12-<Type, Campaign> where [Type]@[sfc_campaign_type_id] in (-46) to Ex11_tempcube1 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 922 Time Spent: 0.001 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM select [Campaign]@[sfc_campaign_id] from RELATION13-<Subtype, Campaign> where [Subtype]@[sfc_campaign_subtype_id] in (-1965, -1716, -1711, -1706, -1703, -1691, -723) to Ex11_tempcube2 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 3216 Time Spent: 0.002 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM select [Campaign]@[sfc_campaign_id] from RELATION12-<Type, Campaign> where [Type]@[sfc_campaign_type_id] in (-46, -44, -42, -41, -22, -17, -14) to Ex11_tempcube3 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 5123 Time Spent: 0.001 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM select [Campaign]@[sfc_campaign_id] from RELATION11-<Unit, Campaign> where [Unit]@[marketing_budget_directory_id] in (-1, 220) to Ex11_tempcube4 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 8728 Time Spent: 0.001 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM select [Campaign]@[sfc_campaign_id], [Campaign]@[sfc_campaign_desc], [Campaign SF ID]@[campaign_sfcid], [Campaign(Link)]@[ID], [Campaign(Link)]@[FORM_1] from RELATION5-<Campaign, Campaign SF ID, Campaign(Link)> to Ex11_tempcube16 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 922 Time Spent: 0.034 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM select [Campaign]@[sfc_campaign_id], [Campaign]@[sfc_campaign_desc], [Campaign SF ID]@[campaign_sfcid], [Campaign(Link)]@[ID], [Campaign(Link)]@[FORM_1] from Ex11_tempcube16 where (<Campaign> in (Ex11_tempcube2) and <Campaign> in (Ex11_tempcube3) and <Campaign> in (Ex11_tempcube4) and (<Campaign> in (Ex11_tempcube0) or <Campaign> in (Ex11_tempcube1))) to Ex11_tempcube17 ************ Individual Step End ************ ************ Individual Step Start ************ Time Spent: 0.000 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM Drop Ex11_tempcube16 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 32 Time Spent: 0.000 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM select * from Left Outer Join( Ex11_tempcube15, Ex11_tempcube17 ) on Campaign, Campaign SF ID to Ex11_tempcube18 ************ Individual Step End ************ ************ Individual Step Start ************ Time Spent: 0.000 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM Drop Ex11_tempcube15 ************ Individual Step End ************ ************ Individual Step Start ************ Time Spent: 0.000 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM Drop Ex11_tempcube0 ************ Individual Step End ************ ************ Individual Step Start ************ Time Spent: 0.000 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM Drop Ex11_tempcube1 ************ Individual Step End ************ ************ Individual Step Start ************ Time Spent: 0.000 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM Drop Ex11_tempcube2 ************ Individual Step End ************ ************ Individual Step Start ************ Time Spent: 0.000 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM Drop Ex11_tempcube3 ************ Individual Step End ************ ************ Individual Step Start ************ Time Spent: 0.000 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM Drop Ex11_tempcube4 ************ Individual Step End ************ ************ Individual Step Start ************ Time Spent: 0.000 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM Drop Ex11_tempcube17 ************ Individual Step End ************ ************ Individual Step Start ************ Number of Rows Returned: 32 Time Spent: 0.000 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM select [Campaign(Link)]@[ID], [Campaign(Link)]@[FORM_1], sum([Lead Qualification Score])@{[Campaign(Link)]}, sum([Total Responses])@{[Campaign(Link)]} from Ex11_tempcube18 where (<Campaign> in (Ex11_tempcube12) and <Campaign> in (Ex11_tempcube13) and <Campaign> in (Ex11_tempcube14) and (<Campaign> in (Ex11_tempcube10) or <Campaign> in (Ex11_tempcube11))) ************ Individual Step End ************ ************ Individual Step Start ************ Time Spent: 0.000 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM Drop Ex11_tempcube18 ************ Individual Step End ************ ************ Individual Step Start ************ Time Spent: 0.000 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM Drop Ex11_tempcube10 ************ Individual Step End ************ ************ Individual Step Start ************ Time Spent: 0.000 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM Drop Ex11_tempcube11 ************ Individual Step End ************ ************ Individual Step Start ************ Time Spent: 0.000 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM Drop Ex11_tempcube12 ************ Individual Step End ************ ************ Individual Step Start ************ Time Spent: 0.000 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM Drop Ex11_tempcube13 ************ Individual Step End ************ ************ Individual Step Start ************ Time Spent: 0.000 sec(s) Query Execution Start Time: 2/4/2019 2:26:18 PM Query Execution End Time: 2/4/2019 2:26:18 PM Drop Ex11_tempcube14 ************ Individual Step End ************

... select [Campaign]@[sfc_campaign_id], [Campaign]@[sfc_campaign_desc], [Campaign SF ID]@[campaign_sfcid], [TempTable143.Lead Qualification Score] as [Lead Qualification Score], [TempTable144.Total Responses] as [Total Responses] from AQ.Campaigns_Marketing with Table Join Tree: TempTable143 OuterJoin TempTable144 with output level Tuple([Campaign]@[sfc_campaign_id]) Join [REL_CAMPAIGN_SF_ID_CAMPAIGN] with output level Tuple([Campaign]@[sfc_campaign_id], [Campaign SF ID]@[campaign_sfcid]) to Ex11_tempcube15 ...