Redesign the database so that it effectively houses data for any month.
I would guess that without knowing the details of current DB structure it would be difficulty to give specific mitigations.
Regarding not knowing the details, that's very true - sorry! I got this database after it had been developed and am having trouble wrapping my head around it, which is why the lack of details at this point. I suppose I could write a script that saves each month's data and calculation results in a "static" table before I do the monthly update. I'll keep playing around with it. Thanks!
1 of 1 people found this helpful
Your description implies that the analysis tables are doing their jobs by using calculation fields that target the total set of Loan records, and this is the reason why you need to swap that set every month (or start with a clean slate).
1. A better way would be to create this derived data (now accessible via par force unstored calculations) via scripts that perform the same logic as the calculation fields, but store their results in that table
2. Attribute each record in the Analysis table to a certain date range, i.e. month.
That's an idea I had not thought of. Thank you!!
At the risk of being blatantly obvious, let me add …
3. Now you can store all loan records in a single table without swapping; but what you need now is to script the analysis, i.e. the creation and storage of the analysis data.
This could be as uncomplicated as transferring the calculation field definitions into a series Set Field result calculations, then re-defining the calc fields to regular fields of the appropriate data type.