The PowerOfThree defines three macros to be used with Ecto.Schema to creates cube config files. The PowerOfThree must be used after using Ecto.Schema. The Ecto.Schema defines table column names to be used in measure and dimensions defenitions.

The definition of the Cude is possible through main APIs: cube/3.

cube/3 has to define sql_table: that is refering Ecto schema source.

After using Ecto.Schema and PowerOfThree define cube with cube/2 macro.

Example

defmodule Example.Customer do
  use Ecto.Schema
  use PowerOfThree

  schema "customer" do
    field(:first_name, :string)
    field(:last_name, :string)
    field(:email, :string)
    field(:birthday_day, :integer)
    field(:birthday_month, :integer)
    field(:brand_code, :string)
    field(:market_code, :string)
  end

  cube :of_customers,       # name of the cube: mandatory
    sql_table: "customer",  # Ecto.Schema `source`: mandatory
                            # Only `sql_table:` is supported. Must reference EctoSchema `:source`
                            # the `sql:` is not supported and never will be. 
    description: "of Customers"
                            # path through options in accordance with Cube DSL

    dimension(
      [:brand_code, :market_code, :email],
                            # several fields of `customer` Ecto.Schema: mandatory
                            # the list is validated against list of fields of EctoSchema
      name: :email_per_brand_per_market,
                            # dimensions `name:`, optional.
      primary_key: true     # This `customer:` table supports only one unique combination of
                            # `:brand_code`, `:market_code`, `:email`
      )

    dimension(
      :first_name,          # a field of `customer` Ecto.Schema: mandatory
                            # validated against list of fields of EctoSchema
      name: :given_name,    # dimension `name:` optional
      description: "Given Name"
                            # path through options in accordance with Dimension DSL
      )

    measure(:count)         # measure of type `count:` is a special one: no column reference in `sql:` is needed
                            # `name:` defaults to `count:`

    measure(:email,         # measures counts distinct of `email:` column
      name: :aquari         # given a `name:` and `description:` and `filter:` in options
      type: :count_distinct,
      description: "Only count one zodiak sign",
      filters: [%{sql: "(birthday_month = 1 AND birthday_day >= 20) OR (birthday_month = 2 AND birthday_day <= 18)"}]
                            # better be correct SQL refrencing correct columns - not validated now
                            # `filter:` uses SQL clause to not count other categories of customers
    )
  end
end

After creating a few dimensions and measures run mix compile. The following yaml is created for the above:

---
cubes:
  - name: of_customers
    description: of Customers
    sql_table: customer
    measures:
      - name: count
        type: count
      - meta:
          ecto_field: email
          ecto_type: string
        name: aquari
        type: count_distinct
        description: Only count one zodiak sign
        filters:
          - sql: (birthday_month = 1 AND birthday_day >= 20) OR (birthday_month = 2 AND birthday_day <= 18)
        sql: email
    dimensions:
      - meta:
          ecto_fields:
            - brand_code
            - market_code
            - email
        name: email_per_brand_per_market
        type: string
        primary_key: true
        sql: brand_code||market_code||email
      - meta:
          ecto_field: first_name
          ecto_field_type: string
        name: given_name
        type: string
        description: Given Name
        sql: first_name

The dimensions and measures derive some defaults from Ecto.Schema.field properties. For example the dimension: type: is derived from ecto if not given explicitly according to this rules:

The goal of PowerOfThree is to cover 80% of cases where the source of Ecto Schema is a table and fields have real column names: where field name =:= database column name

The the support of all cube features is not the goal here. The automation of obtaining the usable cube configs with minimal verbocity is: avoid typing more typos then needed.

The cube DSL allows the sql: - any SQL query. If everyone can write SQL it does not mean everyone should. Writing good SQL is an art a few knew. In the memory of Patrick's Mother the PowerOfThree will not support sql:. While defining custom sql: may looks like an option, how would one validate the creative use of aliases in SQL? Meanwhile Ecto.Schema fields are available for references to define dimensions type:.