Dimension Models

Below is an example of a dimension model called “Customers.” The files below are required and define unchanging properties on customers, or changing properties where changes do not need to be analyzed. For properties where changes do need to be analyzed (such as a customer’s plan), add a daily model to the /dimensions/customers directory.

A SQL query defines the data that will be exposed in Reveloo:

/dimensions/customers/dim.sql

  SELECT
    customers.key AS id,  -- all dims MUST have an `id`
    NVL(customers.name, 'Unset') AS name,  -- all dims MUST have a `name`
    external_data.size AS company_size,
    customers.account_manager,
    customers.country
  FROM public.customers
  LEFT JOIN external_data ON customers.email_domain = external_data.email_domain
  WHERE customers.trashed_at IS False

Each time the model builds, the data for this dimension is completely replaced by the output of this query.

A YAML file annotates the model, giving the data meaning:

/dimensions/customers/dim.yaml

  # all "name" properties are user-facing descriptors
  name: Customers
  # any icon from https://materialdesignicons.com/
  icon: mdi-domain
  # describes in detail, for a human audience, how the dim is defined.
  description: |
    Paying organizations using our service

  # corresponds to the columns returned by customers.sql
  columns:

    # the `id` value (in this case, "id") is the column name in customers.sql
    - id: id
      name: Customer Id
      # can be string, integer, float, datetime, or key
      type: string

    # all dims must have columns for `id` & `name`
    - id: name
      name: Name
      type: string

    - id: company_size
      name: Est Company Size
      type: integer
      # this is displayed in tooltips
      description: |
        The estimated number of employees, as
        determined by our third party data source.
      # summable integers/floats are those where SUM aggregations
      # make sense. default is false.
      summable: true
      # secondary properties are collapsed in the UI by default
      secondary: true

    - id: account_manager
      name: Account Manager
      # for key types, the value must point at the
      # id (external key) of the associated model
      type: key
      model: account_managers

    - id: country
      name: Country
      type: string
      # choices enable breakdowns; if a value isn't in the
      # list of choices, it is rolled up into an "Other" bucket
      choices:
        - id: usa
          name: USA
        - id: can
          name: Canada

  # default sorts & columns visible for this model in the web UI
  default_sort_key: contributors
  default_sort_dir: desc
  default_columns:
    - name
    - account_manager
    # default columns and filters can include columns on `daily` models,
    # and this will display/filter the latest daily value        
    - plan
  default_filters:
    - column_id: plan
      operator: is_not_null

  # columns "indexed" for searching
  search_columns:
    - name
    - id

  # sorting (desc) of search results
  search_sort:
    column: contributors
    name: Contributors

  # when viewing an instance of this dim (a Customer), display UI widgets
  display:

    # metric groups are aggregations defined by metric models
    - type: metric_group
      group_members:

        - type: metric
          model_id: active_users
          name: Active Users
          # column on the metric model's `base_model` that
          # matches *this* model's `id`
          filter_column_id: organization_id

        - type: metric
          model_id: health_score
          name: Health Score
          filter_column_id: id
          # set default filters and breakdowns
          filters: []
          breakdown: null

    # a list of dims with a `key` property referencing this model
    - type: list
      model_id: memberships
      name: Members
      # `memberships` property with a key referencing `customers`
      filter_column_id: organization_id
      # columns to display by default
      columns:
        - name
        - email
        - created_at
        - xd28_active
      sort_key: xd28_active
      sort_dir: desc

    # display a UI widget showing events related to an dim
    - type: events

  # assert that the table has data before committing the model
  assertions:
    min_rows: 1000

Over time, some of these properties will be moved into the web UI to simplify configuration.

A table is generated in the rloo. schema, with data exposed to the web portal:

Redshift table rloo.customers

justin@warehouse=> select * from rloo.customers;

      id      |     name    | company_size | account_manager |  country  |  plan  | active_utilization
--------------+-------------+--------------|-----------------|-----------+--------+--------------------
87fc4b0e-c0df | Acme        | 10           |                 | usa       | gold   | 0.723
a2bf78e9-d89b | Foo Co.     | 2500         | justin          | usa       |        | 0.878
64bfffca-a6aa | World Inc.  |              | robot           | argentina | silver | 0.221

Note that plan and active_utilization are the latest values for columns defined in the daily model.