2

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.

I'm not quite sure if this belongs in SO or some other forum, so please excuse me if it does not.

carexcer
  • 1,407
  • 2
  • 15
  • 27
L_7337
  • 2,650
  • 28
  • 42
  • I found this link which also talked about testing processes and was interesting: http://programmers.stackexchange.com/questions/115421/are-there-best-practices-for-checking-for-errors-in-business-logic-for-db-refres – L_7337 Oct 11 '13 at 17:59
  • I think your question is well suited for SO, but the title of it is misleading. The core of your problem is the GetDate() which is hard to manipulate for test purposes. You should definitely make it into the subject. Asking for 'best practices' may easily end up in the question being closed as "opinion based", it's just matter of a few people that didn't read the text carefully ;) – quetzalcoatl Jan 14 '14 at 12:25
  • Btw, the most obvious answer would be "dont use GetDate, pass the date as parameter", but that's easy when writing the code from scratch, not when everything is already done.. Like http://smalliron.wordpress.com/2007/03/13/unit-testing-current-dates-and-getdate/ . I actually really looking forward for any GetDate testing workarounds, but I'm afraid that there are none. – quetzalcoatl Jan 14 '14 at 12:30
  • FYI: http://stackoverflow.com/questions/2593047/changing-the-output-of-getdate -- so your question actually might be a duplicate of it, please consider that too. – quetzalcoatl Jan 14 '14 at 12:32

2 Answers2

0

Assume the following:

  • P-1 = the previous 6-month period
  • P = the current 6-month period
  • P+1 = the next 6-month period

If I understood the problem, the data in your QA environment is always collected during P and that's why you need to run your tests on P+1 (forcing you to set the server's date in the future).

we load new metrics in our DEV/QA environment and configure them for the upcoming reporting period so we can test

When you configure them [the metrics] for the upcoming reporting period, you could also prepare/convert the test data in a way that it looks like it was collected on P-1. Something like "shift every timestamp 6 months backwards). Then I understand you would have P-1 data, which could be tested on P.

E.Z.
  • 6,393
  • 11
  • 42
  • 69
0

we had to pre-assign dates for Y2K testing in the late 1990s, so yes it has been done. I might suggest you make a %getData MACRO. then, you can set the date in one place. and once testing is done, can just call getData() for your NORMAL processing. It will take a bit of retooling upfront, but will then allow maintenance in just the %getData macro thereafter. Just a thought.

fredmayer23
  • 1