Limitations with using System Date part 3

In the last article we looked at alternatives to returning the system date in the report queries. This article looks at the report and alternatives to using system date functions in report calculations

Replacing use of Current Date within a Web Intelligence Report

The methods in the previous article allow us to control what data is returned by a query when the filtering for ‘current’ data. However there are still some situations where the report also needs to know what the current date is. The example at the start of the article was analysing the dates returned by a query to determine if the dates fell in the current week or previous week and then group the data appropriately in the crosstab. The following Web Intelligence formula was used for this,

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

The CurrentDate() function here returns the system date according to the Web Intelligence server however what we really want is the system date according to the data set returned. Again there is more than one way of resolving this and here we’ll look at a couple of these techniques.

Method 1. Formula to use when using the Max Date method to determine current date

If you are planning on using the max date method then as far as I am aware it isn’t then possible to use the maximum date in the data set to determine whether the data is This Week or Last Week. At first sight the following formula should work,

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

But the max() aggregation formula can return multiple values depending on where it is used (e.g. report body versus footer). Even if we use calculation contexts such as Max([Date]) In Report we still get #MULTIVALUE errors when trying to group by this formula.

To resolve this you would need to push the calculation down to the database and have the query return an extra column that is the maximum date in the data set. Then we can use the technique discussed in method 3 below in the report.

Method 2. Return the Is Today and Is Current Week objects in the query

Use this method when the time dimension flags the date and week that are current. We edit our query and include the object Is Current Week and then in the report we can check this object to determine if the data falls in this week or last week. The Web Intelligence formula is quite simple and becomes,

=If [Is Current Week] = 'Y' Then "This Week" Else "Last Week"

If we create this formula as a variable we can then easily create the required crosstab,

Method 3: Add an extra column to the returning data set that returns the value of the custom system date function

Use this method when using the custom function solution in the data mart. Essentially we add dbo.getMyDate() to the select clause and then in report we can use this value to determine if a date is the current date or is in the current week. We do this by first creating a Current Date object in the Time class of the universe this then allows use to create the query,

SELECT
  dbo.getMyDate(), 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, dbo.getMyDate()), 0)
AND DT.DATE < = dateadd(wk, datediff(wk, 0, dbo.getMyDate()), 6)
GROUP BY
  DT.DATE,
  DP.DEPARTMENT_NAME

Then we can create the following Web Intelligence formula that uses the Current Date object to determine whether the data falls either in This Week or Last Week,

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

From here we create this formula as a variable and build the crosstab is we did in method 2 above.

Other Refinements

Creating a Current Date object that returns either the system date or the value of your current date function has further advantages over the other methods. Primarily we can refer to the Current Date object in other objects whenever we need to use system date functionality. For example if we want to create a predefined condition that filters for the last two weeks data we could write,

DT.DATE >= dateadd(wk, datediff(wk, 7, dbo.getMyDate()), 0) AND
DT.DATE < = dateadd(wk, datediff(wk, 0, dbo.getMyDate()), 6)

However we would be better to refer to the Current Date object,

DIM_TIME.DATE >= dateadd(wk,datediff(wk,7,@Select(Time DimensionCurrent Date)),0) AND
DIM_TIME.DATE < = dateadd(wk,datediff(wk,0,@Select(Time DimensionCurrent Date)),6)

As this then allows us to easily redefine the Current Date without having to rewrite all objects and conditions that use the current date. So for example if the schema name changes from ‘dbo’ to ‘myschema’ then all we need to do is update the Current Date object and all other objects and predefined conditions will pick up this change.

Conclusion

This article looked at why using system date can lead to difficulties in development and testing of a BI system when you can’t guarantee that you will always have the ‘latest’ data. To resolve this issue we have several options the easiest is to create the report to prompt for a date and then default to system date based on the user entering a ‘key’ date. Although easy to implement it does have some limitations and the table below summaries the advantages and limitations of each method. Another option was to assume that the maximum date in the data set was today’s date, a third option was to flag a row in the time dimension to indicate what is the current date. Lastly the final option was to create a custom function that returns a date set in a control table or if none set to return the system date.

The table below summarises the methods

Method	Pros	Cons
Report Prompt with Key Date	Easiest to Implement	Not user friendly Can’t force report to always use system date – user can change prompt Need to set test date in all reports Can’t guarantee consistency in test date value Not easy to identify current date in report
Max Date in Data Set	Not difficult to implement	Query performance may be adversely affected Max date in dataset isn’t always most recent – could be future date Not possible to determine current date in report
Flagged Date in Time Dimension	Current Date centrally defined Can be manually changed	The data mart load process needs to maintain the current date Need a method of telling the data mart process what to use as a current date rather than system date
Custom Function	Very flexible, virtually replaces functionality of system date functions All reports will use same current date Can also be used by other processes such as the data mart load process Easy to identify current date in report	Need to code and test custom function.

So in conclusion the custom function is probably the best method to use, offering the greatest flexibility while keeping the extra development overhead to a minimum.