- dimension: internet_cable_normalized_transtype view_label: Transaction label: Internet Cable Normalized Transtype description: 'Used by Finance and Film/TV to determine when Advances should be paid out' case_sensitive: FALSE suggestable: FALSE sql: | CASE when ${TABLE}.store_name like '%Cable VOD%' and ${TABLE}.transac_type_abbr = 'DV' then 'cEST' when ${TABLE}.store_name not like '%Cable VOD%' and ${TABLE}.transac_type_abbr = 'DV' then 'iEST' when ${TABLE}.store_name like '%Cable VOD%' and ${TABLE}.transac_type_abbr = 'VR' then 'cVOD' when ${TABLE}.store_name not like '%Cable VOD%' and ${TABLE}.transac_type_abbr = 'VR' then 'iVOD' when ${TABLE}.transac_type_abbr = 'AV' then 'AVOD' when ${TABLE}.transac_type_abbr = 'TH' then 'Theatrical' when ${TABLE}.transac_type_abbr = 'PH' then 'Physical' when ${TABLE}.transac_type_abbr = 'TL' then 'Netflix/PayTV' when ${TABLE}.transac_type_abbr = 'NT' then 'Non-Theatrical' when ${TABLE}.transac_type_abbr = 'SV' then 'SVOD' END type: string - dimension: rolled_up_normalized_transtype view_label: Transaction label: Rolled Up Normalized Transtype description: 'Used by Finance and Film/TV to determine when Advances should be paid out' case_sensitive: FALSE suggestable: FALSE sql: | CASE when ${TABLE}.transac_type_abbr = 'DV' then 'EST' when ${TABLE}.transac_type_abbr = 'AV' then 'AVOD' when ${TABLE}.transac_type_abbr = 'TH' then 'Theatrical' when ${TABLE}.transac_type_abbr = 'PH' then 'Physical' when ${TABLE}.transac_type_abbr = 'TL' then 'Netflix/PayTV' when ${TABLE}.transac_type_abbr = 'NT' then 'Non-Theatrical' when ${TABLE}.transac_type_abbr = 'SV' then 'SVOD' when ${TABLE}.transac_type_abbr = 'VR' then 'VOD' END type: string - dimension: film_tv_vs_music_by_product view_label: Bucket label: Music vs Film/TV by Product Type description: 'Music (includes Web videos) vs Film/TV' sql_case: 'Music': ${TABLE}.release_product_type in ('Music','Web Videos') 'Film/TV': ${TABLE}.release_product_type in ('Movie', 'TV Show') else: 'Other' type: string - dimension: label_is_red view_label: Label label: Is Red description: 'Filters out RED labels' sql: | ${TABLE}.label_ownerid = 113 type: yesno - dimension: last_12_months_activity view_label: Date label: Last 12 Months of Activity description: 'Last 12 Months of Activity and 12 Months Prior' sql: | CASE WHEN activity_month >= '2015-03-01' and ${TABLE}.activity_month <'2016-03-01' THEN 'FY2016' WHEN activity_month >= '2014-03-01' and ${TABLE}.activity_month <'2015-03-01' THEN 'FY2015' ELSE 'Rest of activity' END type: string - dimension: sme_affiliate_territory_groupings view_label: Territory label: SME Affiliates - Specified Territories description: 'Territory Groupings based on SME Offices around the World' sql_case: 'Argentina' : ${TABLE}.country_code in ('AR','PY','BO','UY') 'Australia' : ${TABLE}.country_code in ('AU','VU','MP','SB','FM','TO','TL','NU','CK','NR','KI','NF','TV','CX') 'Austria' : ${TABLE}.country_code in ('AT') 'Belgium' : ${TABLE}.country_code in ('BE','LU') 'Brazil' : ${TABLE}.country_code in ('BR') 'Canada' : ${TABLE}.country_code in ('CA') 'Chile' : ${TABLE}.country_code in ('CL') 'China' : ${TABLE}.country_code in ('CN') 'Colombia' : ${TABLE}.country_code in ('CO','PE','EC') 'Costa Rica' : ${TABLE}.country_code in ('GT','CR','SV','DO','HN','PA','NI','TT','BM','BS','KY','BB','JM','BZ','VG','AG','KN','GD','VI','DM','AI','HT','AW','SR','TC','LC','GY','VC','CU','MS') 'Czech Republic' : ${TABLE}.country_code in ('CZ','SK') 'Denmark' : ${TABLE}.country_code in ('DK','IS','FO') 'Finland' : ${TABLE}.country_code in ('FI','LV','LT','EE','AX') 'France' : ${TABLE}.country_code in ('FR','DZ','MA','TN','AD','MC','RE','NC','MQ','GP','PF','GF','YT','PM','WF') 'Germany' : ${TABLE}.country_code in ('DE') 'Great Britain' : ${TABLE}.country_code in ('GB','JE','IM','GI','GG') 'Greece' : ${TABLE}.country_code in ('GR') 'Hong Kong' : ${TABLE}.country_code in ('HK','VN','MO','FJ','MM','MH','IO','PW') 'Hungary' : ${TABLE}.country_code in ('HU') 'India' : ${TABLE}.country_code in ('IN','LK','PK','BD','NP','MV','BT') 'Indonesia' : ${TABLE}.country_code in ('ID','PG') 'Ireland' : ${TABLE}.country_code in ('IE') 'Italy' : ${TABLE}.country_code in ('IT','MT','SM','VA') 'Japan' : ${TABLE}.country_code in ('JP') 'Malaysia' : ${TABLE}.country_code in ('MY','BN') 'Mexico' : ${TABLE}.country_code in ('MX') 'Middle East' : ${TABLE}.country_code in ('SA','AE','EG','KW','QA','JO','OM','BH','LB','IQ','YE','IR','PS','AF','SY') 'Netherlands' : ${TABLE}.country_code in ('NL','AN') 'New Zealand' : ${TABLE}.country_code in ('NZ','WS') 'Norway' : ${TABLE}.country_code in ('NO','SJ') 'Other Europe Regional' : ${TABLE}.country_code in ('IL','RO','CS','BG','HR','CY','SI','BA','AL','MK','GL') 'Philippines' : ${TABLE}.country_code in ('PH') 'Poland' : ${TABLE}.country_code in ('PL') 'Portugal' : ${TABLE}.country_code in ('PT') 'Rep. Korea' : ${TABLE}.country_code in ('KR','KP') 'Russia' : ${TABLE}.country_code in ('RU','UA','KZ','BY','AZ','MD','AM','UZ','TM','KG','MN','TJ','GE') 'Singapore' : ${TABLE}.country_code in ('SG','KH','LA') 'South Africa' : ${TABLE}.country_code in ('ZA','NG','KE','TZ','MZ','NA','GH','MU','UG','BW','ZW','SN','LY','CI','CV','BF','AO','MG','CM','NE','GA','CG','ZM','SZ','ML','CD','TG','GM','RW','BJ','GN','DJ','SC','MR','ET','SD','SO','GQ','MW','SL','BI','KM','CF','TD','LS','ST','LR','GW','SH','ER','EH') 'Spain' : ${TABLE}.country_code in ('ES') 'Sweden' : ${TABLE}.country_code in ('SE') 'Switzerland' : ${TABLE}.country_code in ('CH','LI') 'Taiwan' : ${TABLE}.country_code in ('TW') 'Thailand' : ${TABLE}.country_code in ('TH') 'Turkey' : ${TABLE}.country_code in ('TR') 'USA' : ${TABLE}.country_code in ('US','PR','GU','AS') 'Venezuela' : ${TABLE}.country_code in ('VE') else: 'ROW' type: string - dimension: usa_uk_fr_gsa_nordics_row view_label: Territory label: USA, UK, France, GSA, Nordics or Row description: 'Is the Country USA, UK, France, GSA, Nordics or Row' sql_case: 'USA' : ${TABLE}.countryid = 1 'UK' : ${TABLE}.countryid = 3 'France' : ${TABLE}.countryid = 6 'GSA' : ${TABLE}.countryid in (4,11,16) 'Nordics' : ${TABLE}.countryid in (5,71,59,85,133) else: 'ROW' type: string - dimension: usa_br_mx_es_ar_uk_row view_label: Territory label: USA, Brazil, Mexico, Spain, Argentina, UK, ROW description: 'Is the Country USA, Brazil, Mexico, Spain, Argentina, UK, ROW' sql_case: 'USA' : ${TABLE}.countryid = 1 'Brazil' : ${TABLE}.countryid = 13 'Mexico' : ${TABLE}.countryid = 7 'Spain' : ${TABLE}.countryid = 15 'Argentina' : ${TABLE}.countryid = 23 'UK' : ${TABLE}.countryid = 3 else: 'ROW' type: string - dimension: usa_br_mx_se_es_uk_row view_label: Territory label: USA, Brazil, Mexico, Sweden, Spain, UK, ROW description: 'Is the Country USA, Brazil, Mexico, Sweden, Spain, UK, ROW' sql_case: 'USA' : ${TABLE}.countryid = 1 'Brazil' : ${TABLE}.countryid = 13 'Mexico' : ${TABLE}.countryid = 7 'Sweden' : ${TABLE}.countryid = 5 'Spain' : ${TABLE}.countryid = 15 'UK' : ${TABLE}.countryid = 3 else: 'ROW' type: string - dimension: usa_br_mx_tr_es_uk_row view_label: Territory label: USA, Brazil, Mexico, Turkey, Spain, UK, ROW description: 'Is the Country USA, Brazil, Mexico, Turkey, Spain, UK, ROW' sql_case: 'USA' : ${TABLE}.countryid = 1 'Brazil' : ${TABLE}.countryid = 13 'Mexico' : ${TABLE}.countryid = 7 'Spain' : ${TABLE}.countryid = 15 'Turkey' : ${TABLE}.countryid = 176 'UK' : ${TABLE}.countryid = 3 else: 'ROW' type: string - dimension: label_is_som_livre view_label: Label label: Som Livre or Other description: 'Som Livre or Other' sql_case: 'Som Livre' : ${TABLE}.labelid = 21786 else : 'Other' type: string - dimension: main_portuguese_labels_or_other view_label: Label label: Main Portuguese Labels or Other description: 'Portuguese Main Labels or Other' sql_case: 'Vidisco' : ${TABLE}.labelid = 11054 'Farol' : ${TABLE}.labelid = 11055 'Fantasy Day Unipessoal' : ${TABLE}.labelid = 11002 'Espacial' : ${TABLE}.labelid = 11249 'Ovaçao' : ${TABLE}.labelid = 10893 else : 'Other' type: string - dimension: au_or_row view_label: Territory label: AU or ROW description: 'AU or Row' sql_case: 'AU' : ${TABLE}.country_code = 'AU' else : 'ROW' type: string - dimension: eu_or_row view_label: Territory label: EU or ROW description: 'Is the Continent EU or ROW' sql_case: 'Europe' : ${TABLE}.continent = 'Europe' else : 'ROW' type: string - dimension: youtube_other view_label: Store label: YouTube or Other description: 'YouTube or Other' sql_case: 'YT Audio': ${TABLE}.storeid = 453 and ${TABLE}.transac_type_abbr = 'AS' 'YT Video': ${TABLE}.storeid = 453 and ${TABLE}.transac_type_abbr = 'AV' else: 'Others' type: string - dimension: release_genre_subgenre view_label: Release label: Genre (w/Subgenre) description: 'Genre with subgenre in parens' sql: ${TABLE}.release_genre type: string html: | {% if release_subgenre._value == blank %} {{ value }} {% else %} {{ value }} ({{ release_subgenre._value }}) {% endif %} - dimension: release_genre_classical view_label: Release label: Genre is Classical description: 'Genre with subgenre in parens' sql_case: 'Classical': ${TABLE}.release_genre = 'Classical' else: 'Other' type: string - dimension: release_genre_jazz view_label: Release label: Genre is Jazz description: 'Genre with subgenre in parens' sql_case: 'Jazz': ${TABLE}.release_genre = 'Jazz' else: 'Other' type: string - dimension: release_genre_world_music view_label: Release label: Genre is World Music description: 'Genre with subgenre in parens' sql_case: 'World Music': ${TABLE}.release_genre = 'World Music' else: 'Other' type: string - dimension: release_genre_latin view_label: Release label: Genre is Latin description: 'Genre with subgenre in parens' sql_case: 'Latin Music': ${TABLE}.release_genre = 'Latin Music' else: 'Other' type: string - dimension: gross_revenue_usd_is_neg view_label: Sales USD label: Gross Revenue USD is Negative description: 'is gross_revenue_usd negative' sql: ${TABLE}.gross_revenue_usd < 0 type: yesno - dimension: crossed_music_vs_viedo view_label: Bucket label: is Product and Transac Type Crossed? description: '' sql: ${music_vs_video_by_product} != ${music_vs_video_by_transac} type: yesno - dimension: label_client_manager_group view_label: Label label: Client Manager Group description: '' sql: | CASE WHEN ${TABLE}.label_client_manager in ('Jeremy Rosen','Jennifer Schwartz','Andrew Tell','Doug Shineman','Ben Markowitz','Monisha Gandhi','Maggie Leyden','Abraham Schwartz','Unassigned','Marc Cuevas') THEN 'Film Team' ELSE 'Music' END type: string - dimension: store_name_separating_am view_label: Store label: Name Separating AM description: '' hidden: true case_sensitive: FALSE suggestable: FALSE suggest_explore: bi_store_view suggest_dimension: bi_store_view.store_name sql: | CASE WHEN ${TABLE}.storeid = 1 AND ${TABLE}.transac_typeid = 1 THEN 'Apple Music' WHEN ${TABLE}.storeid = 1 THEN 'iTunes' ELSE ${TABLE}.store_name END type: string - dimension: itunes_applemusic_spotify_youtube_other view_label: Store label: Is iTunes, Apple Music, Spotify, YouTube, or Other description: 'Has four values: iTunes, Spotify, YouTube and All Others; YouTube includes Subscriptions (453 & 569)' sql_case: 'iTunes' : ${store_name_separating_am} = 'iTunes' 'Apple Music' : ${store_name_separating_am} = 'Apple Music' 'Spotify' : ${TABLE}.storeid = 286 'YouTube' : ${TABLE}.storeid in (453, 569) else: 'All Others' type: string - dimension: genre_childrens_rock_classical_country_jazz_other view_label: Release label: Is Kids, Rock, Classical, Country, Jazz or Other description: 'Has six values: Kids, Rock, Classical, Country, Jazz or Other' sql_case: 'Kids' : ${TABLE}.release_genre = 'Children''s' 'Rock' : ${TABLE}.release_genre = 'Rock' 'Classical' : ${TABLE}.release_genre = 'Classical' 'Country' : ${TABLE}.release_genre = 'Country' 'Jazz' : ${TABLE}.release_genre = 'Jazz' else: 'All Others' type: string - dimension: itunes_spotify_deezer_rhapsody_amazon_google_other view_label: Store label: Is iTunes, Spotify, Deezer, Rhapsody, Amazon, Google, or Other description: 'Rhapsody is both Rhapsody + Napster' sql_case: 'iTunes/Apple' : ${TABLE}.storeid = 1 'Spotify' : ${TABLE}.storeid = 286 'Amazon' : ${TABLE}.storeid = 187 'Google Play' : ${TABLE}.storeid = 496 'Deezer' : ${TABLE}.storeid = 348 'Rhapsody' : ${TABLE}.storeid in (4, 2) else: 'All Others' type: string - dimension: itunes_spotify_deezer_rhapsody_amazon_google_youtube_other view_label: Store label: Is iTunes, Spotify, Deezer, Rhapsody, Amazon, Google, YouTube, or Other description: 'Rhapsody is both Rhapsody + Napster' sql_case: 'iTunes/Apple' : ${TABLE}.storeid = 1 'Spotify' : ${TABLE}.storeid = 286 'Amazon' : ${TABLE}.storeid = 187 'Google Play' : ${TABLE}.storeid = 496 'Deezer' : ${TABLE}.storeid = 348 'Rhapsody' : ${TABLE}.storeid in (4, 2) 'YouTube' : ${TABLE}.storeid = 453 else: 'All Others' type: string - dimension: date_added_front_mid_or_cat view_label: Release label: Months since Date Added description: '' alias: date_added_cat_mid_front type: tier tiers: [1,6,12,36] style: interval sql: (CASE WHEN activity_month < release_date_added THEN 0 ELSE DateDiff(month, release_date_added, activity_month) END) + 1 value_format: '#,##0' - dimension: new_releases_during_this_year view_label: Date label: 'New Releases During This Year' description: '' sql_case: 'New Releases': ${TABLE}.release_front_mid_or_cat = 'Frontline' else: 'Catalog' type: string - dimension: germany_or_row view_label: Territory label: Germany or Row description: 'Is the Country DE or ROW' hidden: TRUE sql_case: 'Germany': ${TABLE}.countryid = 4 else: 'ex-Germany' type: string - dimension: southamerica_plus_mexico view_label: Territory label: South America + Mexico (Y/N) hidden: false sql_case: 'Yes' : ${TABLE}.country_code in ('CL', 'SR', 'GY', 'FK', 'UY', 'AR', 'GF', 'PY', 'VE', 'PE', 'CO', 'EC', 'BO', 'BR', 'MX') else: 'No' type: string - dimension: southamerica_plus_mexico_by_county view_label: Territory label: South America + Mexico (by Country) hidden: false sql_case: 'LATAM' : ${TABLE}.country_code in ('CL', 'SR', 'GY', 'FK', 'UY', 'AR', 'GF', 'PY', 'VE', 'PE', 'CO', 'EC', 'BO') 'Brazil' : ${TABLE}.country_code in ('BR') 'Mexico' : ${TABLE}.country_code in ('MX') else: 'No' type: string - dimension: country_name_or_row view_label: Territory label: Country Name or ROW description: 'Country Name or ROW' hidden: false sql_case: 'USA': ${TABLE}.countryid = 1 'Brazil': ${TABLE}.countryid = 13 'Russia': ${TABLE}.countryid = 146 'United Kingdom': ${TABLE}.countryid = 3 'Japan': ${TABLE}.countryid = 9 'Canada': ${TABLE}.countryid = 2 'Mexico': ${TABLE}.countryid = 10 'Germany': ${TABLE}.countryid = 4 'France': ${TABLE}.countryid = 6 'Australia': ${TABLE}.countryid = 8 'Spain': ${TABLE}.countryid = 15 'India': ${TABLE}.countryid = 86 'Italy': ${TABLE}.countryid = 12 'Colombia': ${TABLE}.countryid = 50 'Chile': ${TABLE}.countryid = 48 'Netherlands': ${TABLE}.countryid = 128 else: 'ROW' type: string - dimension: spain_or_latam_or_row view_label: Territory label: Label Country is Spain or LATAM (modified) or ROW description: 'Label Country is Spain or LATAM (modified) or ROW' hidden: false sql_case: 'Spain': ${TABLE}.label_country = 'Spain' 'LATAM': ${TABLE}.label_country in ('Argentina','Chile','Colombia','Mexico','Cuba') else: 'ROW' type: string - measure: percent_of_total view_label: Territory label: Percent of Total Units description: 'percent_of_streams_by_territory' type: percent_of_total sql: ${bi_accounting.units} decimals: 2 - dimension: is_above_thresh view_label: Territory label: Is Above Thresh description: 'Percent of streams > 3' hidden: False sql_case: 'Yes': ${percent_of_total} >= 0.03 else: 'No' type: string - dimension: transac_group_for_units view_label: Transaction label: Transaction Group for Units hidden: false sql_case: 'Download Albums': ${TABLE}.transac_type_abbr in ('DA','UA') 'Download Tracks': ${TABLE}.transac_type_abbr in ('DT','UT') 'Streams': ${TABLE}.transac_type_abbr in ('VS','TA','AEA','AS','CA','MV','US','SE','CV','SV','VU','CL','TD','TR','S','NR','TL','AEV','NS','AV','CAV') 'Other Downloads': ${TABLE}.transac_type_abbr in ('RB','PR','DR','RV','VR','DV','VB','UV','TH','PH','RD') else: 'Unknown' type: string - dimension: sme_transaction_types view_label: Transaction label: SME Transaction Types hidden: false sql_case: 'Downloads': ${TABLE}.transac_type_abbr in ('DA','UA','DT','UT','RB','PR','DR','RV','VR','DV','VB','UV','RD') 'Streams': ${TABLE}.storeid != 453 and ${TABLE}.transac_type_abbr in ('VS','TA','AEA','AS','CA','MV','US','SE','CV','SV','VU','CL','TD','TR','S','NR','TL','AEV','NS','AV','CAV') 'YouTube': ${TABLE}.storeid = 453 and ${TABLE}.transac_type_abbr in ('AS','AV') else: 'Other' type: string - measure: units_cat view_label: Sales label: Units Cat description: '' sql: ${TABLE}.units hidden: true type: number decimals: 0 value_format: '[>=1000000]#,##0.0,," M"; #,###;' - measure: simple_effective_units view_label: Sales type: sum sql: | case when ${stream_vs_download} = 'Stream' then ${units_cat} / 150 else ${units_cat} end value_format: '[>=1000000]#,##0.0,," M"; #,###;'