Creating an object

The definition process begins by creating an object of the when class.

library(when)

w_date <- when()

By default, an object is created based on date, but we can also create it based on time by indicating it using the type parameter.

w_time <- when(type = 'time')

Virtually all configuration options can be defined using the object creation function. They can also be defined later using specific functions, as shown in the following section.

Definition of general aspects

For example, using the following combination of functions we get the same result as defining the type when we create the object.

w_time_2 <- when() |>
  define_characteristics(type = 'time')

identical(w_time, w_time_2)
#> [1] TRUE

By default it considers the system language. Since our system is Spanish, for the literals of day and month names to appear in English, we must configure it explicitly.

w_date <- w_date |>
  define_characteristics(locale = Sys.setlocale("LC_TIME", "English"))

In section Other configuration options we present the rest of the configuration options for dimension generation. For the examples that we are going to use, the default options of the package are enough.

Next we have to select and configure the levels and define the instances. These operations, along with the current one, can be performed in any order.

Definition of instances

We can define the instances by giving a range of values or by indicating the specific instances. The values in both cases will depend on the type of dimension (date-based or time-based).

We can indicate these values at the time of creating the class object or through the define_instances() function.

• In the case of date, we can indicate a year, year and month (with the format yyyy-mm), or year month and day (yyyy-mm-dd).

• In the case of time, we can indicate an hour, hour and minute (with the format hh:mm), or hour, minute and second (hh:mm:ss).

In both cases we can use the string format, the one provided by the lubridate package or, when it is a single number (year or hour), we can use an integer value.

For example, below they are used with the format of lubridate.

w_date <- w_date |>
  define_instances(start = lubridate::today(),
                   end = lubridate::today() + lubridate::years(5))

In the following definitions we configure the dimensions so that they consider only the indicated dates. The second will be appropriate if we want to obtain the dimension at the year level, because we are indicating the specific values to consider (not a period).

w_date_2_1 <-
  when(
    values = c(
      "2023-12-31",
      "2023-01-01",
      "2022-12-31",
      "2022-01-01",
      "2021-12-31",
      "2021-01-01"
    )
  )

w_date_2_2 <- w_date |>
  define_instances(values = 2020:2030)

The following definition will be appropriate at any level of detail because we are giving the range of values to consider (dates between those two years).

w_date_3 <- w_date |>
  define_instances(start = 2020, end = 2030)

When only the year is indicated, it is considered its first day. Therefore the previous definition is equivalent to the following one.

w_date_4 <- w_date |>
  define_instances(start = "2020-01-01", end = "2030-01-01")

identical(w_date_3, w_date_4)
#> [1] TRUE

In the case of time, by default, it is considered at second level and all seconds of the day.

w_time_3 <- w_time |>
  define_instances(start = "00:00:00", end = "23:59:59")

identical(w_time, w_time_3)
#> [1] TRUE

We can also indicate a period or a set of times according to the indicated criteria. In the following example, the two definitions are equivalent.

w_time_4 <- w_time |>
  define_instances(start = 8, end = 17)

w_time_5 <- w_time |>
  define_instances(start = "08:00:00", end = "17:00:00")

identical(w_time_4, w_time_5)
#> [1] TRUE

Selection of levels and attributes

In the definition, by default, the attributes that we have considered to be most frequently used have been included. We can consult them for each level using the get_level_attribute_names() function with the parameter selected = TRUE. If we define the parameter as selected = FALSE (default option), it will return all available attributes. We can indicate the name of the level or, if we do not indicate any, it will obtain the attributes of all the levels. We obtain the level names using the get_level_names() function. Below are examples of using these functions for the date-based dimension.

w_date |>
  get_level_attribute_names(selected = TRUE)
#>  [1] "date"           "month_day"      "week_day"       "day_name"      
#>  [5] "day_num_name"   "year_week"      "week"           "year_month"    
#>  [9] "month"          "month_name"     "month_num_name" "year"

w_date |>
  get_level_names()
#> [1] "day"      "week"     "month"    "quarter"  "semester" "year"

w_date |>
  get_level_attribute_names(name = 'month', selected = TRUE)
#> [1] "year_month"     "month"          "month_name"     "month_num_name"

w_date |>
  get_level_attribute_names(name = 'month')
#> [1] "year_month"     "month"          "month_name"     "month_num_name"
#> [5] "month_abbr"     "month_num_abbr"

Also for the time-based dimension.

w_time |>
  get_level_attribute_names()
#> [1] "time"     "hour"     "minute"   "second"   "day_part"

w_time |>
  get_level_names()
#> [1] "time"

There is a function to configure each level. We can select or deselect each attribute individually using the attribute name as a boolean parameter of the function. We can also select or deselect all attributes defined at the level using the special include_all and exclude_all parameters: Once all the attributes have been updated with these special parameters, in the same function call, we can select or deselect the attributes we want.

For example, if we do not want to include the name of the month, we can configure it using the select_month_level() function or at the time of object creation with a parameter of the same name, as shown below.

w_date_5 <- w_date |>
  select_month_level(month_name = FALSE)

w_date_6 <- when(
  start = lubridate::today(),
  end = lubridate::today() + lubridate::years(5),
  month_name = FALSE
)

identical(w_date_5, w_date_6)
#> [1] TRUE

w_date_5 |>
  get_level_attribute_names(name = 'month', selected = TRUE)
#> [1] "year_month"     "month"          "month_num_name"

If we only want to include the name of the month, we can do it as follows.

w_date_7 <- w_date |>
  select_month_level(exclude_all = TRUE, month_name = TRUE)

w_date_7 |>
  get_level_attribute_names(name = 'month', selected = TRUE)
#> [1] "month_name"

There is available a similar function for each level: time, date, week, month, quarter, semester and year.

In the case of the date-based dimension levels, we can select or deselect them directly using the select_date_levels() function. It allows us to treat the levels globally in the same way that we have been treating the attributes. For example, to exclude the month level we can do the following.

w_date_8 <- w_date |>
  select_date_levels(month_level = FALSE)

w_date_8 |>
  get_level_attribute_names(name = 'month', selected = TRUE)
#> character(0)

We can also perform that same operation at object creation time, as shown below.

w_date_9 <- when(
  start = lubridate::today(),
  end = lubridate::today() + lubridate::years(5),
  month_level = FALSE
)

In the case of a time-based dimension, as we have seen, we only consider one level called “time”, as shown again below.

w_time |>
  get_level_names()
#> [1] "time"

We can configure the attributes it includes using the select_time_level() function. The attribute called “hour” must always be included. If we do not include the “minute” attribute then “second” will not be included either. Below are some examples of definition and the result obtained.

w_time_6 <- w_time |>
  select_time_level(exclude_all = TRUE)

w_time_6 |>
  get_level_attribute_names(selected = TRUE)
#> [1] "hour"

w_time_7 <- w_time |>
  select_time_level(minute = FALSE)

w_time_7 |>
  get_level_attribute_names(selected = TRUE)
#> [1] "time"     "hour"     "day_part"

Generation of the result

Once the characteristics of the dimension are defined, we can generate its table.

Although we have not yet generated the dimension table, the following function returns the names of the attributes it will contain. They can be obtained as a string or as a vector of strings.

w_date |>
  get_table_attribute_names(as_string = FALSE)
#>  [1] "id"             "date"           "month_day"      "week_day"      
#>  [5] "day_name"       "day_num_name"   "year_week"      "week"          
#>  [9] "year_month"     "month"          "month_name"     "month_num_name"
#> [13] "year"

If it is generated in the form of a string (as_string = TRUE, default option), the result can be used to easily change the name of the attributes, when the table is generated, using the set_table_attribute_names() function, if deemed necessary, as shown in section Renaming the attributes.

When we consider that the dimension is already properly defined, we can generate its corresponding table using the generate_table() function. We update the definition of the object. Any errors found will be notified to us.

w_date <- w_date |>
  generate_table()

w_time <- w_time |>
  generate_table()

Once the table is generated, we can export it. If we want to access the table and it is not yet generated, it will send us an error message.

If we make changes to the configuration, it is our responsibility to generate the table again.

Getting the dimension table

We can access the dimension table to work with it in R (for example using the rolap package), or export it in the format that is most convenient for us to work with other tools.

Next we access the generated tables and display them. First of all for the date.

t_date <- w_date |>
  get_table()

The first and last rows of the obtained result are shown below.

pander::pandoc.table(rbind(head(t_date, 5), tail(t_date, 5)),
                     split.table = Inf)

Let’s generate the dimension at the week level including all its attributes (if we change the definition, we have to generate the table again).

t_date <- w_date |>
  select_date_levels(day_level = FALSE) |>
  select_week_level(include_all = TRUE) |>
  generate_table() |>
  get_table()

The first and last rows of the obtained result are shown below.

pander::pandoc.table(rbind(head(t_date, 5), tail(t_date, 5)),
                     split.table = Inf)

Next for the time.

t_time <- w_time |>
  get_table()

The start and end of the result table is shown below.

pander::pandoc.table(rbind(head(t_time, 5), tail(t_time, 5)),
                     split.table = Inf)

day_part is predefined in English. Literals or associated hours can be accessed and changed using the get_day_part() and set_day_part() functions, as shown in section Names of the parts of the day.

Now we are going to generate it at the minute level.

t_time <- w_time |>
  select_time_level(second = FALSE) |>
  generate_table() |>
  get_table()

The start and end from the new result table is shown below.

pander::pandoc.table(rbind(head(t_time, 5), tail(t_time, 5)),
                     split.table = Inf)

We can take the generated tables to any Relational DBMS, as shown below, we just have to pass a connection to the database as a parameter.

my_db <- DBI::dbConnect(RSQLite::SQLite())

w_date |>
  get_table_rdb(my_db)

w_time |>
  get_table_rdb(my_db)

DBI::dbListTables(my_db)
#> [1] "date" "time"

DBI::dbDisconnect(my_db)

In the previous example, since we have not explicitly defined any names for the tables in the configuration, they have been assigned as a name the element on which they are based (date or time). We can define these names, as shown in section Table name.

Functions to get the tables in xlsx and csv format are also available: get_table_xlsx() and get_table_csv().

Dimension table features

The result of a definition operation performed with the where package is a table of a dimension at a given level of granularity, with the attributes selected for that level or associated coarser levels. So far we have focused on level attributes and table instances. In this section we are going to configure other characteristics of it.

wd_1 <- when(name = 'dim_where')

wd_2 <- when() |>
  define_characteristics(name = 'dim_where')

my_db <- DBI::dbConnect(RSQLite::SQLite())

wd_1 |>
  generate_table() |>
  get_table_rdb(my_db)

DBI::dbListTables(my_db)
#> [1] "dim_where"

DBI::dbDisconnect(my_db)

wd_2 |>
  generate_table() |>
  get_table_csv()
#> [1] "C:\\Users\\joses\\AppData\\Local\\Temp\\Rtmp6XNQKs/dim_where.csv"

when() |>
  get_table_attribute_names(as_string = FALSE)
#>  [1] "id"             "date"           "month_day"      "week_day"      
#>  [5] "day_name"       "day_num_name"   "year_week"      "week"          
#>  [9] "year_month"     "month"          "month_name"     "month_num_name"
#> [13] "year"

when(surrogate_key = FALSE) |>
  get_table_attribute_names(as_string = FALSE)
#>  [1] "date"           "month_day"      "week_day"       "day_name"      
#>  [5] "day_num_name"   "year_week"      "week"           "year_month"    
#>  [9] "month"          "month_name"     "month_num_name" "year"

wd_3 <- when() |>
  generate_table()

wd_3 |>
  get_table_attribute_names()
#> [1] "c('id', 'date', 'month_day', 'week_day', 'day_name', 'day_num_name', 'year_week', 'week', 'year_month', 'month', 'month_name', 'month_num_name', 'year')"

wd_3 <- wd_3 |>
  set_table_attribute_names(
    c(
      'id_when',
      'date',
      'month_day',
      'week_day',
      'day_name',
      'day_num_name',
      'year_week',
      'week',
      'year_month',
      'month',
      'month_name',
      'month_num_name',
      'year'
    )
  )

wd_3 |>
  get_table_attribute_names(as_string = FALSE)
#>  [1] "id_when"        "date"           "month_day"      "week_day"      
#>  [5] "day_name"       "day_num_name"   "year_week"      "week"          
#>  [9] "year_month"     "month"          "month_name"     "month_num_name"
#> [13] "year"

Names of the parts of the day

We have associated each hour with the usual part of the day in English. We can consult them and change them if necessary (for example, to define them in another language) using the get_day_part() and set_day_part() functions.

Below is an example where the definition is modified to consider only two parts in the day.

when() |>
  get_day_part()
#>          00          01          02          03          04          05 
#>     "Night"     "Night"     "Night"     "Night"     "Night"   "Morning" 
#>          06          07          08          09          10          11 
#>   "Morning"   "Morning"   "Morning"   "Morning"   "Morning"   "Morning" 
#>          12          13          14          15          16          17 
#> "Afternoon" "Afternoon" "Afternoon" "Afternoon" "Afternoon"   "Evening" 
#>          18          19          20          21          22          23 
#>   "Evening"   "Evening"   "Evening"     "Night"     "Night"     "Night"

when() |>
  set_day_part(hour = c(20:23, 0:5), name = "Night") |>
  set_day_part(hour = c(6:19), name = "Day") |>
  get_day_part()
#>      00      01      02      03      04      05      06      07      08      09 
#> "Night" "Night" "Night" "Night" "Night" "Night"   "Day"   "Day"   "Day"   "Day" 
#>      10      11      12      13      14      15      16      17      18      19 
#>   "Day"   "Day"   "Day"   "Day"   "Day"   "Day"   "Day"   "Day"   "Day"   "Day" 
#>      20      21      22      23 
#> "Night" "Night" "Night" "Night"

Configuration of day and week levels

This section shows the configuration options available for the day and week levels.

wd_1 <- when(week_starts_monday = FALSE)

wd_2 <- when() |>
  define_characteristics(week_starts_monday = FALSE)

t <- get_week_date_range(start = "2024-01-01", end = "2029-12-31")

pander::pandoc.table(rbind(head(t, 5), tail(t, 5)),
                     split.table = Inf)

tw <- when(values = t[["last"]], month_level = FALSE) |>
  select_day_level(exclude_all = TRUE, date = TRUE) |>
  generate_table() |>
  get_table()

pander::pandoc.table(rbind(head(tw, 5), tail(tw, 5)),
                     split.table = Inf)

Change the definition function of an attribute

Each attribute is defined by a specific function. All definition functions have the same structure: they receive a table, a column with the date or time values, possibly additional configuration parameters, and return the table to which a new column with the attribute name and values has been added, calculated from the values.

We can get the definition function of any attribute using the get_attribute_definition_function() function. Below is the function for the “year” attribute and also the function of the year and week combination, “year_week” attribute (it uses one of the additional parameters available, the dimension type).

wd <- when()

wd |>
  get_attribute_definition_function(name = "year")
#> function (table, values, ...) 
#> {
#>     table[["year"]] <- as.character(lubridate::year(values))
#>     table
#> }
#> <bytecode: 0x000001cf0e5cdc48>
#> <environment: namespace:when>

wd |>
  get_attribute_definition_function(name = "year_week")
#> function (table, values, ...) 
#> {
#>     dots <- list(...)
#>     type <- dots[["type"]]
#>     switch(type, iso = {
#>         year <- lubridate::isoyear(values)
#>         week <- lubridate::isoweek(values)
#>     }, epi = {
#>         year <- lubridate::epiyear(values)
#>         week <- lubridate::epiweek(values)
#>     }, {
#>         year <- lubridate::year(values)
#>         week <- lubridate::week(values)
#>     })
#>     table[["year_week"]] <- paste0(year, "-", sprintf("%02d", 
#>         week))
#>     table
#> }
#> <bytecode: 0x000001cf0e628150>
#> <environment: namespace:when>

In view of the functions, we can define one with the same structure and establish it as a new attribute definition function using the set_attribute_definition_function() function. For example, below shows how to change the year attribute definition function to get an additional attribute if the year associated with the week can be different from the year of the date.

f <- function(table, values, ...) {
  dots <- list(...)
  type <- dots[['type']]
  table[['year']] <- as.character(lubridate::year(values))
  if (type == 'iso') {
    table[['week_year']] <- as.character(lubridate::isoyear(values))
  } else if (type == 'epi') {
    table[['week_year']] <- as.character(lubridate::epiyear(values))
  }
  table
}

wd <- wd |>
  set_attribute_definition_function(name = "year", f)

In this case, what we do is add two columns for the year: one with the year of the date and another with the year of the week if this can be different from the first. We can see the result by changing the type of the dimension and generating the table, as shown below (the two year attributes have been added).

t <- wd |>
  define_characteristics(type = 'iso') |>
  generate_table() |>
  get_table()

names(t)
#>  [1] "id"             "date"           "month_day"      "week_day"      
#>  [5] "day_name"       "day_num_name"   "year_week"      "week"          
#>  [9] "year_month"     "month"          "month_name"     "month_num_name"
#> [13] "year"           "week_year"