We often need to be able to determine whether a date is a working day. I’ve done it multiple times and every time need to re-invent the wheel; I thought I’d stick it on my blog and save myself some work next time. In this instance we simply want to be able to add a flag highlighting a working day.
There are three key components to this solution; a dimension staging table storing national holidays; a PLSQL function that can use the table to help determine whether a day should be flagged and the flag itself on a date dimension table.
Creating the National Holiday Table
The database table defined below must be created. It needn’t be exposed in obiee, but you may choose to do so. It will be used during the ETL process and must be updated by an administrator, dependent upon how complete you make the table to begin with.
There is a single record for each holiday in each country; in our case we create holidays between 2008 and 2012 for GBP only.
And in our case we would need to update this table beyond 2012 if/when necessary.
Creating the Business Day Function
For a given date and country code we want to be able to determine whether it is a working day. We need to verify the date does not exist in the national holiday table and, in our case, that it is not on either a Saturday or Sunday.
I have created a PLSQL function, below. Rather than return simply a flag, the function returns the previous working date, allowing users to determine when the last working day was. In the front end I will expose both a flag and the Last Business Date columns.
Loading the Date Dimension Table
I have already blogged about the obiee Date Dimension; we need to update the SQL that creates this table to include our two new columns. The new SQL is given below and the changes highlighted.
You can see from the highlighted areas of the statement that we call our Business Day Function to populate the last business date; and we create a flag column, bus day, which is true when the Last Business Date is equal to the date - otherwise false.
The Final Piece to the Puzzle
That is all the components that are needed at the database level; which is all of the hard work done. All that remains is to update the physical, logical and presentation layers of the RPD with the two new columns to expose them in the front end.
There are endless ways that this functionality may be extended, dependent upon requirements; such as time based equations in the fact table that may be needed for call centre statistics, or using the new columns to limit queries to business dates, which is actually what my client would like.
There are three key components to this solution; a dimension staging table storing national holidays; a PLSQL function that can use the table to help determine whether a day should be flagged and the flag itself on a date dimension table.
Creating the National Holiday Table
The database table defined below must be created. It needn’t be exposed in obiee, but you may choose to do so. It will be used during the ETL process and must be updated by an administrator, dependent upon how complete you make the table to begin with.
SQL to Create National Holiday Table
And in our case we would need to update this table beyond 2012 if/when necessary.
Creating the Business Day Function
For a given date and country code we want to be able to determine whether it is a working day. We need to verify the date does not exist in the national holiday table and, in our case, that it is not on either a Saturday or Sunday.
I have created a PLSQL function, below. Rather than return simply a flag, the function returns the previous working date, allowing users to determine when the last working day was. In the front end I will expose both a flag and the Last Business Date columns.
SQL to create Business Day Function
I have already blogged about the obiee Date Dimension; we need to update the SQL that creates this table to include our two new columns. The new SQL is given below and the changes highlighted.
You can see from the highlighted areas of the statement that we call our Business Day Function to populate the last business date; and we create a flag column, bus day, which is true when the Last Business Date is equal to the date - otherwise false.
The Final Piece to the Puzzle
That is all the components that are needed at the database level; which is all of the hard work done. All that remains is to update the physical, logical and presentation layers of the RPD with the two new columns to expose them in the front end.
There are endless ways that this functionality may be extended, dependent upon requirements; such as time based equations in the fact table that may be needed for call centre statistics, or using the new columns to limit queries to business dates, which is actually what my client would like.
No comments:
Post a Comment