DWM BI360 Error: Generating time dimension

Summary:

This article details an error, "Error in generating time dimension" and how to resolve it.

Article sections:

• Symptoms
• Cause
• Resolution
  • Application Timeout
  • Transactions Outside of the Range of d_Time
• Properties

Symptoms:

When adding a future year, following error will be returned, "Error in generating time dimension".

Causes:

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. 

Resolution:

Application Timeout

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

1. Open Data Warehouse Manager
2. Click Info > Settings
3. 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. 

Transactions Outside of the Range of 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

1. A listing stating that no transactions were found outside the range of the d_Time for the respective module
2. 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 

1. Delete these transactions
2. 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

Properties:

Data Warehouse Manager 4.x