-------------------------------------------------------------- -- [20:14] sink() diverted to this file, " /mnt/data/home/rsaporta//git/orch/log/MgmtReport/Automation_ETL+MGMT_LogFile_20141017_0313.logr " ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ------------------ git Pull Request ------------------ Executing git pull under user rsaporta Already up-to-date. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Reloading utils ... LOADED. Updating tables analytics -- [20:14] ---- bucket :: ' s3://dev-rsaporta/BI_Tables/analytics/20141019/analytics_ ' Running query [Began at 00:24 AM] --------------------------------------------------------------------------------------------------------------- TRUNCATE TABLE bi.analytics_new_20141019 --------------------------------------------------------------------------------------------------------------- No results were returned... exiting gracefully. -----------{ STARTING LOAD ANALYTICS }----------- -- [21:24] Cluster is 9 and DB.settings being used are: drv = PostgreSQL host = orcd-dw-prod-09.ccvpswrgleoi.us-east-1.redshift.amazonaws.com user = rsaporta password = ****** port = 5439 dbname = dev Cluster is 9 and DB.settings being used are: drv = PostgreSQL host = orcd-dw-prod-09.ccvpswrgleoi.us-east-1.redshift.amazonaws.com user = rsaporta password = ****** port = 5439 dbname = dev Populating bi.analytics_new_20141019 from bucket s3://dev-rsaporta/BI_Tables/analytics/20141019/ -- [21:24] Table bi.analytics_new_20141019 exists and has 1919736953 rows The latest dates are 2014-10-17 (download_activity_date), 2014-10-01 (download_accounting_month) Running query [Began at 01:57 AM] --------------------------------------------------------------------------------------------------------------- SELECT count(*) AS rows FROM bi.analytics_new_20141019 --------------------------------------------------------------------------------------------------------------- Dim of results is 1 rows X 1 cols Total time to execute query was 0.249 seconds validation successfull -- renaming DB Running query [Began at 01:57 AM] --------------------------------------------------------------------------------------------------------------- -- TODO: (1) change the download ringtone transactions (ask Joanna for correct value) -- TODO: (2) check for any NULL dates in either table -- TODO: (3) check for any NULL music_vs_video in either table -- DONE! (4) Check SelectO Margin - This should be different based on date -- TODO: (5) Double Check Cricket -- FROM Joanna: -- "the tethered download transactions for Muve prior to March should be listed as streams" -- "the download ringtones and ringback for muve should be under downloads of course" -- Music on storeid == 427 (Myspace Video) - how should that be considered -- Investigate this $0.90 line in DT.acc -- DT.acc[storeid == 29 & date > "2014-01-01"] -- OLD 20140923 : ---====================================================-- -- OLD 20140923 : --_____________________________________________________-- -- OLD 20140923 : -- ** THIS IS THE DIFFERENCE BETWEEN gest / gest_no_rd ** -- OLD 20140923 : --_____________________________________________________-- -- OLD 20140923 : -- if royaltydollar = 0 then use gest, -- OLD 20140923 : -- otherwise, use royaltydollar -- OLD 20140923 : UPDATE bi.analytics_new_20141019 -- OLD 20140923 : SET gest = royaltydollar -- OLD 20140923 : WHERE (NOT store_uses_royaltydollar) -- OLD 20140923 : ; -- label_is_dthree : default value is 'N' UPDATE bi.analytics_new_20141019 SET label_is_dthree = 'N' WHERE ((label_is_dthree is NULL) OR (label_is_dthree = '')) ; --3.01 ---------------------------------------------------- -- CLEAN UP MISSING VALUES -- -- (ie, label_sc_group) -- ---------------------------------------------------- -- label_sc_group : default value is 'Orchard' UPDATE bi.analytics_new_20141019 SET label_sc_group = 'Orchard' WHERE ((label_sc_group is NULL) OR (label_sc_group = '')) ; -- release_is_compilation : default value is false UPDATE bi.analytics_new_20141019 SET release_is_compilation = false WHERE (release_is_compilation is NULL) ; -- release_product_type : default value is 'zUncategorized' UPDATE bi.analytics_new_20141019 SET release_product_type = 'zUncategorized' WHERE ((release_product_type is NULL) OR (release_product_type = '')) ; ---------------------------------------------------- -- 3.02 ---------------------------------------------------- -- UPDATE gest with date ratio -- -- (ie, for incomplete months) -- -- THIS HAS TO RUN BEFORE THE MARGINS UPDATE -- ---------------------------------------------------- -- FILTER BY paidunits_expected_ratio != 1 -- THEN MULTIPLY gest := gest * paidunits_expected_ratio UPDATE bi.analytics_new_20141019 SET gest = gest * paidunits_expected_ratio WHERE (paidunits_expected_ratio != 1) ; ---------------------------------------------------- -- 3.03 ---------------------------------------------------- -- MARGINS and ROYALTY_DOLLAR FIXES -- -- (ie, Net of Supply Chain) -- ---------------------------------------------------- UPDATE bi.analytics_new_20141019 SET gest_net_of_SC = gest WHERE label_sc_group = 'Orchard' ; UPDATE bi.analytics_new_20141019 SET gest_net_of_SC = gest * 0.06 WHERE label_sc_group = 'RED' ; -- Allegro UPDATE bi.analytics_new_20141019 SET gest_net_of_SC = gest * 0.10 WHERE label_SC_group = 'Allegro' ; -- SelectO -- NOTE: SelectO Margin changed on 2014-03-01 from .12 to .14 UPDATE bi.analytics_new_20141019 SET gest_net_of_SC = gest * 0.12 WHERE label_SC_group = 'SelectO' AND download_accounting_month < '2014-03-01' ; UPDATE bi.analytics_new_20141019 SET gest_net_of_SC = gest * 0.14 WHERE label_SC_group = 'SelectO' AND download_accounting_month >= '2014-03-01' ; ---------------------------------------------------- ------------------------------------------------------------- ------------------------------------------------------------- -- 3.11 ---------------------------------------- -- UPDATE music_vs_video COLUMN -- ---------------------------------------- -- First Transac, then Track, then Product, then 'zUncategorized' UPDATE bi.analytics_new_20141019 SET music_vs_video = CASE WHEN music_vs_video_by_transac in ('Music', 'Video') THEN music_vs_video_by_transac ELSE CASE WHEN music_vs_video_by_tracktype in ('Music', 'Video') THEN music_vs_video_by_tracktype ELSE CASE WHEN music_vs_video_by_product in ('Music', 'Video') THEN music_vs_video_by_product ELSE 'zUncategorized' END -- ..by_product END -- ..by_tracktype END -- ..by_transac ; ---------------------------------------- -- 3.12 ---------------------------------------- -- UPDATING Muve -- -- should be considered Streams -- -- regardless of transaction type -- ---------------------------------------- UPDATE bi.analytics_new_20141019 SET stream_vs_download = 'Stream', transac_type_group = 'Stream' -- storeid 497 is Muve / Cricket WHERE storeid = 497 ; ---------------------------------------- ---------------------------------------- -- UPDATING YouTube -- -- music_vs_video should always -- -- be considered Video -- ---------------------------------------- -- NOTE: Only changing the actual music_vs_video -- All the rest, remain as is. UPDATE bi.analytics_new_20141019 SET music_vs_video = 'Video' -- , music_vs_video_by_transac = 'Video' -- , music_vs_video_by_tracktype = 'Video' -- , music_vs_video_by_product = 'Video' -- These are all of the YouTube like IDs in dim_store WHERE storeid in (312, 453, 569, 592, 45301, 45302, 45303) -- 463 = 'YouTube Movies' aka Google Play and does NOT get this specific treatment -- OLD 20140923 : -- WHERE store_group = 'YouTube' ; ---------------------------------------- ---------------------------------------- -- UPDATING is_store_itunesUSA -- -- identify which rows are -- -- in iTunes and in USA -- ---------------------------------------- UPDATE bi.analytics_new_20141019 SET is_store_itunesUSA = true WHERE storeid = 1 and country_code = 'US' ; ---------------------------------------- -- UNUSED. KEEP FOR REFERENCE OR IN CASE OF FUTURE CHANGES. -- --------------------------------------------- -- -- UPDATING accounting_month_adjusted -- -- -- shift it when iTunes-US-RED -- -- -- otherwise, -- -- --------------------------------------------- -- UPDATE bi.analytics_new_20141019 -- SET accounting_month_adjusted = CASE -- WHEN label_SC_group = 'RED' AND storeid = 1 and country_code = 'US' -- THEN cast(DATEADD(month, 1, download_accounting_month) as date) -- ELSE download_accounting_month -- END -- ; -- ---------------------------------------- --------------------------------------------------------------------------------------------------------------- No results were returned... exiting gracefully. renaming from bi.analytics to analytics_removed_20141019 Running query [Began at 03:10 AM] --------------------------------------------------------------------------------------------------------------- ALTER TABLE bi.analytics RENAME TO analytics_removed_20141019 --------------------------------------------------------------------------------------------------------------- renaming from bi.analytics_new_20141019 to analytics Running query [Began at 03:10 AM] --------------------------------------------------------------------------------------------------------------- ALTER TABLE bi.analytics_new_20141019 RENAME TO analytics --------------------------------------------------------------------------------------------------------------- Verifyicationg process, part 2 of 2 -- [00:10] Running query [Began at 03:10 AM] --------------------------------------------------------------------------------------------------------------- SELECT CAST(TO_CHAR (download_activity_date, 'YYYY-MM-01') AS date) AS date, NOT (gest=0 OR gest IS NULL) AS has_gest, sum(gest) AS gest, sum(freeunits) AS freeunits, sum(paidunits) AS paidunits, sum(units) AS units, count(*) AS row_count FROM bi.analytics WHERE download_activity_date >= '2013-01-01' GROUP by 1, 2 ORDER BY 2, 1 --------------------------------------------------------------------------------------------------------------- Dim of results is 44 rows X 7 cols Total time to execute query was 35.368 seconds Running query [Began at 03:11 AM] --------------------------------------------------------------------------------------------------------------- SELECT CAST(TO_CHAR (download_activity_date, 'YYYY-MM-01') AS date) AS date , SUM(freeunits) AS freeunits , SUM(paidunits) AS paidunits , SUM(units) AS units , COUNT(*) AS row_count FROM production.fact_analytics WHERE download_activity_date >= '2013-01-01' GROUP BY 1 ORDER BY 1 --------------------------------------------------------------------------------------------------------------- Dim of results is 22 rows X 5 cols Total time to execute query was 4.07 minutes Running query [Began at 03:15 AM] --------------------------------------------------------------------------------------------------------------- SELECT CAST(TO_CHAR (downloaddate, 'YYYY-MM-01') AS date) AS date , SUM(freeunits) AS freeunits , SUM(paidunits) AS paidunits , SUM(units) AS units , COUNT(*) AS row_count FROM production.fact_analytics_error WHERE downloaddate >= '2013-01-01' GROUP BY 1 ORDER BY 1 --------------------------------------------------------------------------------------------------------------- Dim of results is 22 rows X 5 cols Total time to execute query was 5.527 seconds Total rows for 2014 in fact table is 9,970,287,640 Total rows for 2014 in aggd table is 1,072,799,287 A decrease of 89.24 % Dont forget to delete old bucket and old table Running query [Began at 03:15 AM] --------------------------------------------------------------------------------------------------------------- DROP TABLE bi.analytics_removed_20141017; --------------------------------------------------------------------------------------------------------------- Table Created: bi.analytics ----------- RUNNING MgmtReport UPDATE ----------- -- [00:15] -- Sourcing 16 files -- (of 21 identifed candidates, and 21 total in folder) ** --------------------------------------------------------------------------------------------- ** ** Importing file: GL_pt0_a Workspace Setup.r ** ** --------------------------------------------------------------------------------------------- ** params are : -Xmx8g ** --------------------------------------------------------------------------------------------- ** ** Importing file: GL_pt0_b Keys Folders and Variables.r ** ** --------------------------------------------------------------------------------------------- ** Assigning new value to dropCurrentTable.manualnumbs in environment [1] TRUE Using exisitng value for skip_exportOfBudget from environment [1] FALSE Assigning new value to verbose.joinColsProcessing in environment [1] FALSE Assigning new value to verbose.showdbsettings in environment [1] TRUE Assigning new value to verbose.qry.oa in environment [1] FALSE ** --------------------------------------------------------------------------------------------- ** ** Importing file: GL_pt0_c Col Name Clean (for restoring backups).r ** ** --------------------------------------------------------------------------------------------- ** dict detected. Using keys as old_colnames and values as new_colnames ** --------------------------------------------------------------------------------------------- ** ** Importing file: GL_pt1_a Import OA from DB Querying Aggregated Tables.r ** ** --------------------------------------------------------------------------------------------- ** Will run queries using Cluster is 9 and DB.settings being used are: drv = PostgreSQL host = orcd-dw-prod-09.ccvpswrgleoi.us-east-1.redshift.amazonaws.com user = rsaporta password = ****** port = 5439 dbname = dev Error in (function (dimensions, measures, tbl, dateCol, minDate, dateCol.max = dateCol, : The following columns are missing from accounting "label_is_distributor", "label_is_red", "label_is_supplychain" Calls: source ... eval -> eval -> lapply -> FUN -> do.call -> Execution halted