Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / OLAP и DWH Новый топик    Ответить
 Как соиденить 3 факта?  [new]
aleksrov
Member

Откуда:
Сообщений: 905
Всем привет!
Все дальше погружаюсь в DWH и тут назрел вопрос по одному проекту...
Есть финансовая организация которая дает кредиты, в системе есть такое понятие как Заявка, это первый факт, у нее есть свои измерение с атрибутами, далее если заявка принята создается Договор, это второй факт, у него тоже есть свои измерения со своими атрибутами (разумеется у них есть общие типа клиент, работник, дата и т.д.), далее идет таблица платежей, это 3 факт, вопрос в том как все это связать?
У контракта обязательно есть связанная с ним заявка, у заявки контракта может не быть (отказали к примеру и до контракта не дошло), платежи отслеживается в источнике по номеру контракта.
Если выносить в Dimensioanl таблицу Договор или Заявка то размерам она будет как факт, хотя в источнике и та и та постоянно обновляются, в особенности не закрытые договора (к примеру у них меняется статус и некоторые другие аттрибуты).
13 июл 18, 13:42    [21568861]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
aleksrov
Member

Откуда:
Сообщений: 905
Добавлю что бизнес интересуют отчеты в разрезе как заявок так и Договоров так и вместе.
Т.е. к примеру сумма поступивших заявок и сумма заключенных договоров (т.е. я подал заявку yа 100 000, ее приняли, но дали только 90, у заявки будет статус acepted и сумма 100, у договора статус active и сумма 90) мне нужно суммировать эту разницу у узнать сколько мы всего не "додали" клиентам, в разрезе даты, страны и т.д.
Также договор тоже может быть не заключен, т.е. заявка acepted, создался договор, но клиент передумал, договор будет аннулирован.
13 июл 18, 13:55    [21568933]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
aleksrov
Member

Откуда:
Сообщений: 905
И еще добавлю, что есть факт отправка СМС, в нем есть тип смс, статус, номер Заявки и даты.
Т.е. 3 факта связаны по номеру заявки (СМС, Заявка, Контракт) и один по номеру контракта (платеж)
13 июл 18, 15:37    [21569301]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
Полковник.
Member

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

Факты вяжутся через измерения. Если хочешь, можешь протащить свой суррогатный ключ заявки до последнего факта, что бы видеть по каким заявкам был платеж.
13 июл 18, 15:43    [21569312]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
aleksrov
Member

Откуда:
Сообщений: 905
Полковник.,

Это понятно что не напрямую, вопрос через какое измерение?
Сделать отдельное измерение заявка, и добавить id заявки ко всем фактам, но тогда это измерение будет оооооооооооч большое, т.к. есть куча заявок которые были отбракованы и клиент вводил данные снова, т.е. кол-во Контрактов это % 5, или такое измерение нормально?
Если сделать так тогда в факте (опустим другие измерения) FactApplication - сур.ключ, id заявки, сумма и прочее., в DimApp ее трибуты, но эти атрибуты скорее к факту относятся, типа id customer или id менеджера, что тогда останется в DimApp?
13 июл 18, 15:55    [21569366]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
Полковник.
Member

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

Не важно, что большое. Результирующий запрос будет быстро отрабатывать. Мусорные заявки по которым не было договоров можно сваливать в отдельную мусорную таблицу фактов, это тоже нормально. Но справочник с id заявками все равно должен быть один.
13 июл 18, 16:15    [21569412]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
aleksrov
Member

Откуда:
Сообщений: 905
Полковник.,

ok, спасибо, попробую.
Тогда еще вопрос, получается здесь будет только id заявки, в этом Dim? Т.к. все атрибуты заявки, типа даты, id customer, id менеджера, статус, причина отказа (если 0 не отказано, больше 1 то это код отказа и отдельная Dim к причинам) и прочее будут в факте?
По поводу "мусорной" таблицы фактов как вариант, я думал что делать с горой заявок где клиент просто что-то неправильно ввел и пытался снова, их можно по коду отказа выбирать, но оставлять тоже нужно, т.к. нужен анализ и по ним в том числе.
13 июл 18, 16:36    [21569471]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
Полковник.
Member

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

Можете не только ID там держать, продублируйте ID связанных справочников, дату, еще, что там у вас. Иногда это выручает на отладке скриптов загрузки, что бы быстро выбрать нужные записи в справочнике. Не жалейте место на диске, дублируйте данные, если нужно. В модели данных в фактах, если вы выбираете по клиентам, то клиент должен быть отдельным справочником. Или вот есть города в них клиенты, что бы быстро выбрать факты по городам, нужно в таблицу фактов в которой есть только клиент добавить город, вытащив его из связки город-клиент, а клиента от города отвязать или оставить но тогда будет два города, один как справочник. другой сидеть в иерархии справочника клиент.
13 июл 18, 17:49    [21569659]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
aleksrov
Member

Откуда:
Сообщений: 905
Полковник.,

Подниму тему, решил сделать как тут обсуждали. Есть факт Заявка, там id ключей разных Dim (клиент, тип, geography, соц статус клиента, в какой области он работает (хоть эти данные по идее относятся к клиенту, все они хранятся именно в заявках) и прочее), также есть Dim Заявка, туда вынес разные флаги (получилось 7 полей, id заявки в одном источнике, id в другом (заявки идут с двух разных), страна, и флаги), в факте оставил также сумму, срок, даты. Создал вторую таблицу 1-к-1 с фактом куда вынес всякий текстовый хлам, ip, комменты, адрес (это простой текст), имя компании и прочее, по этой таблице куба не будет, она нужна только для выгрузок инфы в другие системы. Тоже самое сделал с контрактоми, отдельная DimContract и FactContract, в диме разные флаги, в факте даты и суммы. DimЗаявка также соеденил с фактом звонки, смс, Dimконтракт с фактом платежами и изменение статусов (это еще факт, когда клиент вносит деньги в первый раз там появляется строка активен и дата, когда у него просрочка, там появляется первая дата, т.е. следующий день после планового платежа со статусом просрочен, также он может продлевать, т.е. не вносить всю сумму, а только часть, тогда дата и статус продлен), последний факт очень важен бизнесу для анализа сколько контрактов в разных статусах на данный момент, среднее время просрочки, самое большее, кол-во продлений и прочее. Разумеется у все этих фактов есть свои уникальные Dim'ы также.
Такая схема удолетворяет все потребности бизнеса, у меня только два вопроса, как быстро это будет работать в SSAS?
И как лучше поступить в DimЗаявка и Контракт, имеется ввиду ETL. заявка уникально определяется по Idзаявки источник1 + idзаявки источник2 + страна, протаскивать такое по фактам как то не очень, нужен сурогатный ключ, и здесь вопрос, как это будет работать, мы сканим таблицу заявок в источнике (точнее в staging), выбираем эти поля, назначаем им сур. ключи и потом грузим факт и еще раз сканим таблицу заявок, с моей точки зрения как DBA это преступление или в данном случае это норм?
Заранее спасибо за ответы!
9 авг 18, 10:00    [21635447]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
bideveloper
Member

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

У меня сделано так:
Сначала обновляем Dim заявки в DWH через Merge из Stage, новые записи получают новый суррогатный ключ через identity.
Потом грузим факт из Stage в DWH и делаем lookup заявки в SSIS по ключевым полям.
9 авг 18, 13:34    [21635977]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
aleksrov
Member

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

Сколько записей, как быстро отрабатывает?
У меня сейчас тысяч 50 в день будет.
9 авг 18, 13:43    [21636008]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
982183
Member

Откуда: VL
Сообщений: 2383
А мне кажется, это один документ. имеющий поля "номер заявки" и "номер договора"
9 авг 18, 14:36    [21636180]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
Alex_496
Member

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

похоже на розничное кредитование, сделочная модель.
В целом, неплохо продвигаетесь, но огрехи проектирования/потери производительности будут.
Эту тему проходил в 5 разных банках, и каждый раз было что поулучшать.

Измерение "Кредитные заявки" не разделял на отдельные: одобренные/отказанные/черновик/... Это статусы.
Большое измерение да, 60-70 млн. записей шуршит. Создавая натуральные многоуровневые иерархии получается красиво. Иерархии потребуют разрешения проблем с данными на уровне DWH, ETL, хорошо бы lookUp через мастер справочники.

Модель данных можем обсуждать с Вами и вашими заказчиками
9 авг 18, 14:53    [21636223]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
Полковник.
Member

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

Перечитал три раза, ничего не понял. Последовательность всегда такая 1 грузим все в стейджинг, из него сначала формируются измерения, потом на эти измерения цепляются грузятся факты.
Факты по стутусам можно по разному делать, можно держать "статус" как измерение и хранить в фактах построчно, дата будет дата статуса. Можно держать как таблицу остатков - считать статусы на каждую дату.
9 авг 18, 14:54    [21636224]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
aleksrov
Member

Откуда:
Сообщений: 905
Alex_496
aleksrov,

похоже на розничное кредитование, сделочная модель.
В целом, неплохо продвигаетесь, но огрехи проектирования/потери производительности будут.
Эту тему проходил в 5 разных банках, и каждый раз было что поулучшать.

Измерение "Кредитные заявки" не разделял на отдельные: одобренные/отказанные/черновик/... Это статусы.
Большое измерение да, 60-70 млн. записей шуршит. Создавая натуральные многоуровневые иерархии получается красиво. Иерархии потребуют разрешения проблем с данными на уровне DWH, ETL, хорошо бы lookUp через мастер справочники.

Модель данных можем обсуждать с Вами и вашими заказчиками


Мне самому нравится как сейчас получается :)
Мой заказчик это мой работадатель, я думаю он не оценит привлечение аутсорса, т.к. для этого наняли меня. Хоть мой и основной профиль DBA, я сейчас изучаю как сумасшедший все что связано с хранилищем (я не против, именно это я и искал). У моего начальника есть опыт в этой области, но мне как не очень его дергать, мне поставили сроки, когда будет готовый проект, покажу. Ну или когда совсем тупик могу спросить совета. Также скоро будем искать человека именно с опытом, т.к. бизнес растет и одному тут дохрена и больше работы, но спасибо за предложение! :)
Я не стал разделять Факт заявок, для статусов есть отдельная Dim, там два столбца, статус в первой системе и во второй (как я писал заявки идут из двух систем, точнее она перетекает из одной системы в другую), оставил два столбца т.к. это требование бизнеса, смотреть кол-во заявок которые в одном статусе в первой системе и другом во второй. Единственное думаю что еще делать с измерениями вроде семейный статус (я знаю что это относится к клиенту, но в источнике это относится к заявке и фильтровать по этому условию будут именно заявки). Сейчас это отдельная маленькая Dim с одним столбцом и 5 значениями (в принципе как и статусы), они не меняются и вшиты в код, если их хранить как отдельная Dim, то для юзеров это будет не очень удобно, куча разных измерений, если это засунуть в DimЗаявка, с учетом того что это измерение будет большое, считай как факт, то оно разбухнет сильно (ведь это текст), но так проще юзерам + тогда по этому критерию можно будет фильтровать не только заявки но и контракты к примеру. Или как вариант тогда частично перейти на снежинку, что мне кажется более верным.
9 авг 18, 15:30    [21636318]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
aleksrov
Member

Откуда:
Сообщений: 905
Точнее я факт заявок разделил, но по критерию о котором я писал выше, во второй таблице то что не нужно для куба и разный текст.
9 авг 18, 15:32    [21636322]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
Alex_496
Member

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

Рассуждения начальников одинаковы ~ везде. Дальнейшее развитие событий предсказуемо, везде как под копирку. Аутсорс мне не надо.

Подсказка: не плодите количество измерений и тогда и скорострельность куба будет выше и пользователи будут довольны за интересные измерения и другие плюшки. Не зря Кимбал называл Dimension Modeling, он глубокий смысл вкладывал.
Чтобы хорошо спроектировать измерения, нужно хорошо знать бизнес область и особенности данных компании.
Когда вижу кучу измерений с одним или 2-мя атрибутами, то либо наспех/цейтнот делано, либо разработчик ленился
9 авг 18, 16:18    [21636416]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
aleksrov
Member

Откуда:
Сообщений: 905
Alex_496
aleksrov,

Рассуждения начальников одинаковы ~ везде. Дальнейшее развитие событий предсказуемо, везде как под копирку. Аутсорс мне не надо.

Подсказка: не плодите количество измерений и тогда и скорострельность куба будет выше и пользователи будут довольны за интересные измерения и другие плюшки. Не зря Кимбал называл Dimension Modeling, он глубокий смысл вкладывал.
Чтобы хорошо спроектировать измерения, нужно хорошо знать бизнес область и особенности данных компании.
Когда вижу кучу измерений с одним или 2-мя атрибутами, то либо наспех/цейтнот делано, либо разработчик ленился


Я сейчас этим и занимаюсь. Бизнес процессы изучил и данные тоже.
За совет большое спасибо! Мне тоже не очень нравилось такое кол-во измерений, тут я думал над двумя возможностями: первое это сделать что-то вроде junk'а и туда поместить как раз статусы заявок, семейное положение, область работы, и прочее, или засунуть это в DimЗаявка, но так как она будет большая, вариант как по мне не очень, поэтому остановлюсь на первом. В таком случае, для факта заявка останется только измерение Заявка, Юзер, Geography, Статусы отказа (она большая, атрибутов много) и последняя Junk со всем остальным.
9 авг 18, 16:32    [21636436]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
Alex_496
Member

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

Подсказка еще: заявка = прототип договора
9 авг 18, 17:59    [21636545]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
aleksrov
Member

Откуда:
Сообщений: 905
Апнем тему, нужно мнение более опытных людей :)
1) Расписание платежей это факт или dim? Здесь вопрос. Есть DimContract, контракт разбивается на платежи, допустим 10 строк, первый столбец это плановая дата платежа, остальное столбцы вроде сумма, %, различные сборы и прочее, столбцов 10, одни суммы. Таких таблицы 2, одна фактический план, который был присвоен в первый день, вторая таблица реальное положение плана, во второй столбцов больше (соответственно там есть столбцы вроде день просрочки, пеня и прочее) и она постоянно апдейтится. Я сделал как факты, на этих таблицах будет много разных мер. Также была идея соединить в одну, но не фига, сущности разные, клиент может продлить или погасить заранее, тогда вторая таблица измениться, а первая нет.
2) Как вы храните коменты? Я сделал как отдельная Dim (ну и Кимбалл так в своей книге советует). Еще был вопрос по staff который оставил этот коммент, соединять два дима не стал (comment и staff), и добавил в факт поле StaffMakeCommentKey (т.е. в факте 2 ключа стафа, один кто создал контракт, а второй кто оставил коммент, это могут быть разные люди)
21 авг 18, 16:53    [21649788]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 30513
Блог
aleksrov,

Это факты, которые могут быть и измерением.
Комменты лежат в бд, если кому-то нужно - пользователь всегда может сделать из куба детализацию, которая сформирует sql и выплюнет что угодно в эксель или другое ПО. Чем меньше в кубах таких неагрегируемых штук, как комментарии, тем лучше эти самые кубы работают.
22 авг 18, 02:22    [21650184]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
aleksrov
Member

Откуда:
Сообщений: 905
Критик,

Спасибо за ответ!
Значит и оставлю как факты с 2 Dim (Geo и Contract), для текущих требований больше не надо. А как это может выступать в качестве измерения, если не трудно можно пример?
По коментам не так написал видимо, в кубе они и не нужны, они нужны в DWH. В хранилище данные будут литься из дохрена источников, в районе 20. Поэтому требование чтобы в DWH все было, т.к. сейчас разные выгрузки и прочее работают с кучей линков и прочее, это медленно и неудобно, должен быть один источник, dwh. Я взял коменты как Dim и подцепил в 3 фактам, где могут быть коменты (заявка, контракт, звонок), вроде норм. Также вполне вероятно потом могут возникнуть требования для анализа кол-во коментов в разрезе сотрудника, сколько времени он потратил на их заполнение и прочее, тогда в куб это надо будет включить, но без текста (скорее всего просто вьюха будет с нужными столбцами для DSV), но это возможно будет потом. Здесь такой принцип как по таблице что я писал выше, где текстовый хлам, в кубе она не будет, но она нужна для выгрузок разных или когда есть какие-то подозрения на мошеничество и прочее.
22 авг 18, 08:32    [21650221]     Ответить | Цитировать Сообщить модератору
 Re: Как соиденить 3 факта?  [new]
полудух
Member

Откуда: планета орков, г.Зверополис
Сообщений: 254
aleksrov
Всем привет!
Все дальше погружаюсь в DWH и тут назрел вопрос по одному проекту...
Есть финансовая организация которая дает кредиты, в системе есть такое понятие как Заявка, это первый факт, у нее есть свои измерение с атрибутами, далее если заявка принята создается Договор, это второй факт, у него тоже есть свои измерения со своими атрибутами (разумеется у них есть общие типа клиент, работник, дата и т.д.), далее идет таблица платежей, это 3 факт, вопрос в том как все это связать?
У контракта обязательно есть связанная с ним заявка, у заявки контракта может не быть (отказали к примеру и до контракта не дошло), платежи отслеживается в источнике по номеру контракта.
Если выносить в Dimensioanl таблицу Договор или Заявка то размерам она будет как факт, хотя в источнике и та и та постоянно обновляются, в особенности не закрытые договора (к примеру у них меняется статус и некоторые другие аттрибуты).


делал я такую CRM для микрофинансовой организации выдающей кредиты...
хотя в контексте заявок это неважно.
В общем заявки обрабатываются так:
1. заявка = юзер. Сразу и без вариантов. Юзер - первая сущность всех систем. Чем раньше вы его создаёте и чем меньше будет при нём атрибутов, тем меньше головняка со всякими проверками и мульти-инзертами в БД в будущем.
К слову, в системе нет клиентов, есть только юзеры. Любой юзер (в т.ч. м-р) может быть клиентом.
2. если телефон из заявки уже есть в БД (дубль), значит этому юзеру вешается флажок и у м-ра вылазит задача - обработать юзера повторно.
3. новому юзеру из заявки вешается флажок на обработку, т.е. м-р сразу в задачах видит, что юзера надо взять и обработать - созвониться.
4. из самой заявки надо вытащить utm_*, тех.данные письмА, итд. Но использовать их больше не надо будет нигде, кроме отчётов.
22 авг 18, 09:42    [21650292]     Ответить | Цитировать Сообщить модератору
Все форумы / OLAP и DWH Ответить