Генератор представлений (View) в MS SQL по структуре базы данных 1С 8.3

Публикация № 1270915

Администрирование - Администрирование данных 1С - Поиск данных

обработка sql powerbi qlik view t-sql создать представления Tableau

Если вам нужно автоматически генерировать view к вашей базе данных MS SQL по структуре метаданных 1С, то вам необходима данная обработка. Наш "Генератор View" - незаменимый помощник для бизнес-аналитиков, работающих с базами 1С из Power BI/ Qlik Sense/ Tableau и т.д.

Генератор View к таблицам базы данных MS-SQL для любых конфигураций 1С 8.3

Пролог

В нашей компании есть направление бизнес-аналитики. Мы делаем витрины данных, различные интеграции, а также внедряем у клиентов OLAP (MS Analysis Services) и  PowerBI.

Почти в каждом проекте у нас возникает необходимость в получении данных из баз данных 1С.

Мы собираем данные из 1С напрямую, читая данные БД 1С в MS SQL Server. 

Кто-то скажет, что это не очень правильно. Но зато (скажу я)- это очень эффективно. (опять же изучая опыт западных решений - очень многие предоставляют удобный доступ к своей реляционной базе данных, если и не к таблицам, то к View точно [посмотрите хотя бы на MS CRM])

 

Состав решения

В результате наших изысканий, мы разработали весьма удобный конструктор view (представлений) почти ко всем таблицам 1С (на данный момент тестировались конфигурации под 1С 8.3 как внутри компании, так и у клиентов). Обработка умеет генерировать View к:

  1. Справочники и их табличные части
  2. Документы и их табличные части
  3. Планы видов характеристик
  4. Регистры сведений
  5. Регистры накопления
  6. Перечисления (тут особенность :). Мы заполняем view Перечисления всеми данными, которые в них есть, включая наименование, понимающие в этом - оценят)
  7. Константы

Но это только верхушка айсберга

Есть еще опциональные вещи:

  1. Приведение ключевых полей таблиц к типу bigint (как вы знаете, ключевые поля в 1С хранятся в binary(16))
  2. создание view  с директивой  WITH (NOLOCK)
  3. для регистров накопления, помимо view к таблицам с данными, можно создавать еще и view к таблицам оборотов
  4. для регистров накопления, помимо view к таблицам с данными, можно создавать еще и view к таблицам итогов
  5. для регистров накопления, можно создавать дополнительные поля (Движение, Приход, Расход)
  6. из полей с датами, опционально можно вырезать время. Т.е. приводить поля с типом datetime к date
  7. для именования полей и представлений (view) использовать как названия из метаданных, так и "синонимы"
  8. Добавлять префиксы к view в названии )или не добавлять)
  9. Пересоздавать представления (drop... create...)
  10. Восстанавливать выбор ранее созданных view (представлений), чтобы не вспоминать какие из представлений надо переделать
  11. Добавлять строку в представление (view) с кодом 0 (требуется для корректной работы LOOKUPVALUE и PATH в Power BI

Особенной фишкой нашего "Генератора View" является поиск и правильное именование ключевых полей в представлениях, что в 70% случаев позволяет использовать автоматические связи (актуально для Qlik Sense и Power BI, где если ключевое поле справочника и поле документа/регистра совпадают по наименованию, то Qlisk Sense/Power BI создаст связь между этими полями автоматически)

Обработка реализована под MS SQL Server.

PS:

При необходимости, можно обсудить ее доработку и под PostgreSQL (пока таких запросов не было)

 

Требования и ограничения, условия и т.д.

  1. Работает под конфигурациями на 1С 8.3 (тестировалось с 1С 8.3.13.ххх и выше), управляемые формы
  2. Необходимо подключение к БД 1С (клиент-серверный режим с использованием MS SQL Server. Тестировалось не ниже MS SQL Server 2012)
  3. тестировалось и корректно работает на УТ 11.4 и 1С ERP 2.1. Аналогично будет работать и на любых других конфигурациях под 1С 8.3 (1С Розница, 2, КА 2 и т.д.), так как используются общие для 1С 8.3 платформенные механизмы чтения и обработки метаданных
  4. На обычных формах не проверялось, будет потребность - надо обсуждать
  5. Обработка претендует на некоторую универсальность

 

Достоинства

Важным преимуществом обработки является 

  1. значительное ускорение подготовки View к таблицам 1С в MS SQL Server для бизнес-аналитика
  2. удобное создание и пересоздание View
  3. Простой и интуитивно понятный интерфейс

Гарантия возврата денег

ООО "Инфостарт" гарантирует Вам 100% возврат оплаты, если программа не соответствует заявленному функционалу из описания. Деньги можно вернуть в полном объеме, если вы заявите об этом в течение 14-ти дней со дня поступления денег на наш счет.

Программа настолько проверена в работе, что мы с полной уверенностью можем дать такую гарантию. Мы хотим, чтобы все наши покупатели оставались довольны покупкой.

Для возврата оплаты просто свяжитесь с нами.

Поддержка

Специальные предложения

Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. Noy 1067 04.08.20 10:50 Сейчас в теме
Добрый день.
Есть одно замечание и один вопрос...

Приведение ключевых полей таблиц к типу bigint (как вы знаете, ключевые поля в 1С хранятся в binary(16))

binary(16) нельзя привести к bigint (8 байт) без потери данных.
После такого приведения возможно появление дублей ключевых полей в созданной вьюшке.
Также невозможно провести обратное преобразование в ссылку. Хотя вам такое преобразование видимо не нужно.

Перечисления (тут особенность :). Мы заполняем view Перечисления всеми данными, которые в них есть, включая наименование, понимающие в этом - оценят)


В случае добавления нового значения перечисления в режиме конфигуратора - оно отобразится в представлении? С именем?
3. Техподдержка 04.08.20 12:15
(1)
binary(16) нельзя привести к bigint (8 байт) без потери данных.
После такого приведения возможно появление дублей ключевых полей в созданной вьюшке.
Также невозможно провести обратное преобразование в ссылку. Хотя вам такое преобразование видимо не нужно


Еще дополнение.
Технически можно доработать обработку так, что она фоновым заданием будет автоматически пересоздавать/обновлять все необходимые вам View вообще без участия пользователя
И таким образом получить слепок конфигурации в виде view, т.е. в "человекочитаемом" формате
7. Техподдержка 21.01.21 10:54
(1)
В случае добавления нового значения перечисления в режиме конфигуратора - оно отобразится в представлении? С именем?


Для этого достаточно пересоздать View из обработки
2. Техподдержка 04.08.20 12:12
В случае с работой с витриной данных - обратное приведение к binary(16) действительно не требуется на практике
А насчет дублей ключевых полей при приведении в bigint готов поспорить, так как при приведении binary в int - мы дейсвтительно в своей практике получали дубли ключевых полей, а вот при приведении к bigint - нет с таким ни разу не сталкивались


Что касается перечислений, то по "метаданным" в режиме 1С предприятия опрашиваются все Перечисления, и если у вас при запуске обработки выявлено Новое "Перечисление", то оно появится в списке и по нему можно будет сделать View

Т.е. конфигурация с новым перечислением должна быть применена и существовать в БД 1С (пример Перечисления ниже)
Прикрепленные файлы:
4. Loklir 11.12.20 14:21 Сейчас в теме
По поводу ссылки и bigint, не совсем верно если необходимо однозначно определять тип поля лучше binary(16) преобразовывать в char(36) - строку УИД - xmlстрока(ссылка). Тогда возможнокак прямое так и обратное преобразование.
Тексты функций

Прямое
CREATE function dbo.getStringUUID (*binaryUUID binary(16))
returns char(40)
as
begin
declare *buffer varchar(40)
select *buffer = replace(convert(varchar(40),cast(*binaryUUID as uniqueidentifier)), '-', '')

return LOWER(right(*buffer, 8) + '-' +
substring(*buffer, 21, 4) + '-' +
substring(*buffer, 17, 4) + '-' +
substring(*buffer, 7, 2) +
substring(*buffer, 5, 2) + '-' +

substring(*buffer, 3, 2) +
substring(*buffer, 1, 2) +
substring(*buffer, 11, 2) +
substring(*buffer, 9, 2) +
substring(*buffer, 15, 2) +
substring(*buffer, 13, 2))

end
GO

Обратное

CREATE FUNCTION dbo.GeIDrefFromUID (*UID char(36))
RETURNS binary(16)
AS
BEGIN

RETURN convert(BINARY(16),'0x'+UPPER(SUBSTRING(*uid,20,4)+SUBSTRING(*uid,25,12)+SUBSTRING(*uid,15,4)+SUBSTRING(*uid,10,4)+LEFT(*uid,8)),1)

END

GO
5. mserg27 14.01.21 10:20 Сейчас в теме
(4) Если добавить вызов этих функций в view, то получите многократное падение скорости выборки
6. Техподдержка 21.01.21 10:53
(4) ничто не мешает сохранять в View одновременно и сам ID (в binary) и сконвертированный ключ в bigint
Деградации производительности не будет

Что важно: в Power BI приводите все ключи к типу значения Text
9. Noy 1067 21.01.21 13:40 Сейчас в теме
(4) mserg27 прав. Такие функции действительно замедлят выборку.

Можно чуть оптимальнее:
CONVERT(varchar(32), Номенклатура._IDRRef,2)

Это будет не чистый 1С УникальныйИдентификатор() а то что мы видим при ЗначениеВстрокуВнутр().
Но это быстро преобразовывается обратно в оригинальные бинарные ссылки.
Да и в УникальныйИдентификатор преобразовать легко обычной перестановкой.
10. Техподдержка 21.01.21 17:02
(9)
Это будет не чистый 1С УникальныйИдентификатор() а то что мы видим при ЗначениеВстрокуВнутр()


Согласен - так сделать можно и это вполне допустимо.
Но если Вам нужно будет хранить данные в таблицах БД, то varchar занимает больше места, нежели bigint
И на больших объемах (десятки миллионов строк) - это будет вести к большому расходу дискового пространства

PS:
Тут больше вопрос в том - в каких случаях может понадобиться обратная ковертация в binary из того-же Power BI ?
11. Noy 1067 21.01.21 21:41 Сейчас в теме
(10)
Недопустимо делать так, как сделали вы. При этом вам уже два разных участника об этом написали.

Я объясню на примере:

select cast(0x00000000000000000000000000000000 as bigint) --пустая ссылка, она же 0 в bigint
UNION ALL
select cast(0x12345678123456780000000000000000 as bigint) --еще одна не пустая ссылка, но внезапно опять 0 в bigint
UNION ALL

-- а теперь две непустые ссылки, которые явно не равны, но в bigint полностью идентичны
select cast(0x87654321876543211234567812345678 as bigint) 
UNION ALL
select cast(0x12345678123456781234567812345678 as bigint)

Показать

выполните это код и увидите ответ:
0
0
1311768465173141112
1311768465173141112



То есть при преобразовании первичных ключей вы отрезаете ровно половину данных - это хорошо видно на примере выше. Рано или поздно решения, построенные на данном коде, либо вызовут исключение с ошибкой на дублирование первичных ключей, либо в данных будут искажения, если первичные ключи не контролируются.

То что вы не натолкнулись на подобную ситуацию это всего лишь одно из:
-счастливая случайность
-малое количество выполненных проектов
-нестандартный алгоритм формирования УИД в движке 1С (при условии неиспользования в конфигурации метода создания ссылок из произвольного GUID-а). И то - это всего лишь предположение.
12. Техподдержка 22.01.21 09:53
(11)
То есть при преобразовании первичных ключей вы отрезаете ровно половину данных - это хорошо видно на примере выше. Рано или поздно решения, построенные на данном коде, либо вызовут исключение с ошибкой на дублирование первичных ключей, либо в данных будут искажения, если первичные ключи не контролируются.


Спасибо
обдумаю, что можно с этим сделать

С bigint действительно пока ни разу (я лично) не наталкивался на дублирование данных изза потерь при конвертации, хотя базы большие есть - как источники данных для DWH
Возможно - это связано именно с форматом генерации Уникальных идентификаторов в binary(16) в 1С


(11)
-нестандартный алгоритм формирования УИД в движке 1С (при условии неиспользования в конфигурации метода создания ссылок из произвольного GUID-а)

Думается - это не позволяет нам поймать ваш вариант "неуникальности"
8. Техподдержка 21.01.21 10:57
В ближайшее время, добавим еще "Задачи" для генерации view
Оставьте свое сообщение