Транспонирование таблиц

[alphabi_community]

Транспонирование - это поворот данных из строк в столбцы и наоборот. Такой способ обработки данных позволяет сделать более удобным визуальный анализ таблиц и преобразовать набор данных для дальнейшего анализа.

Например, в таблице наименования разных видов медицинских учреждений (“ЦРБ”, “Районные”, “участковые”) находятся в разных столбцах.

Пример исходника:

Субъект РФ	Год	ЦРБ	районные	участковые
Белгородская область	2013	21	3
Брянская область	2013	27		1
Владимирская область	2013	13	13	5
Воронежская область	2013	32
Ивановская область	2013	21	1

С точки зрения куба эти наименования являются элементами одной размерности, например, “Виды мед. учреждений”, а значения являются мерой, показателем и т.п., поэтому вместо последних трех столбцов в таблицу необходимо загружать два столбца с видом медучреждения и числовым значением.

Как данные должны выглядеть после преобразования:

Субъект РФ	Год	Вид мед.учреждения	Значение
Белгородская область	2013	ЦРБ	21
Брянская область	2013	ЦРБ	27
Владимирская область	2013	ЦРБ	13
Воронежская область	2013	ЦРБ	32
Ивановская область	2013	ЦРБ	21
Белгородская область	2013	районные	3
Брянская область	2013	районные
Владимирская область	2013	районные	13
Воронежская область	2013	районные
Ивановская область	2013	районные	1
Белгородская область	2013	участковые
Брянская область	2013	участковые	1
Владимирская область	2013	участковые	5
Воронежская область	2013	участковые
Ивановская область	2013	участковые

SQL-запрос

В нашем примере таблица, к которой будет отправлен запрос, выглядит так:

В ETL-процессе

В ETL-процессе добавим блок “SQL запрос к базе данных”, в настройках укажем соединение и текст запроса:

SQL-запрос

WITH
s AS (SELECT *,
			 -- добавляем поля с видом мед. учреждения
			 'ЦРБ' AS црб,
			 'Районнные' AS районные,
			 'Участковые' AS участковые
	  FROM statistics_med)
SELECT s.subject,
	   s.year,
	   -- формируем массивы со значениями и видами МУ, после чего транспонируем их
	   unnest(array [црб, районные, участковые]) AS type,
	   unnest(array [crb, district, local]) AS value
FROM s

Результат транспонирования:

В бизнес-объекте

Создаем бизнес-объект типа “SQL-запрос”, в редакторе БО указываем соединение (настройки подключения к БД), в поле “Текст SQL-запроса” добавим скрипт:

SQL-запрос

WITH
s AS (SELECT *,
			 -- добавляем поля с видом мед. учреждения
			 'ЦРБ' AS црб,
			 'Районнные' AS районные,
			 'Участковые' AS участковые
	  FROM statistics_med)
SELECT 1 as "Id",
	   s.subject,
	   s.year,
       -- формируем массивы со значениями и видами МУ, после чего транспонируем их
	   unnest(array [црб, районные, участковые]) AS type,
	   unnest(array [crb, district, local]) AS value
FROM s

Для обращения к таблицам (бизнес-объектам) БД AlphaBI используется соединение Rdbms.

В настройках полей нажимаем кнопку “Автозаполнение полей и параметров по хранилищу” - > “Из полей SQL-запроса”, поля БО будут созданы автоматически на основании полученных данных.

Сохраняем и опубликовываем бизнес-объект, проводим синхронизацию схемы. В окне просмотра БО видим результат выполнения запроса:

ETL

Источник данных - Excel-файл с элементами размерности “Год” в разных столбцах.

Требуется, чтобы года (2007, 2008 и т.д.) были в одном столбце. Для этого нужно создать следующий ETL-процесс:

1. В ETL-шаге Генератор строк нужно указать число, равное количеству столбцов, содержащих элементы одной размерности. Так как у нас с 2007 по 2017, то в генераторе указано - 11.
   
   
2. В ETL-шаге JOIN - объединить генератор строк с блоком извлечения данных Excel-файла. Тип join - inner. Условия нет.
   
   
3. В ETL-шаге Калькулятор нужно добавить 2 поля - для меры и размерности - и вставить текст формулы:
   
   

• Формула для меры:
  GETARRAYELEMENT((TOARRAY([@Field2], [@Field3], [@Field4], [@Field5], [@Field6], [@Field7], [@Field8], [@Field9], [@Field10], [@Field11], [@Field12])),(([@RowNum] - 1)))
  , где [@Field2], [@Field3] … - это коды полей с числовыми значениями.
  В режиме блокли выглядит так:
  

  

• Формула для размерности:
  GETARRAYELEMENT((TOARRAY("2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015", "2016", "2017")),(([@RowNum] - 1)))
  , где "2007", "2008"… - элементы размерности
  

  
  
  При предпросмотре результат будет следующий:
  
  

Примечание:

1. Вариант формулы написан для режима выполнения ETL-процесса в оперативной памяти.
2. В случае выполнения процесса в режиме SQL-зации:
   Если планируется записывать данные в бизнес-объект, необходимо убрать вычитание единицы из обеих формул для корректной обработки столбца в момент выполнения ETL-процесса, так как индексация элементов списка начинается с 0 - вычитание 1 необходимо для корректного сопоставления значений, где строке под номером 1 будет соответствовать элемент с индексом 0 (“2007”):

• Формула для размерности:
  GETARRAYELEMENT((TOARRAY("2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015", "2016", "2017")),[@RowNum])
• Формула для меры:
  GETARRAYELEMENT((TOARRAY([@Field2], [@Field3], [@Field4], [@Field5], [@Field6], [@Field7], [@Field8], [@Field9], [@Field10], [@Field11], [@Field12])),[@RowNum])

Далее заполняем БО, указав соответствующие поля для заполнения:

Получаем таблицу с транспонированными столбцами: