Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Проверка наличия группы в BridgeTable с последующей вставкой.  [new]
aleksrov
Member

Откуда:
Сообщений: 948
В общем есть таблица сотрудников (у меня таких таблицы 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.
ХЗ насколько это правильно и быстро, но вчера весь день просидел и ничего более не придумал, если у сообщества есть идеи буду крайне признателен.
6 сен 18, 09:26    [21666563]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия группы в BridgeTable с последующей вставкой.  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
Почему не хотите сделать справочник групп ролей?
С таким справочником задача получения кода группы ролей решается тривиальным образом через merge с serializable. AppLock не потребуется.
Примерно так:
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
6 сен 18, 11:18    [21666675]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия группы в BridgeTable с последующей вставкой.  [new]
aleksrov
Member

Откуда:
Сообщений: 948
invm,
Ну... Потому что я до такого не додумался, спасибо! :)
Но это не то все ровно, по этому справочнику в PowerBI потом выборка будет идти, поэтому user не должен видеть
aaa, bbb
aaa,
а только aaa, bbb. Сколько групп имеют вхождение этих ролей не суть.
Поэтому есть справочник просто ролей, для каждой роли одна строка, по этому справочнику и будет идти фильтрация, и есть bridge, который как раз обрабатывает многие ко многим логику.
Вариант с хэшом сейчас попробывал, работает быстро довольно. По сути это и будет справочник групп ролей, только с хэшом. Хранить именно так кстати не вариант, бывают строки до 5000, с 40 вариантами, хэш будет лучше для группы, чем хранить саму группу.
6 сен 18, 11:32    [21666692]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия группы в BridgeTable с последующей вставкой.  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
aleksrov
Но это не то все ровно, по этому справочнику в PowerBI потом выборка будет идти, поэтому user не должен видеть
aaa, bbb
aaa,
а только aaa, bbb. Сколько групп имеют вхождение этих ролей не суть.
Поэтому есть справочник просто ролей, для каждой роли одна строка, по этому справочнику и будет идти фильтрация, и есть bridge, который как раз обрабатывает многие ко многим логику.
Не вижу противоречий.
Справочник групп ролей - сугубо служебный, для получения значения GroupRoleKey. К справочнику ролей никак не относится.
6 сен 18, 11:49    [21666717]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия группы в BridgeTable с последующей вставкой.  [new]
aleksrov
Member

Откуда:
Сообщений: 948
invm,

Он так дублит строки или я что-то не так делаю.
Если в источнике 100 строк с группой 'ааа' и такой группы еще нет он сто раз вставит.
6 сен 18, 13:25    [21666877]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия группы в BridgeTable с последующей вставкой.  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
aleksrov
Если в источнике 100 строк с группой 'ааа' и такой группы еще нет он сто раз вставит.
В моем примере такое невозможно. Ибо есть соответствующее ограничение на справочнике.
А что мешает сначала отобрать уникальные группы из источника?
6 сен 18, 13:34    [21666902]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия группы в BridgeTable с последующей вставкой.  [new]
aleksrov
Member

Откуда:
Сообщений: 948
invm,

Согласен, можно.
Я убрал ограничение, т.к. как иначе связать группы со справочником. И группа и код должны повторятся, вроде
группа 1, код роли 1, 'aaa, bbb'
группа 1, код роли 2, 'aaa, bbb'
ну и т.д.
Я просто не могу понять ваш вариант до конца, если честно.
Сам то запрос понятен, как в общую картину его засунуть.
6 сен 18, 13:50    [21666931]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия группы в BridgeTable с последующей вставкой.  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
aleksrov
И группа и код должны повторятся, вроде
группа 1, код роли 1, 'aaa, bbb'
группа 1, код роли 2, 'aaa, bbb'
ну и т.д.
aleksrov
Он так дублит строки или я что-то не так делаю.
Если в источнике 100 строк с группой 'ааа' и такой группы еще нет он сто раз вставит.
Так группа ролей уникальна или нет?
Если да, то зачем убрали ограничение? Кстати, без ПК (или индекса) по roles, serializable приведет к блокированию таблицы и выстраиванию всех таких запросов в очередь.
Если нет, то зачем вообще искать группу по хешу и т.п.?
6 сен 18, 14:04    [21666965]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия группы в BridgeTable с последующей вставкой.  [new]
aleksrov
Member

Откуда:
Сообщений: 948
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 групп.
6 сен 18, 14:11    [21666971]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия группы в BridgeTable с последующей вставкой.  [new]
aleksrov
Member

Откуда:
Сообщений: 948
Как это сделать если групп будет одной строкой?
6 сен 18, 14:13    [21666976]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия группы в BridgeTable с последующей вставкой.  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
aleksrov
и как SQL поймет что роль с ключом 1, есть в группе 'aaaa,bbbb' и 'ccc, aaaa'?
По таблице BridgeRoleGroup
aleksrov
Теперь когда пользователь выберет роль admin ему покажутся все члены 1 и 4 групп.
Примерно так
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';
6 сен 18, 14:26    [21666998]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия группы в BridgeTable с последующей вставкой.  [new]
aleksrov
Member

Откуда:
Сообщений: 948
invm,

Т.е. вы предлагаете Bridge в SSAS сделать как представление подобного вида?
6 сен 18, 14:30    [21667009]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия группы в BridgeTable с последующей вставкой.  [new]
aleksrov
Member

Откуда:
Сообщений: 948
Если я правильно вас понял, у нас физически Bridge будет одна строка для группы, два столбца - id, группа.
Получаем так:
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 вроде

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 
	) 
6 сен 18, 14:45    [21667032]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия группы в BridgeTable с последующей вставкой.  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
aleksrov,

Да, примерно так.
6 сен 18, 15:02    [21667058]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия группы в BridgeTable с последующей вставкой.  [new]
aleksrov
Member

Откуда:
Сообщений: 948
invm,

Алилуя, дошло. В таком случае ваш вариант конечно проще.
Первым merge я проверяю наличие новых групп, если есть то вставляем
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 я уже проверяю что делать с сотрудниками в базе, обновлять или вставлять
 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 пакета получится не такая сложная.
6 сен 18, 15:13    [21667078]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия группы в BridgeTable с последующей вставкой.  [new]
aleksrov
Member

Откуда:
Сообщений: 948
invm,

В общем огромное спасибо, сделаю так!
Еще маленький вопрос, как такое будет работать при больших строках, как я говорил в других 2 Bridge есть группы где 5000 символов, также строка через запятую. Т.е. в справочнике 100 значений где-то, в каких то группах бывает 80 иди 90 значений, в общем строка большая. Но сейчас посмотрел, групп немного, в районе 1000.
6 сен 18, 15:19    [21667088]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия группы в BridgeTable с последующей вставкой.  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
aleksrov,

Справочник на 1000 строк - мизер. Думаю, что проблем не будет.
К тому же, в вашем первоначальном варианте с хешированием все равно пришлось бы хранить и значение, по которому был получен хеш.
6 сен 18, 16:01    [21667162]     Ответить | Цитировать Сообщить модератору
 Re: Проверка наличия группы в BridgeTable с последующей вставкой.  [new]
aleksrov
Member

Откуда:
Сообщений: 948
invm,

Просто изначально я думал именно саму Bridge хранить как то что выдает представление, т.е. оригинальная строка нигде не хранилась бы по сути. Хотел так сделать как раз из-за больших групп в другом Bridge, но тогда ETL совсем геморным получается, также как и обновление существующих строк, ваш вариант конечно в разы проще.
Еще раз спасибо большое за помощь!
6 сен 18, 16:17    [21667190]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить