We will require the capability to delete the data for an individual month's measure within the Measure Loader screen of PMF. We currently load data for a single month at a time into our QA system and then have our business process owners review the data for accuracy prior to loading it into production. This month, we had a case where a measure was loaded in QA and then had to be reloaded due to the need for refined filtering. Records that should have been removed due to the new filtering were not eliminated from the PMF measure during the reload, but all data still in the measure that was included were updated properly. This caused a data integrity issue which needed to be resolved by restoring that measure to it's pre-load state from our production copy of PMF. Has anyone else experienced this issue? Is there a capability either within PMF or an outside utility to clear data out of the PMF database for a selected time period so that new data can be accurately reloaded? Thanks in advance for your help.

Mike--

The measure loader does has the ability to selectively change or destroy measures - on the Measure Loader Filters tab, you can specify a Filter and just indicate which point in time you want to refresh the data. This effectively applies a filter on the source data before it is drawn into the loader, so you'd apply a filter on the source data's year and month columns. When you then run the loader, it pinpoints only the source rows and overwrites only the ones for that month.

This requires understanding of the actions in the Load: dropdown on the Measure Loader, so I'll go into some detail.

There are two possible situations where you'd want to change a period of data, and I'll cover them both.

If the dimensionality was correct for the month of data you want to delete, you would set the Load: dropdown in the measure loader to Add New/Replace Actual Target, then set the filter to include ONLY the month you want to fix, and then run the load. Lastly you would then change the load back to the way it was.

If the dimensionality for some reason was incorrect (say, you added new dimension level values but messed up the keys in the source data so that measures were linked to the wrong place, or set the measure to link to the wrong dimensional level just for that month) and you thus wanted to wipe out just that month and refresh it, you'd do it like this:

first pass:

Set the filter to LEAVE OUT the month you want to replace (e.g., set the filter to NE for that month) and set the Load: dropdown in the measure loader to Add new, replace act/targ, delete old, then save and run the load. This destroys that bad month of data.

second pass:

Set the filter to INCLUDE just the month you want to replace (e.g., set the filter to EQ for that month) and set the Load: dropdown in the measure loader to Add New/Replace Actual Target, then save and run the load. This adds back the good month of data. Lastly you would then change the load back to the way it was.

Hope this helps!

Bob,

Your solution works very well assuming that all the data is in one file. Unfortunately that isn't the case.

In Mike's case, he generates a new file each month. So in January we load 3 records a, b and c. In February we load records d and e but we shouldn't have loaded record e. There is no easy way to reload February with just record d and delete only record e leaving records a,b and c. The suggestion is to have a delete function that will delete specific time dimension records. Then we could delete all of February only and reload.

Open to suggestions.

The wizards are designed to handle 99.9% of the situations but not to handle that squeaky .1%. Our competition doesn't even handle the 99% without complex ETL. We usually spare you.

But...

If you have such a complex situation as you suggest - where no doubt what is loaded in is already the result of a complex hand-written program logic - then you'll want to customize the way the data is loaded in as well. For those we'd recommend you write a custom job to handle this deletion. For that you could use WebFOCUS ETL or another method. Don't forget you're dealing with WebFOCUS here.

It's stright-forward enough for example to write a MODIFY or MAINTAIN job that deletes all records in a series at a particular time intersection. We're talking a 4-line MODIFY or SQL transaction to perform the deletion - select the records that match a particular intersection of time dimension and measure series, and delete them.

Yes, writing our own delete routines is definitely an option. In order to maintain database integrity, would the measures and Measures_history tables be the only tables effected?

Just as a point of clarification, the issue we had was not really complex. We had a series of data loaded that had a future date (i.e. May 2007 was loaded with a May 2008 date by mistake) during our initial QA system load. When we went to verify May 2007, we saw no records, so reloaded with the right date. Then when May 2008 rolled around, we already had those old records in the database and they needed to be cleared out. Although I hope we don't do this often, I can see this scenario happening from time-to-time (maybe 1 in 10) in a QA environment.

Janice, in PMF 4, the affected tables are measures, feedback, and measures_history.

Mike, thanks for the information. We'll keep an eye on this.