starschemar

The multidimensional data model was defined in the 1990s with the aim of supporting data analysis. Data in multidimensional systems is obtained from operational systems and is transformed to adapt it to the new structure.

Transformations can be carried out using professional ETL (extract, transform and load) tools. Recently, tools aimed at end users have emerged, which are also aimed at performing transformation operations. All these tools are very useful to carry out the transformation process, they provide a development environment to define the transformation operations in a general way.

Frequently, the operations to be performed aim to transform a flat table (with data that comes from operational systems) into a star schema (which implements a multidimensional system). With the tools mentioned above, this transformation can be carried out, but it requires a lot of work. I am not aware of any tools with operations designed to specifically support this transformation process.

The goal of starschemar is to define transformations that allow you to easily obtain star schemas from flat tables. In addition, it includes basic data cleaning, dimension enrichment, incremental data refresh and query operations, adapted to this context.

Based on this package, the rolap package has been developed. It incorporates the main functionalities for which starschemar was initially intended and it has a new data model defined with the aim of facilitating its maintenance and evolution.

Installation

You can install the released version of starschemar from CRAN with:

install.packages("starschemar")

And the development version from GitHub with:

# install.packages("devtools")
devtools::install_github("josesamos/starschemar")

Example

To illustrate how the package works we will use a small part of the Deaths in 122 U.S. cities - 1962-2016. 122 Cities Mortality Reporting System data set in the form of a flat table, the first rows of which are shown below.

Year	WEEK	Week Ending Date	REGION	State	City	Pneumonia and Influenza Deaths	All Deaths	Other Deaths
1962	1	1962-01-06	1	CT	Bridgeport	3	46	43
1962	2	1962-01-13	1	CT	Bridgeport	2	43	41
1962	3	1962-01-20	1	CT	Bridgepor	2	40	38
1962	1	1962-01-06	9	WA	Tacoma	4	50	46
NA	NA	1962-01-13	9	WA	Tacoma	2	45	43
1962	3	1962-01-20	9	WA	Tacoma	0	39	39

The original data has been modified to have some erroneous or missing data.

The transformation to obtain a star schema from the flat table using starschemar package is as follows:

library(starschemar)

# columns to consider in the definition
dput(colnames(ft))
#> c("Year", "WEEK", "Week Ending Date", "REGION", "State", "City", 
#> "Pneumonia and Influenza Deaths", "All Deaths", "Other Deaths"
#> )

dm <- 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 = "where",
    attributes = c(
      "REGION",
      "State",
      "City"
    )
  )

st <- star_schema(ft, dm) |>
  snake_case() |>
  character_dimensions(
    NA_replacement_value = "Unknown",
    length_integers = list(week = 2)
  )

The tables of dimensions and facts of the obtained star schema are shown below.

when_key	week_ending_date	week	year
1	1962-01-06	01	1962
2	1962-01-13	02	1962
3	1962-01-13	Unknown	Unknown
4	1962-01-20	03	1962

where_key	region	state	city
1	1	CT	Bridgepor
2	1	CT	Bridgeport
3	9	WA	Tacoma

when_key	where_key	pneumonia_and_influenza_deaths	other_deaths	nrow_agg
1	2	3	43	1
1	3	4	46	1
2	2	2	41	1
3	3	2	43	1
4	1	2	38	1
4	3	0	39	1

The tables show the erroneous and missing data. We are going to perform some data cleaning operations to correct them.

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

when <- st |>
  get_dimension("when")

updates <- record_update_set() |>
  update_selection(
    dimension = where,
    columns = c("city"),
    old_values = c("Bridgepor"),
    new_values = c("Bridgeport")
  ) |>
  match_records(dimension = when,
                old = 3,
                new = 2)

st <- st |>
  modify_dimension_records(updates)

The new dimension and fact tables are shown below.

when_key	week_ending_date	week	year
1	1962-01-06	01	1962
2	1962-01-13	02	1962
3	1962-01-20	03	1962

where_key	region	state	city
1	1	CT	Bridgeport
2	9	WA	Tacoma

where_key	when_key	pneumonia_and_influenza_deaths	other_deaths	nrow_agg
1	1	3	43	1
1	2	2	41	1
1	3	2	38	1
2	1	4	46	1
2	2	2	43	1
2	3	0	39	1

In addition to the operations in the examples shown, starschemar offers support for defining role playing and role dimensions in a star schema, fact constellations with conformed dimensions, dimension enrichment, incremental data refresh operations, the ability to export results in various tibble-based formats, and query operations, adapted to this context.