Semantic Layer Builder

# Generated by BlueCloud ConsultantOS
# Semantic Layer Definition

semantic_models:
  - name: customers
    description: "Customer master data with segmentation"
    model: ref('dim_customers')
    
    defaults:
      agg_time_dimension: created_at
    
    entities:
      - name: customer
        type: primary
        expr: customer_id
        
    dimensions:
      - name: customer_id
        type: categorical
        expr: customer_id
        
      - name: customer_segment
        type: categorical
        expr: customer_segment
        description: "VIP, Regular, Occasional, or Prospect"
        
      - name: activity_status
        type: categorical
        expr: activity_status
        description: "Active, At Risk, or Churned"
        
      - name: created_at
        type: time
        type_params:
          time_granularity: day
        expr: created_at
        
    measures:
      - name: customer_count
        description: "Count of distinct customers"
        agg: count_distinct
        expr: customer_id
        
      - name: total_lifetime_value
        description: "Sum of customer lifetime values"
        agg: sum
        expr: lifetime_value
        
      - name: avg_lifetime_value
        description: "Average customer lifetime value"
        agg: average
        expr: lifetime_value

  - name: orders
    description: "Order transactions with amounts"
    model: ref('fct_orders')
    
    entities:
      - name: order
        type: primary
        expr: order_id
      - name: customer
        type: foreign
        expr: customer_id
        
    dimensions:
      - name: order_date
        type: time
        type_params:
          time_granularity: day
        expr: order_date
        
      - name: order_status
        type: categorical
        expr: order_status
        
    measures:
      - name: order_count
        agg: count_distinct
        expr: order_id
        
      - name: total_revenue
        agg: sum
        expr: net_amount
        
      - name: avg_order_value
        agg: average
        expr: net_amount

metrics:
  - name: active_customers
    description: "Count of active customers"
    type: simple
    type_params:
      measure: customer_count
    filter: |
      {{ Dimension('customer__activity_status') }} = 'Active'
      
  - name: vip_revenue
    description: "Revenue from VIP customers"
    type: simple
    type_params:
      measure: total_revenue
    filter: |
      {{ Dimension('customer__customer_segment') }} = 'VIP'
      
  - name: revenue_per_customer
    description: "Average revenue per customer"
    type: derived
    type_params:
      expr: total_revenue / customer_count
      metrics:
        - total_revenue
        - customer_count