This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.
TrueSight Capacity Optimization
All version of TrueSight Capacity Optimization (TSCO)
In an environment where there is already and active TSOM ETL running and properly extracting data from the TSOM database each day, what is the best way to recover a substantial amount of historical data from the TSOM database from before the first time the ETL was executed?
First, a few questions to decide the best way to proceed with the historic data extraction:
(1) How long does it take for your TSOM ETL to run to import 1 day's worth of data?
(2) How long a period are you trying to recover?
IMPORTANT: When running an ETL recovery TrueSight Capacity Optimization (TSCO) will only import data as far back as the SYS_DATA_DETAIL aging configuration setting. If it is necessary to recover historical data before that under Administration -> Data Warehouse -> Aging Configuration use the "Disable Aging" toggle to disable aging within the environment temporarily while the data from the historic recovery works its way through the Datahub. This setting will allow TSCO to import data older than the DETAIL aging.
If the execution the TSOM ETL is pretty fast then it might make sense to un schedule your regular nightly ETL, reset the Lastcounter back to the beginning of the period you want to recover, set the 'Days to Extract' to be a reasonable number of days to recover for each execution, and then re-run your ETL until you've caught up with September 10th. Then you can reset the Lastcounter back to the end of the period, reschedule the ETL, and have it run automatically to extract the new data.
This document describes that basic process:
KA# 000097275 : Historical recovery: missing data. How to perform an historical recovery (https://bmcsites.force.com/casemgmt/sc_KnowledgeArticle?sfdcid=000097275)
The other option, if the ETL takes awhile to complete extraction for a single day (and would thus run for a very long time to extract 60+ days of data) would be to create a recovery ETL that is a duplicate of your nightly new data extraction ETL and shares the lookup table the nightly ETL. Then you could execute that ETL to do the recovery while still leaving your nightly production ETL scheduled and running to extract new data.
For example, if a 1 day extraction of data takes 4 hours and you are trying to recover 3 months of historic data that means we'll need to set up a Recovery ETL to run in parallel with your daily BPPM ETL.
Here are the steps to set up that ETL and do the recovery:
The steps to do that are:
(1) Create a 'Recovery' ETL.
(1.a) Under Administration -> Scheduler -> ETL Tasks select 'Add... -> Add ETL')
(1.b) Create a copy of your original BPPM ETL. In the 'Lookup Sharing' tab make sure that you select the "SHARED - the lookup table is shared with the specified ETL task" and then select the original BPPM ETL as the "Sharing with ETL" value or if that ETL is already sharing it’s looking with another ETL select that ETL. The goal is that the Lookup of this recovery ETL _MUST_ be shared with the primary daily extraction ETL.
(1.c) Set the 'Days to Extract' value in the ETL Run Configuration to 1
(2) Execute the recovery ETL. It will extract data from the current day. The purpose of this execution is to get the appropriate 'Last Counter' table entries defined for this recovery ETL.
(3) Set your 'Aging Configuration' SYS_DATA_STAGE value back long enough to include the beginning of the time period you want to recover data for (Steps #1 and #2 from 000097275: 'Historical recovery: missing data. How to perform an historical recovery' ( https://bmcsites.force.com/casemgmt/sc_KnowledgeArticle?sfdcid=000097275). Note that you'll need to reload the Configuration in the BCO Near-real-time data warehouse after making this change [per the refernced document]).
(4) In your 'Recovery' ETL, reset the 'Last counter' values to the beginning of the time period you want to recover. (Steps #3 - #5 of 000097275). But note you will be doing this in your Recovery ETL, not in your scheduled nightly TSOM ETL.
(5) In your Recovery ETL, set the 'Days to Extract' to be an appropriate time period. Generally I would recommend starting at 2 days and then if that succeeds increase to 4 days, then if that works increase to 7 days. The goals if for the 'Days to Extract' value to be short enough that it doesn't cause a resource problem on your console and that the extraction completes.
(6) Run your Recovery ETL. It should extract historical data from the beginning of the period for 'Days to Extract' days. Repeat as necessary until all of the missed days have been recovered. You can schedule this ETL to run daily or multiple times per day to automate the recovery. But when you reach the first day of data properly imported you should unschedule it.
(7) After the historic recovery of data is complete you can reset your 'Aging Configuration' SYS_DATA_DETAIL value back to the desired detail data historical retention period (Default: 65 days)
Q: What information should be captured for debugging purposes if the recovery ETL doesn't seem to be working?
If you don't see data then send the following:
* All of the "Links to produced files" attachments from the ETL.
000032103: How to gather BCO ETL logs : https://bmcsites.force.com/casemgmt/sc_KnowledgeArticle?sfdcid=000032103
* The logGrabber output from the BCO console (since the ETL is running on the primary scheduler)
000099787: Gathering data using Log Grabber https://bmcsites.force.com/casemgmt/sc_KnowledgeArticle?sfdcid=000099787
* A screenshot of the ETL Datasets page (which lists the 'Lastcounter' values showing the 'NTSYSTEM' and its surrounding Datasets (or the 'system' data set for one of the platforms the ETL should be extracting). This information will be used to determine that the Lastcounter has been updated.
To capture the Lastcounter screenshot:
Administration -> System -> ETL Tasks -> Select the TSOM ETL -> Scroll down and show the 'Status' table showing the 'NTSYSTEM' dataset. Take a screenshot of that.