Star schema definition

A star schema is defined from a flat table and a dimensional model definition. Once defined, a star schema can be transformed by defining role playing dimensions, changing the writing style of element names or the type of dimension attributes. These operations are carried out through the following functions:

• star_schema(): Creates a star_schema object from a flat table (implemented by a tibble) and a dimensional_model object. Example:

st <- star_schema(mrs_age, dm_mrs_age)

• role_playing_dimension(): Given a list of star_schema dimension names, all with the same structure, a role playing dimension with the indicated name and attributes is generated. The original dimensions become role dimensions defined from the new role playing dimension. Example:

st <- star_schema(mrs_age, dm_mrs_age) |>
  role_playing_dimension(
    dim_names = c("when", "when_available"),
    name = "When Common",
    attributes = c("Date", "Week", "Year")
  )

• snake_case(): Transform fact, dimension, measurement, and attribute names according to the snake case style. Example:

st <- star_schema(mrs_age, dm_mrs_age) |>
  snake_case()

• character_dimensions(): Transforms numeric type attributes of dimensions into character type. In a star_schema numerical data are measurements that are situated in the facts. Numerical data in dimensions are usually codes, day, week, month or year numbers. There are tools that consider any numerical data to be a measurement, for this reason it is appropriate to transform the numerical data of dimensions into character data. It also allows indicating the literal to be used in case the numerical value is not defined. Example:

st <- star_schema(mrs_age, dm_mrs_age) |>
  character_dimensions()

Star schema rename

Once a star schema is defined, we can rename its elements. It is necessary to be able to rename attributes of dimensions and measures of facts because the definition operations only allowed us to select columns of a flat table. For completeness also dimensions and facts can be renamed. To carry out these operations, the following functions are available:

• rename_dimension(): Set new name for a dimension. Example:

st <- st_mrs_age |>
  rename_dimension(name = "when", new_name = "when_happened")

• get_dimension_attribute_names(): Get the name of attributes in a dimension, so that it is easier to modify them if necessary. Example:

attribute_names <- 
  st_mrs_age |> get_dimension_attribute_names("when")

• rename_dimension_attributes(): Set new names of some attributes in a dimension. Example:

st <-
  st_mrs_age |> rename_dimension_attributes(
    name = "when",
    attributes = c("when_happened_week", "when_happened_year"),
    new_names = c("week", "year")
  )

• rename_fact(): Set new name for facts. Example:

st <- st_mrs_age |> rename_fact("age") 

• get_measure_names(): Get the name of the measures in fact, so that it is easier to modify them if necessary. Example:

measure_names <- 
  st_mrs_age |> get_measure_names()

• rename_measures(): Set new names of some measures in facts. Example:

st <-
  st_mrs_age |> rename_measures(measures = c("n_deaths"),
                                 new_names = c("num_deaths"))

Constellation definition

Based on various star schemas, a constellation can be defined in which star schemas share common dimensions. Dimensions with the same name must be shared. It is defined by the following function:

• constellation(): Creates a constellation object from a list of star_schema objects. All dimensions with the same name in the star schemas have to be conformable. Example:

ct <- constellation(list(st_mrs_age, st_mrs_cause), name = "mrs")

Obtaining components

We can obtain dimensions from a star schema or conformed dimensions from a fact constellation. Available functions in both cases are similar.

dn <- st_mrs_age |>
  get_dimension_names()

where <- st_mrs_age |>
  get_dimension("where")

dn <- ct_mrs |>
  get_conformed_dimension_names()

when <- ct_mrs |>
  get_conformed_dimension("when")

stn <- ct_mrs |>
  get_star_schema_names()

age <- ct_mrs |>
  get_star_schema("mrs_age")

Definition of updates

Modifications are defined on dimension rows in various ways based exclusively on the values of the dimension fields. Although the surrogate key intervenes in the definition, the result, internally, does not depend on it so that it can be applied more generally in other star schemas.

• record_update_set(): A record_update_set object is created. Stores updates on dimension records. Each update is made up of a dimension name, an old value set, and a new value set. Example:

updates <- record_update_set()

• match_records(): For a dimension, given the primary key of two records, it adds an update to the set of updates that modifies the combination of values of the rest of attributes of the first record so that they become the same as those of the second. Example:

updates <- record_update_set() |>
  match_records(dimension = where,
                old = 1,
                new = 2)

• update_record(): For a dimension, given the primary key of one record, it adds an update to the set of updates that modifies the combination of values of the rest of attributes of the selected record so that they become those given. Example:

updates <- record_update_set() |>
  update_record(
    dimension = who,
    old = 1,
    values = c("1: <1 year")
  )

• update_selection(): For a dimension, given a vector of column names, a vector of old values and a vector of new values, it adds an update to the set of updates that modifies all the records that have the combination of old values in the columns with the new values in those same columns. Example:

updates <- record_update_set() |>
  update_selection(
    dimension = where,
    columns = c("city"),
    old_values = c("Bridgepor"),
    new_values = c("Bridgeport")
  )

• update_selection_general(): For a dimension, given a vector of column names, a vector of old values for those columns, another vector column names, and a vector of new values for those columns, it adds an update to the set of updates that modifies all the records that have the combination of old values in the first column vector with the new values in the second column vector. Example:

updates <- record_update_set() |>
  update_selection_general(
    dimension = where,
    columns_old = c("state", "city"),
    old_values = c("CT", "Bridgepor"),
    columns_new = c("city"),
    new_values = c("Bridgeport")
  )

Updates application

Defined updates can be applied on a star schema or on the conformed dimension of a fact constellation.

st <- st_mrs_age |>
  modify_dimension_records(updates_st_mrs_age)

ct <- ct_mrs |>
  modify_conformed_dimension_records(updates_st_mrs_age)

Star schema

• incremental_refresh_star_schema(): Incrementally refresh a star schema with the content of a new one that is integrated into the first. Once the dimensions are integrated, if there are records in the fact table whose keys match the new ones, new ones can be ignored, they can be replaced by new ones, all of them can be grouped using the aggregation functions, or they can be deleted. Therefore, the possible values of the existing parameter are: “ignore”, “replace”, “group” or “delete”. Example:

st <- st_mrs_age |>
  incremental_refresh_star_schema(st_mrs_age_w10, existing = "replace")

Sometimes the data refresh consists of eliminating data that is no longer necessary, generally because it corresponds to a period that has stopped being analysed but it can also be for other reasons. This data can be selected using the following function:

• filter_fact_rows(): Filter fact rows based on dimension conditions in a star schema. Dimensions remain unchanged. Filtered rows can be deleted using the incremental_refresh_star_schema function. Example:

st <- st_mrs_age |>
  filter_fact_rows(name = "when", when_happened_week <= "03") |>
  filter_fact_rows(name = "where", city == "Bridgeport")

st2 <- st_mrs_age |>
  incremental_refresh_star_schema(st, existing = "delete")

Once the fact data is removed (using the other incremental refresh functions), we can remove the data for the dimensions that are no longer needed using the following function:

• purge_dimensions_star_schema(): Delete instances of dimensions not related to facts in a star schema. Example:

st3 <- st2 |>
  purge_dimensions_star_schema()

Constellation

• incremental_refresh_constellation(): Incrementally refresh a star schema in a constellation with the content of a new star schema that is integrated into the first. Example:

ct <- ct_mrs |>
  incremental_refresh_constellation(st_mrs_age_w10, existing = "replace")

• purge_dimensions_constellation(): Delete instances of dimensions not related to facts in a star schema. It performs the operation for each of the component star_schemas and also for the conformed dimensions. Example:

ct <- ct_mrs |>
  purge_dimensions_constellation()

Star schema

• star_schema_as_flat_table(): We can again obtain a flat table, implemented using a tibble, from a star schema. Example:

ft <- st_mrs_age |>
  star_schema_as_flat_table()

• star_schema_as_multistar(): We can obtain a multistar. A multistar only distinguishes between general and conformed dimensions, each dimension has its own data. It can contain multiple fact tables. Example:

ms <- st_mrs_age |>
  star_schema_as_multistar()

• star_schema_as_tibble_list(): We can obtain a tibble list with them. Role playing dimensions can be optionally included. Example:

tl <- st_mrs_age |>
  star_schema_as_tibble_list(include_role_playing = TRUE)

Constellation

• constellation_as_multistar(): We can obtain a multistar. A multistar only distinguishes between general and conformed dimensions, each dimension has its own data. It can contain multiple fact tables. Example:

ms <- ct_mrs |>
  constellation_as_multistar()

• constellation_as_tibble_list(): We can obtain a tibble list with them. Role playing dimensions can be optionally included. Example:

tl <- ct_mrs |>
  constellation_as_tibble_list(include_role_playing = TRUE)

multistar

• multistar_as_flat_table(): We can obtain a flat table, implemented using a tibble, from a multistar (which can be the result of a query). If it only has one fact table, it is not necessary to provide its name. Example:

ft <- ms_mrs |>
  multistar_as_flat_table(fact = "mrs_age")