Limitations with using System Date part 1

Often in a reporting or data analysis scenario the analyst or business user wants to view data that is current, for example, sales for the current week or employee turnover for the current month etc. The analyst could create the report and then just enter whatever the current date is or in a operational report the user could enter today’s date when the report however this is obviously not ideal. Furthermore if we are creating a report that is scheduled to run daily then it would need to automatically choose the current date. This issue is easily and we create these reports using functions that return the system date for example SYSDATE in Oracle, getDate() in SQL Server or CurrentDate() in a Web Intelligence report. However although this at first seems an adequate solution there are limitations of using system date functions during the development and testing phases of a BI project.

Typically the development and test systems won’t have current data in their databases. Normally a sample data set is taken from the production or backup database that is, for example, a two month range of data taken from last year. Now any report created in dev or test that uses the system date to filter the data won’t return any data as there is no ‘current’ data in the underlying database.

This article looks at what we can do to allow proper development and testing of reports that will still use the correct system date when released to production. Although the article will use BusinessObjects as the BI application with a SQL Server data mart the techniques described here can apply to other BI Applications and database types.

Problem Overview

Before we look at the available solutions let us first fully understand the issue at hand by considering the following reporting requirement.

A business user has requested a sales report that display this weeks and last weeks sales revenue by department, something similar to the following,

The user wants this report to be scheduled to be updated at the end of every week to display the latest data.

Below is a screenshot of the classes and objects in the Universe along with the data mart tables that are the source for the report.

From this universe we can create the following query that returns the current weeks and the previous weeks sales data to populate the report,

SELECT
  DT.DATE, DP.DEPARTMENT_NAME, SUM(FT.SALES_AMOUNT)
FROM
  FACT_TRANSACTION AS FT
  INNER JOIN DIM_TIME AS DT ON FT.TIME_ID = DT.TIME_ID
  INNER JOIN DIM_PRODUCT AS DP ON FT.PRODUCT_ID = DP.PRODUCT_ID
WHERE
    DT.DATE >= dateadd(wk, datediff(wk, 7, getdate()), 0)
AND DT.DATE <= dateadd(wk, datediff(wk, 0, getdate()), 6)
GROUP BY
  DT.DATE, DP.DEPARTMENT_NAME

The getdate() function returns the current system date, the dateadd() and datediff() functions are used to determine the last day of the current week and the first day of the previous week. This calculation was obtain from Gregory Larson’s article which details the logic behind the formula. Note, these formula consider Monday as the first day of week and we can alter the last digit in the formula to use other days for example dateadd(wk, datediff(wk, 0, getdate()), 5) will use Sunday as first day of the week.

In this way we can restrict our query to select the current week and the previous week. Now this will provide a data set that has total sales for each day per department so in the Web Intelligence report we need to group these days into the weeks ‘This Week’ and ‘Last Week’. The Web Intelligence report uses the following formula to create a variable that will return the values ‘This Week’ or ‘Last Week’ depending on whether the transaction date falls within this week or last week with respect to the system date of the Web Intelligence server.

=If [Date] <= LastDayOfWeek(CurrentDate()) And
    [Date] >= RelativeDate(LastDayOfWeek(CurrentDate());-6)
    Then "This Week" Else "Last Week"

Note, In Web Intelligence XI3 LastDayOfWeek will use Sunday as the last day of the week (In XIr2 Saturday was considered last day of the week). If you want to use other days for start and end of week then use RelativeDate to add or subtract the appropriate number of days.

You can see that both the SQL statement and the report formula make use of functions that return the system date. We know that in testing we wont have any current data so we need to look for a solution that will allow us to effectively specify a different system date for the dev and test environments.

So in summary we have two issues that need addressed here:

1. how do create a query that will return the latest data based on system date in production but return a specific data set in dev and test based on a user specified date?
2. how do we determine if the data in the report is current if in dev and test our ‘current’ data is 6 months old?

Let us address these in turn in the next article in this series.