Перейти к основному содержимому

01. Создание кубов из UDF функций

Вызов хранимых процедур с использованием фильтров работает только для двух СУБД: PostgreSQL и MSSQL (в ДЕМО режиме )

Для этого необходимо указать специализированный SQL при определении куба (см. ниже). Кроме этого, конфиге куба необходимо указать настройки:

{
  skip_where: 1,
  is_template: 1
}

Все размерности и факты в таком кубе можно определить вручную или изначальный куб построить на какой-то таблице или представлении (view), которая будет соответствовать ответу используемой UDF, а затем заменить SQL на уже созданном кубе.

SQL для куба на базе UDF в PostgreSQL:​

SELECT * FROM custom.some_function(${udf_args('', ql(dim1), '', ql(dim2))})

где:

  • custom.some_function - udf функция в PostgreSQL
  • dim1, dim2 - название размерностей в управляющем деше, значения фильтра по которой будет использоваться в качестве параметра для udf функции в PostgreSQL

или, для передачи всех фильтров в одном параметре в формате JSON:

SELECT * FROM custom.some_function(${udf_args('', ql(koob_filters()))})

SQL для куба на базе UDF в MSSQL:​

SELECT * FROM OPENQUERY([servername], 'EXEC fortests.fortests.get_data ${udf_args(param1, ql(ql(dim1)), param2, ql(ql(dim2)))}')

это должно будет преобразоваться в вызов:

SELECT * FROM OPENQUERY([servername], 'EXEC fortests.fortests.get_data @param1 = ''Moscow@piter@tumen'', @param2 = ''23000035''')

Для вызова потребуется прилинковать сервер:

 EXEC master.dbo.sp_addlinkedserver @server = N'instancename\servername',
    @srvproduct=N'',
    @provider=N'SQLNCLI';

Чтобы определить имя сервера: SELECT @@SERVERNAME;
Чтобы найти имя инстанса: SELECT @@servicename;
Может еще быть ошибка ‘Server is not configured for DATA ACCESS’ тогда требуется подать команду:

EXEC sp_serveroption 'servername', 'DATA ACCESS', TRUE;

Кроме этого, требуется настроить отдельный куб для Управляющего деша, в котором будут присутствовать размерности с полным списком значений для возможности использования в фильтре.

Для MSSQL, пока что, в целях демонстрации, фильтр будет работать при снятии всех галочек, а затем выбора одной или нескольких значений размерности.

Пример функции для PostgreSQL​

Определение куба

SELECT * FROM custom.some_function(${udf_args('', ql(koob_filters()))})

приведёт к вызову:

SELECT * FROM custom.some_function('{"luxmsbi.pgfunc.producttype":["=","Мука","Сахар"]}')

То есть, функция в PostgreSQL должна принимать в качестве параметра JSONB и обрабатывать его. Прилагаем пример такой функции:

CREATE OR REPLACE FUNCTION custom.some_function(_filter jsonb)
 RETURNS TABLE(region_id integer, val double precision, city character varying, country character varying, producttype character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE
  _f JSONB;
BEGIN

IF _filter IS null or _filter::text = '{}' or (_filter->'luxmsbi.pgfunc.producttype'->>0 = '!=' and  jsonb_array_length(_filter->'luxmsbi.pgfunc.producttype') = 1) THEN
    RETURN QUERY 
    SELECT * FROM custom.region_bar;
else
    if _filter->'luxmsbi.pgfunc.producttype'->>0 = '!=' THEN
        RETURN QUERY 
        SELECT * FROM custom.region_bar
            where not (_filter->'luxmsbi.pgfunc.producttype' ? type);
    else
        RETURN QUERY 
        SELECT * FROM custom.region_bar
            where _filter->'luxmsbi.pgfunc.producttype' ? type;
    end if;
end if;
  
  RETURN;
END
$function$
;

Пример функции для PostgreSQL с передачей имени пользователя​

В при использовании UDF можно не ограничиваться одним параметром с содержимым фильтра, имеется возможность передать какую-либо ещё информацию в других параметрах, например имя текущего пользователя. В таком случае определении функции может выглядеть так:
CREATE OR REPLACE FUNCTION custom.some_function_username(_filter jsonb, _username text)

При определении Куба можно использовать LPE функции, например, для извлечения username текущего пользователя Luxms BI:

SELECT * FROM custom.some_function_username(${udf_args('', ql(koob_filters()),  '', ql(get_in(user, 'username')) )})

Варианты фильтров, которые могут быть переданы из управляющего деша​

В массиве с условием фильтра ключ — это размерность (dimension), значение — это массив с условиями по размерности.
Пример фильтров:

{
    "category": ["!=", "Руководители"],
    "education": ["=", "Ученые", "Студенты"]
}

Возможные варианты первого элемента в массиве (актуально для версии Luxms BI 9.2 выше):​

= 
!=
ilike
between
>=
<=