Forecaster 7 and the amazing expanding Z_RAW table

It’s been a while since I blogged simply because we are so busy at the moment. GP2010 is flying off the shelves in a very satisfactory way.


An odd issue with Forecaster 7 came to light the other day so I thought it best to set aside some time to create an article.

It all centres on the Z_RAW sql table which holds the data against cost code and account by period. The issue is caused by the way the data is stored. Every time you create a new period in forecaster it creates a new column against that cost code/account combination in the Z_RAW table.

Now because data is held against period, if you want to run previous input sets and reports as well as current ones you are going to need to keep creating new periods rather than re using the same ones.

So if you have 12 periods (Not unusual) and you want to do Quarterly Forecasts you will need to create 4 sets of the 4 periods which works out at 48 separate periods , so that is 48 extra columns added to the Z_RAW table.

There is a 1024 column limit on a non wide SQL table and a 1000 column limit when using replication. So using the above example it is going to take you 20 years till you reach the limit of the SQL table (That’s not so bad, I hear you cry). Well we have several clients who like to report on a weekly period structure, so the standard structure is 52 periods and if using the quarterly format that’s 208 extra periods a year. This means you reach the table limits in 4 and half years. If you throw in a 12 period frame work as well you reach that limit in just under 4 years.

The long and the short of it is, it’s not an issue if you know about it in advance as you can plan ahead and remove old data but if you don’t know about it, it could catch you out.

Forewarned is forearmed with forecaster.