ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Проверка наличия группы в BridgeTable с последующей вставкой.
18 сообщений из 18, страница 1 из 1
Проверка наличия группы в BridgeTable с последующей вставкой.
    #39698846
aleksrov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleksrov
Участник
В общем есть таблица сотрудников (у меня таких таблицы 3 на самом деле, где есть Bridge), каждый сотрудник может иметь несколько ролей, в источнике это выглядит как
Staff table - id, name, roles (здесь строка с перечислением ролей), etc. Справочника ролей нет, это константа в коде.
В хранилище, таблица DimStaff - Key, Id, SourceId, Name, GroupRoleKey, etc
Таблица BridgeRoleGroup - GroupRoleKey, RoleKey
Таблица DimRoles - RoleKey, RoleName. К примеру Admin - 1, Manager - 2, Head - 3
Как это выглядит, прилетели сотрудники id - 1, Name - John, Roles - Admin, Manager, Head, id - 2, Name - Lacky, Roles - Admin, id - 3, Name - Michael, Roles -Admin, Head. Во время ETL мы смотрим есть ли в BridgeRoleGroup группа с сочетанием этих полей, к примеру в BridgeRoleGroup для этих троих должно быть так: для первого должно быть 3 строки BridgeRoleGroup - 1, 1, 1; RoleKey - 1,2,3, для второго 1, BridgeRoleGroup - 2; RoleKey - 1 ну и т.д.
Проблема в том что я не знаю как реализовать это в ETL, т.е. как сделать проверку на то что у нас нет группы Bridge у которых RoleKey как у данного сотрудница и BridgeRoleGroup один и тот же, если есть, тащим Id'шник, если нет, создаем новую группу и тащим id'шник.
Единственное что пока придумал в BridgeRoleGroup добавить поле Hash, во время ETL для каждого сотрудника через hashbytes (с MD5), вычисляем hash и проверяем есть ли этот хэш в BridgeRoleGroup, если нет создаем новую группу с соответствующим хэшом. Роли разбираем через string_split, а id новый будет выдавать хранимка, к ней будет одновременно обращаться много потоков, а id'шники должны быть уникальны, буду использовать AppLock.
Много групп создадутся при Initial Load, а потом новые группы будут крайне редко появляться, по всем 3 таблицам где есть бриджи, 95% попадают в 5% возможных групп, поэтому большинство будут делать проверку по хэшу и вытаскивать нужны id.
ХЗ насколько это правильно и быстро, но вчера весь день просидел и ничего более не придумал, если у сообщества есть идеи буду крайне признателен.
...
Рейтинг: 0 / 0
06.09.2018, 09:26
| Ответить | Цитировать | Написать  
Проверка наличия группы в BridgeTable с последующей вставкой.
    #39698899
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Почему не хотите сделать справочник групп ролей?
С таким справочником задача получения кода группы ролей решается тривиальным образом через merge с serializable. AppLock не потребуется.
Примерно так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
use tempdb;
go

create table dbo.RoleGroups (id int identity unique, roles varchar(1000) primary key);
insert into dbo.RoleGroups values ('aaa, bbb');

declare @dummy int;
declare @result table (id int, roles varchar(1000));

with Request as
(
 select
  t.roles
 from
  (values ('aaa, bbb'), ('bbb')) t(roles)
)
merge into dbo.RoleGroups with (serializable) t
using Request s on s.roles = t.roles
when not matched then
 insert (roles) values (s.roles)
when matched then
 update
  set
   @dummy = 1
output
 inserted.id, inserted.roles into @result (id, roles);

select * from @result;
go

drop table dbo.RoleGroups;
go
...
Рейтинг: 0 / 0
06.09.2018, 11:18
| Ответить | Цитировать | Написать  
Проверка наличия группы в BridgeTable с последующей вставкой.
    #39698905
aleksrov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleksrov
Участник
invm,
Ну... Потому что я до такого не додумался, спасибо! :)
Но это не то все ровно, по этому справочнику в PowerBI потом выборка будет идти, поэтому user не должен видеть
aaa, bbb
aaa,
а только aaa, bbb. Сколько групп имеют вхождение этих ролей не суть.
Поэтому есть справочник просто ролей, для каждой роли одна строка, по этому справочнику и будет идти фильтрация, и есть bridge, который как раз обрабатывает многие ко многим логику.
Вариант с хэшом сейчас попробывал, работает быстро довольно. По сути это и будет справочник групп ролей, только с хэшом. Хранить именно так кстати не вариант, бывают строки до 5000, с 40 вариантами, хэш будет лучше для группы, чем хранить саму группу.
...
Рейтинг: 0 / 0
06.09.2018, 11:32
| Ответить | Цитировать | Написать  
Проверка наличия группы в BridgeTable с последующей вставкой.
    #39698913
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
aleksrovНо это не то все ровно, по этому справочнику в PowerBI потом выборка будет идти, поэтому user не должен видеть
aaa, bbb
aaa,
а только aaa, bbb. Сколько групп имеют вхождение этих ролей не суть.
Поэтому есть справочник просто ролей, для каждой роли одна строка, по этому справочнику и будет идти фильтрация, и есть bridge, который как раз обрабатывает многие ко многим логику.Не вижу противоречий.
Справочник групп ролей - сугубо служебный, для получения значения GroupRoleKey. К справочнику ролей никак не относится.
...
Рейтинг: 0 / 0
06.09.2018, 11:49
| Ответить | Цитировать | Написать  
Проверка наличия группы в BridgeTable с последующей вставкой.
    #39698992
aleksrov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleksrov
Участник
invm,

Он так дублит строки или я что-то не так делаю.
Если в источнике 100 строк с группой 'ааа' и такой группы еще нет он сто раз вставит.
...
Рейтинг: 0 / 0
06.09.2018, 13:25
| Ответить | Цитировать | Написать  
Проверка наличия группы в BridgeTable с последующей вставкой.
    #39699003
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
aleksrovЕсли в источнике 100 строк с группой 'ааа' и такой группы еще нет он сто раз вставит.В моем примере такое невозможно. Ибо есть соответствующее ограничение на справочнике.
А что мешает сначала отобрать уникальные группы из источника?
...
Рейтинг: 0 / 0
06.09.2018, 13:34
| Ответить | Цитировать | Написать  
Проверка наличия группы в BridgeTable с последующей вставкой.
    #39699019
aleksrov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleksrov
Участник
invm,

Согласен, можно.
Я убрал ограничение, т.к. как иначе связать группы со справочником. И группа и код должны повторятся, вроде
группа 1, код роли 1, 'aaa, bbb'
группа 1, код роли 2, 'aaa, bbb'
ну и т.д.
Я просто не могу понять ваш вариант до конца, если честно.
Сам то запрос понятен, как в общую картину его засунуть.
...
Рейтинг: 0 / 0
06.09.2018, 13:50
| Ответить | Цитировать | Написать  
Проверка наличия группы в BridgeTable с последующей вставкой.
    #39699042
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
aleksrovИ группа и код должны повторятся, вроде
группа 1, код роли 1, 'aaa, bbb'
группа 1, код роли 2, 'aaa, bbb'
ну и т.д.aleksrovОн так дублит строки или я что-то не так делаю.
Если в источнике 100 строк с группой 'ааа' и такой группы еще нет он сто раз вставит.Так группа ролей уникальна или нет?
Если да, то зачем убрали ограничение? Кстати, без ПК (или индекса) по roles, serializable приведет к блокированию таблицы и выстраиванию всех таких запросов в очередь.
Если нет, то зачем вообще искать группу по хешу и т.п.?
...
Рейтинг: 0 / 0
06.09.2018, 14:04
| Ответить | Цитировать | Написать  
Проверка наличия группы в BridgeTable с последующей вставкой.
    #39699046
aleksrov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleksrov
Участник
invm,

Группа уникальна, я про то как потом мне это в модель засунуть в таком виде? Как эти группы будут фильтроваться по справочнику ролей? Пользователь выбрал Admin в таблице справочнике, и как SQL поймет что роль с ключом 1, есть в группе 'aaaa,bbbb' и 'ccc, aaaa'?
id roles value RoleKey
1 {admin,call_center} admin 2
1 {admin,call_center} call_center 3
2 {phone_verificator,call_center} phone_verificator 1
2 {phone_verificator,call_center} call_center 3
3 {phone_verificator} phone_verificator 1
4 {accountant,admin} accountant 9
4 {accountant,admin} admin 2
5 {phone_verificator,call_center,admin} phone_verificator 1
5 {phone_verificator,call_center,admin} call_center 3
Я думал это так будет выглядеть. Теперь когда пользователь выберет роль admin ему покажутся все члены 1 и 4 групп.
...
Рейтинг: 0 / 0
06.09.2018, 14:11
| Ответить | Цитировать | Написать  
Проверка наличия группы в BridgeTable с последующей вставкой.
    #39699050
aleksrov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleksrov
Участник
Как это сделать если групп будет одной строкой?
...
Рейтинг: 0 / 0
06.09.2018, 14:13
| Ответить | Цитировать | Написать  
Проверка наличия группы в BridgeTable с последующей вставкой.
    #39699065
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
aleksrovи как SQL поймет что роль с ключом 1, есть в группе 'aaaa,bbbb' и 'ccc, aaaa'?По таблице BridgeRoleGroup
aleksrovТеперь когда пользователь выберет роль admin ему покажутся все члены 1 и 4 групп.Примерно так
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select distinct
 rm.*
from
 Roles r join
 RoleGroups rgr on rgr.role_id = r.role_id join
 RoleGroups rgg on rgg.group_id = rgr.group_id join
 RoleMembers rm on rm.role_id = rgg.role_id 
where
 r.role_name = 'admin';
...
Рейтинг: 0 / 0
06.09.2018, 14:26
| Ответить | Цитировать | Написать  
Проверка наличия группы в BridgeTable с последующей вставкой.
    #39699071
aleksrov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleksrov
Участник
invm,

Т.е. вы предлагаете Bridge в SSAS сделать как представление подобного вида?
...
Рейтинг: 0 / 0
06.09.2018, 14:30
| Ответить | Цитировать | Написать  
Проверка наличия группы в BridgeTable с последующей вставкой.
    #39699081
aleksrov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleksrov
Участник
Если я правильно вас понял, у нас физически Bridge будет одна строка для группы, два столбца - id, группа.
Получаем так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
merge into [Staging].[dbo].[BridgeStaffRoles] with (serializable) as  t
using (SELECT Roles as RoleGroup
         from [Staging].[dbo].[DimStaff_Stage]
		 group by Roles) as s
on Roles = s.RoleGroup
when not matched then
 insert (Roles) values (s.RoleGroup)
 when matched then
 update
  set
   @dummy = 1
output inserted.RoleGroupKey, inserted.Roles into @result (id, Roles);



А в SSAS bridge строить на основе View вроде

Код: sql
1.
2.
3.
4.
5.
6.
select r.id as RoleGroupKey, rol.RoleKey from @result as r
cross apply 
	(string_split (replace(replace(roles,'{',''),'}',''),',') 
	join [Staging].[dbo].[DimStaffRoles] as rol
	on value = rol.rolenameinsource 
	)
...
Рейтинг: 0 / 0
06.09.2018, 14:45
| Ответить | Цитировать | Написать  
Проверка наличия группы в BridgeTable с последующей вставкой.
    #39699095
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
aleksrov,

Да, примерно так.
...
Рейтинг: 0 / 0
06.09.2018, 15:02
| Ответить | Цитировать | Написать  
Проверка наличия группы в BridgeTable с последующей вставкой.
    #39699103
aleksrov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleksrov
Участник
invm,

Алилуя, дошло. В таком случае ваш вариант конечно проще.
Первым merge я проверяю наличие новых групп, если есть то вставляем
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
merge into [DWH].[dbo].[BridgeStaffRoles] with (serializable) as  t
using (SELECT Roles as RoleGroup
         from [Staging].[dbo].[DimStaff_Stage]
		 group by Roles) as s
on Roles = s.RoleGroup
when not matched then
 insert (Roles) values (s.RoleGroup);
 go


Базы Staging и DWH на одном серваке.
Вторым Merge я уже проверяю что делать с сотрудниками в базе, обновлять или вставлять
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
 merge into [DWH].[dbo].[DimStaff] as t
 using (SELECT
       [Country]
      ,[StaffID]
      ,[FullName]
      ,rl.[RoleGroupKey]
--etc
  FROM [Staging].[dbo].[DimStaff_Stage] as st
  join [DWH].[dbo].[BridgeStaffRoles] as rl
  on st.roles = rl.roles) as s
  on t.country = s.country and t.staffid = s.staffid
when not matched then
insert ([Country]
      ,[StaffID]
      ,[FullName]
      ,[RoleGroupKey]
--etc
)
	  values 
	  (
	   s.[Country]
      ,s.[StaffID]
      ,s.[FullName]
       ,s.[RoleGroupKey]
--etc
--Далее если такая строка есть то обновляем
);



Все это можно засунуть в Task SQL и выполнить сразу после dataflow грузящего staff. В итоге эта ветка ETL пакета получится не такая сложная.
...
Рейтинг: 0 / 0
06.09.2018, 15:13
| Ответить | Цитировать | Написать  
Проверка наличия группы в BridgeTable с последующей вставкой.
    #39699107
aleksrov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleksrov
Участник
invm,

В общем огромное спасибо, сделаю так!
Еще маленький вопрос, как такое будет работать при больших строках, как я говорил в других 2 Bridge есть группы где 5000 символов, также строка через запятую. Т.е. в справочнике 100 значений где-то, в каких то группах бывает 80 иди 90 значений, в общем строка большая. Но сейчас посмотрел, групп немного, в районе 1000.
...
Рейтинг: 0 / 0
06.09.2018, 15:19
| Ответить | Цитировать | Написать  
Проверка наличия группы в BridgeTable с последующей вставкой.
    #39699130
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
aleksrov,

Справочник на 1000 строк - мизер. Думаю, что проблем не будет.
К тому же, в вашем первоначальном варианте с хешированием все равно пришлось бы хранить и значение, по которому был получен хеш.
...
Рейтинг: 0 / 0
06.09.2018, 16:01
| Ответить | Цитировать | Написать  
Проверка наличия группы в BridgeTable с последующей вставкой.
    #39699148
aleksrov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleksrov
Участник
invm,

Просто изначально я думал именно саму Bridge хранить как то что выдает представление, т.е. оригинальная строка нигде не хранилась бы по сути. Хотел так сделать как раз из-за больших групп в другом Bridge, но тогда ETL совсем геморным получается, также как и обновление существующих строк, ваш вариант конечно в разы проще.
Еще раз спасибо большое за помощь!
...
Рейтинг: 0 / 0
06.09.2018, 16:17
| Ответить | Цитировать | Написать  
18 сообщений из 18, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Проверка наличия группы в BridgeTable с последующей вставкой.
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]