Генератор представлений (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 к:

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

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

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

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

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

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

PS:

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

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

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

Достоинства

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

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

Сравнение версий

Версия 2.3.2

Добавлен новый функционал:
1. Сделан вариант конвертации идентификаторов 1С (_IdRef) из Binary в Varchar(32)

2. Добавлена выгрузка "Задачи"
3. Добавлена выгрузка "Бизнес-процессы"
4. Мелкая оптимизация кода

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

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

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

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

–

Обработка

	Наименование	Файл	Версия	Размер
	Генератор представлений (View) в MS SQL по структуре базы данных 1С 8.3 1 мес. 1 мес.		2.3.2		1	35000 руб. Моментальная доставка

Поддержка

	Наименование	Файл	Версия	Размер
	Техническая поддержка решения 1 мес.				0	6500 руб. Моментальная доставка

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

Комментарии

В избранное Подписаться на ответы

Свернуть все

1. Noy 1067 04.08.20 10:50 Сейчас в теме

Добрый день.
Есть одно замечание и один вопрос...

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

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

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

В случае добавления нового значения перечисления в режиме конфигуратора - оно отобразится в представлении? С именем?

3. Техподдержка 04.08.20 12:15

(1)

Еще дополнение.
Технически можно доработать обработку так, что она фоновым заданием будет автоматически пересоздавать/обновлять все необходимые вам 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-а)

Думается - это не позволяет нам поймать ваш вариант "неуникальности"

14. Техподдержка 29.01.21 22:31

(12) Добавили опциональный вариант конвертации bigint в varchar

8. Техподдержка 21.01.21 10:57

В ближайшее время, добавим еще "Задачи" для генерации view

13. Техподдержка 29.01.21 22:31

(8) добавлены Задачи и Бизнес-процессы

Оставьте свое сообщение

E-mail:

Отдел продаж и
служба техподдержки:

Россия: 8-800-5555-256

Москва: 8 (495) 668-08-22

СПб: 8 (812) 309-06-46

(с 08:00 до 21:00 МСК)

support@infostart.ru

Оформить заказ

Заявка на демонстрацию

Задать вопрос

Техподдержка

Заказать звонок

Статистика:

Просмотры 3845

Загрузки 1

Рейтинг 5

Создание 31.07.20 14:10

Обновление 29.01.21 21:56

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

Характеристики:

Рубрики Поиск данных

Кому Системный администратор ,
Программист

Тип файла Внешняя обработка (ert,epf)

Платформа Управляемые формы

Конфигурация 1С:Розница ,
1С:Документооборот ,
1С:ERP Управление предприятием 2 ,
1С:Управление торговлей 11 ,
1С:Управление холдингом ,
1С:Комплексная автоматизация 2.х ,
1С:CRM ПРОФ, КОРП

Операционная система Не имеет значения

Страна Не имеет значения

Отрасль Не имеет значения

Налоги Не имеет значения

Вид учета Не имеет значения

Доступ к файлу Платные (руб)

Код открыт Да

Посмотреть все

См. также

Удаление данных с отбором и построением дерева ссылок в базах 1С 8.1-8.3 (УТ 10.3, БП 2, ЗУП 2.5, КА 1.1, УТ 11, БП 3, УНФ 1.6, КА 2, ЗУП 3 и т.д.) Промо

Чистка базы Универсальные обработки Обработка документов Поиск данных Обработка справочников v8 v8::УФ 1cv8.cf Платные (руб)

Данные обработки помогут Вам легко и, главное быстро, выполнить удаление любых данных в Ваших базах 1С на платформах 8.1-8.3. Обработки помогут легко просмотреть связи ссылок в виде дерева, выбрать что удалять, а что нет, используя любые отборы. Это позволит уменьшить объем лишней и не нужной информации в справочниках и документах, планах видов характеристик и др. объектах и облегчит работу с данными пользователям и Вам. Понятное расположение команд и настроек, в сочетании с описанием и справкой, еще упростят процесс. Если что-то будет неясно по работе с обработками – я готов Вам помочь. А так же обновления Вы получаете бесплатно в течение года с момента приобретения данных обработок! Данные обработки входят в ТОП-100 продаж за все время, что является залогом популярности, надежности в работе, и признанием других покупателей. (Обновление от 05.04.2020, версия 3.5)

5990 руб.

22.02.2013 107397 201 131

Оптимизация поиска в списках через Elasticsearch

Поиск данных Производительность и оптимизация (HighLoad) v8 ERP2 БП3.0 УТ11 КА2 Россия Платные (руб)

Расширение для ускорения поиска по спискам справочников или документов.

10000 руб.

04.02.2020 4824 1 0

QR - штрихкодирование документов (+Телеграм-бот) БЕЗ изменения печатных форм, с произв. алгоритмами создания QR -кода и обработки для УТ 11 (все), ERP 2, КА 2, УТ 10.3, УПП 1.3, Розница 2.2, УНФ 1.6, БП 3

Обработка документов Поиск данных Сканер штрих-кода v8 УТ10 Розница УПП1 УНФ ERP2 БП3.0 УТ11 КА2 УУ Платные (руб)

Все знают, что в QR - код можно закодировать большое количество информации, но не все знают что это можно использовать в автоматизации бизнеса, в частности в плане документооборота. Представьте, что есть система, которая НЕ ТРЕБУЕТ изменения конфигурации, НЕ ТРЕБУЕТ изменения ни одной печатной формы для добавления QR-кода, включая внешние, НЕ ХРАНИТ данные штрихкодов и их связь, от чего база не "пухнет", ИМЕЕТ возможность закодировать в QR-коде произвольные данные параметров для последующей обработки полученных данных, УМЕЕТ обработать считанный QR-код как ВЫ захотите - например, ОТКРЫТЬ документ, СОЗДАТЬ документ любого вида по входным параметрам, ОТОБРАТЬ из документа определенные данные и перенести их в другой документ, ОТКРЫТЬ отобранные данные по данным QR-кода в виде таблицы для сверки данных и прочее. А так же ИМЕЕТ удобный интерфейс, ХРАНИТ историю операций в обход базы для каждого пользователя в отдельности и УМЕЕТ работать с 2D - сканерами в режиме клавиатуры и эмуляции USB COM. А так же автоматически распознавать отсканированные печатные формы (картинки или pdf-файлы) и выполнять заданные произвольные алгоритмы! Так же система может работать со своим телеграм ботом! Об это подробнее описано ниже(Обновление от 07.10.2020, версия 2.1-2.3)

9990 руб.

26.08.2018 27705 9 43

Поиск номенклатуры по акцизной марке в РМК конфигурации 1С:Розница 2.2.

Розничная торговля Поиск данных Обработка справочников Сканер штрих-кода Розничная торговля v8::ОУ v8::УФ Розница Розничная и сетевая торговля (FMCG) Россия УУ Платные (руб)

Поиск номенклатуры при оформлении Чека ККМ по акцизной марке (штрих-код товара можно не сканировать). Не редко бывает, что алкогольная продукция одной марки, но разных производителей имеет одинаковый штрих-код. При сканировании штрих-кода таких товаров, продавцы далеко не всегда проверяют правильность выбора именно той товарной позиции, которая поступила по ТТН, что в дальнейшем приводит к пересортице. Предлагаемая обработка получает код ЕГАИС из акцизной марки и на основании данных регистра «Соответствие номенклатуры ЕГАИС» определяет продаваемую номенклатуру. Начиная с версии 2.9 поиск номенклатуры по акцизной марке в РМК осуществляется штатными процедурами типовой конфигурации - использование предлагаемой обработки для баз данных с конфигурациями начиная с 2.2.9.18 нецелесообразно.

1000 руб.

15.08.2017 23946 29 46

SALE! 50%

PowerTools Промо

Универсальные обработки Администрирование данных 1С Поиск данных Распределенная БД (УРИБ, УРБД) Сервисные утилиты Обработка документов Обработка справочников Чистка базы Консоль запросов Прочие инструменты разработчика Структура метаданных v8 v8::УФ 1cv8.cf Россия Платные (руб)

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

~~2000~~ 1000 руб.

14.01.2013 123274 622 0

"Что? Где? Когда?" или журнал изменений с восстановлением состояния реквизитов ссылочных объектов (для платформ выше 8.2.16+, любой конфигурации, управляемые формы)

Архивирование (backup) Журнал регистрации Поиск данных v8 v8::УФ 1cv8.cf Платные (руб)

База данных «сама» меняет данные в документах/справочниках? Тогда данный журнал изменений для Вас! Практически не влияет на скорость записи объектов за счет быстрого алгоритма! Скорость записи на 10-15% выше типового механизма "История изменений"! Позволяет следить за изменениями в любых ссылочных объектах конфигурации, с возможностью архивации, свертки данных в другой базе. А так же, может восстановить состояние реквизитов (значения) до момента изменения или удаления объекта из базы. Ну и конечно – подробная бесплатная справка! Работает на любых платформах выше 8.2.16+ и любых конфигурациях! Версия 1.04 от 29.10.2019

12990 руб.

15.05.2017 28057 6 15

Устранение расхождений между регистрами РАУЗ (регл) и регистрами учета ТМЦ, НЗП в УПП 1.3 и КА 1.1

Чистка базы Поиск данных Закрытие периода Производство готовой продукции (работ, услуг) Закрытие периода Производство готовой продукции (работ, услуг) v8 КА1 УПП1 Россия БУ НУ УУ Платные (руб)

Наведите порядок в остатках номенклатуры и НЗП в УПП Устраните расхождения в остатках номенклатуры Устраните расхождения в остатках НЗП Устраните отрицательные остатки в учете ТМЦ и НЗП

6000 руб.

08.09.2015 40455 6 13

Выборочное удаление помеченных объектов с отображением иерархии ссылок (с возможностью немонопольного удаления, порционного удаления, быстрой очистки регистров сведений для управляемых форм 1С 8.2)

Чистка базы Поиск данных Универсальные обработки v8 v8::УФ 1cv8.cf Россия Платные (руб)

Обработка для выборочного удаления помеченных на удаление объектов с возможностью отображения дерева ссылок на помеченный объект,а также последующего удаления объектов по данным найденным ссылкам в этой иерархии из-за чего первоначальный объект удалить невозможно (так называемый "рекурсивный обход"). Обработка позволяет вручную проставить пометки на удаление, включая записи регистров сведений. Также она позволяет производить т.н. "порционное удаление" объектов, то есть порциями с настраиваемым количеством штук в одной порции и периодом повторных запусков удаления следующих порций. Также в обработке можно устанавливать период для документов, из которого обработка начнет искать помеченные на удаление.

3990 руб.

23.08.2012 47448 24 15

Быстрый поиск дублей с четким/нечетким поиском по любому сочетанию реквизитов/реквизитов таб. частей с отбором и быстрой заменой значений в ЛЮБЫХ базах 8.1-8.3 (УТ 10.3, БП 2, ЗУП 2.5, КА 1.1, УТ 11, БП 3, УНФ 1.6, КА 2, ЗУП 3 и т.д.) Промо

Чистка базы Поиск данных Универсальные обработки Обработка справочников Обработка документов v8 v8::УФ 1cv8.cf Платные (руб)

Данные обработки помогут Вам легко и, главное, быстро (в 5 раз и более быстрее штатной обработки 1С), выполнить поиск дублирующих данных в Ваших базах 1С на платформах 8.1-8.3. Это позволит уменьшить объем лишней информации в справочниках и документах, планах видов характеристик и др., упростит работу с данными пользователям. А так же можно, одним нажатием, узнать в каких ссылочных объектах есть вообще дубли! Понятное расположение команд и настроек, в сочетании с описанием и справкой, еще упростят процесс. Если что-то будет неясно по работе с обработками – я готов Вам помочь. А так же обновления Вы получаете бесплатно в течение года с момента приобретения данных обработок! Данные обработки входят в ТОП-100 продаж за все время, что является залогом популярности, надежности в работе, и признанием других покупателей. (Обновление от 23.08.2019, версия 6.20, 6.30)

5990 руб.

14.05.2012 118558 264 230