Additional considerations for your date hierarchy attributes for easier reporting.
In my experience, I have worked with over 40 companies and organizations. I consistently run into several elements that are missing, that when implemented, make it much easier to perform dynamic date reporting at levels higher than the Date level (Month, Quarter, Year).
The typical Month data has the following columns:
MonthID - 201701
MonthDesc - January, 2017
MonthNumber - 1
QuarterID - 20171
YearID - 2017
Adding in additional elements provides much more flexibility and easier use of the MicroStrategy platform.
Time period beginning and/or end date - When this is included as an additional attribute form of the Week/Month/Quarter/Year attribute, this permits using the Dynamic Date functionality.
Time period from current – This allows filtering or sorting based on a calculation of a date difference of the current date and a point in the time period. The current month would have a value of 0, the prior month would have value of 1, and next month would have a value of -1. Incrementing/decrementing from there.
Time period number of days – Provides the number of days within the time period. This may be used for fact extensions.
Time period prior period – Used for Transformations.
Time period prior year period – Used for Transformations.
These can very easily be included in a view, and for some things such as the Time Period from Current, should actually be calculated in a view. In a previous post, I detailed use cases for using views rather than directly using a table.
http://www.mastrarchitect.com/single-post/2016/1/26/The-Case-for-Database-Views
Example 1 Time period beginning date
Example 2 – Time period from current
Sample code for views:
Time period beginning and/or end date - When this is included as an additional attribute form of the Week/Month/Quarter/Year attribute, this permits using the Dynamic Date functionality.
SQL Server:
Start Date
CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(date_id)-1),date_id),101) AS month_start_date
case when date_id = '1900-01-01' then '1900-01-01' else DATEADD(DAY, 1 - DATEPART(WEEKDAY, date_id), CAST(date_id AS DATE)) end week_start_date
cast(DATEADD(q, DATEDIFF(q, 0, date_id), 0) as date) AS quarter_start_date
convert(date,cast(((datepart(yyyy,date_id) * 10000) + 101) as varchar(8)),112) AS year_start_date
End Date
case when date_id = '9999-12-31' then '9999-12-31' else DATEADD(DAY, 7 - DATEPART(WEEKDAY, date_id), CAST(date_id AS DATE)) end week_end_date
case when datepart(yyyy,date_id) = 9999 then '9999-12-31' else
CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,date_id))),DATEADD(mm,1,date_id)),101) end AS month_end_date
case when datepart(yyyy,date_id) = 9999 then '9999-12-31' else cast(DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, date_id) + 1, 0)) as date) end AS quarter_end_date
convert(date,cast(((datepart(yyyy,date_id) * 10000) + 1231) as varchar(8)),112) AS year_end_date
Time Period From Current
datediff(mm,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(date_id)-1),date_id),101),getdate()) AS month_from_current
datediff(q,cast(DATEADD(q, DATEDIFF(q, 0, date_id), 0) as date),getdate()) AS quarter_from_current
datepart(yyyy,getdate()) - datepart(yyyy,date_id) AS year_from_current
Time Period Number of Days
case when datepart(yyyy,date_id) = 9999 then 31 else datepart(d,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,date_id))),DATEADD(mm,1,date_id)),101)) end AS days_in_month
case when datepart(yyyy,date_id) = 9999 then 90 else datediff(d,cast(DATEADD(q, DATEDIFF(q, 0, date_id), 0) as date), cast(DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, date_id) + 1, 0)) as date)) + 1 end AS days_in_quarter
datepart(dy,convert(date,cast(((datepart(yyyy,date_id) * 10000) + 1231) as varchar(8)),112)) AS days_in_year
Time period prior period
(datepart(yyyy,dateadd(m,-1,date_id)) * 100) + datepart(mm,dateadd(m,-1,date_id)) prior_month_id
(datepart(yyyy,dateadd(q,-1,date_id)) * 10) + datepart(q,dateadd(q,-1,date_id)) AS prior_quarter_id
datepart (yyyy,date_id) -1 prior_year_id
Time period prior year period
(datepart(yyyy,dateadd(m,-12,date_id)) * 100) + datepart(mm,dateadd(m,-12,date_id)) prior_year_month_id
(datepart(yyyy,dateadd(m,-12,date_id)) * 10) + datepart(q,dateadd(m,-12,date_id)) AS prior_year_quarter_id
Oracle
Start Date
CASE WHEN TO_NUMBER (TO_CHAR (date_id, 'D')) = 7 THEN MYDATE
ELSE MYDATE - TO_NUMBER (TO_CHAR (date_id, 'D'))
END AS week_start_date
TRUNC (date_id, 'mm') AS month_start_date
TRUNC (date_id, 'Q') AS quarter_start_date
TRUNC (date_id, 'Y') AS year_start_date
End Date
CASE WHEN TO_NUMBER (TO_CHAR (date_id, 'D')) = 7 THEN date_id + 6
ELSE date_id + (6- (TO_NUMBER (TO_CHAR (date_id, 'D'))))
END AS week_end_date
LAST_DAY (TRUNC (date_id, 'mm')) + 1 - 1/86400 AS month_end_date
ADD_MONTHS (TRUNC (date_id, 'Q'), 3) - 1/86400 AS quarter_end_date
ADD_MONTHS (TRUNC (date_id, 'Y'), 12) - 1/86400 AS year_end_date
Time Period From Current
months_between(TRUNC (date_id, 'mm'), sysdate) month_from_current
months_between(TRUNC (date_id, 'Q'),TRUNC (sysdate, 'Q'))/3 quarter_from_current
TO_NUMBER(TO_CHAR (sysdate, 'yyyy')) - TO_NUMBER(TO_CHAR (date_id, 'yyyy'))
Time Period Number of Days
TO_NUMBER ( TO_CHAR( LAST_DAY (TRUNC (date_id, 'mm')), 'DD')) AS days_in_month
ADD_MONTHS (TRUNC (date_id, 'Q'), 3) - TRUNC (date_id, 'Q') AS days_in_quarter
ADD_MONTHS (TRUNC (date_id, 'Y'), 12) - TRUNC (date_id, 'Y') AS days_in_year
Time period prior period
TO_NUMBER((TO_CHAR (add_months(date_id,-1), 'yyyy') * 100) + TO_NUMBER (TO_CHAR (add_months(date_id,-1), 'MM'))) prior_month_id
TO_NUMBER((TO_CHAR (add_months(date_id,-3), 'yyyy') * 10) + TO_NUMBER (TO_CHAR (add_months(date_Id,-3), 'Q'))) prior_quarter_id
TO_NUMBER(TO_CHAR (date_id, 'yyyy')) - 1 prior_year_id
Time period prior year period
TO_NUMBER((TO_CHAR (add_months(date_id,-1), 'yyyy') * 100) + TO_NUMBER (TO_CHAR (date_id, 'MM'))) AS prior_year_month_id
TO_NUMBER((TO_CHAR (add_months(date_id,-12), 'yyyy') * 10) + TO_NUMBER (TO_CHAR (date_Id, 'Q'))) prior_year_quarter_id
- Ryan Forsythe, Course Creator, MaSTR Architect, Chief Analytics Officer, AllScient