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
A SQL query defines the data that will be exposed in Reveloo:
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:
# 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
active_utilization are the latest values for columns defined in the daily model.