29 нояб. 2013 г.

Azure'ние QlikView

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


Поэтому ради интереса я задумал подцепить тот самый QlikView к удаленным данным 1С. При этом обойтись без этих всяких коннекторов, а обращаться к облаку из скрипта загрузки на чистом SQL. И чтобы особо не заморачиваться над переводом запросов 1С в запросы SQL было решено быстренько, пока теплые, надергать их из профайлера SQL.

Сначала займемся сервером SQL. Поскольку часть настроек можно я уже выполнил в предыдущем посте, сразу же перехожу к созданию конечной точки в панели Azure.



В панели управления сервером SQL добавляем пользователя QlikView. На вкладке General задаем пароль и убираем отметку Enforce password expiration.


На вкладке User Mapping назначаем роль db_datareader для базы 1С trade_azure.


Запускаем QlikView на локальной машине и начинаем настройку скрипта загрузки.


Выбираем поставщика данных, Microsoft OLE DB Provider for SQL Server или SQL Server Native Client и переходим к настройке соединения.


При использовании SQL Server Native Client необходимо на вкладке "Все" выставить параметры Persist Security Info = True и Integrated Security = false (хотя true тоже подошло, при пустом значении подключение не удается).


После настройки строки подключения переходим к формированию запроса к SQL серверу. В качестве подопытной базы будет использоваться демо-база Управление Торговлей, редакция 11.1 (11.1.2.22). Источником данных будет служить регистр ВыручкаИСебестоимостьПродаж.
На SQL сервере запустим новый сеанс отладки в SQL Profiler. Я создал сеанс на основе шаблона Standart и дополнительно указал все события веток Strored Procedures и TSQL.


Выполним простой запрос к этому регистру:

ВЫБРАТЬ *
ИЗ РегистрНакопления.ВыручкаИСебестоимостьПродаж КАК ВыручкаИСебестоимостьПродаж

и перейдем в профайлер, где найдем события, содержащие текст нашего запроса, преобразованный в T-SQL.


SELECT
T1._Period,
T1._RecorderTRef,
T1._RecorderRRef,
T1._LineNo,
T1._Active,
T1._Fld4486RRef,
T1._Fld4487_TYPE,
T1._Fld4487_RTRef,
T1._Fld4487_RRRef,
T1._Fld4488RRef,
T1._Fld4489RRef,
T1._Fld4490RRef,
T1._Fld7580RRef,
T1._Fld15854RRef,
T1._Fld4491,
T1._Fld4492,
T1._Fld11014,
T1._Fld4493,
T1._Fld11015,
T1._Fld4494,
T1._Fld11016,
T1._Fld11017,
T1._Fld11018,
T1._Fld4495RRef,
T1._Fld13736RRef
FROM dbo._AccumRg4485 T1 WITH(NOLOCK)
WHERE (T1._Fld11258 = 0.0)

Теперь вставляем этот запрос в QlikView, после строки подключения указываем команду LOAD *;, в конце запроса так же ставим ";".

OLEDB CONNECT32 TO [Provider=SQLNCLI11.1;Persist Security Info=True;User ...
LOAD *;
SELECT
T1._Period,
...
T1._Fld13736RRef
FROM dbo._AccumRg4485 T1 WITH(NOLOCK)
WHERE (T1._Fld11258 = 0.0);

Запускаем скрипт на выполнение, после загрузки получаем следующую таблицу:


Данные получены, но совершенно нечитаемы. Поэтому продолжим настраивать запрос в 1С. Определим, какие поля нам конкретно нужны.

ВЫБРАТЬ
ВыручкаИСебестоимостьПродаж.Период,
ВыручкаИСебестоимостьПродаж.АналитикаУчетаНоменклатуры.Номенклатура,
ВыручкаИСебестоимостьПродаж.АналитикаУчетаПоПартнерам.Партнер,
ВыручкаИСебестоимостьПродаж.Подразделение,
ВыручкаИСебестоимостьПродаж.Количество,
ВыручкаИСебестоимостьПродаж.СуммаВыручки,
ВыручкаИСебестоимостьПродаж.Себестоимость
ИЗ
РегистрНакопления.ВыручкаИСебестоимостьПродаж КАК ВыручкаИСебестоимостьПродаж

Перед запуском запроса в 1С рекомендую перейти в профайлер SQL и очистить результаты трассировки, чтобы не искать новый результат среди предыдущих.

SELECT
T1._Period,
T2._Fld6989RRef,
T3._Fld6992RRef,
T1._Fld4489RRef,
T1._Fld4491,
T1._Fld4492,
T1._Fld4493
FROM dbo._AccumRg4485 T1 WITH(NOLOCK)
LEFT OUTER JOIN dbo._Reference55 T2 WITH(NOLOCK)
ON (T1._Fld4486RRef = T2._IDRRef) AND (T2._Fld11258 = 0.0)
LEFT OUTER JOIN dbo._Reference56 T3 WITH(NOLOCK)
ON (T1._Fld4488RRef = T3._IDRRef) AND (T3._Fld11258 = 0.0)
WHERE (T1._Fld11258 = 0.0)

Запрос SQL изменился, появились связи с другими таблицами базы. Теперь настроим текстовое представление данных ссылочного типа.

ВЫБРАТЬ
ВыручкаИСебестоимостьПродаж.Период,
ПРЕДСТАВЛЕНИЕ(ВыручкаИСебестоимостьПродаж.АналитикаУчетаНоменклатуры.Номенклатура),
ПРЕДСТАВЛЕНИЕ(ВыручкаИСебестоимостьПродаж.АналитикаУчетаПоПартнерам.Партнер),
ПРЕДСТАВЛЕНИЕ(ВыручкаИСебестоимостьПродаж.Подразделение),
ВыручкаИСебестоимостьПродаж.Количество,
ВыручкаИСебестоимостьПродаж.СуммаВыручки,
ВыручкаИСебестоимостьПродаж.Себестоимость
ИЗ
РегистрНакопления.ВыручкаИСебестоимостьПродаж КАК ВыручкаИСебестоимостьПродаж

Переносим получившийся запрос SQL в QlikView и запускаем.

SELECT
T1._Period,
T2._Fld6989RRef,
T3._Description,
T4._Fld6992RRef,
T5._Description,
T1._Fld4489RRef,
T6._Description,
T1._Fld4491,
T1._Fld4492,
T1._Fld4493
FROM dbo._AccumRg4485 T1 WITH(NOLOCK)
LEFT OUTER JOIN dbo._Reference55 T2 WITH(NOLOCK)
ON (T1._Fld4486RRef = T2._IDRRef) AND (T2._Fld11258 = 0.0)
LEFT OUTER JOIN dbo._Reference64 T3 WITH(NOLOCK)
ON (T2._Fld6989RRef = T3._IDRRef) AND (T3._Fld11258 = 0.0)
LEFT OUTER JOIN dbo._Reference56 T4 WITH(NOLOCK)
ON (T1._Fld4488RRef = T4._IDRRef) AND (T4._Fld11258 = 0.0)
LEFT OUTER JOIN dbo._Reference70 T5 WITH(NOLOCK)
ON (T4._Fld6992RRef = T5._IDRRef) AND (T5._Fld11258 = 0.0)
LEFT OUTER JOIN dbo._Reference101 T6 WITH(NOLOCK)
ON (T1._Fld4489RRef = T6._IDRRef) AND (T6._Fld11258 = 0.0)
WHERE (T1._Fld11258 = 0.0)

И получаем ошибку, потому что у нас неуникальные поля _Description, для которых должны быть заданы синонимы в запросе.


Добавим синонимы к этим полям
...
T2._Fld6989RRef,
T3._Description as Tovar,
T4._Fld6992RRef,
T5._Description as Partner,
T1._Fld4489RRef,
T6._Description as Otdel,
...


Приведем запрос 1С к окончательному виду, добавим приведение периода к началу дня и группировки.

ВЫБРАТЬ
НачалоПериода(ВыручкаИСебестоимостьПродаж.Период, ДЕНЬ) КАК Период,
ПРЕДСТАВЛЕНИЕ(ВыручкаИСебестоимостьПродаж.АналитикаУчетаНоменклатуры.Номенклатура) КАК НоменклатураНаименование,
ПРЕДСТАВЛЕНИЕ(ВыручкаИСебестоимостьПродаж.АналитикаУчетаПоПартнерам.Партнер) КАК ПартнерСтрока,
ПРЕДСТАВЛЕНИЕ(ВыручкаИСебестоимостьПродаж.Подразделение) КАК ПодразделениеСтрока,
СУММА(ВыручкаИСебестоимостьПродаж.Количество) КАК Количество,
СУММА(ВыручкаИСебестоимостьПродаж.СуммаВыручки) КАК СуммаВыручки,
СУММА(ВыручкаИСебестоимостьПродаж.Себестоимость) КАК Себестоимость
ИЗ
РегистрНакопления.ВыручкаИСебестоимостьПродаж КАК ВыручкаИСебестоимостьПродаж

СГРУППИРОВАТЬ ПО
НачалоПериода(ВыручкаИСебестоимостьПродаж.Период, ДЕНЬ),
ВыручкаИСебестоимостьПродаж.АналитикаУчетаНоменклатуры.Номенклатура,
ВыручкаИСебестоимостьПродаж.АналитикаУчетаПоПартнерам.Партнер,
ВыручкаИСебестоимостьПродаж.Подразделение

Задаем синонимы полям полученного запрос SQL и помещаем его в скрипт загрузки QlikView.

SELECT
DATEADD(DAY,CAST(DATEPART(DAY,T1._Period) AS NUMERIC(4)) - 1,DATEADD(MONTH,CAST(DATEPART(MONTH,T1._Period) AS NUMERIC(4)) - 1,DATEADD(YEAR,(CAST(DATEPART(YEAR,T1._Period) AS NUMERIC(4)) - 2000) - 2000,{ts '2000-01-01 00:00:00'}))) as День,
T2._Fld6989RRef as ТоварID,
T3._Description as Товар,
T4._Fld6992RRef as ПартнерID,
T5._Description as Партнер,
T1._Fld4489RRef as ОтделID,
T6._Description as Отдел,
CAST(SUM(T1._Fld4491) AS NUMERIC(26, 8)) as Количество,
CAST(SUM(T1._Fld4492) AS NUMERIC(27, 8)) as Сумма,
CAST(SUM(T1._Fld4493) AS NUMERIC(27, 8)) as Себестоимость
FROM dbo._AccumRg4485 T1 WITH(NOLOCK)
LEFT OUTER JOIN dbo._Reference55 T2 WITH(NOLOCK)
ON (T1._Fld4486RRef = T2._IDRRef) AND (T2._Fld11258 = 0.0)
LEFT OUTER JOIN dbo._Reference64 T3 WITH(NOLOCK)
ON (T2._Fld6989RRef = T3._IDRRef) AND (T3._Fld11258 = 0.0)
LEFT OUTER JOIN dbo._Reference56 T4 WITH(NOLOCK)
ON (T1._Fld4488RRef = T4._IDRRef) AND (T4._Fld11258 = 0.0)
LEFT OUTER JOIN dbo._Reference70 T5 WITH(NOLOCK)
ON (T4._Fld6992RRef = T5._IDRRef) AND (T5._Fld11258 = 0.0)
LEFT OUTER JOIN dbo._Reference101 T6 WITH(NOLOCK)
ON (T1._Fld4489RRef = T6._IDRRef) AND (T6._Fld11258 = 0.0)
WHERE (T1._Fld11258 = 0.0)
GROUP BY DATEADD(DAY,CAST(DATEPART(DAY,T1._Period) AS NUMERIC(4)) - 1,DATEADD(MONTH,CAST(DATEPART(MONTH,T1._Period) AS NUMERIC(4)) - 1,DATEADD(YEAR,(CAST(DATEPART(YEAR,T1._Period) AS NUMERIC(4)) - 2000) - 2000,{ts '2000-01-01 00:00:00'}))),
T2._Fld6989RRef,
T4._Fld6992RRef,
T1._Fld4489RRef,
T2._Fld6989RRef,
T3._Description,
T4._Fld6992RRef,
T5._Description,
T1._Fld4489RRef,
T6._Description

Ярлыки: , , , , , , , , ,

15 комментариев:

  1. Анонимный8 мая 2015 г. в 13:27

    Добрый день,
    Подскажите возможно работать одновременно с двумя базами SQL ?

    ОтветитьУдалить
    Ответы
    1. mickey.1cx8 мая 2015 г. в 13:49

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

      Удалить
    2. Анонимный8 мая 2015 г. в 19:42

      Спасибо, Ваши статьи реально помогли разобраться. Правда с коннектором так и не понял, почему загружаются только теги $text.
      Всего наилучшего, удачи

      Удалить
    3. mickey.1cx8 мая 2015 г. в 20:04

      И вам спасибо, что зашли. Насчет запроса ответил в другой теме.

      Удалить
    4. Анонимный14 мая 2015 г. в 16:16

      Михаил, возникла проблема с переводом символьного кода SQL. Помочь сможете?

      Удалить
    5. mickey.1cx14 мая 2015 г. в 16:24

      Можно попробовать, опишите проблему.

      Удалить
    6. Анонимный14 мая 2015 г. в 16:37

      имею след. запрос:
      sql exec sp_executesql N'SELECT
      T1._IDRRef,
      T1._IDRRef as контрагент_id,
      T6._Description as контрагент,
      ISNULL(T2.Fld22294RRef,@P1) as ABC,
      T2.Fld22294RRef
      FROM dbo._Reference173 T1 WITH(NOLOCK)
      LEFT OUTER JOIN (SELECT
      T5._Fld22293RRef AS Fld22293RRef,
      T5._Fld22294RRef AS Fld22294RRef
      FROM (SELECT
      T4._Fld22293RRef AS Fld22293RRef,
      MAX(T4._Period) AS MAXPERIOD_
      FROM dbo._InfoRg22292 T4 WITH(NOLOCK)
      WHERE T4._Active = 0x01
      GROUP BY T4._Fld22293RRef) T3
      INNER JOIN dbo._InfoRg22292 T5 WITH(NOLOCK)
      ON T3.Fld22293RRef = T5._Fld22293RRef AND T3.MAXPERIOD_ = T5._Period) T2
      ON (T1._IDRRef = T2.Fld22293RRef)
      LEFT OUTER JOIN dbo._Reference173 T6 WITH(NOLOCK)
      ON T1._IDRRef = T6._IDRRef
      WHERE (T1._Folder = 0x01)',N'@P1 varbinary(16)',0x00000000000000000000000000000000;

      при обработке скрипта, все загружается. ISNULL(T2.Fld22294RRef,@P1) as ABC. После загрузки скрипта в столбце АВС (АВС класс контрагента) данные отображаются символьным кодом. Нужны данные АВС класс контрагента

      Удалить
    7. mickey.1cx14 мая 2015 г. в 16:42

      Запрос 1С тоже выложите

      Удалить
    8. Анонимный14 мая 2015 г. в 16:48

      ВЫБРАТЬ РАЗРЕШЕННЫЕ
      Контрагенты.Ссылка КАК Контрагент,
      ПРЕДСТАВЛЕНИЕ(Контрагенты.Ссылка) КАК КонтрагентПредставление,
      ЕСТЬNULL(ABCКлассификация.ABCКлассПокупателя, ЗНАЧЕНИЕ(Перечисление.ABCКлассификация.ПустаяСсылка)) КАК ABCКласс,
      ПРЕДСТАВЛЕНИЕ(ABCКлассификация.ABCКлассПокупателя) КАК ABCКлассПредставление
      {ВЫБРАТЬ
      Контрагент.*,
      ABCКласс}
      ИЗ
      Справочник.Контрагенты КАК Контрагенты
      {ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ABCКлассификацияПокупателей.СрезПоследних(, {(Контрагент).* КАК Контрагент}) КАК ABCКлассификация
      ПО Контрагенты.Ссылка = ABCКлассификация.Контрагент}
      ГДЕ
      Контрагенты.ЭтоГруппа = ЛОЖЬ
      {ГДЕ
      Контрагенты.Ссылка.* КАК Контрагент,
      (ЕСТЬNULL(ABCКлассификация.ABCКлассПокупателя, ЗНАЧЕНИЕ(Перечисление.ABCКлассификация.ПустаяСсылка))) КАК ABCКласс}
      {УПОРЯДОЧИТЬ ПО
      Контрагент.*,
      ABCКласс}

      Удалить
    9. mickey.1cx14 мая 2015 г. в 17:57

      попробуйте выполнить преобразование двоичного значения перечисления, отдельно символьное представление в sql не хранится:
      CAST(ISNULL(T2.Fld22294RRef,@P1) as varchar) as ABC

      Удалить
    10. Анонимный14 мая 2015 г. в 19:09

      появились вот такие значения: ЃЬ†0 bѓбJЏ V €p

      Удалить
    11. mickey.1cx14 мая 2015 г. в 20:45

      Да, погорячился я с cast'ом ) В sql в таблице перечислений хранятся ссылки, все остальные данные хранятся в бинарных данных таблицы config.
      Варианты следующие:
      1. Добавить регистр наименований перечислений и завязываться на него в запросах.
      2. В случае с регистром сведений можно добавить свой строковый реквизит в регистр и через подписку на события, либо в модуле набора прописать его заполнение наименованием перечисления.

      Удалить
  2. Сергей18 мая 2015 г. в 11:03

    Добрый день, подскажите в примере который вы представляете возможно выделить месяцы и годы с помощью встроенных функций? И каким образом это можно сделать?

    ОтветитьУдалить
    Ответы
    1. mickey.1cx18 мая 2015 г. в 12:41

      Используйте следующие функции работы с датами в запросе 1С, в зависимости от необходимого вам результата:

      ВЫБРАТЬ
      ВТ.Период,
      ГОД(ВТ.Период) КАК ГодЧисло,
      МЕСЯЦ(ВТ.Период) КАК МесяцЧисло,
      НАЧАЛОПЕРИОДА(ВТ.Период, ГОД) КАК ГодДата,
      НАЧАЛОПЕРИОДА(ВТ.Период, МЕСЯЦ) КАК МесяцДата
      ИЗ
      ВТ КАК ВТ

      В профайлере можно будет посмотреть результат преобразования.

      Если посмотрите запросы в статье, то увидите, что НачалоПериода(ВыручкаИСебестоимостьПродаж.Период, ДЕНЬ) в 1С
      преобразуется в sql в:
      DATEADD(DAY,CAST(DATEPART(DAY,T1._Period) AS NUMERIC(4)) - 1,DATEADD(MONTH,CAST(DATEPART(MONTH,T1._Period) AS NUMERIC(4)) - 1,DATEADD(YEAR,(CAST(DATEPART(YEAR,T1._Period) AS NUMERIC(4)) - 2000) - 2000,{ts '2000-01-01 00:00:00'}))) as День,

      Удалить
    2. Сергей18 мая 2015 г. в 13:40

      Спасибо, буду разбираиься

      Удалить

Подписаться на: Комментарии к сообщению (Atom)