Worksheet Rollovers Delete existing data
November 17, 2012 at 10:24 pm #448
We have two separate budget models relating to different entities that use the same budget worksheet and worksheet versions. We rolled over the first model and worksheet data, then at a much later date rolled the second (related to different accounts) over. We were shocked to discover that when the second model worksheet data was rolled over, the existing worksheet data from the first model was deleted. Fortunately we had copied our production database into a test environment two days earlier so were able to contain the damage to those worksheets modified during that two day period. By running transaction reports on our budget ledger restricted to records where Post Date was either of those two days we identified the accounts related to worksheets and contacted the people effected with the bad news.
We then re-created the exact same scenario with fresh models and ledgers in our test environment and confirmed this had actually happened and would happen again. Our version is 11.09.009.August 16, 2013 at 6:21 pm #595
I guess the main thing to be aware of is how budget models and worksheets integrate and what the rollover process actually does. Since the same budget worksheet can exist in many models simultaneously (especially if you create a new model as part of the End of Year process or you have simply just made a copy for side development/backup) then the issue is understanding how data writes to the budget worksheet and what data the budget model can “see”.
In this case the worksheet name assigned within the model to the specific budget worksheet (in the Budget Worksheets/User Budget Worksheets sections of Budget Model Maintenance) AND the worksheet Version of the current Active version of the model specified in the “Budget Versions section of the model contribute to determining what data will “show up” in your budget packs.
As the budget worksheet is simply a database table at the end of the day, then these two criteria are applied to the selection of the worksheet data (so when you are looking at your 2013 budget you can see your e.g. 2013 salaries details but if you open a 2012 budget pack you will see the lines for 2012).
When you run a budget rollover you are effectively telling the system:
For every budget and user budget worksheet present in the model I am rolling over:
1) Copy all the data lines for the Current Worksheet Name and Active Version
2) Change the version number in the copied data to reflect the Version number in the Version Definition Grid (in Budget Model Maintenance) that I am rolling to
3) Delete existing data on the worksheet matching the WS_NAME (worksheet name) and WS_VERS (Worksheet Version) criteria (the worksheet version here being the version you are rolling to)
4) Re-Import the copied data by appending it to the rest of the table’s data (Insert Into SQL).
If you want the rollovers to not affect each other then you have to use different worksheet versions (or worksheet names) in your different models but that all depends on what you are trying to do and why you have multiple models pointing to the same worksheet data.
Further to this and perhaps where most of my former T1 clients had grievances is that if the worksheet contains link fields then whilst you can load the data to the budget worksheet either by the Worksheet Import Function (csv) or via the Budget Pack (xls) and a worksheet enquiry will show those lines of data exist in the database table, when you actually open a budget pack some lines may be “missing”.
This is almost always due to the fact that in the ledgers you have specified in the model, the combination of link fields do not exist (e.g. a new department code has never been transacted on, or the selection types for hierarchies have been changed without a chart posting (e.g. dept 1234 used to belong to business unit ABC and now belongs to XYZ). However in the GLF_LDG_ACCT_DESC1 table there are no account numbers from the ledgers included in the models which have DEPT 1234 associated with XYZ.
The solution is to add a new account in your new budget/forecast ledger which will inherit the hierarchy as at today, but this will not bring in your historical comparatives onto the same line.
The only way to deal with this scenario is to force a chart posting across all accounts for ALL the ledgers in the budget model to ensure they are consistent and will hence consolidate to the same line. Of course doing so loses the historical context of where that department belonged in that year.
You would probably also need to perform a “Recalculate link fields” to update existing worksheet data to match the new hierarchical structure (like a chart posting for budget worksheets).
If your link field is based on a chart component then do NOT include both the chart component and a hierarchical selection type off this chart component as link fields.
If in the example I have been using, I need the Department code to post to the GL but budgeting is actually done at a Business Unit level, then having a link field of only Department will mean that I can have packs at the department level and only my departments lines will show up OR if I have it at a Business Unit level, then all the relevant department lines will show up in my pack (as required) (I do NOT need a Business Unit link field as business unit can be determined from the department).
A lot less chance of inconsistency and having disappearing data. Of course if some account numbers used Business Unit ABC and then it got switched during the year to Business Unit XYZ (without a chart posting across all accounts) then you now have the situation where the lines show up in both ABC’s budget pack AND XYZ’s budget pack.
This potentially gives you other errors/headaches.
Hopefully that makes sense. Easier to demonstrate by actually showing the behaviour in person.
EmmanuelAugust 17, 2013 at 10:08 pm #596
Thanks for your thoughts Manny, appreciate your taking the time to share your experiences. In our situation, we had multiple models dealing with different accounts. For example Model A covering accounts where Account Component 1 = 10, and Model B where Account Component 1 = 20. When we rolled over worksheet data for Model A it should have only have deleted data where Account Component 1 = 10. This was the logical design fault which TechnologyOne have subsequently acknowledged and fixed. We did work around it in the meantime though using different worksheet version numbers.
Thanks too for your additional contributions regarding linking options, we’ve faced similar issues where the selection codes linked to components change, and where we’ve included linked selection codes in worksheet tables to facilitate reporting. Look forward to catching up.
Best Regards, Jamie
You must be logged in to reply to this topic.