We have a dashboard that contains metrics based on a 6 month reporting period. Users log in and see their metrics based on the current 6-month reporting period.
Every 6 months, we load new metrics in our DEV/QA environment and configure them for the upcoming reporting period so we can test. But, our application and ETL use GETDATE(), so for the ETL to calculate properly, we have been setting the server's DATE in the future so everything will calculate and display as if we were in the future.
Our DBA group is now telling us that this should not be done and no
one else in the industry does this. Can someone give me some examples
of what they have done for testing in this situation? Should we be
looking to re-design our ETL and application and what are some ideas
for best practices?
We have unit testing for the application, so it will work. We are more worried that the data from the ETL will not be correct on Day 1 of the new period. It is very important that the metrics are displaying properly and the data is correct on Day 1.
Best Answer
I would not use GetDate() for this fixed-period scenario as the end date for queries, because the reports are only meningful for specific periods.
A better approach is to use a specific run-date for each reporting period. That run-date is not the current date.
For example, let's say one of your KPIs is the sales amount for the first 6 months of 2013. Your system should use end of June as the end date not the current run-date regardless of when the report is run. If you do this, you'd not have to touch the data.