Different measures in view not working

[AntonyKlipperAS]

Hi,
I'm having a problem with a measure that us working fine in the playground but not when used in a view.
** Count measure referencing another cube**

The measure reference another cube and is defined like this:

measures: {
   countOrdersPaidAndPicked: {
     sql: `${CUBE}."ID_ORDER"`,
     type: `count`,
     filters: [
       { sql: `${dim_order_status.key} = 'paid-and-picked'` }
     ]
   }

I tried different things (to rewrite to ${CUBE.id_order or 'ID_ORDER'}` , the only thing that worked for me was to leave the sql property out of it, like this:

  measures: {
   countOrdersPaidAndPicked: {
     type: `count`,
     filters: [
       { sql: `${dim_order_status.status_key} = 'paid-and-picked'` } 
     ]
   }

But I want to clearly understand what is happening wrong when using the sql property because I have other issues with different measures as well.

My datamodel:

1. I have a fact_order cube

cube(`fact_order`, {
  sql_table: `"INTERMEDIATE"."FACT_ORDER"`,
  public: true,
  joins: {
    dim_order_product: {
      sql: `${CUBE}."ID_ORDER" = ${dim_order_product}."FK_ORDER"`,
      relationship: `one_to_many`
    },
    
    dim_order_status: {
      sql: `${CUBE}."FK_ORDER_STATUS" = ${dim_order_status}."ID_ORDER_STATUS"`,
      relationship: `many_to_one`
    },
    
    fact_order_lead_time_status: {
      sql: `${CUBE}."ID_ORDER" = ${fact_order_lead_time_status}."ID_ORDER"`,
      relationship: `one_to_one`
    },
    
    dim_date: {
      sql: `${CUBE}."DATE_KEY" = ${dim_date}."DATE_KEY"`,
      relationship: `many_to_one`
    },
    
    dim_customer: {
      sql: `${CUBE}."FK_CUSTOMER" = ${dim_customer}."DCUST_UUID"`,
      relationship: `many_to_one`
    }
  },
  
  
  dimensions: {
    id_order: {
      sql: `${CUBE}."ID_ORDER"`,
      type: `number`,
      primary_key: true
    },
    
    updated_by: {
      sql: `${CUBE}."UPDATED_BY"`,
      type: `number`
    },
    
    id_bu: {
      sql: `${CUBE}."ID_BU"`,
      type: `number`
    },
    
    created_by: {
      sql: `${CUBE}."CREATED_BY"`,
      type: `number`
    },
    
    fk_delivery_option: {
      sql: `${CUBE}."FK_DELIVERY_OPTION"`,
      type: `number`
    },
    
    fk_order_status: {
      sql: `${CUBE}."FK_ORDER_STATUS"`,
      type: `number`
    },
    
    fk_store: {
      sql: `${CUBE}."FK_STORE"`,
      type: `number`
    },
    
    status_updated_by: {
      sql: `${CUBE}."STATUS_UPDATED_BY"`,
      type: `number`
    },
    
    id_fascia: {
      sql: `${CUBE}."ID_FASCIA"`,
      type: `number`
    },
    
    id_market: {
      sql: `${CUBE}."ID_MARKET"`,
      type: `number`
    },
    
    id_shop: {
      sql: `${CUBE}."ID_SHOP"`,
      type: `number`
    },
    
    fk_customer: {
      sql: `${CUBE}."FK_CUSTOMER"`,
      type: `string`
    },
    
    label: {
      sql: `${CUBE}."LABEL"`,
      type: `string`
    },
    
    uuid: {
      sql: `${CUBE}."UUID"`,
      type: `string`
    },
    
    created_at: {
      sql: `${CUBE}."CREATED_AT"`,
      type: `time`
    },
    
    status_updated_at: {
      sql: `${CUBE}."STATUS_UPDATED_AT"`,
      type: `time`
    },
    
    updated_at: {
      sql: `${CUBE}."UPDATED_AT"`,
      type: `time`
    },
    
    date_key: {
      sql: `${CUBE}."DATE_KEY"`,
      type: `time`
    }
  },

  measures: {
   countOrdersPaidAndPicked: {
     type: `count`,
     filters: [
       { sql: `${dim_order_status.status_key} = 'paid-and-picked'` } 
     ]
   },
    countOrdersThisWeek: {
      type: `count`,
      filters: [
        { sql: `${dim_date}."MONTH" = DATE_PART('WEEK', CURRENT_DATE)` } // mistake to test 
      ]
    }, 
    countOrdersThisMonth: {
      type: `count`,
      filters: [
        { sql: `${dim_date}."MONTH" = DATE_PART('MONTH', CURRENT_DATE)` }
      ]
    } , 
    revenue: {
      type: `sum`,
      sql: `SALES`
    }, 
    avgSales: {
      sql: `AVG(SALES)`,
      type: `number`

2. I have a dim_order_status cube that joins on my fact_order

cube(`dim_order_status`, {
  sql_table: `"INTERMEDIATE"."DIM_ORDER_STATUS"`,
  public: true,
 
  dimensions: {
    id_order_status: {
      sql: `${CUBE}."ID_ORDER_STATUS"`,
      type: `number`,
      primary_key: true
    },
    
    searchable: {
      sql: `${CUBE}."SEARCHABLE"`,
      type: `string`
    },
    
    status_active: {
      sql: `${CUBE}."ACTIVE"`,
      type: `string`
    },
    
    display_name: {
      sql: `${CUBE}."DISPLAY_NAME"`,
      type: `string`
    },
    
    visible: {
      sql: `${CUBE}."VISIBLE"`,
      type: `string`
    },
    
    status_key: {
      sql: `${CUBE}."KEY"`,
      type: `string`
    },
    
    created_at: {
      sql: `${CUBE}."CREATED_AT"`,
      type: `time`
    },
    
    updated_at: {
      sql: `${CUBE}."UPDATED_AT"`,
      type: `time`
    }
  },
  
  measures: {
    count: {
      type: `count`
    }
  },
  
  pre_aggregations: {
    // Pre-aggregation definitions go here.
    // Learn more in the documentation: https://cube.dev/docs/caching/pre-aggregations/getting-started
  }
});

So it seems like a pretty easy measure? I want to do a count on my order where a status = 'paid-and-picked'.

but it's creating this weird sql in the view where it's also referencing an alias table outside of it's own creation. I mean by this for example ‘select AliasTable.* from (select * from table_x as AliasTable)." which is not possible.

generated sql:

 "keys"."service_hub__avg_days_between_orders", 
      CASE 
        WHEN ( 
          CASE 
            WHEN ( 
              "fact_order_key__dim_order_status"."KEY" = 'paid-and-picked' 
            ) THEN "fact_order_key__fact_order"."ID_ORDER" 
          END 
        ) IS NOT NULL THEN 1 
      END "fact_order__count_orders_paid_and_picked" 
    FROM 
      ( 
        SELECT 
          DISTINCT "fact_order_key__fact_order"."ID_ORDER" "service_hub__id_order", 
          "fact_order_key__fact_order"."UPDATED_BY" "service_hub__updated_by", 
          "fact_order_key__fact_order"."ID_BU" "service_hub__id_bu", 
          "fact_order_key__fact_order"."CREATED_BY" "service_hub__created_by", 
          "fact_order_key__fact_order"."FK_DELIVERY_OPTION" "service_hub__fk_delivery_option", 
          "fact_order_key__fact_order"."FK_ORDER_STATUS" "service_hub__fk_order_status", 
          "fact_order_key__fact_order"."FK_STORE" "service_hub__fk_store", 
          "fact_order_key__fact_order"."STATUS_UPDATED_BY" "service_hub__status_updated_by", 
          "fact_order_key__fact_order"."ID_FASCIA" "service_hub__id_fascia", 
          "fact_order_key__fact_order"."ID_MARKET" "service_hub__id_market", 
          "fact_order_key__fact_order"."ID_SHOP" "service_hub__id_shop", 
          "fact_order_key__fact_order"."LABEL" "service_hub__label", 
          "fact_order_key__fact_order"."UUID" "service_hub__uuid", 
          "fact_order_key__fact_order"."CREATED_AT" "service_hub__created_at", 
          "fact_order_key__fact_order"."STATUS_UPDATED_AT" "service_hub__status_updated_at", 
          "fact_order_key__fact_order"."UPDATED_AT" "service_hub__updated_at", 
          "fact_order_key__fact_order"."DATE_KEY" "service_hub__date_key", 
          "fact_order_key__fact_order_lead_time_status"."LATEST_ORDER_STATUS" "service_hub__latest_order_status", 
          "fact_order_key__fact_order_lead_time_status"."OPEN_LEAD_TIME" "service_hub__open_lead_time", 
          "fact_order_key__fact_order_lead_time_status"."LEAD_TIME_STATUS_1_2" "service_hub__lead_time_status_1_2", 
          "fact_order_key__fact_order_lead_time_status"."LEAD_TIME_STATUS_4_5" "service_hub__lead_time_status_4_5", 
          "fact_order_key__fact_order_lead_time_status"."LEAD_TIME_STATUS_2_4" "service_hub__lead_time_status_2_4", 
          "fact_order_key__fact_order_lead_time_status"."LEAD_TIME_STATUS_2_5" "service_hub__lead_time_status_2_5", 
          "fact_order_key__fact_order_lead_time_status"."LEAD_TIME_STATUS_3_4" "service_hub__lead_time_status_3_4", 
          "fact_order_key__fact_order_lead_time_status"."LEAD_TIME_STATUS_2_3" "service_hub__lead_time_status_2_3", 
          "fact_order_key__fact_order_lead_time_status"."FIRST_OPERATION_AT" "service_hub__first_operation_at", 
          "fact_order_key__fact_order_lead_time_status"."LAST_OPERATION_AT" "service_hub__last_operation_at", 
          "fact_order_key__dim_order_status"."ID_ORDER_STATUS" "service_hub__id_order_status", 
          "fact_order_key__dim_order_status"."SEARCHABLE" "service_hub__searchable", 
          "fact_order_key__dim_order_status"."ACTIVE" "service_hub__status_active", 
          "fact_order_key__dim_order_status"."VISIBLE" "service_hub__visible", 
          "fact_order_key__dim_order_status"."KEY" "service_hub__status_key", 
          "fact_order_key__dim_order_product"."ID_ORDER_PRODUCT" "service_hub__id_order_product", 
          "fact_order_key__dim_order_product"."PRODUCT_REF" "service_hub__product_ref", 
          "fact_order_key__dim_order_product"."FK_PRODUCT_REF_TYPE" "service_hub__fk_product_ref_type", 
          "fact_order_key__dim_order_product"."FK_BOX" "service_hub__fk_box", 
          "fact_order_key__dim_order_product"."FK_ORDER" "service_hub__fk_order", 
          "fact_order_key__dim_order_product"."QRCODE" "service_hub__qrcode", 
          "fact_order_key__dim_order_product"."LABEL" "service_hub__order_product_label", 
          "fact_order_key__dim_order_product_serice"."ID_ORDER_PRODUCT_SERVICE" "service_hub__id_order_product_service", 
          "fact_order_key__dim_order_product_serice"."FK_ORDER_PRODUCT" "service_hub__fk_order_product", 
          "fact_order_key__dim_order_product_serice"."FK_SERVICE" "service_hub__fk_service", 
          "fact_order_key__dim_order_product_serice"."FK_ORDER_PRODUCT_SERVICE_STATUS" "service_hub__fk_order_product_service_status", 
          "fact_order_key__dim_order_product_serice"."FK_PRODUCT_DETAILS" "service_hub__fk_product_details", 
          "fact_order_key__dim_order_product_serice"."DESCRIPTION" "service_hub__description", 
          "fact_order_key__dim_order_product_serice"."STATE_DESCRIPTION" "service_hub__state_description", 
          "fact_order_key__dim_order_product_serice"."NON_PAYABLE" "service_hub__non_payable", 
          "fact_order_key__dim_order_product_serice"."METADATA" "service_hub__metadata", 
          "fact_order_key__dim_order_product_service_status"."ID_ORDER_PRODUCT_SERVICE_STATUS" "service_hub__id_order_product_service_status", 
          "fact_order_key__dim_order_product_service_status"."ACTIVE" "service_hub__active", 
          "fact_order_key__dim_order_product_service_status"."KEY" "service_hub__key", 
          "fact_order_key__dim_order_product_service_status"."DISPLAY_NAME" "service_hub__display_name", 
          "fact_order_key__dim_customer"."DCUST_UUID" "service_hub__dcust_uuid", 
          "fact_order_key__dim_customer"."DCUST_F_IS_EXPLORE_MORE" "service_hub__dcust_f_is_explore_more", 
          "fact_order_key__dim_customer"."DCUST_SKEY" "service_hub__dcust_skey", 
          "fact_order_key__dim_customer"."DCUST_ID_COUNTRY" "service_hub__dcust_id_country", 
          "fact_order_key__dim_customer"."DCUST_NUMBER_OF_CHILDREN" "service_hub__dcust_number_of_children", 
          "fact_order_key__dim_customer"."DCUST_F_DO_NOT_EMAIL" "service_hub__dcust_f_do_not_email", 
          "fact_order_key__dim_customer"."DCUST_FIRST_NAME" "service_hub__dcust_first_name", 
          "fact_order_key__dim_customer"."DCUST_CODE" "service_hub__dcust_code", 
          "fact_order_key__dim_customer"."DCUST_F_DO_NOT_MAIL" "service_hub__dcust_f_do_not_mail", 
          "fact_order_key__dim_customer"."DCUST_ADDRESS" "service_hub__dcust_address", 
          "fact_order_key__dim_customer"."DCUST_F_DO_NOT_PHONE" "service_hub__dcust_f_do_not_phone", 
          "fact_order_key__dim_customer"."DCUST_STORE_CREATED_BKEY" "service_hub__dcust_store_created_bkey", 
          "fact_order_key__dim_customer"."DCUST_HOUSENUMBER" "service_hub__dcust_housenumber", 
          "fact_order_key__dim_customer"."DCUST_BKEY" "service_hub__dcust_bkey", 
          "fact_order_key__dim_customer"."DCUST_EXPLORE_MORE_CARD_ID" "service_hub__dcust_explore_more_card_id", 
          "fact_order_key__dim_customer"."DCUST_GENDER" "service_hub__dcust_gender", 
          "fact_order_key__dim_customer"."DCUST_CITY" "service_hub__dcust_city", 
          "fact_order_key__dim_customer"."DCUST_EMAIL" "service_hub__dcust_email", 
          "fact_order_key__dim_customer"."DCUST_SURNAME" "service_hub__dcust_surname", 
          "fact_order_key__dim_customer"."DCUST_DATE_OF_BIRTH" "service_hub__dcust_date_of_birth", 
          "fact_order_key__dim_date"."WEEKDAY" "service_hub__weekday", 
          "fact_order_key__dim_date"."DAY_OF_WEEK" "service_hub__day_of_week", 
          "fact_order_key__dim_date"."QUARTER" "service_hub__quarter", 
          "fact_order_key__dim_date"."DAY" "service_hub__day", 
          "fact_order_key__dim_date"."DAY_OF_YEAR" "service_hub__day_of_year", 
          "fact_order_key__dim_date"."MONTH" "service_hub__month", 
          "fact_order_key__dim_date"."WEEK_OF_YEAR" "service_hub__week_of_year", 
          "fact_order_key__dim_date"."YEAR" "service_hub__year", 
          "fact_order_key__dim_date"."TODAY_FLAG" "service_hub__today_flag", 
          "fact_order_key__dim_date"."QUARTER_NAME" "service_hub__quarter_name", 
          "fact_order_key__dim_date"."DAY_TYPE" "service_hub__day_type", 
          "fact_order_key__dim_date"."MONTH_NAME" "service_hub__month_name", 
          "fact_order_key__dim_date"."DATE_FORMAT" "service_hub__date_format", 
          "fact_order_key__dim_date"."END_OF_MONTH" "service_hub__end_of_month", 
          "fact_order_key__customer_avg_order_interval".fk_customer "service_hub__fk_customer", 
          "fact_order_key__customer_avg_order_interval".avg_days_between_orders "service_hub__avg_days_between_orders", 
          "fact_order_key__fact_order"."ID_ORDER" "fact_order__id_order" 
        FROM 
          "INTERMEDIATE"."FACT_ORDER" AS "fact_order_key__fact_order" 
          LEFT JOIN "INTERMEDIATE"."DIM_ORDER_STATUS" AS "fact_order_key__dim_order_status" ON "fact_order_key__fact_order"."FK_ORDER_STATUS" = "fact_order_key__dim_order_status"."ID_ORDER_STATUS" 

[igorlukanin]

Hi @AntonyKlipperAS 👋

I tried different things (to rewrite to ${CUBE.id_order or 'ID_ORDER'}` , the only thing that worked for me was to leave the sql property out of it, like this

For count measures, you don't need to specify the sql parameter.

Regarding the rest of your question: in Slack, where you originally asked about this, I have suggested to bring the dimension that you're filtering your measure on into the measure's cube as a subquery dimension and point the filter to it. Is might work as a workaround.

Did it help?

[AntonyKlipperAS]

Hi @igorlukanin

Leaving the sql parameter did indeed help and it works as expected.