Skip to content

RMD

RMD (Register of measures and dimensions) - A structured catalog containing descriptions of measures, dimensions, facts, dimension groups, and their relationships. Used to standardize and manage the semantic layer of the data warehouse.

  • Measures - aggregated quantities that the business monitors and that are calculated by formulas based on specific values of facts and dimensions
    • For example, the measure "Total Returns" could be defined by the formula "SUM({[Operation Type]="Return"} [Transaction Amount])" and calculated as the sum of the fact "Transaction Amount" for operations where the dimension "Operation Type" has the value "Return"
  • Dimensions - specific fields (columns) in the database containing attributes of objects or events, which typically have a small number of possible values and are used as slices or filters within which the business monitors measures
    • The majority of dimensions reside in dimension groups (separate dimension tables), although some individual dimensions may not require being placed into dimension groups and can reside directly in fact tables
    • For example, the dimension "Store Type" could correspond to the field "store_type" in the dimension table "dim_stores" and have value options "Standard" and "Discount," while the dimension "Operation Type" could correspond to the field "operation_type" in the fact table "fact_sales" and have value options "Sale," "Pre-order," and "Return"
    • Service or key fields that have no direct business meaning and are not used for calculating or filtering measures are not dimensions
    • For example, the field "store_id_unique," existing in the dimension table "dim_stores," might only be used for establishing relationships between this dimension table and fact tables, and the field "load_dttm," existing in the fact table "fact_sales," might only be used for storing the timestamp of when each specific record was saved in this fact table (not when the registered sale occurred). Dimensions should not be created for such fields
  • Facts - specific fields (columns) in the database containing countable values (usually numeric) and used as the primary source for calculating measures
    • Facts reside in fact tables and typically contain numeric values
    • For example, the fact "Transaction Amount" could correspond to the field "total_gross" in the fact table "fact_sales" and be used to calculate the measures "Total Returns" and "Total Sales"
    • Fields with numeric values residing in dimension groups (dimension tables) are not facts
    • For example, the field "item_price" with numeric values, existing in the dimension table "dim_items" and having direct business meaning, cannot be defined as a fact, as such a field should be defined as a dimension
  • Dimension groups - groups of dimensions united by a common theme
    • Dimension groups correspond to dimension tables in the database and typically contain separate attributes (dimensions) of specific objects
    • For example, the dimension group "Stores" could correspond to the dimension table "dim_stores" and contain the dimensions "Store Name," "Store Type," and "Store Class"