04 API для работы с OLAP кубами

Доступные эндпойнты:

GET api/db/${schema_name}.cubes/

Получить конфигурации всех кубов (локальных и глобальных (schema_name = 'koob'))

Ответ:

[
  {
    "id": "mysource.custom_dm_itog_reestr",
    "source_ident": "mysource",
    "is_source_global": 0,
    "name": "custom_dm_itog_reestr",
    "is_global": 0,
    "title": "custom_dm_itog_reestr",
    "sql_query": "select dm_itog_reestr_values.class_code1_name as class_code1_name,\n       dm_itog_reestr_values.class_code3_name as class_code3_name,\n       dm_itog_reestr_values.class_code4_name as class_code4_name,\n       dm_itog_reestr_values.cost_categories_name as cost_categories_name,\n       dm_itog_reestr_values.cost_sub_categories_name as cost_sub_categories_name,\n       dm_itog_reestr_values.do_name as do_name,\n       dm_itog_reestr_values.year_period as dt,\n       dm_itog_reestr_values.year_period as year_period,\n      dm_itog_reestr_values.fact as fact,\n       dm_itog_reestr_values.mnt_period as mnt_period,\n       dm_itog_reestr_values.mr_name as mr_name,\n      dm_itog_reestr_values.param_name as param_name,\n       dm_itog_reestr_values.plan as plan,\n       dm_itog_reestr_values.process as process,\n       do_code, mr_code, process_code, cost_categories_id, cost_sub_categories_id, class_code1, class_code3, class_code4 \nfrom dm_itog_reestr_values\nwhere ${filters()}  and mnt_period <> 'БП'\nunion all \nselect dm_itog_reestr_drivers.class_code1_name as class_code1_name,\n       dm_itog_reestr_drivers.class_code3_name as class_code3_name,\n       dm_itog_reestr_drivers.class_code4_name as class_code4_name,\n       dm_itog_reestr_drivers.cost_categories_name as cost_categories_name,\n       dm_itog_reestr_drivers.cost_sub_categories_name as cost_sub_categories_name,\n       dm_itog_reestr_drivers.do_name as do_name,\n       dm_itog_reestr_drivers.year_period as dt,\n       dm_itog_reestr_drivers.year_period as year_period,\n       dm_itog_reestr_drivers.fact as fact,\n       dm_itog_reestr_drivers.mnt_period as mnt_period,\n       dm_itog_reestr_drivers.mr_name as mr_name,\n       dm_itog_reestr_drivers.param_name as param_name,\n       dm_itog_reestr_drivers.plan as plan,\n       dm_itog_reestr_drivers.process as process,\n       do_code, mr_code, process_code, cost_categories_id, cost_sub_categories_id, class_code1, class_code3, class_code4 \nfrom dm_itog_reestr_drivers\nwhere ${filters(do_code,mr_code,dt,mnt_period,mnt_dt)} and mnt_period <> 'БП'",
    "config": {
      "skip_where": 1,
      "is_template": 1
    }
  },
  {
    "id": "mysource.itog_table",
    "source_ident": "mysource",
    "is_source_global": 0,
    "name": "itog_table",
    "is_global": 0,
    "title": "itog_table",
    "sql_query": "/*SELECT * FROM dm_itog_table_t where  ${filters()}*/\nselect * from dm_itog_table_t \nwhere ord not in (202,203,204,205,206,207,208,209)\nand ${filters()}\nunion all\n select distinct mnt_period,do_code,mr_code,process_code,cost_categories_id,cost_sub_categories_id,0,0,0,grp_name,metric_name,value,ord,unit,norm,unit_id,dt\n from dm_itog_table_t \n where ord in (202,203,204,205,206,207,208,209) \n and ${filters(except(class_code1,class_code3,class_code4))}",
    "config": {
      "skip_where": 1,
      "is_template": 1
    }
  },
  {
    "id": "mysource.transport_e_m",
    "source_ident": "mysource",
    "is_source_global": 0,
    "name": "transport_e_m",
    "is_global": 0,
    "title": "transport_e_m",
    "sql_query": "select dm_itog_final_all.class_code1 as class_code1, dm_itog_final_all.class_code1_name as class_code1_name, dm_itog_final_all.class_code3 as class_code3, dm_itog_final_all.class_code3_name as class_code3_name, dm_itog_final_all.class_code4 as class_code4, dm_itog_final_all.class_code4_name as class_code4_name, dm_itog_final_all.cost_categories_id as cost_categories_id, dm_itog_final_all.cost_categories_name as cost_categories_name, dm_itog_final_all.cost_sub_categories_id as cost_sub_categories_id, dm_itog_final_all.cost_sub_categories_name as cost_sub_categories_name, dm_itog_final_all.do_code as do_code, dm_itog_final_all.do_name as do_name, dm_itog_final_all.driver_code as driver_code, dm_itog_final_all.driver_id as driver_id, dm_itog_final_all.dt as dt, dm_itog_final_all.etalon as etalon, dm_itog_final_all.mnt_period as mnt_period, dm_itog_final_all.mr_code as mr_code, dm_itog_final_all.mr_name as mr_name, dm_itog_final_all.norm as norm, dm_itog_final_all.process as process, dm_itog_final_all.process_code as process_code, dm_itog_final_all.unit as unit, dm_itog_final_all.unit_id as unit_id, dm_itog_final_all.value as value,  dm_itog_final_all.etalon_do_name as etalontitle,  dm_itog_final_all.marka as marka\nfrom dm_itog_final_all",
    "config": {}
  }
]

Для эндпойнтов типа api/db/${schema}.${table} доступна фильтрация через блок ./filter(${boolean_expression}), например такая

.filter(is_source_global=0&&is_global=0) - вернет конфиги только локальных кубов с локальными источниками

GET api/db/${schema_name}.dimensions/

Получить конфигурации всех дименшнов всех кубов (локальных и глобальных (schema_name = 'koob'))

Ответ:

[
  {
    "id": "mysource.custom_dm_itog_reestr.class_code1",
    "source_ident": "mysource",
    "cube_id": "mysource.custom_dm_itog_reestr",
    "cube_name": "custom_dm_itog_reestr",
    "is_cube_global": 0,
    "name": "class_code1",
    "is_global": 0,
    "type": "NUMBER",
    "title": "class_code1",
    "sql_query": "class_code1",
    "config": {
      "possible_aggregations": [
        "sum",
        "avg",
        "min",
        "max",
        "count"
      ]
    }
  },
  {
    "id": "mysource.custom_dm_itog_reestr.class_code1_name",
    "source_ident": "mysource",
    "cube_id": "mysource.custom_dm_itog_reestr",
    "cube_name": "custom_dm_itog_reestr",
    "is_cube_global": 0,
    "name": "class_code1_name",
    "is_global": 0,
    "type": "STRING",
    "title": "class_code1_name",
    "sql_query": "class_code1_name",
    "config": {
        "possible_aggregations": []
    }
  },
  {
    "id": "mysource.custom_dm_itog_reestr.class_code3",
    "source_ident": "mysource",
    "cube_id": "mysource.custom_dm_itog_reestr",
    "cube_name": "custom_dm_itog_reestr",
    "is_cube_global": 0,
    "name": "class_code3",
    "is_global": 0,
    "type": "NUMBER",
    "title": "class_code3",
    "sql_query": "class_code3",
    "config": {
      "possible_aggregations": [
        "sum",
        "avg",
        "min",
        "max",
        "count"
      ]
    }
  },
]

Доступна фильтрация через .filter(${boolean_expr}):

.filter(source_ident='${source_ident}'&&cube_name='${cube_name}'&&is_global=0) - вернет конфиги дименшнов локальных кубов

POST /api/v3/${schema_name}/data

Получить данные для указанного куба. Возвращается декартово произведение дименшнов и меж.

Тело запроса:

Content-type: application/json

{
  /* REQUIRED идентификатор куба, STRING */
  with: "luxmsbi.custom_country_currency",
  
  /* REQUIRED массив строк, которые есть или идентификаторы дименшнов, или аггрегационные функции над полями, STRING[] */
  columns: ["country_code", "sum(cnt):sum_cnt", "count(cnt):ccc"],
  
  /* REQUIRED В общем виде объект с фильтрами на данные при запросе, аналогично тому, как это задается в конфиге дешлетов (кроме записей вида `country_code: true`) */
  filters: {
    country_code: ["=", "BLR", "CHN", "KAZ", "RUS", "USA"]
  },
  
  /* отступ от начала таблицы, NUMBER */
  offset: 0,
  
  /* количество строк, NUMBER */
  limit: 128, // по умолчанию 128 строк, для отключения органичений укажите 0
  
  /* массив строк типа +id, -date, где + и - это ASC и DESC,а остальное - идентификаторы дименшнов */
  sort: ["+sum_cnt"],
  
  /* массив опций для управления иерархиями и блоками в БД типа "Все" */
  /* MemberAll - */
  /* ParallelHierarchyFilters - */
  options: ['!MemberAll', '!ParallelHierarchyFilters'],
  
  /* массив идентификаторов дименшнов, по которым будут считаться подытоги */
  subtotals : ["country_code"]
  
  /* массив идентификаторов дименшнов, по которым нужны distinct запросы, не имеет смысла, если есть хоть одна межа в запросе */
  distinct: ["country_code"]
  }

Ответ:

Accept: application/stream+json
Content-type: application/json

{"country_code":"BLR","sum_cnt":1,"ccc":1,"∑country_code":0}
{"country_code":"CHN","sum_cnt":1,"ccc":1,"∑country_code":0}
{"country_code":"KAZ","sum_cnt":1,"ccc":1,"∑country_code":0}
{"country_code":"RUS","sum_cnt":1,"ccc":1,"∑country_code":0}
{"country_code":"USA","sum_cnt":1,"ccc":1,"∑country_code":0}

POST /api/v3/${schema_name}/data?meta

Получить вспомогательную информацию по запросу за данными. Например итоговый запрос SQL, выполняющийся в БД

Тело запроса:

Аналогично телу для /data

Ответ:

Accept: application/stream+json
Content-type: application/json

{
  "id": "luxmsbi", 
  "query": "SELECT DISTINCT country_code as country_code, sum(cnt) as sum_cnt, count(cnt) as ccc, GROUPING(country_code) AS \"∑country_code\"\nFROM (select custom.country_currency.country_code as country_code,\n       custom.country_currency.currency_code as currency_code,\n       1 cnt\nfrom custom.country_currency) AS custom_country_currency\nWHERE (country_code IN ('BLR', 'CHN', 'KAZ', 'RUS', 'USA'))\nGROUP BY GROUPING SETS ((country_code),\n                        (country_code)\n                       )\nORDER BY sum_cnt LIMIT 128 OFFSET 0", 
  "datagate_url": "http://datagate", 
  "preserveCase": true
}

POST /api/v3/${schema_name}/count

Получить число строк для запроса в указанный куб (запрос аналогичен /data, но отдается COUNT(*)).

Тело запроса:

Аналогично телу для /data

Ответ:

Accept: application/stream+json
Content-type: application/json

{"count":5}