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.
fact_account360_denorm_gbl_DDS__v
| 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 |
