I have a requirement to store the usual date dimensional columns, as well as, the number of business days (fiscal year, FY to date, month, month-to-date, etc.) for each operating unit in our company.
A couple of things make it tricky, b/c we have multiple operating units spread among 6+ countries...all with different definitions of work days for each year. So, the date dim i'm currently using has two surrogate keys (the_date, and an operating_unit_key).
I'm thinking 1), it would probably be simpler to create a single numeric unique key for the date dimension and 2) getting rid of the extra logic in my ETL process to populate the business days by FY, Month, etc., etc. Then, handle that at the reporting level with aggregate calcs. The only problem there is that I wouldn't be able to calculate the full month number of business days for the current month (or current fiscal year) because the fact table i'll be joining to is made up of sales (history and up to yesterday).
Just wondering how people handle this and what the best practices would be.
- Posts : 1
Join date : 2014-02-24
Permissions in this forum:You cannot reply to topics in this forum