When adding a future year, following error will be returned.
"Error in generating time dimension"
We have ran into two causes for this error:
- The application timeout is set too low in order for the new period year(s) to generate
- There are transactions in the transactional tables that fall outside the range of the currently configured periods in d_Time table in Data Warehouse Database.
In addressing the timeout setting, by default Data Warehouse Manager (4.6+) is set to a timeout of 30 seconds.
- If you are on an older version of Data Warehouse Manager, an upgrade is required.
Making the following change is done for your computer install only. If Data Warehouse Manager is installed on other computers, this may need to be applied there as well.
You can adjust this by
- Open Data Warehouse Manager
- Click Info > Settings
- Edit the Timeout Setting value. If it is set to 30 seconds, 300 is typically a good first number to increase this value to
After increasing this number, try extending the period config again.
Transaction that fall outside the range of the d_Time
If the above does not work, typically this can be an issue where there are transactions that fall outside of the range of the d_Time table. This can sometimes happen thru a data load process.
The below query can be ran, to find if there are transactions that fall outside the d_Time.
Declare moduleLists CURSOR LOCAL FAST_FORWARD FOR select Module as Module from dbo.ModuleLabel Declare @module nvarchar(max) OPEN moduleLists FETCH NEXT FROM moduleLists into @module BEGIN TRANSACTION WHILE @@FETCH_STATUS = 0 BEGIN declare @SQL NVARCHAR(max) = 'if(select Count(*) from f_Trans_'+@module+' f' +char(13)+ 'left join d_Time t on t.MemberId = f.Timeperiod' +char(13)+ 'where t.MemberId is null) > 0' +char(13)+ 'begin'+char(13)+ 'select '+'''ERROR ''+'+ 'cast(Count(*) as nvarchar) +'' transaction(s) for the '+@module+' module outside the range of the d_Time''' +' from f_Trans_'+@module+' f' +char(13)+ 'left join d_Time t on t.MemberId = f.Timeperiod' +char(13)+ 'where t.MemberId is null' +char(13)+ 'END'+char(13)+ 'ELSE PRINT ''No Transactions found outside the range of the d_Time in the '+@module+' Module'';'+char(13) --print @SQL exec sp_executeSQL @sql FETCH NEXT FROM moduleLists into @module END COMMIT TRANSACTION; CLOSE moduleLists DEALLOCATE moduleLists
This query can return two results
- A listing stating that no transactions were found outside the range of the d_Time for the respective module
- A list stating the ## number of transactions were found outside the range of the d_Time for the respective module.
It #2 is returned, then you need to query the respective module that contains transactions outside the range of the d_time and decide what to do with those. You can either
- Delete these transactions
- Extend the Data Warehouse Period configuration to include these periods before extending the period configuration within the Data Warehouse
Ensure that you have sufficient time set before the application timeouts: in Data Warehouse Manager Info Settings
Applies to: Data Warehouse Manager 4.x