In Service Analytics, 'Calculated Fields' may show incorrect or no data. Also, when in a report, the Refresh date at the top of the report may not be shown and just show a dash ' - '. |
This bug is planned to be fixed in the update/release - Summer Release - Footprints 12.1.03 However, after this bug is resolved, the following steps may still need to be taken to fix workspaces that have already encountered the problem. This can be resolved now by running two different scripts and then deleting one of the tables and initiating a full Datamart load. Also, deleting the contents of the Datamart history tables and forcing a reload. WARNING: THIS CHANGES DATA IN THE DATABASE - A FULL DATABASE BACKUP IS REQUIRED! Attached are two zip files, one for SQL and one for PostgreSQL. 1. Stop Tomcat. 2. Make Full Database backup. 3. Run the following query against the FootPrints database. (You may need to update the database/schema name, if not fpscdb001) Query File from zip: 03 Create table and display bad status defn names.sql - If this returns results, go to Step 4. - If this returns NO results. go to Step 5. 4. Run the following query against the FootPrints database. (You may need to update the database/schema name, if not fpscdb001) Query File from zip: 20 fix bad status defn names.sql - Re-run Step 3. to verify no result now. 5. Run the following query to look for any STAGE3 tables that need to be dropped: Declare @sql varchar(1000); select distinct 'drop table ' + c.TABLE_SCHEMA+'.'+c.TABLE_NAME as FullTableName from INFORMATION_SCHEMA.COLUMNS c where c.TABLE_NAME like '%_FH_STAGE3' and c.COLUMN_NAME in ('domain_obj_id','sortsequence') and IS_NULLABLE = 'YES';- If query returns results, copy the rows of results, paste into the query window, select the rows, and execute. The results will be the 'drop table' commands for the affected tables. EXAMPLE drop table fpscdb001_ws_001.issue_FH_STAGE3 drop table fpscdb001_ws_001.problem_FH_STAGE3 drop table fpscdb001_ws_001.incident_FH_STAGE3 drop table fpscdb001_ws_001.work_order_FH_STAGE3 NOTE: If no results were returned from Step 3 AND Step 5; follow the next step to force datamart rebuild 6. No matter the results of the previous steps run the following query: --FORCE THE RELOAD OF ALL DYNAMIC SCRIPTS FOR ALL ITEMS - CHANGE the DATABASE and SCHEMA TO MATCH-- DELETE FROM [fpscdb001].[fpscdb001_system].[dyn_table_script_history]; --CLEAR DATAMART LOAD HISTORY/LOCK/QUEUE-- DELETE FROM [fpscdb001].[fpscdb001_system].[data_mart_load_history]; DELETE FROM [fpscdb001].[fpscdb001_system].[data_mart_load_lock]; DELETE FROM [fpscdb001].[fpscdb001_system].[data_mart_rqst_history]; DELETE FROM [fpscdb001].[fpscdb001_system].[data_mart_rqst_queue];7. Follow the steps in this article to update the Footprints 12.1 database indexes to improve performance during the Datamart rebuild. Footprints Service Core 12 - Database Reindex Script 8. Start Tomcat. 9. Login to FootPrints. 10. Publish each workspace, one at a time. 11. Once the Datamart is rebuilt, the 'Calculated' fields should return results. Warning: The Datamart rebuild may take over an hour How to monitor when the Datamart rebuild is finished: 1. Run the the following query to view the datamart load queue: SELECT * FROM fpscdb001_system.v_data_mart_rqst_queue;- You can continue to rerun this query until it returns no results. |