Category Archives: VizTool Priority List

Dong Shin 07.08.2014

  • deployed new FA
    • Status ItemRenderer changes when scrolling in Query Builder – fixed
  • working on new Query Builder
    • refining master data table to include all appropriation years in a row instead of separated by a year
    • SELECT
      SUM(IF(year_count=1,SUM(IF(type = ‘Planned Obligated $:’, month_1, 0)),0)) AS planned_obligated_year_1_month_1,
      SUM(IF(year_count=1,SUM(IF(type = ‘Reported FACTS Obligated $:’, month_1, 0)),0)) AS reported_facts_obligated_year_1_month_1,
      SUM(IF(year_count=1,SUM(IF(type = ‘Planned Outlay $:’, month_1, 0)),0)) AS planned_outlay_year_1_month_1,
      SUM(IF(year_count=1,SUM(IF(type = ‘Reported Outlay $:’, month_1, 0)),0)) AS reported_outlay_year_1_month_1,
      SUM(IF(year_count=1,SUM(IF(type = ‘PM Actuals (Invoiced) Outlay $:’, month_1, 0)),0)) AS pm_actuals_year_1_month_1,
      SUM(IF(year_count=1,SUM(IF(type = ‘Outlay $ (Reported in FACTS)’, month_1, 0)),0)) AS reported_facts_outlay_year_1_month_1
      FROM obligations_outlays
      GROUP BY funding_id

Dong Shin 06.26.2014

  • tried to deploy the new Query Builder, the queries are too slow… 6 minutes! will try this tomorrow
    • ‘Copying to tmp table’, ‘2.432915’
      ‘Sorting result’, ‘0.899327’
      ‘Sending data’, ‘0.783034’
    • SHOW VARIABLES LIKE ‘max_heap_table_size’;
      • ‘max_heap_table_size’, ‘16777216’
    • SHOW VARIABLES LIKE ‘tmp_table_size’;
      • ‘tmp_table_size’, ‘16777216’
  • added last COGNOS upload status to FA
  • added status (Red, Yellow, Green) to Obligated, Outlay, PM Actuals % value columns in Query Builder (old)

Dong Shin 06.20.2014

  • deployed new FA with the reworked Query Builder – all is well.
  • experimented with MariaDB and some database performance testing on-site.
  • working on Query Builder
    • added Obligation, Outlay, PM Actuals columns back…. still need % values
    • required columns added to queryBuilderConfig.xml

Dong Shin 06.19.2014

  • did some performance testing of the temporary table, __view_project_detailed_data
    • data generation took 90 sec
    • query is faster
    • need Update button in Query Builder when data generation is needed
    • installed MariaDB and compared the performance
      • data generation too 20 sec
      • some error running stored procedure… may need to run – mysql_upgrade.exe -uroot -p –force 
        • #1728 - Cannot load from mysql.proc. The table is probably corrupted 
  • working on Query Builder
    • added Update Data in Query Builder to update project detailed data
    • added _table_last_update_status to keep track of tables status
    • added triggers to update the _table_last_update_status – obligations_outlays, budget_centers, committed_amount, and contracts_cognos for now
    • modified users queries to match new structure of __view_project_detailed_data

Dong Shin 06.13.2014

  • checked out projects from new SVN repo
    • builds, but cannot check in…. permission issues
    • org.apache.subversion.javahl.ClientException: Permission denied
      svn: Commit failed (details follow):
      svn: Can’t move ‘/home/vectr/svn/307519/db/txn-protorevs/3263-2iv.rev’ to ‘/home/vectr/svn/307519/db/revs/3/3264’: Permission denied

Dong Shin 06.12.2014

  • working on Query Builder
    • converted old queries to new format
    • reworked __view_total_committed_amount_by_budget_center
      • CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `__view_total_committed_amount_by_budget_center` AS select `committed_amounts`.`budget_center_id` AS `budget_center_id`,sum(`committed_amounts`.`committed_amount`) AS `total_committed_amount`,MAX(`committed_amounts`.`committed_date`) AS `max_committed_date`, group_concat(`committed_amounts`.`committed_date` separator ‘,’) AS `committed_dates` from `committed_amounts` group by `committed_amounts`.`budget_center_id`;
    • reworked __view_project_detailed_data to include obligation date and committed date
    • going through the user queries….
  • svn migration complete – trying to check out…