# encoding: utf-8

Dwh.schema.define do
  # Shared dimensions
  dimension 'Customer' do
    hierarchy has_all: true, all_member_name: 'All Customers', primary_key: 'customer_key' do
      table 'customer_dimension'
      level 'Region', column: 'customer_region', unique_members: true
      level 'State', column: 'customer_state', unique_members: true
      level 'City', column: 'customer_city', unique_members: false
      level 'Customer Name', column: 'customer_name', unique_members: false
    end
  end

  dimension 'Gender' do
    hierarchy has_all: true, all_member_name: 'All Genders', primary_key: 'customer_key' do
      table 'customer_dimension'
      level 'Gender', unique_members: true do
        key_expression { sql "COALESCE(customer_gender, 'Company')" }
      end
    end
  end

  dimension 'Product' do
    hierarchy has_all: true, all_member_name: 'All Products', primary_key: 'product_key' do
      table 'product_dimension' do
        sql "product_dimension.product_version = 1"
      end
      level 'Product Category', column: 'category_description', unique_members: true do
        name_expression { sql "TRIM(category_description)" }
      end
      level 'Product Department', column: 'department_description', unique_members: false do
        name_expression { sql "TRIM(department_description)" }
      end
      level 'Product Name', column: 'product_description', unique_members: false
    end
  end

  dimension 'Time', type: 'TimeDimension' do
    hierarchy has_all: true, all_member_name: 'All Time', primary_key: 'date_key' do
      table 'date_dimension'
      level 'Year', column: 'calendar_year', type: 'Numeric', unique_members: true, level_type: 'TimeYears'
      level 'Quarter', column: 'calendar_quarter', name_column: 'calendar_year_quarter', unique_members: false, level_type: 'TimeQuarters'
      level 'Month', column: 'calendar_month_number_in_year', type: 'Numeric', unique_members: false, level_type: 'TimeMonths' do
        name_expression { sql "calendar_month_name || ' ' || calendar_year" }
      end
      level 'Day', column: 'day_number_in_calendar_month', type: 'Numeric', name_column: 'full_date_description', unique_members: false, level_type: 'TimeDays'
    end
  end

  cube 'Store Sales' do
    table 'store_sales_fact', schema: 'store'
    dimension_usage 'Customer', foreign_key: 'customer_key'
    dimension_usage 'Gender', foreign_key: 'customer_key'
    dimension_usage 'Product', foreign_key: 'product_key'
    dimension_usage 'Time', foreign_key: 'date_key'
    dimension 'Transaction Type' do
      hierarchy has_all: true, all_member_name: 'All Transaction Types' do
        level 'Transaction Type', column: 'transaction_type'
      end
    end

    measure 'Store Sales Count', column: 'sales_quantity', aggregator: 'count'
    measure 'Store Sales Quantity', column: 'sales_quantity', aggregator: 'sum'
    measure 'Store Sales Amount', column: 'sales_dollar_amount', aggregator: 'sum'
    measure 'Store Cost Amount', column: 'cost_dollar_amount', aggregator: 'sum'
  end
end