Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
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] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9646 |
Почему не хотите сделать справочник групп ролей? С таким справочником задача получения кода группы ролей решается тривиальным образом через 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] Ответить | Цитировать Сообщить модератору |
aleksrov Member Откуда: Сообщений: 948 |
invm, Ну... Потому что я до такого не додумался, спасибо! :) Но это не то все ровно, по этому справочнику в PowerBI потом выборка будет идти, поэтому user не должен видеть aaa, bbb aaa, а только aaa, bbb. Сколько групп имеют вхождение этих ролей не суть. Поэтому есть справочник просто ролей, для каждой роли одна строка, по этому справочнику и будет идти фильтрация, и есть bridge, который как раз обрабатывает многие ко многим логику. Вариант с хэшом сейчас попробывал, работает быстро довольно. По сути это и будет справочник групп ролей, только с хэшом. Хранить именно так кстати не вариант, бывают строки до 5000, с 40 вариантами, хэш будет лучше для группы, чем хранить саму группу. |
6 сен 18, 11:32 [21666692] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9646 |
Справочник групп ролей - сугубо служебный, для получения значения GroupRoleKey. К справочнику ролей никак не относится. |
||
6 сен 18, 11:49 [21666717] Ответить | Цитировать Сообщить модератору |
aleksrov Member Откуда: Сообщений: 948 |
invm, Он так дублит строки или я что-то не так делаю. Если в источнике 100 строк с группой 'ааа' и такой группы еще нет он сто раз вставит. |
6 сен 18, 13:25 [21666877] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9646 |
А что мешает сначала отобрать уникальные группы из источника? |
||
6 сен 18, 13:34 [21666902] Ответить | Цитировать Сообщить модератору |
aleksrov Member Откуда: Сообщений: 948 |
invm, Согласен, можно. Я убрал ограничение, т.к. как иначе связать группы со справочником. И группа и код должны повторятся, вроде группа 1, код роли 1, 'aaa, bbb' группа 1, код роли 2, 'aaa, bbb' ну и т.д. Я просто не могу понять ваш вариант до конца, если честно. Сам то запрос понятен, как в общую картину его засунуть. |
6 сен 18, 13:50 [21666931] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9646 |
Если да, то зачем убрали ограничение? Кстати, без ПК (или индекса) по roles, serializable приведет к блокированию таблицы и выстраиванию всех таких запросов в очередь. Если нет, то зачем вообще искать группу по хешу и т.п.? |
||||
6 сен 18, 14:04 [21666965] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
aleksrov Member Откуда: Сообщений: 948 |
Как это сделать если групп будет одной строкой? |
6 сен 18, 14:13 [21666976] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9646 |
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] Ответить | Цитировать Сообщить модератору |
aleksrov Member Откуда: Сообщений: 948 |
invm, Т.е. вы предлагаете Bridge в SSAS сделать как представление подобного вида? |
6 сен 18, 14:30 [21667009] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9646 |
aleksrov, Да, примерно так. |
6 сен 18, 15:02 [21667058] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
aleksrov Member Откуда: Сообщений: 948 |
invm, В общем огромное спасибо, сделаю так! Еще маленький вопрос, как такое будет работать при больших строках, как я говорил в других 2 Bridge есть группы где 5000 символов, также строка через запятую. Т.е. в справочнике 100 значений где-то, в каких то группах бывает 80 иди 90 значений, в общем строка большая. Но сейчас посмотрел, групп немного, в районе 1000. |
6 сен 18, 15:19 [21667088] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9646 |
aleksrov, Справочник на 1000 строк - мизер. Думаю, что проблем не будет. К тому же, в вашем первоначальном варианте с хешированием все равно пришлось бы хранить и значение, по которому был получен хеш. |
6 сен 18, 16:01 [21667162] Ответить | Цитировать Сообщить модератору |
aleksrov Member Откуда: Сообщений: 948 |
invm, Просто изначально я думал именно саму Bridge хранить как то что выдает представление, т.е. оригинальная строка нигде не хранилась бы по сути. Хотел так сделать как раз из-за больших групп в другом Bridge, но тогда ETL совсем геморным получается, также как и обновление существующих строк, ваш вариант конечно в разы проще. Еще раз спасибо большое за помощь! |
6 сен 18, 16:17 [21667190] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |