Dimensions and dimension attributes

Attributes considered by the designer as dimensions can be grouped taking into account the natural affinities between them. In particular, they can be grouped as they describe the “who, what, where, when, how and why” associated with the modelled business process. Two attributes share a natural affinity when they are only related in one context. When their relationships are determined by transactions or activities, they can occur in multiple contexts, if this occurs, they must be located in different dimensions.

In this way, a dimension is made up of a set of naturally related dimension attributes that describe the context of facts. Dimensions are used for two purposes: fact selection and fact grouping with the desired level of detail.

Additionally, in the dimensions hierarchies with levels and descriptors can be defined. More details can be found at Jensen, Pedersen, and Thomsen (2010). These concepts are not used in the current version of the package.

Facts and measures

A fact has a granularity, which is determined by the attributes of the dimensions that are considered at each moment. Thus, a measure in a fact has two components, the numerical property of the fact and an formula, frequently the SUM aggregation function, that allows combining several values of this measure to obtain a new value of the same measure with a coarser granularity (Jensen, Pedersen, and Thomsen 2010).

According to their behaviour to obtain a coarser granularity, three types of measures are distinguished: additive, semi-additive and non-additive. For additive measures, SUM is always a valid formula that maintains the meaning of the measure when the granularity changes. For semi-additive measures, there is no point in using SUM when changing the level of detail in any of the dimensions because the meaning of the measure changes, this frequently occurs in dimensions that represents time and measures representing inventory level. For non-additive measures, values cannot be combined across any dimension using SUM because the result obtained has a different meaning from the original measure (generally occurs with ratios, percentages or unit amounts such as unit cost or unit price).

The most useful measures are additive. If we have non-additive measures, they can generally be redefined from other additive measures.

Dimension tables

Dimension tables contain the context associated with business process measures. Although they can contain any type of data, numerical data is generally not used for dimension attributes because some query tools consider any numeric data as a measure.

Dimension attributes with NULL value are a source of problems when querying since DBMS and query tools sometimes handle them inconsistently, the result depends on the product. It is recommended to avoid the use of NULL and replace them with a descriptive text. In the case of dates, it is recommended to replace the NULL values with an arbitrary date in the very far future.

Fact table

At the centre of a star schema is the fact table. In addition to containing measures, the fact table includes foreign keys that refer to each of the surrogate keys in the dimension tables.

Multiple fact tables

It is frequent the need to have several fact tables for various reasons:

• We find measurements with different grain.

• There are measures that do not occur simultaneously, for example, when one occurs, we have no value for others and vice versa.

In reality it is about different business processes, each one has to have its own fact table but they have dimensions in common. This is known as a fact constellation which corresponds to the Kimball enterprise data warehouse bus architecture.

Conformed dimensions

When star schemas share a set of common dimensions, these dimensions are called conformed dimensions.

There are several possibilities to have conformed dimensions, the most obvious form is that the dimension tables share structure and content, that is, they are identical dimensions. This is the one considered in this version of the starschemar package.

Cleaning and conforming data

When data is loaded into a star schema, errors or inconsistencies can be discovered in some of them. In some cases, it is best to make corrections at the source of the data, in operational systems. Sometimes this is not possible and there is no other option but to modify the data before loading it into the star schema or even when it is already loaded.

Inconsistencies are often found in dimensions, when dimensions are integrated into one, for example to generate a role-playing dimension or conformed dimensions. Support for modifying dimension data is provided in the package.

Dimension enrichment

The more attributes the dimensions have, the more possibilities they offer to filter or aggregate data. For this reason, it is convenient to enrich the dimensions with new attributes, whenever possible. New attributes are often defined based on existing ones. There must be a way to associate the new attributes with the dimensions.

The more attributes the dimensions have, the more possibilities they offer to filter or aggregate data. For this reason, it is convenient to enrich the dimensions with new attributes, whenever possible. New attributes are often defined based on existing ones. There must be a way to associate the new attributes with the dimensions.

Operations have been defined in the package to export dimension attributes so that their values are not repeated, and also to import them, once the new attributes have been added.

Incremental refresh

When a star schema is built, an initial load is performed with all available data from a moment in time onwards.

Operational systems continue to operate and produce data. If we want to incorporate these data into the star schema, we have two possibilities:

• Carry out a new load with all the data available at that time.

• Perform an incremental refresh with only the new data.

In order to carry out this second option, the CDC (change data capture) system allows to exclusively obtain the new data produced.

Sometimes it is also convenient to delete data that is considered to be no longer necessary, generally data from the more distant past.

In this package, it has been considered that we can obtain the new data, possibly mixed with updates to data already incorporated into the star schema, in order to carry out an incremental refresh of star schemas with them. Operations are also offered to select data and delete it if it is not considered necessary.

Dimensional modelling data according to age range

To avoid having to write the name of the attributes of the table, with the following function we can have them in the form of a string. Thus, we can copy and paste each name as needed.

dput(colnames(mrs_age))
#> c("Reception Year", "Reception Week", "Reception Date", "Data Availability Year", 
#> "Data Availability Week", "Data Availability Date", "Year", "WEEK", 
#> "Week Ending Date", "REGION", "State", "City", "Age Range", "Deaths"
#> )

The definition of the dimensional model for the data considered is shown below.

library(tidyr)
library(starschemar)

dm_mrs_age <- dimensional_model() %>%
  define_fact(
    name = "mrs_age",
    measures = c(
      "Deaths"
    ),
    agg_functions = c(
      "SUM"
    ),
    nrow_agg = "nrow_agg"
  ) %>%
  define_dimension(
    name = "when",
    attributes = c(
      "Week Ending Date",
      "WEEK",
      "Year"
    )
  ) %>%
  define_dimension(
    name = "when_available",
    attributes = c(
      "Data Availability Date",
      "Data Availability Week",
      "Data Availability Year"
    )
  ) %>%
  define_dimension(
    name = "where",
    attributes = c(
      "REGION",
      "State",
      "City"
    )
  ) %>%
  define_dimension(
    name = "who",
    attributes = c(
      "Age Range"
    )
  )

In this case, all the elements have been explicitly defined, including aggregation functions and the name of an additional measure representing the number of rows aggregated, which is always included. Only data from two of the three possible time-related dimensions have been considered.

Dimensional modelling data according to cause

In the case of data according to cause of death, the definition of the model is shown below.

dm_mrs_cause <- dimensional_model() %>%
  define_fact(
    name = "mrs_cause",
    measures = c(
      "Pneumonia and Influenza Deaths",
      "Other Deaths"
    ),
  ) %>%
  define_dimension(
    name = "when",
    attributes = c(
      "Week Ending Date",
      "WEEK",
      "Year"
    )
  ) %>%
  define_dimension(
    name = "when_received",
    attributes = c(
      "Reception Date",
      "Reception Week",
      "Reception Year"
    )
  ) %>%
  define_dimension(
    name = "when_available",
    attributes = c(
      "Data Availability Date",
      "Data Availability Week",
      "Data Availability Year"
    )
  ) %>%
  define_dimension(
    name = "where",
    attributes = c(
      "REGION",
      "State",
      "City"
    )
  )

If no aggregation function is indicated, by default, SUM is considered. Although not explicitly stated, it also includes by default the measure relative to the number of rows aggregated. In this case, the three dimensions related to the date have been defined.

Star schema definition for data according to age range

The basic definition operation of a star schema is shown below³.

st_mrs_age <- star_schema(mrs_age, dm_mrs_age)

The first rows of the obtained dimension and fact tables are shown below.

The data from the original flat table has been structured in the form of dimension tables and fact tables. Data in the columns of the original table included in the dimensions is not repeated. A surrogate key has been added to each of the dimension tables that are foreign keys in the fact table.

Next, we will apply format modification operations to the original structure obtained.

st_mrs_age <- st_mrs_age %>%
  role_playing_dimension(
    dim_names = c("when", "when_available"),
    name = "When Common",
    attributes = c("date", "week", "year")
  ) %>%
  snake_case() %>%
  character_dimensions(NA_replacement_value = "Unknown",
                       length_integers = list(week = 2))

First, a role playing dimension has been defined based on the dimensions related to dates. Then, to work with databases, the names have been adapted to the snake case criterion. Finally, the data type of the attributes of the dimensions has been transformed so that all columns except the date columns are of the character data type, in the case of numerical data, it is allowed to indicate the length of the field to fill with leading zeros, and undefined values have been replaced by the indicated value.

The first rows of the new dimension and fact tables are shown below.

In the result, it can be seen that the dimensions related to date are now role dimensions and do not have their own data, a role playing dimension has been generated with the integrated data. The fact table continues to refer to role dimensions, the value of foreign keys has been adapted to the possible new values of the surrogate keys. Additionally it can be seen that the week field now has length 2 and has 0 on the left (this is useful to sort numbers in text format).

Star schema definition for data according to cause

We are going to define the star schema and apply similar transformations to the other flat table. The transformations can be applied in any order.

st_mrs_cause <- star_schema(mrs_cause, dm_mrs_cause) %>%
  snake_case() %>%
  character_dimensions(
    NA_replacement_value = "Unknown",
    length_integers = list(
      week = 2,
      data_availability_week = 2,
      reception_week = 2
    )
  ) %>%
  role_playing_dimension(
    dim_names = c("when", "when_received", "when_available"),
    name = "when_common",
    attributes = c("date", "week", "year")
  )

In this case, since the role playing dimension definition is the last transformation defined, the format of the week column had to be defined in the three date dimensions to obtain an equivalent result. The result obtained is shown below.

In this case we have three role dimensions defined on a role playing dimension.

Star schema transformation, cleaning and conforming data

Star schemas are defined from flat table fields. In some cases it may be interesting to rename elements of the schema, especially attributes of dimensions and measures. On the other hand, dimensions can be enriched by adding additional attributes, generally derived from the rest of the attributes. These are the transformations considered in this section.

We can perform data cleaning and conforming operations on star schema dimensions. Updates defined in a star schema can be applied on another with common dimensions.

st_mrs_age <-
  st_mrs_age %>% rename_dimension_attributes(
    name = "when",
    attributes = c("week_ending_date", "week", "year"),
    new_names = c(
      "when_happened_date",
      "when_happened_week",
      "when_happened_year"
    )
  ) %>%
  rename_dimension_attributes(
    name = "where",
    attributes = c("region"),
    new_names = c("division")
  )

st_mrs_cause <-
  st_mrs_cause %>% rename_dimension_attributes(
    name = "when",
    attributes = c("week_ending_date", "week", "year"),
    new_names = c(
      "when_happened_date",
      "when_happened_week",
      "when_happened_year"
    )
  ) %>%
  rename_dimension_attributes(
    name = "where",
    attributes = c("region"),
    new_names = c("division")
  )

st_mrs_age <-
  st_mrs_age %>% rename_measures(measures = c("deaths"),
                                 new_names = c("n_deaths"))

dim_names <- st_mrs_age %>%
    get_dimension_names()

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

# View(where)
# where[where$where_key %in% c(1, 2, 62), ]

when <- st_mrs_age %>%
  get_dimension("when")

# View(when)
# when[when$when_key %in% c(36, 37, 73), ]

who <- st_mrs_age %>%
  get_dimension("who")

# View(who)

updates_st_mrs_age <- record_update_set() %>%
  match_records(dimension = where,
                old = 1,
                new = 2) 

updates_st_mrs_age <- updates_st_mrs_age %>%
  update_selection_general(
    dimension = where,
    columns_old = c("state", "city"),
    old_values = c("DE", "Wilimington"),
    columns_new = c("city"),
    new_values = c("Wilmington")
  ) 

updates_st_mrs_age <- updates_st_mrs_age %>%
  match_records(dimension = when,
                old = 37,
                new = 36) %>%
  update_record(
    dimension = when,
    old = 73,
    values = c("1962-02-17", "07", "1962")
  )

updates_st_mrs_age <- updates_st_mrs_age %>%
  update_selection(
    dimension = who,
    columns = c("age_range"),
    old_values = c("<1 year"),
    new_values = c("1: <1 year")
  ) %>%
  update_selection(
    dimension = who,
    columns = c("age_range"),
    old_values = c("1-24 years"),
    new_values = c("2: 1-24 years")
  ) %>%
  update_selection(
    dimension = who,
    columns = c("age_range"),
    old_values = c("25-44 years"),
    new_values = c("3: 25-44 years")
  ) %>%
  update_selection(
    dimension = who,
    columns = c("age_range"),
    old_values = c("45-64 years"),
    new_values = c("4: 45-64 years")
  ) %>%
  update_selection(
    dimension = who,
    columns = c("age_range"),
    old_values = c("65+ years"),
    new_values = c("5: 65+ years")
  )

st_mrs_age <- st_mrs_age %>%
  modify_dimension_records(updates_st_mrs_age)

st_mrs_cause <- st_mrs_cause %>%
  modify_dimension_records(updates_st_mrs_age)

tb_who <-
  enrich_dimension_export(st_mrs_age,
                          name = "who",
                          attributes = c("age_range"))

v <-
  c("0-24 years", "0-24 years", "25+ years", "25+ years", "25+ years")
tb_who <-
  tibble::add_column(tb_who,
                     wide_age_range = v)

st_mrs_age <-
  st_mrs_age %>%
  enrich_dimension_import(name = "who", tb_who)

tb_where <-
  enrich_dimension_export(st_mrs_age,
                          name = "where",
                          attributes = c("division"))

tb_where <-
  tibble::add_column(
    tb_where,
    division_name = c(
      "New England",
      "Middle Atlantic",
      "East North Central",
      "West North Central",
      "South Atlantic",
      "East South Central",
      "West South Central",
      "Mountain",
      "Pacific"
    ),
    region = c('1',
               '1',
               '2',
               '2',
               '3',
               '3',
               '3',
               '4',
               '4'),
    region_name = c(
      "Northeast",
      "Northeast",
      "Midwest",
      "Midwest",
      "South",
      "South",
      "South",
      "West",
      "West"
    )
  )

st_mrs_age <-
  st_mrs_age %>%
  enrich_dimension_import(name = "where", tb_where)

st_mrs_cause <-
  st_mrs_cause %>%
  enrich_dimension_import(name = "where", tb_where)

tb_missing <-
  st_mrs_age %>%
  enrich_dimension_import_test(name = "where", ft_usa_states)

tb_where_state <- ft_usa_states %>%
  tibble::add_row(state = "Unknown", state_name = "Unknown")

st_mrs_age <-
  st_mrs_age %>%
  enrich_dimension_import(name = "where", tb_where_state)

st_mrs_cause <-
  st_mrs_cause %>%
  enrich_dimension_import(name = "where", tb_where_state)

tb_where_county <- ft_usa_city_county %>%
  tibble::add_row(city = "Unknown",
                  state = "Unknown",
                  county = "Unknown")

st_mrs_age <-
  st_mrs_age %>%
  enrich_dimension_import(name = "where", tb_where_county)

st_mrs_cause <-
  st_mrs_cause %>%
  enrich_dimension_import(name = "where", tb_where_county)

Constellation definition

A constellation is defined from a list of star schemas, as shown below.

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

All dimensions of the same name in star schemas must be compatible in structure and type of columns, and are defined as conformed dimensions. The conformed dimensions share all the instances of the original dimensions, this implies possible modifications in the surrogate keys that are transmitted to foreign keys of the component fact tables.

The tables of the obtained conformed dimensions are shown below.

It can be seen that the conformed dimensions are considered regardless of the type of dimension in the star schema. Definition of conformed dimensions does not imply any change in the definition of dimensions in star schemas: Role and role playing dimensions remain the same, only their rows may have changed.

In this particular case there are no discrepancies in the values of the star schemas dimension instances, since the data source is the same for both.

If discrepancies are detected once the integration has been carried out, new modification operations can be defined on conformed dimensions (as it has been done for the dimensions of star schemas) that are applied at the constellation level and are automatically transmitted to the component star schemas (modify_conformed_dimension_records).

Refresh operations for data according to age range

Below you can see the function that groups the transformations defined for the data according to the age range.

mrs_age_definition <-
  function(ft,
           dm,
           updates,
           tb_who,
           tb_where,
           tb_where_state,
           tb_where_county) {
    star_schema(ft, dm) %>%
      role_playing_dimension(
        dim_names = c("when", "when_available"),
        name = "When Common",
        attributes = c("date", "week", "year")
      ) %>%
      snake_case() %>%
      character_dimensions(NA_replacement_value = "Unknown",
                           length_integers = list(week = 2)) %>%
      rename_dimension_attributes(
        name = "when",
        attributes = c("week_ending_date", "week", "year"),
        new_names = c(
          "when_happened_date",
          "when_happened_week",
          "when_happened_year"
        )
      ) %>%
      rename_dimension_attributes(
        name = "where",
        attributes = c("region"),
        new_names = c("division")
      ) %>%
      rename_measures(measures = c("deaths"),
                      new_names = c("n_deaths")) %>%
      modify_dimension_records(updates) %>%
      enrich_dimension_import(name = "who", tb_who) %>%
      enrich_dimension_import(name = "where", tb_where) %>%
      enrich_dimension_import(name = "where", tb_where_state) %>%
      enrich_dimension_import(name = "where", tb_where_county)
  }

We apply this function to new data sets, as shown below.

st_mrs_age_w10 <-
  mrs_age_definition(
    mrs_age_w10,
    dm_mrs_age,
    updates_st_mrs_age,
    tb_who,
    tb_where,
    tb_where_state,
    tb_where_county
  )

st_mrs_age_w11 <-
  mrs_age_definition(
    mrs_age_w11,
    dm_mrs_age,
    updates_st_mrs_age,
    tb_who,
    tb_where,
    tb_where_state,
    tb_where_county
  )

Errors may occur because the data is different from the original, especially in the dimension enrichment part, if data is missing. In this case, we must eliminate from the function the lines corresponding to enrichment and later try to enrich the dimension by checking the errors with enrich_dimension_import_test.

Once we have the data in the same format, we can apply the incremental refresh to the original star schema, as follows.

st_mrs_age <- st_mrs_age %>%
  incremental_refresh_star_schema(st_mrs_age_w10, existing = "replace") %>%
  incremental_refresh_star_schema(st_mrs_age_w11, existing = "replace")

In this case, it has been assumed that if data from previous periods appears among the new data, the new data has to replace the previous data (value “replace” in existing parameter).

If the star schema has been integrated into a constellation, the incremental refresh can be performed on it, as follows.

ct_mrs <- ct_mrs %>%
  incremental_refresh_constellation(st_mrs_age_w10, existing = "replace") %>%
  incremental_refresh_constellation(st_mrs_age_w11, existing = "replace")

In this case, the corresponding star schema, the conformed dimensions and all the star schemas that share them are updated.

Refresh operations for data according to cause

Similar to how it has been done for age data, it can be done for cause data, as shown below.

mrs_cause_definition <-
  function(ft,
           dm,
           updates,
           tb_where,
           tb_where_state,
           tb_where_county) {
    star_schema(ft, dm) %>%
      snake_case() %>%
      character_dimensions(
        NA_replacement_value = "Unknown",
        length_integers = list(
          week = 2,
          data_availability_week = 2,
          reception_week = 2
        )
      ) %>%
      role_playing_dimension(
        dim_names = c("when", "when_received", "when_available"),
        name = "when_common",
        attributes = c("date", "week", "year")
      ) %>%
      rename_dimension_attributes(
        name = "when",
        attributes = c("week_ending_date", "week", "year"),
        new_names = c(
          "when_happened_date",
          "when_happened_week",
          "when_happened_year"
        )
      ) %>%
      rename_dimension_attributes(
        name = "where",
        attributes = c("region"),
        new_names = c("division")
      ) %>%
      modify_dimension_records(updates) %>%
      enrich_dimension_import(name = "where", tb_where) %>%
      enrich_dimension_import(name = "where", tb_where_state) %>%
      enrich_dimension_import(name = "where", tb_where_county)
  }

st_mrs_cause_w10 <-
  mrs_cause_definition(
    mrs_cause_w10,
    dm_mrs_cause,
    updates_st_mrs_age,
    tb_where,
    tb_where_state,
    tb_where_county
  )

st_mrs_cause_w11 <-
  mrs_cause_definition(
    mrs_cause_w11,
    dm_mrs_cause,
    updates_st_mrs_age,
    tb_where,
    tb_where_state,
    tb_where_county
  )

st_mrs_cause <- st_mrs_cause %>%
  incremental_refresh_star_schema(st_mrs_cause_w10, existing = "group") %>%
  incremental_refresh_star_schema(st_mrs_cause_w11, existing = "group")

ct_mrs <- ct_mrs %>%
  incremental_refresh_constellation(st_mrs_cause_w10, existing = "group") %>%
  incremental_refresh_constellation(st_mrs_cause_w11, existing = "group")

In this case, the previously existing data is treated differently than it was in the previous case, now what is done is grouping it using the defined aggregation functions, assuming it is additional data that has not been entered before (value “group” in existing parameter).

Filter and purge operations

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. In general, they can be selected considering any combination of dimensions, not just the time dimension.

Suppose we want to delete the Boston data working at the constellation level. First, we select them considering the stars that make up the constellation.

st1 <- ct_mrs %>%
  get_star_schema("mrs_age") %>%
  filter_fact_rows(name = "where", city == "Boston")

st2 <- ct_mrs %>%
  get_star_schema("mrs_cause") %>%
  filter_fact_rows(name = "where", city == "Boston")

We can work both at the star or constellation level. In this example, we are going to do it at the constellation level (working on a temporary variable so as not to lose data), as we have been doing.

ct_tmp <- ct_mrs %>%
  incremental_refresh_constellation(st1, existing = "delete") %>%
  incremental_refresh_constellation(st2, existing = "delete")

These operations have only removed the fact data. The first records of the where dimension are shown below.

Once the fact data is removed, we can remove the data for the dimensions that are no longer needed using the following function.

ct_tmp <- ct_tmp %>%
  purge_dimensions_constellation()

The result in the where dimension is shown below.

The Boston data has disappeared and the surrogate keys have been reassigned (on the temporary variable).

Star schema

Various export possibilities are offered. Specifically, for a star schema one of them is to export the data as a flat table. The main difference from the initial data is that we have cleaned and conformed it. This operation is offered for completeness. To work only with flat tables, this package is not suitable.

To work with databases, it is useful to be able to export a star schema as a list of tibble with dimension and fact tables, as shown below.

tl <- st_mrs_age %>%
  star_schema_as_tibble_list()

Optionally, the export function allows the role playing dimensions to be included.

Constellation

To export constellation data, as well as a tibble list, the multistar format may be interesting, where you have a list of tibble for fact tables and another for dimension tables.

ms_mrs <- ct_mrs %>%
  constellation_as_multistar()

multistar

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.

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

The first rows of the flat table obtained as a result are shown below.

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")