mprove-build-malloy-model

Call read-docs tool with pageId: reference/model-malloy

For Malloy documentation you can check https://docs.malloydata.dev/documentation

Parts of Malloy language are not supported in Mprove Models yet

Do not use it:

Malloy Nest
Malloy Calculations
Malloy Views

Joins

Joins in Malloy are lazy. Actual SQL joins calculated based on selected and filtered fields. When you build Mprove Malloy Model - join all possible leaf sources on model top source level (if they make sense).

For tables that have relationhips A->B->C:

source A should join source B
source A should join source C after joining B, using keys of B and C

Use this flat style instead of joining source D that already have source C joined.

Ensure that each table has its own malloy source (leaf) in a separate file for reusability.

Mprove Model Labels

Use this pattern for top level source:

#(mprove) model
#(mprove) label="Order Items (Postgres)"
#(mprove) top_label="Order Items"
source: c1_order_items is c1_order_items_tx extend {
  join_one: orders is c1_orders_tx on order_id = orders.order_id

This way we remove "c1_" prefix in Mprove UI Model Labels.

Build Model Metrics

Mprove Reports are based on Metrics.

When you build Mprove Malloy Model - always build metrics if dwh table schema has meaningful time field.

Use this pattern:

##! experimental{sql_functions}
source: c1_orders_table is c1_postgres.table('ecommerce.orders') extend {}
source: c1_orders_tx is c1_orders_table extend {
  primary_key: order_id
  #(mprove) build_metrics field_group="Created At"
  dimension:
    created_at_ts is sql_timestamp(""" TIMESTAMP 'epoch' + ${created_at} * INTERVAL '1 second' """)
    created_at_day is created_at_ts.day
    created_at_week is created_at_ts.week
    created_at_month is created_at_ts.month
    created_at_quarter is created_at_ts.quarter
    created_at_year is created_at_ts.year
    created_at_hour is created_at_ts.hour
    created_at_minute is created_at_ts.minute
    created_at_second is created_at_ts.second
  measure: orders_count is count()
}

Add ##! experimental{sql_functions} malloy source tag if using sql functions like sql_timestamp.

It is important to have all time parts to be present (.day, .week, .month, etc) so user will be able to select any Time Detail in UI.

build_metrics tag creates metrics for each pair of measure and time dimension of field_group.

  #(mprove) build_metrics field_group="Created At"

Dimensions in field_group must be based on the same "_ts" field. Create "_ts" dimension based on time column.

Mprove build_metrics and field_group tags should be applied at the sources (prefer "_tx") that have useful time columns.

.mdx

Types

Use and apply Malloy types for sources that are based on tables (prefer "_table" suffix sources). The type should list all columns of the source table.

https://docs.malloydata.dev/documentation/experiments/virtual_sources