The Calculated Field's within Service Analytics uses calculated data that is stored in the Datamart. Occasionally the data for Calculated fields can stop displaying and the datamart may need to be rebuilt. Below are steps that explain how to do this:
This problem was addressed in Footprints 12.1.08 or higher. The incremental datamart load is updating all records as expected.
The problem occurs on the "INCREMENTAL" load for the data mart. It does not occur in the "FULL LOAD". The work around is to periodically force a "FULL LOAD" to occur.
The easiest way to force a "FULL LOAD":
1. Go to the Administration>System Management>Work Schedules page
2. Edit the work schedule used by the problem Workspace container. Be aware, this may affect multiple workspaces or containers
3. Change the starting hour for "Monday", save the change
4. IMMEDIATELY change the starting hour back, and save
5. After sufficient tome to allow the full loads to complete, all calculations will be correct in the data mart, and the Age of Ticket will be correct.
6. Tickets closed after the workaround may reset their ages, at which point the workaround steps can be repeated.
Updating the database is only required if there are errors in the Footprints log about the datamart!
WARNING: THIS CHANGES DATA IN THE DATABASE - A FULL DATABASE BACKUP IS REQUIRED!
1. Stop Tomcat.
2. Make Full Database backup.
3. No matter the results of the previous steps run the following query:
--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];
4. 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
5. Start Tomcat.
6. Login to FootPrints.
7. Publish each workspace, one at a time.
8. 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.