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

Facts have 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 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 end user 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 database 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.

Constellation

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

• We find measures 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 constellation which corresponds to the Kimball enterprise data warehouse bus architecture.

Conformed dimensions

When star databases share a set of common dimensions in a constellation, 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 rolap package.

Star Schema

We create an star_schema object using the star_schema() function, as shown below.

s <- star_schema()

We already have an empty schema, we can add facts and dimensions to it, as shown in the sections that follow.

Dimensions

We can define the dimensions through variables, using the dimension_schema() function and then the define_dimension() function in a star_schema object, or directly in a star_schema object , using the define_dimension() function. In the example below, the result in both cases is the same. The advantage is that by using variables we save defining them several times if the same dimension appears in several star schemes.

# definition 1
when <- dimension_schema(name = "When",
                         attributes = c("Year",
                                        "WEEK"))
s_age <- star_schema() |>
  define_dimension(when)

# definition 2
s_age <- star_schema() |>
  define_dimension(name = "When",
                         attributes = c("Year",
                                        "WEEK"))

Facts

For completeness, facts can also be defined as variables or directly associated with the star_schema object. As for the dimensions, in the example below, the result in both cases is the same.

# definition 1
mrs_cause <- fact_schema(name = "MRS Cause",
               measures = c("Pneumonia and Influenza Deaths",
                            "All Deaths"))
s_cause <- star_schema() |>
  define_facts(mrs_cause)

# definition 2
s_cause <- star_schema() |>
  define_facts(name = "MRS Cause",
               measures = c("Pneumonia and Influenza Deaths",
                            "All Deaths"))

Each measure has an aggregation function associated with it, by default it is SUM. Alternatively, the MAX or MIN functions can be used. AVG cannot be used because the average of averages is not the total average. Instead, a field called nrow_agg (an alternative name can be specified) is always added where the number of records added in the result row is stored. With that value and the result of SUM, the value of the AVG function can be obtained.

The aggregation functions are needed to get the data at the granularity defined in the star schema, which can be equal to or coarser than the granularity defined in the table.

s_cause <- star_schema() |>
  define_facts(name = "MRS Cause",
               measures = c("Pneumonia and Influenza Deaths",
                            "All Deaths"),
               agg_functions = c("MAX", "SUM"),
               nrow_agg = "Num" 
               )

In the above example, aggregation functions have been explicitly defined for the measures and the default name of the measure corresponding to the number of records added has been changed.

Star databases

From a star_schema object and a table (tibble) that contains all the fields that appear in it, using the star_database() function, we obtain a star_database object. As an example we will consider data on causes.

The definition of the star schema for the data considered for the cause case is shown below.

s_cause <- star_schema() |>
  define_facts(name = "MRS Cause",
               measures = c("Pneumonia and Influenza Deaths",
                            "All Deaths"),
               nrow_agg = "Num") |>
  define_dimension(name = "When",
                         attributes = c("Year",
                                        "WEEK",
                                        "Week Ending Date")) |>
  define_dimension(name = "Where",
                          attributes = c("REGION",
                                         "State",
                                         "City"))

Next we get the star database.

db_cause <- star_database(s_cause, ft_num)

For now the objective of this database is to export its tables to be used in OLAP query tools. Two functions are offered for this purpose, firstly snake_case() to transform the column names to snake case and secondly as_tibble_list() to generate a tibble list with the database tables, as shown in the following example.

l_cause <- db_cause |>
  snake_case() |>
  as_tibble_list()

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

It can be seen that the data in the fact table has the same granularity as the data in the original table (they have the same number of records).

nrow(ft_num)
#> [1] 20

nrow(l_cause[[3]])
#> [1] 20

The same can be seen in the num field in the fact table which shows the number of records added to get each record, in this case it is always 1. Dimensions contain only the distinct value combinations for the attribute sets they contain.

Constellations

From two or more star databases we can define a constellation.

when <- dimension_schema(name = "When",
                         attributes = c("Year"))

where <- dimension_schema(name = "Where",
                          attributes = c("State",
                                         "City"))
s_cause <- star_schema() |>
  define_facts(name = "MRS Cause",
               measures = c("Pneumonia and Influenza Deaths",
                            "All Deaths")) |>
  define_dimension(when) |>
  define_dimension(where)

ft_num2 <- ft_num |>
  dplyr::filter(Year > "1962") |>
  dplyr::filter(City == "Boston" | City == "Bridgeport")

db_cause <- star_database(s_cause, ft_num2) |>
  snake_case()

l_cause <- db_cause |>
  as_tibble_list()

s_age <- star_schema() |>
  define_facts(name = "MRS Age",
               measures = c("All Deaths")) |>
  define_dimension(when) |>
  define_dimension(where) |>
  define_dimension(name = "Who",
                         attributes = c("Age"))

ft_age2 <- ft_age |>
  dplyr::filter(Year < "1964") |>
  dplyr::filter(City != "Boston" & City != "Bridgeport")

db_age <- star_database(s_age, ft_age2) |>
  snake_case()

l_age <- db_age |>
  as_tibble_list()

ct <- constellation("MRS", db_cause, db_age)

lc <- ct |>
  as_tibble_list()

s_age2 <- star_schema() |>
  define_facts(name = "MRS Age 2",
               measures = c("All Deaths")) |>
  define_dimension(when) |>
  define_dimension(where) |>
  define_dimension(name = "Who",
                         attributes = c("Age"))

db_age2 <- star_database(s_age2, ft_age) |>
  snake_case()

ct2 <- constellation("MRS2", ct, db_age2)

tibble list

The following shows the results that are obtained for a star database.

db_cause |>
  as_tibble_list()
#> $when
#> # A tibble: 2 × 2
#>   when_key year 
#>      <int> <chr>
#> 1        1 1963 
#> 2        2 1964 
#> 
#> $where
#> # A tibble: 2 × 3
#>   where_key state city      
#>       <int> <chr> <chr>     
#> 1         1 CT    Bridgeport
#> 2         2 MA    Boston    
#> 
#> $mrs_cause
#> # A tibble: 4 × 5
#>   when_key where_key pneumonia_and_influenza_deaths all_deaths nrow_agg
#>      <int>     <int>                          <int>      <int>    <int>
#> 1        1         1                              2         46        1
#> 2        1         2                             10        276        1
#> 3        2         1                              8         45        1
#> 4        2         2                             22        569        2

Also for a constellation.

ct |>
  as_tibble_list()
#> $when
#> # A tibble: 3 × 2
#>   when_key year 
#>      <int> <chr>
#> 1        1 1962 
#> 2        2 1963 
#> 3        3 1964 
#> 
#> $where
#> # A tibble: 4 × 3
#>   where_key state city      
#>       <int> <chr> <chr>     
#> 1         1 CT    Bridgeport
#> 2         2 CT    Hartford  
#> 3         3 MA    Boston    
#> 4         4 MA    Cambridge 
#> 
#> $who
#> # A tibble: 5 × 2
#>   who_key age        
#>     <int> <chr>      
#> 1       1 1-24 years 
#> 2       2 25-44 years
#> 3       3 45-64 years
#> 4       4 65+ years  
#> 5       5 <1 year    
#> 
#> $mrs_cause
#> # A tibble: 4 × 5
#>   when_key where_key pneumonia_and_influenza_deaths all_deaths nrow_agg
#>      <int>     <int>                          <int>      <int>    <int>
#> 1        2         1                              2         46        1
#> 2        2         3                             10        276        1
#> 3        3         1                              8         45        1
#> 4        3         3                             22        569        2
#> 
#> $mrs_age
#> # A tibble: 15 × 5
#>    when_key where_key who_key all_deaths nrow_agg
#>       <int>     <int>   <int>      <int>    <int>
#>  1        1         2       1          2        2
#>  2        1         2       2          3        2
#>  3        1         2       3         35        2
#>  4        1         2       4         54        2
#>  5        1         2       5         10        2
#>  6        1         4       1          0        1
#>  7        1         4       2          2        1
#>  8        1         4       3          7        1
#>  9        1         4       4         29        1
#> 10        1         4       5          1        1
#> 11        2         2       1          5        3
#> 12        2         2       2         13        3
#> 13        2         2       3         51        3
#> 14        2         2       4        107        3
#> 15        2         2       5         16        3

dm object

The dm package allows you to link tables implemented using the tibble class in R with tables in RDBMS.

The following example shows the transformation of a star database to an object of class dm.

# star database
db_cause_dm <- db_cause |>
  as_dm_class()
class(db_cause_dm)
#> [1] "dm"
db_cause_dm
#> ── Metadata ────────────────────────────────────────────────────────────────────
#> Tables: `when`, `where`, `mrs_cause`
#> Columns: 10
#> Primary keys: 3
#> Foreign keys: 2

Also for a constellation.

# constellation
ct_dm <- ct |>
  as_dm_class(pk_facts = TRUE)
class(ct_dm)
#> [1] "dm"
ct_dm
#> ── Metadata ────────────────────────────────────────────────────────────────────
#> Tables: `when`, `where`, `who`, `mrs_cause`, `mrs_age`
#> Columns: 17
#> Primary keys: 5
#> Foreign keys: 5

For example, the dm class object can be used to represent the constellation tables, as it’s shown in the following (it can also be used to store them in a DBMS).

ct_dm |> 
  dm::dm_draw(rankdir = "LR", view_type = "all")

In the example below, a local SQLite database is used to demonstrate how to store the model in a database. dm works with any RDBMS supported by DBI.

db <- DBI::dbConnect(RSQLite::SQLite())
ct_dm_db <- dm::copy_dm_to(db, ct_dm)
ct_dm_db
#> ── Table source ────────────────────────────────────────────────────────────────
#> src:  sqlite 3.46.0 []
#> ── Metadata ────────────────────────────────────────────────────────────────────
#> Tables: `when`, `where`, `who`, `mrs_cause`, `mrs_age`
#> Columns: 17
#> Primary keys: 5
#> Foreign keys: 5
DBI::dbDisconnect(db)

Single tibble list

The following shows the results that are obtained for a star database seen as a single tibble list. Each single tibble is the result of performing an inner join between the fact table and the dimension tables.

db_cause |>
  as_single_tibble_list()
#> $mrs_cause
#> # A tibble: 4 × 6
#>   year  state city       pneumonia_and_influenza_deaths all_deaths nrow_agg
#>   <chr> <chr> <chr>                               <int>      <int>    <int>
#> 1 1963  CT    Bridgeport                              2         46        1
#> 2 1963  MA    Boston                                 10        276        1
#> 3 1964  CT    Bridgeport                              8         45        1
#> 4 1964  MA    Boston                                 22        569        2

Also for a constellation.

ct |>
  as_single_tibble_list()
#> $mrs_cause
#> # A tibble: 4 × 6
#>   year  state city       pneumonia_and_influenza_deaths all_deaths nrow_agg
#>   <chr> <chr> <chr>                               <int>      <int>    <int>
#> 1 1963  CT    Bridgeport                              2         46        1
#> 2 1963  MA    Boston                                 10        276        1
#> 3 1964  CT    Bridgeport                              8         45        1
#> 4 1964  MA    Boston                                 22        569        2
#> 
#> $mrs_age
#> # A tibble: 15 × 6
#>    year  state city      age         all_deaths nrow_agg
#>    <chr> <chr> <chr>     <chr>            <int>    <int>
#>  1 1962  CT    Hartford  1-24 years           2        2
#>  2 1962  CT    Hartford  25-44 years          3        2
#>  3 1962  CT    Hartford  45-64 years         35        2
#>  4 1962  CT    Hartford  65+ years           54        2
#>  5 1962  CT    Hartford  <1 year             10        2
#>  6 1962  MA    Cambridge 1-24 years           0        1
#>  7 1962  MA    Cambridge 25-44 years          2        1
#>  8 1962  MA    Cambridge 45-64 years          7        1
#>  9 1962  MA    Cambridge 65+ years           29        1
#> 10 1962  MA    Cambridge <1 year              1        1
#> 11 1963  CT    Hartford  1-24 years           5        3
#> 12 1963  CT    Hartford  25-44 years         13        3
#> 13 1963  CT    Hartford  45-64 years         51        3
#> 14 1963  CT    Hartford  65+ years          107        3
#> 15 1963  CT    Hartford  <1 year             16        3