Как я могу создать предагрегацию, способную запрашивать куб с фильтром на его соединениях с кубом?

Вопрос или проблема

cube(`orders`, {
  sql_table: `public.orders`,

  data_source: `default`,

  joins: {
    order_items: {
      sql: `${CUBE}.id = ${order_items}.order_id`,
      relationship: `one_to_many`
    },
  },

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primary_key: true
    },

    type: {
      sql: `type`,
      type: `string`,
    },
  },

  measures: {
    count: {
      type: `count`
    },
  }
});

cube(`order_items`, {
  sql_table: `public.order_items`,

  data_source: `default`,

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primary_key: true
    },

    status: {
      sql: `status`,
      type: `string`
    },
  },

  measures: {
    count: {
      type: `count`
    },

    unique_order_count: {
      sql: `order_id`,
      type: `count_distinct`,
    }
  }
});

  pre_aggregations: {
    main: {
      measures: [
        orders.count,
      ],
      dimensions: [
        orders.type,
        order_items.status,
      ],
      time_dimension: orders.updated_at,
      granularity: `hour`,
      partition_granularity: `month`,
      refresh_key: {
        every: `1 day`,
        sql: `SELECT MAX(updated_at) FROM orders`
      },
    }
  }

  pre_aggregations: {
    uniq_by_order: {
      measures: [
        order_items.unique_order_count,
      ],
      dimensions: [
        order_items.status,
      ],
      time_dimension: order_items.updated_at,
      granularity: `hour`,
      partition_granularity: `month`,
      refresh_key: {
        every: `1 day`,
        sql: `SELECT MAX(updated_at) FROM order_items`
      },
    }
  }

Ответ или решение

Создание преподсчета (preaggregation) для кубов Order и OrderItem с фильтром на статусах

Ваша задача заключается в том, чтобы создать эффективный механизм для выполнения запросов к кубу Order, с динамическим фильтром по статусам элементов заказа (OrderItem). В текущей архитектуре возникли определенные сложности, и ниже приведены рекомендации для их решения.

Анализ текущих кубов

У вас есть два куба:

1. Куб Order:

   • Имеет поля id, type.
   • Использует one_to_many связь с кубом OrderItem.

2. Куб OrderItem:

   • Содержит поля id, status, а также уникальный счетчик для order_id.

Таким образом, вы можете формировать запросы, которые будут опираться на связи между этими кубами. Но для достижения вашей цели (фильтрация по статусу OrderItem) нужно правильно настроить преподсчеты.

Настройка преподсчета в кубе Order

Чтобы решить задачу фильтрации по статусу, убедитесь, что вы правильно настроили компоненты преподсчета. Приведенный вами код для pre_aggregations в кубе Order выглядит следующим образом:

pre_aggregations: {
  main: {
    measures: [
      orders.count,
    ],
    dimensions: [
      orders.type,
      order_items.status,
    ],
    time_dimension: orders.updated_at,
    granularity: `hour`,
    partition_granularity: `month`,
    refresh_key: {
      every: `1 day`,
      sql: `SELECT MAX(updated_at) FROM orders`
    },
  }
}

При этом возникают следующие проблемы:

• Ошибка подключения к cubestore-router, что может указывать на сетевые проблемы или неправильную конфигурацию сервера.
• Преподсчет не выполняется, когда он нуждается в использовании меры unique_order_count из куба OrderItem.

Рекомендации:

1. Проверка сетевых подключений: Убедитесь, что ваш сервер доступен по указанному адресу и порту. Используйте команды диагностики, такие как ping или telnet, чтобы проверить возможность подключения.

2. Оптимизация структуры запроса: Возможно, следует создать отдельный преподсчет для фильтрации по статусам в кубе Order, который будет включать связь с OrderItem. Это можно сделать, добавив статус как измерение, даже если это приводит к некоторому дублированию данных.

Настройка преподсчета в кубе OrderItem

Для того, чтобы unique_order_count использовал преподсчеты, рассмотрите следующую структуру:

pre_aggregations: {
  uniq_by_order: {
    measures: [
      order_items.unique_order_count,
    ],
    dimensions: [
      order_items.status,
    ],
    time_dimension: order_items.updated_at,
    granularity: `hour`,
    partition_granularity: `month`,
    refresh_key: {
      every: `1 day`,
      sql: `SELECT MAX(updated_at) FROM order_items`
    },
    // Добавляем важные параметры для улучшения выборки
    filters: [
      { sql: `${order_items}.status IN ('Approved')` }
    ]
  }
}

Общие советы:

• При использовании COUNT DISTINCT в запросах, укрупняйте группировку по другим важным полям, чтобы повлиять на выборку и улучшить производительность.
• Рассмотрите возможность добавления индексов на уровень базы данных для полей, которые активно используются в фильтрации (status, order_id далее).

Заключение

Создание эффективных и производительных преподсчетов требует внимания как к механике запросов, так и к наладке сетевых соединений. Осуществление вышеописанных рекомендаций должно помочь улучшить запросы и обеспечить возможность фильтрации по статусам OrderItem при работе с кубом Order. Постоянно следите за производительностью и трудностями при изменении конфигурации и реагируйте на возникающие ошибки.