Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
I agree with your DBA group that its time to refactor your ETL and application's code. While changing the date of the server works, its nothing more than a bandaid solution which might cause an accidental problem in the server(In case something gets deployed in the future that relies on the getdate function as well). –  Maru de Vera Oct 11 '13 at 18:38
add comment

migrated from programmers.stackexchange.com Oct 11 '13 at 22:16

This question came from our site for professional programmers interested in conceptual questions about software development.

2 Answers

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.

share|improve this answer
add comment

DBAs have learned through hard experience that Murphy's Law should be applied to all situations - Anything that can go wrong, will go wrong. It is definitely unreasonable for you to expect the DBAs to tinker with the environments in strange ways to save you the difficulty of making your application work in a more flexible way. There is also no reason why the system would produce different outputs depending on if the date is gathered from a GetDate or from a user input, as long as the values are the same. Unless you have left some key information out here, your worries are unfounded and your DBAs are right to take a stand on this situation.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.