CRM Dimensions
Dimension Territory Table
The dim_territory__v dimension for CRM Star Schema includes columns to show hierarchical relationships between territories. These columns allow users to build drill-downs through the territory hierarchy when examining data at a territory level.
The following new fields are available:
- territory_level_x_name__v - Hierarchical Tier X Territory Name. Listed level for each individual territory.
- dim_territory_level_x_id__v - Levels 1-10. The direct line of parent to child relationship. Level 1 is always the top parent or Corporation/CEO. Level 2 for all territories includes those who report into the Corporation/CEO territory, and so on until there are no child territories. Maximum 10 levels.
- dim_territory_parent_id__v - Parent 1-9. Shows direct relationship to the territory. Parent 1 is the direct parent of each territory. Parent 2 is then two levels above that and so on. Maximum nine parents.
Dimension Product Table
The dim_product__v dimension for CRM Star Schema includes fields that display hierarchical relationships between products (product_name__v). This allows users to view analytics at a parent (Detail) product level as well as the child (Sample) level. Six direct line levels and five parent levels are included.
| Table | Field Name | CRM Location |
|---|---|---|
| dim_account__v | crm_account_id__v | account.id |
| dim_account__v | account_name__v | account.name |
| dim_account__v | kol?__v | account.kol?_vod__c |
| dim_account__v | specialty_1__v | account.specialty_1_vod__c |
| dim_account__v | credentials__v | account.credentials_vod__c |
| dim_account__v | restricted_products__v | account.restricted_product_vod__c |
| dim_account__v | country_code__v | |
| dim_account__v | account_record_type__v | account.recordtype |
| dim_product__v | crm_product_id__v | product_vod__c.id |
| dim_product__v | product_level_1_name__v | |
| dim_product__v | product_level_2_name__v | |
| dim_product__v | product_level_3_name__v | |
| dim_product__v | product_level_4_name__v | |
| dim_product__v | product_level_5_name__v | |
| dim_product__v | product_level_6_name__v | |
| dim_product__v | product_name__v | product_vod__c.name |
| dim_product__v | product_parent_1_name__v | |
| dim_product__v | product_parent_2_name__v | |
| dim_product__v | product_parent_3_name__v | |
| dim_product__v | product_parent_4_name__v | |
| dim_product__v | product_parent_5_name__v | |
| dim_product__v | product_type__v | product_vod__c.product_type_vod__c |
| dim_product__v | therapeutic_area__v | product_vod__c.therapeutic_area_vod__c |
| dim_product__v | therapeutic_class__v | product_vod__c.therapeutic_class_vod__c |
| dim_product__v | restricted__v | product_vod__c.restricted_vod__c |
| dim_product__v | schedule__v | product_vod__c.schedule_vod__c |
| dim_product__v | company_product__v | product_vod__c.compan_product_vod__c |
| dim_product__v | sample_u_m__v | product_vod__c.samep_u_m_vod__c |
| dim_user__v | crm_user_id__v | user.id |
| dim_user__v | profile__v | user.profileid |
| dim_user__v | profile_name__v | user.name |
| dim_crm_call_discussion__v | crm_call_discussion_id__v | call2_discussion_vod__c.id |
| dim_crm_call_discussion__v | activity__v | call2_discussion_vod__c.activity__c |
| dim_crm_call_discussion__v | attendee_type__v | call2_discussion_vod__c.attendee_type_vod__c |
| dim_crm_call_discussion__v | detail_group__v | call2_discussion_vod__c.detail_group_vod__c |
| dim_crm_call_discussion__v | discussion_topics__v | call2_discussion_vod__c.discussion_topics__c |
| dim_crm_call_discussion__v | indication__v | call2_discussion_vod__c.indication__c |
| dim_crm_call_discussion__v | call_discussion_record_type__v | call2_discussion_vod__c.recordtypeid |
| dim_crm_sample__v | crm_call_sample_id__v | call2_sample_vod__c.id |
| dim_crm_sample__v | amount__v | call2_sample_vod__c.quantity_vod__c |
| dim_crm_sample__v | delivery_status__v | call2_sample_vod__c.delivery_status_vod__c |
| dim_crm_sample__v | limit_applied__v | call2_sample_vod__c.limit_applied_vod__c |
| dim_crm_sample__v | manufacturer__v | call2_sample_vod__c.manufacturer_vod__c |
| dim_crm_call_detail__v | crm_call_detail_id__v | call2_call_detail_vod__c.id |
| dim_crm_call_detail__v | detail_group__v | call2_call_detail_vod__c.detail_group_vod__c |
| dim_crm_call_detail__v | type__v | call2_call_detail_vod__c.type__v |
| dim_territory__v | account_access_level__v | territory.Account_Access_Level__V |
| dim_territory__v | crm_territory_id__v | territory.Crm_Territory_Id__V |
| dim_territory__v | description__v | territory.Description__V |
| dim_territory__v | developer_name__v | territory.Developer_Name__V |
| dim_territory__v | dim_territory_id__v | territory.Dim_Territory_Id__V |
| dim_territory__v | last_modified_by_id__v | territory.Last_Modified_By_Id__V |
| dim_territory__v | master_align_id_vod__v | territory.Master_Align_Id_Vod__V |
| dim_territory__v | territory_name__v | territory.territory_name__v |
| dim_territory__v | parent_territory_name__v | |
| dim_territory__v | territory_level_1_name__v | |
| dim_territory__v | territory_level_2_name__v | |
| dim_territory__v | territory_level_3_name__v | |
| dim_territory__v | territory_level_4_name__v | |
| dim_territory__v | territory_level_5_name__v | |
| dim_territory__v | territory_level_6_name__v | |
| dim_territory__v | territory_level_7_name__v | |
| dim_territory__v | territory_level_8_name__v | |
| dim_territory__v | territory_level_9_name__v | |
| dim_territory__v | territory_level_10_name__v | |
| dim_territory__v | territory_parent_2_name__v | |
| dim_territory__v | territory_parent_3_name__v | |
| dim_territory__v | territory_parent_4_name__v | |
| dim_territory__v | territory_parent_5_name__v | |
| dim_territory__v | territory_parent_6_name__v | |
| dim_territory__v | territory_parent_7_name__v | |
| dim_territory__v | territory_parent_8_name__v | |
| dim_territory__v | territory_parent_9_name__v | |
| dim_territory__v | territory_parent_10_name__v | |
| dim_date__v | activitydate__v | |
| dim_date__v | claendar_month_end_date__v | |
| dim_date__v | date__v | |
| dim_date__v | day__v | |
| dim_date__v | day_in_year__v | |
| dim_date__v | day_name__v | |
| dim_date__v | half_of_year__v | |
| dim_date__v | id__v | |
| dim_date__v | month__v | |
| dim_date__v | month_name__v | |
| dim_date__v | quarter__v | |
| dim_date__v | quarter_begin_date__v | |
| dim_date__v | rolling_month_number__v | |
| dim_date__v | rolling_week_number__v | |
| dim_date__v | rolling_quarter_number__v | |
| dim_date__v | rolling_year_number__v | |
| dim_date__v | rolling_semester_number__v | |
| dim_date__v | sales_week_ending_friday__v | |
| dim_date__v | sales_month_num__v | |
| dim_date__v | sales_week_num__v | |
| dim_date__v | week__v | |
| dim_date__v | week_begin_date__v | |
| dim_date__v | weekend_flag__v | |
| dim_date__v | year__v | |
| dim_channel__v | channel__v | Hard coded based on Veeva CRM definition |
| dim_channel__v | configurable_channel__v | Based on target_value__v in nitro_reference_data__v.configurable_channel__v |
| dim_crm_call__v | crm_call2_id__v | call2_vod__c.id |
| dim_crm_call__v | status__v | call2_vod__c.status_vod__c |
| dim_crm_call__v | call_type__v | call2_vod__c.call_type_vod__c |
| dim_crm_call__v | is_parent_call__v | call2_vod__c.is_parent_call_vod__c |
| dim_crm_content__v | key_message_id__v | key_message_vod__c.id |
| dim_crm_content__v | clm_key_message__v | key_message_vod__c.name |
| dim_crm_content__v | clm_presentation__v | clm_presentation_vod__c.name |
| dim_crm_content__v | crm_presentation_id__v | call2_key_message_vod__c.presetnation_vod_id__c |
| dim_crm_content__v | crm_approved_document_id__v | approved_document_vod__c.id |
| dim_crm_content__v | approved_document__v | approved_document_vod__c.name |
