Всем привет!
Есть вопрос, интересно мнение сообщества, не могу найти примеры подобной реализации.
Есть хранилище, на нем табличная модель SSAS и Power BI, вопрос в реализации измерений.
Есть такая воронка заявка - кредит - транзакция. Заявок много, млн 20 и быстро растет, кредитов % 10 от заявок, транзакций тоже 20 млн. Так вот, есть измерения у которых есть бизнес ключ в источнике, это просто, id клиента, id geo данных (город и т.д.), а что делают обычно если нужны аттрибуты заявки которые в источнике лежат прям в факте, т.е. заявки в источнике это широченная таблица (там нарушены все нормальные форма, но это другой разговор)
Пример тут https://www.sql.ru/forum/1311409/power-bi-kak-svyazat-2-sreza
К примеру у заявки есть куча разных аттрибутов, из примера выше Repeat Sale (1,0), Channel и т.д., на самом деле их очень много. Бизнес хочет по ВСЕМ этим аттрибутам фильтровать всю воронку, т.е. я выбираю Channel = Mobile и вижу кол-во заявок, сумму которую хотели получить (цифра в заявке), кол-во контрактов, сумму контрактов и сколько было выплачено % по ним. Эти атрибуты есть только в заявке, в источнике в контракте только ID заявки, а в транзакции ID контракта. Вроде как заявка получается как Dim, но уж очень она большая выходит, и нужен ее анализ, т.е. сколько создали, суммы, шаги (в факте заявок есть разные даты, вроде отправки смс и прочее). Как сейчас, 1 шагом гружу в стейдж все заявки, потом формирую справочник, а именно выбираю нужные столбцы (channel, repeat sale и прочее), группирую (т.е. это и будет бизнес ключ) и делаю Merge DimAppAttribute, через Identity получаю суррогатный ключ, потом когда гружу факты, делаю Lookup по каждому полю чтобы получить суррогатный ключ атрибутов. Потом гружу контракты, и делаю Lookup к фату заявок, чтобы получить ключ DimAppAttribute. Как-то так.
Больше всего последнее смущает, обращаюсь к факту как к Dim, но не вижу смысла делать заявки измерением, во первых они здоровые, во вторых по требованию (анализ который необходим) это факт и в 3х к одному контракту только 1 заявка идет, поэтому все что мне нужно это только ее атрибуты для анализа, чтобы видеть по ним воронку.

И да, дополню.
Таких справочника 3, в 1м только флаги, их в 15, во втором данные по рискам (скор оценка, версия скор модели и прочее) и 3я все остальное вроде статусов, кол-во приложенных доков, доход клиента (это к заявке относится), была ли отправка смс, кол-ва раз сколько отправили и прочее, прочее. Разбил так как одна таблица получилась бы большая по кол-ву строк и ширине (в одной таблице вышло полей 50). В самом факте, если говорить про заявки, только даты (10 полей), ключи справочников, суммы (3 поля) + пару текстовых полей (это обновляемые поля, вроде статуса контракта. Когда контракт выдается \ отменяется \ закрывается, здесь проставляется нужное значение. Статус нужен для анализа прохождения заявки по основным шагам, сколько прошло времени, т.е. заявки которые дошли до контракта были в работе не больше 2 дней, 3 и более большой % контрактов был отменен, такую инфу можно только в заявках взять, но нужно знать дошла ли заявка до контракта).
Из-за последнего думал сделать контракты как Dim, но по ним тоже большой анализ в разрезе разных атрибутов, но из контрактов в заявке нужен только статус (на текущий момент), решил сделать как обновляемое поле.
P.S. Все работает быстро, пока... ))) Но правильно ли.

И еще дополню, история не нужна. Т.е. если поменялся статус у заявки то просто ей присвоится новый ключ, заявка прошла на новый этап, в одном из полей проставится нужная дата и т.д.

Alex_496,

Спасибо за ответ! Я думал так сделать, меня смутило что
1) Это сейчас 20 млн, к концу года заявок будет под 50, что потом когда перейдет отметку в 100. 100 млн справочник, круто выходит.
2) В таком варианте факт будет 1 к 1 со справочником, если я правильно понял. Т.е. в справочнике у нас ID заявки и ее атрибуты (которые сейчас у меня в 3х справочниках), в факте суммы и даты. Тогда получается это будет также справочником к контрактом, который в 10 раз больше самих контрактов.
В такой реализации тогда сур. ключ заявки надо пробрасывать дальше в звонки и транзакции, чтобы видеть всю воронку.
Тоже самое как я понимаю тогда касается контрактов, это будет факт - дим которые 1 к 1 идут, а потом сур ключ кидается в транзакции и пару других фактов.
Плюсы такой реализации это проще сделать и понятнее, но справочники получаются уж очень до хрена большие + 1 к 1 с фактом связь, это дикое зло если верить Кимболу и другим умным дядькам.
Плюс в той реализации что у меня это небольшие справочники, 3 по тысяч сто строк, фильтрация работает оч быстро. Минус это гемор с загрузкой и в ETL без бутылки лучше не лезть.
Не сделал как советуете вы по сути по 2м причинам, что заявок много и их будет еще больше, и мне не интересны данные заявка - контракт (кроме разве что одного случая как я выше писал когда нужно знать дошла ли заявка до контракта). 99% отчетов это меры по атрибутам, т.е. сколько у ID с заявкой контрактов и сколько по ней заплатили не очень интересно. Интересно сколько было заявок с флагом Repeat, сколько транзакций и т.д.

По сути система не сильно измениться от того что есть у меня, только вы предлагаете по сути из 3 таблиц Junk Dimension сделать одну со всеми атриьутам заявки, и ключ этой дим кидать в факт. т.е. сейчас это так как на картинке. Видно 2 Junk таблицы с аттрибутами, они сольются в одну и к ним добавиться только ID заявки по сути.

Alex_496,

Это что то вроде быстро деньги, только европейский вариант.
Сейчас попробовал сделать по быстрому связь заявки факт, заявки Dim (1 к 1 с фактом заявок) и цепляем к контрактам (по сути тут тоже 1 к 1, хотя могут быть исключения, когда контракт отменили, но потом создали новый по той же заявке, это тоже важные данные вроде как, пока не нужны, но скоро точно нужен будет анализ такого, раньше такого в источнике не было, вот в том числе из-за них я и начал думать что я что-то не то делаю), вроде быстро...
Да, все аттрибуты заявки важны для договора (именно аттрибуты). Т.е. дата создания заявки это в факте заявок будет, т.к. (если сделать как вы советуете) я фильтрую по димзаявки допустим по полю ChannelPartner (имя партнера) и вижу что за месяц было создано (мера просто Countrows допустим по факту заявок) 100 000 тысяч заявок и выдан 1 контракт и то по нему ничего не платили, то такого партнера надо слать.
В таком случае Junk Dimension даже и не будет, все три что сейчас есть сольются в справочник заявок.

Но все ровно остается вопрос когда кол-во заявок приблизится к 100 млн, насколько система и пользователи будут страдать.

Спасибо за советы, попробую на выходных что то быстрое собрать и посмотреть на скорость.

По МФО, я нейтрально к ним отношусь, не плохо не хорошо.

Мне кажется именование важно в особености если разбивать как вы советуете.
Вот есть контракт, он и Dim и Факт. Факт т.к. нам интересны кол-во, сумма, средняя сумма и прочее, Dim т.к. по нему мы фильтруем по аттрибутам контракта (флаги, статус и прочее) транзакции (для одного контракта их сотни может быть) и к примеру таблицу логов контракта (допусти интересно сколько раз в какой статус попадали контракты с разными атрибутами), т.е. здесь также идет разбивка, DimContract где ID_Contract и все аттрибуты и FactContract где сур.ключ из DimContract (связь 1 к 1) и ключи других справчоников (продукт, клиент, сотрудник) а также суммы и даты (связанные с календарем). И вопрос как это тогда назвать если не DimContract и FactContract, если только ContractAttribute и Contract?