Global DDS Facts Table

This fact summarizes activities & sales that have occurred with an Account. The types of activities in this fact include Call Details, Call Samples and Call Key Messages. Sales activities included are Symphony Non-Retail. It also contains all dimensional data in a single table.

Global DDS Field Type Value
activities__v numeric(15,2) Count of records in the Customer Interaction Respoistory (CIR) where end_date__v is null. -OR- Count of rows of patient_status_gbl_ods__v
qty__v numeric(15,2) Sum of Pack Units from Symphony Non-Retail Sales when populated from CSR. Quantity of samples left when populated from CIR.
nrx__v   null
trx__v   null
cost__v   null
fact_account_360_id__v varchar(1000) |dim_global_demographic__v.vid__v|dim_global_product__v.native_id__v|dim_date__v.date__v| dim_global_territory__v.vault_territory_id__v|dim_global_activity__v.dim_global_activity_id__vdim_global_channel__v.|dim_global_channel_id__v
sp_patient_status__v VARCHAR(255) Status value of the transaction
sp_patient_status_reason VARCHAR(255) Status reason for the Status value of the transaction
net_new_patients INT Number of new patients that are active/pending have no prior transactions
new_patients INT Number of new patients that had previously been discontinued/canceled/denied
lost_patients INT Number of patients who were previously active/pending but are now discontinued/cancled/denied
demographic_dim_global_demographic_id__v bigint n/a
demographic_account_name__v varchar(300) account_name__v
demographic_account_country__v varchar(200) account_country__v
demographic_address_country__v varchar(765) address_country__v
demographic_address_line_1__v varchar(240) address_line_1__v
demographic_address_line_2__v varchar(300) address_line_2__v
demographic_locality__v varchar(150) locality__v
demographic_first_name__v varchar(200) first_name__v
demographic_middle_name__v varchar(200) middle_name__v
demographic_last_name__v varchar(240) last_name__v
demographic_speciality_1__v varchar(765) speciality_1__v
demographic_credentials__v varchar(765) credentials__v
demographic_email__v varchar(240) email__v
demographic_npi__v varchar(75) npi__v
demographic_phone__v varchar(120) phone__v
demographic_postal_code__v varchar(60) postal_code__v
demographic_administrative_area__v varchar(60) administrative_area__v
demographic_vid__v varchar(765) vid__v
demographic_ispersonaccount__v boolean ispersonaccount__v
demographic_formatted_name__v varchar(500) formatted_name__v
demographic_gender__v varchar(100) gender__v
demographic_account_status__v varchar(100) account_status__v
demographic_hco_type__v varchar(100) hco_type__v
demographic_hcp_type__v varchar(100) hcp_type__v
demographic_hin__v varchar(10) hin__v
demographic_me_id__v varchar(10) me_id__v
demographic_pdrp_optout__v varchar(100) pdrp_optout__v
demographic_pdrp_optout_date__v date pdrp_optout_date__v
demographic_sha_id__v varchar(10) sha_id__v
demographic_alternate_customer_id__v varchar(100) alternate_customer_id__v
demographic_lastmodifieddate__v timestamp lastmodifieddate__v
payer_dim_global_payer_id__v bigint --
payer_payer_name__v varchar(255) payer_name__v
specialty_pharmacy_dim_global_specialty_pharmacy_id__v bigint --
specialty_pharmacy_specialty_pharmacy_dea__v VARCHAR(255) specialty_pharmacy_dea__v
specialty_pharmacy_specialty_pharmacy_id__v VARCHAR(255) specialty_pharmacy_id__v
specialty_pharmacy_specialty_pharmacy_junior_parent_name__v VARCHAR(255) specialty_pharmacy_junior_parent_name__v
specialty_pharmacy_specialty_pharmacy_name__v VARCHAR(255) specialty_pharmacy_name__v
specialty_pharmacy_specialty_pharmacy_npi__v VARCHAR(255) specialty_pharmacy_npi__v
specialty_pharmacy_specialty_pharmacy_state__v VARCHAR(255) specialty_pharmacy_state__v
specialty_pharmacy_specialty_pharmacy_zip__v VARCHAR(255) specialty_pharmacy_zip__v
product_dim_global_product_id__v bigint n/a
product_native_id__v varchar(18) |crm_product_vod_id__v|related_id__v|related_source__v|
product_global_product_name__v varchar(240) parent_product_name__vIf no dim_global_product__v.parent_product_name__v, use crm_product_gbl_ods__v.global_product_name__v
product_raw_product_name__v varchar(240) global_product_name__v
product_brand__v varchar(240) map_crm_product_gbl_ods__v.display_name__v
product_product_country__v varchar(240) map_crm_product_gbl_ods__v.country__v
product_product_type__v varchar(765) product_type__v
product_parent_product_id__v varchar(765) parent_product__v
product_parent_product_name__v varchar(765) global_product_name__v for corresponding record where parent_product__v=native_id__v (look up to same table)&end_date__v is null for lookup
product_therapeutic_area__v varchar(765) therapeutic_area__v
product_market_name__v varchar(110) map_crm_product_gbl_ods__v.market_name__v
product_market_id__v varchar(18) map_crm_product_gbl_ods__v.market_id__v
product_therapeutic_class__v varchar(765) therapeutic_class__v
product_company_product__v boolean company_product__v
product_display_in_reports__v boolean map_crm_product_gbl_ods__v.display_in_reports__v
product_last_modifed_date__v timestamp last_modifed_date__v
territory_dim_global_territory_id__v bigint  
territory_territory_name__v varchar(384) territory_name__v
territory_territory_description__v varchar(3000) territory_description__v
territory_territory_start_date__v date territory_start_date__v
territory_territory_end_date__v date territory_end_date__v
territory_vault_territory_id__v varchar(18) native_id__v
territory_parent_territory_id__v varchar(18) parent_terrtory_id__v
territory_parent_territory_name__v varchar(384) parent_territory_name__v
territory_hierarchy_level__v varchar(1280) hierarchy_level__v
territory_territory_external_id__v varchar(360) territory_external_id__v
channel_dim_global_channel_id__v bigint n/a
channel_veeva_activity_channel__v varchar (255) veeva_activity_channel__v
channel_configurable_channel__v varchar(255) configurable_channel__v
channel_vendor__v varchar(110) null
activity_dim_global_activity_id__v bigint n/a
activity_status__v varchar(765) status__v
activity_type__v varchar(765) source_table_name__v
activity_sub_type__v varchar(765) activity_type__v
activity_is_parent_activity__v numeric(18) is_parent_activity__v