Flat table with the initial data

We use a dataset based on the Deaths in 122 U.S. cities - 1962-2016. 122 Cities Mortality Reporting System dataset. Two additional dates have been generated, which were not present in the original dataset.

We have selected a small dataset so that the result can be more easily appreciated. Several columns with time information have been added so that it is justified to define a time dimension that can play several roles.

Star schema

To define the schema, we get the table columns as shown below.

dput(colnames(ft_cause_rpd))
#> c("Reception Year", "Reception Week", "Reception Date", "Data Availability Year", 
#> "Data Availability Week", "Data Availability Date", "Year", "WEEK", 
#> "Week Ending Date", "REGION", "State", "City", "Pneumonia and Influenza Deaths", 
#> "All Deaths", "Other Deaths")

The definition of the star schema is shown below.

when <- dimension_schema(name = "When",
                         attributes = c("Year",
                                        "WEEK",
                                        "Week Ending Date"))
when_available <- dimension_schema(
  name = "When Available",
  attributes = c(
    "Data Availability Year",
    "Data Availability Week",
    "Data Availability Date"
  )
)
where <- dimension_schema(name = "where",
                          attributes = c("REGION",
                                         "State",
                                         "City"))
s <- star_schema() |>
  define_facts(fact_schema(
    name = "MRS Cause",
    measures = c("Pneumonia and Influenza Deaths",
                 "All Deaths")
  )) |>
  define_dimension(when) |>
  define_dimension(when_available) |>
  define_dimension(dimension_schema(
    name = "When Received",
    attributes = c("Reception Year",
                   "Reception Week",
                   "Reception Date")
  )) |>
  define_dimension(where)

As we will define more star schemas with dimensions in common, some of the dimensions have been defined as variables, to avoid repeating their definition later.

Star database

From the star schema and the flat table, we define the star database, as can be seen below.

db <- star_database(s, ft_cause_rpd) |>
  snake_case()

It has been transformed to snake case because it is usually more appropriate if we work in RDBMS (Relational Database Management Systems).

It can be seen that the tables associated with the dimension when (when, when_available, when_received) have the same structure, although the names of their attributes are different because they come from the same flat table and the field names cannot be repeated in the same table.

The instances of the tables of dimensions and facts of the star database are shown below.

We observe that each of the when dimensions (when, when_available, when_received) has its own set of instances, exclusively those necessary for the concept they represent.

Star database with a role-playing dimension and role dimensions

In the star database, we can define that any one of the dimensions is the role-playing dimension and the other dimensions are various roles of that dimension. The only requirement is that all dimensions must have the same number of attributes. This definition is shown below.

db_1 <- db |>
  role_playing_dimension(
    rpd = "when",
    roles = c("when_available", "when_received")
  )

The result can be seen in the instances of the star database tables, below.

We can see that the tables associated with the when dimension contain the same instances and the fact table has been updated appropriately.

In this example, the field names have been preserved, but surely we may want all tables to have the same field names, except for the surrogate key. This can be easily achieved by indicating that we want the names of the fields of the role-playing dimension or by indicating new names in the function. The first alternative is shown below, using the rpd_att_names parameter.

db_2 <- db |>
  role_playing_dimension(
    rpd = "when",
    roles = c("when_available", "when_received"),
    rpd_att_names = TRUE
  )

In this case, the schema is displayed as the result since the content of the tables is the same.

The physical tables are kept in the star database but we work with a RDBMS and we want to work with only one physical table for the dimension, the rest of the tables can be easily defined by views.

Second star database

We start with a similar dataset, with dates added to the original dataset, as shown below.

dput(colnames(ft_age_rpd))
#> c("Arrival Year", "Arrival Week", "Arrival 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 star schema is shown below.

s_2 <- star_schema() |>
  define_facts(fact_schema(
    name = "MRS Age",
    measures = c(
      "Deaths"
    )
  )) |>
  define_dimension(when) |>
  define_dimension(when_available) |>
  define_dimension(dimension_schema(
    name = "When Arrived",
    attributes = c(
      "Arrival Year",
      "Arrival Week",
      "Arrival Date"
    )
  )) |>
  define_dimension(dimension_schema(
    name = "Who",
    attributes = c(
      "Age Range"
    )
  )) |>
  define_dimension(where)

We have reused the definitions of the When and When Available dimensions from the previous schema. A new dimension When Arrived has been added with different names in order to show how the definition works.

The definition of the star database is shown below.

db_3 <- star_database(s_2, ft_age_rpd) |>
  role_playing_dimension(
    rpd = "When Arrived",
    roles = c("When Available"),
    att_names = c("Year", "Week", "Week Ending Date")
  ) |>
  snake_case()

In this case, a role-playing dimension and a role dimension have been defined. The When dimension has not been included in this definition also to show how the system works.

The tables of the new star database are shown below.

It can be seen that the When dimension is independent, it does not have the same instances as the other dimensions.

Constellation

The definition of the constellation is shown below.

ct <- constellation("MRS", db_2, db_3)

Below we can see the graphic representation of the tables that compose it.

The situation is that the When dimension in this second star database is independent of the other dimensions, but it is a common dimension with the first star database: it has to be a conformed dimension.

Below are the instances of the tables to better appreciate the result.

We can see that all the tables with information related to When dimension have the same instances, including all the instances of both star databases. In this example, the dimension attributes have been renamed.

The definition of the role-playing dimension and the role dimensions have been updated when integrating the two star databases. It can be queried using the following function.

ct  |>
  get_role_playing_dimension_names()
#> $rpd_1
#> [1] "when"           "when_arrived"   "when_available" "when_received"

The role-playing and role dimensions that share instances are displayed.