MicroStrategy ONE
Time Hierarchy
The Time hierarchy attributes are based off UTC timezone. Both MicroStrategy Intelligence Server and Identity Server send the server timezone with the transactional logs. The timezone is then standardized to UTC in the Platform Analytics ETL.
In addition to the standard time attributes (Day, Month, Year, Month of Year, etc), there are supplementary attributes to provide additional levels of analysis. They include:
- Time Period: Predefined time periods such as Yesterday, Last Week, etc.
- Week Time Window: Rolling seven day increments of week windows.
lu_date
The source table for the Date attribute which tracks MicroStrategy and Badge transactions. Each day, a new date entry is added to the lu_date table.
|
Column |
Description |
Data-Type |
|---|---|---|
|
date_id |
The generated numeric ID for Date. The format for date_id is yyyy-mm-dd. For example, 2017-01-02 |
date |
|
previous_date_id |
The Previous Date ID used for transformation in the Platform Analytics project. For example, 2017-01-01 |
date |
|
week_id |
The generated numeric ID for Week. The format for week_id is yyyyww. For example, 201701 |
mediumint)6) |
|
day_of_week_id |
The corresponding Day of Week ID. |
smallint(6) |
|
month_id |
The corresponding month_id in format yyyymm. For example, 201701 |
int(11) |
|
month_desc |
The description form of Month. For example, January, 2017 |
varchar(25) |
|
previous_month_id |
The Previous Month ID. For example, 201612 |
int(11) |
|
month_of_year_id |
The Month of Year ID. |
tinyint(4) |
|
year_id |
The Year ID. The source column for the Year attribute in the Platform Analytics project. |
int(11) |
| quarter_id |
The Quarter ID the day resides in. |
int(11) |
| previous_quarter_month_id | The Month ID of the quarter the day resides in from the previous month. | int(11) |
lu_month
The lu_month tracks on which Month that a MicroStrategy or Badge transaction occurred.
|
Column |
Description |
Data-Type |
|---|---|---|
|
month_id |
The generated numeric ID for Month. The format for month_id is yyyymm. For example, 201802. |
int(11) |
|
month_desc |
The descriptive form of the Month. The format is Month, Year. For example, February, 2018. |
varchar(32) |
|
previous_month_id |
The Previous Month ID used for transformation in the Platform Analytics project. For example, 201801. |
int(11) |
lu_month_of_year
List on which Month of Year the MicroStrategy or Badge transaction occurred.
|
Column |
Description |
Data-Type |
|---|---|---|
|
month_of_year_id |
The fixed numeric ID for the Month of Year. |
tinyint(4) |
|
month_of_year_desc |
The descriptive form of the Month of Year. For example, January February March |
varchar(25) |
|
month_of_year_short_desc |
The short descriptive form of the Month of Year. For example, Jan Feb Mar |
varchar(10). |
|
previous_month_of_year_id |
The Previous Month of Year ID used for transformation in the Platform Analytics project. |
tinyint(4) |
lu_day_of_week
Day of Week indicates which day the MicroStrategy or Badge transaction occurred.
|
Column |
Description |
Data-Type |
|---|---|---|
|
day_of_week_id |
The fixed numeric ID for the Day of Week. |
smallint(6) |
|
day_of_week_desc |
The descriptive form of Day of Week. For example, Monday Tuesday Wednesday |
varchar(25) |
|
day_of_week_short_desc |
The short descriptive form of Day of Week. For example, Mon Tue Wed |
varchar(10) |
|
part_of_week_id |
The fixed numeric ID for the Part of Week. |
smallint(6) |
lu_part_of_week
Part of Week indicates whether the MicroStrategy or Badge transaction occurred on the Weekend or Weekday.
|
Column |
Description |
Data-Type |
|---|---|---|
|
part_of_week_id |
The fixed numeric ID for the Part of Week. |
tinyint(4) |
|
part_of_week_desc |
The descriptive form of Part of Week. The Part of Week can be: Weekday - defined as Monday through Friday. Weekend - defined as Saturday or Sunday. |
varchar(25) |
lu_time_period
Time Period is used to track predefined rolling time windows. The predefined Time Periods are intended to be overlapping. For example, the Time Period for Last Week will include the actions for Yesterday and Last 2 Months will include all the actions for all other time windows. The rel_date_timeperiod table is updated daily in the Platform_Analytics_daily_etl.xxx procedure and therefore the Time Period attribute does not store data for the current date.
|
Column |
Description |
Data-Type |
|---|---|---|
|
time_period_id |
The fixed numeric ID for the defined Time Periods. |
tinyint(4) |
|
time_period_desc |
The descriptive form of the Time Period. The Time Period are defined as: Yesterday - today minus 1 day. Last Week - today minus 7 days. Last Month - today minus 30 days. Last 2 Months - today minus 60 days. |
varchar(50) |
rel_date_timeperiod
The relationship table used to track the rolling Time Periods. The predefined Time Periods are intended to be overlapping. For example, the Time Period for Last Week will include the actions for Yesterday and Last 2 Months will include all the actions for all other time windows. The rel_date_timeperiod table is updated daily in the Platform_Analytics_daily_etl.xxx procedure and therefore the Time Period attribute does not store data for the current date.
|
Column |
Description |
Data-Type |
|---|---|---|
| date_id | The Date corresponding to the specific Time Period. | date |
| time_period_id | The fixed numeric ID for the defined Time Period. | tinyint(4) |
lu_week
The source table for the Week attribute which tracks MicroStrategy and Badge transactions. The lu_week table stores the week elements until overflow in the year 2035.
|
Column |
Description |
Data-Type |
|---|---|---|
|
week_id |
The generated numeric ID for Week. The format for week_id is yyyyww. For example, 201720. |
mediumint(9) |
|
week_desc |
The week description. For example, Week 20, 2017. |
varchar(16) |
|
week_begin_date |
The beginning date from the week range. For example, 2017-05-21. |
date |
|
week_end_date |
The ending date from the week range. For example, 2017-05-28. |
date |
|
week_range |
The week_begin_date to week_end_date. For example, 05/21/2017 - 05/28/2017. |
varchar(50) |
|
previous_week_id |
The Previous Week ID used for transformation in the Platform Analytics project. For example, 201719. |
mediumint(9) |
|
month_id |
The corresponding month_id in format yyyymm. For example, 201705. |
int(11) |
|
year_id |
The Year ID. |
smallint(6) |
lu_week_time_window
Week Time Windows is used to track predefined rolling week windows. The Week Time Windows are consecutive and not overlapping. For example, the Last Week will include the last seven dates. It will not overlap with the dates for two weeks ago. The rel_date_weektime_window table is updated daily in the Platform_Analytics_daily_etl.xxx procedure and, therefore, the Week Time Windows attribute does not store data for the current date.
|
Column |
Description |
Data-Type |
|---|---|---|
|
week_time_window_id |
The fixed numeric ID for the defined Week Time Windows. |
tinyint(4) |
|
week_time_window_desc |
The descriptive form of the Week Time Windows. The time windows are defined as: Last Week - today minus 7 day 2 Weeks Ago - 8 to 14 days ago 3 Weeks Ago - 15 to 21 days ago 4 Weeks Ago - 22 to 28 days ago 5 Weeks Ago - 29 to 35 days ago |
varchar(50) |
| previous_week_time_window_id | The Previous Week Time Window ID used for transformation in the Platform Analytics project. | tinyint(4) |
rel_date_weektime_window
The relationship table used to track the Dates for the rolling Week Time Windows. The Week Time Windows are consecutive and not overlapping. For example, the Last Week will include the last seven dates. It will not overlap with the dates for two weeks ago. The rel_date_weektime_window table is updated daily in the Platform_Analytics_daily_etl.xxx procedure and therefore the Week Time Windows attribute does not store data for the current date.
|
Column |
Description |
Data-Type |
|---|---|---|
|
date_id |
The Date corresponding to the specific Week Time Window. |
date |
|
week_time_window_id |
The fixed numeric ID for the Week Time Window. |
tinyint(4) |
lu_minute
The Minute when a Badge or MicroStrategy transaction occurs.
|
Column |
Description |
Data-Type |
|---|---|---|
|
minute_id |
The fixed numeric ID for the Minute. |
int(11) |
|
minute_desc |
The descriptive form of the Minute. The Minute is stored in the 24 hours format of hh:mm. For example: 10:09 - represents 10:09 am 14:45 - represents 2:45 pm 23:30 -represents 11:30 pm |
varchar(8) |
|
hour_id |
The numeric ID for the corresponding Hour. |
tinyint(4) |
lu_hour
The Hour when a Badge or MicroStrategy transaction occurs.
|
Column |
Description |
Data-Type |
|---|---|---|
|
hour_id |
The fixed numeric ID for the Hour. |
tinyint(4) |
|
hour_desc |
The descriptive form of the Hour. For example, 12AM 1AM 2AM |
varchar(25) |
|
part_of_day_id |
The numeric ID corresponding to the Part of Day. |
tinyint(4) |
lu_part_of_day
The Part of Day when a MicroStrategy or Badge action occurs (i.e. Morning, Afternoon). The Part of Day is predefined based on the relationship with Hour.
|
Column |
Description |
Data-Type |
|---|---|---|
|
part_of_day_id |
The fixed numeric ID for the Part of Day. |
tinyint(4) |
|
part_of_day_desc |
The descriptive form representing the time range for the Part of Day. The Part of Day can be: Morning - hours 6am to 11am Afternoon - hours 12pm to 5pm Evening - hours 6pm to 10pm Night - hours 11pm to 5am |
varchar(25) |
lu_quarter
The Quarter when a MicroStrategy or Identity action occurs.
|
Column |
Description |
Data-Type |
|---|---|---|
|
quarter_id |
The fixed numeric ID for the Quarter. |
int(11) |
|
quarter_desc |
The descriptive form representing the Quarter. For example: Q1 2017 Q2 2018 Q3 2019 |
varchar(25) |
| previous_quarter_id | The fixed numeric ID of the quarter previous to the current one. This is the source column for the Previous Quarter transformation. | int(11) |
|
last_year_quarter_id |
The fixed numeric ID of the same quarter in the previous year. This is the source column for the Last Year Quarter transformation. |
int(11) |
| quarter_of_year_id |
The fixed number ID of the quarter number within the year. For example Q3 2019 would be 3. |
tinyint(4) |