# Model - Malloy

For SQL connections, Mprove creates Data Models based on [Malloy Sources](https://docs.malloydata.dev/documentation/language/source).

<Callout>
In Malloy, the term “model” usually refers to a file containing Malloy code.

In Mprove, the term “model” refers to a Malloy Source with the tag `#(mprove) model`.

To minimize Malloy compilation time in Mprove, it is important to store Malloy sources
in separate files from other Malloy elements, such as `run: ...` or `query: ...`.

This file structure is also easier to maintain.

</Callout>

Malloy Sources are reusable. Multiple Sources and Queries can reference the same Source.

```malloy title="c1_order_items.malloy"
##! experimental{}

import '../../givens.malloy'
import './tables/c1_order_items_tx.malloy';
import './tables/c1_orders_tx.malloy';
import './tables/c1_users_tx.malloy';
import './tables/c1_user_order_facts_tx.malloy';
import './tables/c1_inventory_items_tx.malloy';
import './tables/c1_products_tx.malloy';
import './tables/c1_distribution_centers_tx.malloy';

#(mprove) model
#(mprove) label="Order Items"
#(mprove) top_label="Order Items"
#(mprove) access_roles="sales, marketing"
source: c1_order_items is c1_order_items_tx extend {
  join_one: orders is c1_orders_tx on order_id = orders.order_id
  join_one: users is c1_users_tx on orders.user_id = users.user_id
  join_one: user_order_facts is c1_user_order_facts_tx on users.user_id = user_order_facts.user_id
  join_one: inventory_items is c1_inventory_items_tx on inventory_item_id = inventory_items.inventory_item_id
  join_one: products is c1_products_tx on inventory_items.product_id = products.product_id
  join_one: distribution_centers is c1_distribution_centers_tx on inventory_items.distribution_center_id = distribution_centers.distribution_center_id

  measure:
    total_profit is total_sale_price - inventory_items.total_actual_cost
    profit_margin is total_profit / total_sale_price

  where:
    orders.created_at_ts >= $START_TS AND distribution_centers.distribution_center_id IN $DISTRIBUTION_CENTER_IDS
}
```

Example of building Metrics that can be used in [Reports](/content/docs/reference/report):

```malloy title="tables/c1_orders_tx.malloy"
##! experimental{}

type: c1_orders_type is {
  order_id :: string,
  user_id :: string,
  created_at :: number,
  created_at_ts :: timestamptz,
  status :: string
}

source: c1_orders_table is c1_postgres.table('ecommerce.orders')::c1_orders_type extend {
  rename: created_at_timestamp is created_at_ts
}

source: c1_orders_tx is c1_orders_table include {
  internal:
    created_at_timestamp
    created_at
  public: *
} extend {
  primary_key: order_id

  measure:
    orders_count is count()

  #(mprove) build_metrics field_group="Created At"
  dimension:
    created_at_t is created_at_timestamp
    created_at_ts is created_at_t
    created_at_day is created_at_t.day
    created_at_week is created_at_t.week
    created_at_month is created_at_t.month
    created_at_quarter is created_at_t.quarter
    created_at_year is created_at_t.year
    created_at_hour is created_at_t.hour
    created_at_minute is created_at_t.minute
    created_at_second is created_at_t.second
}
```

## Reference

### Source #(mprove) Tags

| Name                   | Type | Default | Description                                                                                                                                                                                                                       |
| ---------------------- | ---- | ------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| model                  | tag  | -       | If specified, the Malloy source will become available for exploration as an Mprove model.                                                                                                                                         |
| label                  | tag  | -       | Model label in UI                                                                                                                                                                                                                 |
| top_label              | tag  | -       | Top node label in the Model Schema                                                                                                                                                                                                |
| access_roles           | tag  | -       | User roles list separated by comma. If `access_roles` tag is set, only users with specified roles will be able to explore the model. If `access_roles` tag is not specified, all project users will be able to explore the model. |
| tree_double_underscore | tag  | -       | If this tag is set, the portion of the field name preceding the double underscore will be used to create a top-level group in the model fields tree in the UI.                                                                    |

### Dimension #(mprove) Tags

| Name          | Type | Default | Description                                                                                                                                                                                                                                                         |
| ------------- | ---- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| build_metrics | tag  | -       | Mprove creates a metric for each pair of measure and tagged time based dimension (for all measures of the malloy source). `build_metrics` `field_group` must have fields with "\_t" and "\_ts" suffixes. Timeframes can only be based on a field with "\_t" suffix. |
| field_group   | tag  | -       | Mprove adds the tagged fields to a subgroup in the web interface (model fields tree).                                                                                                                                                                               |