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. Добрый день,
    Подскажите возможно работать одновременно с двумя базами SQL ?

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

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

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

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

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

      Удалить
    6. имею след. запрос:
      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. Запрос 1С тоже выложите

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

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

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

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

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

    ОтветитьУдалить
    Ответы
    1. Используйте следующие функции работы с датами в запросе 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. Спасибо, буду разбираиься

      Удалить