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

Откуда: Feorina "Fury" 161
Сообщений: 4331
У меня есть 8 таблиц с фактами, из которых необходимо сделать аналитику.
И ещё куча таблиц-словарей типа фамилии, названия городов, складов и т.д.

Для аналитики (powerBI) необходимо, чтобы все факты располагались в центральной таблице. Это даст возможность сделать какую-то аналитику вроде "сумма фактов в третьей таблице разделить на сумму фактов в первой" и т.д.
Приходится делать так:
CREATE VIEW facts(A_fact, B_fact, C_fact, D_fact, E_fact, F_fact, G_fact, H_fact)
SELECT ФИО, Офис, A_fact, null, null, null, null...
FROM A
UNION ALL
SELECT ФИО, Офис, null, B_fact, null, null, null...
FROM B
UNION ALL
SELECT ФИО, Офис, null, null, C_fact, null, null...
FROM C
UNION ALL
...

Есть ли какой-то способ не указывать эти "null"? При попытке добавить новый столбец фактов в таблицу B
...
UNION ALL
SELECT ФИО, Офис, null, B_fact1, B_fact2, null, null, null...
FROM B
UNION ALL
...

приходится терпеливо прописывать null во всех остальных таблицах.

Или эта задача вообще совсем иначе решается?
18 дек 18, 18:16    [21767525]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
aleks222
Member

Откуда:
Сообщений: 847
Ныне, присно и во веки веков 8 фактов будет?

Анализируй это.
18 дек 18, 18:29    [21767537]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4331
aleks222
Ныне, присно и во веки веков 8 фактов будет?

Анализируй это.

факты могут добавиться.
И какие могут быть предложения? Вот, у меня 8 таблиц, а фактов нифига не 8. Там есть и текстовые типа "категории", и т.д. - я задолбался уже эти столбцы добавлять и null по всем 8 таблицам раскидывать.

Это те не факты типа "сумма в рублях", к которым в любой момент могут добавиться "сумма в долларах", "сумма в йенах" и т.д.
Т.е. это именно глобальные факты: "объём дохода", "объём затрат", "объём заказа" и т.д.
Есть таблица с инфой о подписанных договорах - она содержащих инфу о доходах,
есть таблица со счетами всякими - она содержит инфу об затратах
есть таблица поступивших заказов - она содержит инфу о заказах

и так далее. Далее в PowerBI вычитаю из первого второе и получаю прибыль и т.д.

В кубах подобные задачи решаются. Но ничего не могу поделать - нет тут сервера кубов у меня.
18 дек 18, 18:58    [21767579]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
PizzaPizza
Member

Откуда:
Сообщений: 309
Charles Weyland,

Колонки - это атрибуты, а у вас получается, что колонки - это сущности.

Но задача в целом мне непонятна. С примером было бы гораздо нагляднее.
18 дек 18, 19:13    [21767597]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4331
PizzaPizza
Charles Weyland,

Колонки - это атрибуты, а у вас получается, что колонки - это сущности.

Но задача в целом мне непонятна. С примером было бы гораздо нагляднее.

Все столбцы, начиная с четвёртого, - в рублях.
Исполнитель_ID Офис_ID Дата Запланировано дохода Объём поступившего заказа(руб) объём(кол-во) отгружено(руб) отгружено(кол-во)
11 1.01.2018 400nullnull
11 1.02.2018 600nullnull
11 1.03.2018 600nullnull
11 24.01.2018 null 80 5 null null
11 26.01.2018 null 120 3 null null
11 31.01.2018 null 110 4 null null
11 4.02.2018 null 50 3 null null
11 6.02.2018 null 220 6 null null
11 15.02.2018 null 90 6 null null
11 25.02.2018 null 190 9 null null
11 4.02.2018 null 230 4 null null
11 26.01.2018 null null null 40 2
11 29.01.2018 null null null 140 2
11 3.02.2018 null null null 130 4
11 27.02.2018 null null null 100 6
11 7.02.2018 null null null 300 6

Таблица - результат объёдинения нескольких таблиц (планы, доходы, заказы). Это позволяет мне сделать ту самую пресловутую схему "звезда", зафигачив все факты в одну таблицу. И таким образом выбрав конкретного исполнителя, работающего в конкретном офисе, могу узнать его эффективность работы в январе, феврале, марте и суммарную, сравнить "Ожидания и реальность" и т.д.
18 дек 18, 19:51    [21767649]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4331
ну вот, даже здесь две колонки забыл null'ами забить.
18 дек 18, 19:52    [21767651]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Charles Weyland
Таблица - результат объёдинения нескольких таблиц (планы, доходы, заказы). Это позволяет мне сделать ту самую пресловутую схему "звезда", зафигачив все факты в одну таблицу. И таким образом выбрав конкретного исполнителя, работающего в конкретном офисе, могу узнать его эффективность работы в январе, феврале, марте и суммарную, сравнить "Ожидания и реальность" и т.д.

А есть дата оформления заказа. Есть дата поступления товара на склад под заказ клиента. Есть дата начала действия заказа (когда клиент ожидает поступления товара). Есть дата первой отгрузки. Есть дата последней отгрузки. Есть дата первой оплаты. Есть дата последней оплаты. Есть дата возврата части оплаты как коррекции между бухгалтериями.

Продавец продал 1 января клиенту вагон скрепок. 1 февраля вагон поступл на склад фирмы. 1 марта его перегрузили на грузовики и отправили. 1 апреля грузовик номер 1 доехал до клиента и был принят на его складе. 2 апреля грузовик оформили накладной. 3 апреля оформили платежное поручение на оплату за первый грузовик. 4 апреля приехал второй грузовик. Оказалось, что скрепки проржавели, пока ехали. 4 мая возвращенный грузовик оформили как возврат от клиента на складе фирмы. 1 июня пришел очередной вагон со скрепками для ДРУГОГО клиента. С которым было согласовано, что ему отправят один грузовик из двух, а один отправят тому клиенту, который уже давно ожидает. Разумеется, при оформлении поступления на склад 2 июня все скрепки из второго вагон были оформлены как заказ клиента номер 2.
Далее второй грузовик в июле был отправлен и разгружен у первого клиента, в августе последний - второй платеж - до последнего шекеля был отправлен на банковский счет. 1 сентября бухгалтерия после сверки закрыла договор поставки как исполненный. Менеджер готовится получить свои бонусы за продажу.


Вопрос - как по дате отследить успешность реализации для клиента номер 1 и менеджера по продажам номер 1, если выбрать фильтром заказы января? Видите, какой хороший вопрос? Ваши заказчики вообще понимают, ЧТО они увидят в отчетах PowerBI, или будет как в песне малоизвестной российской исполнительницы Алены Апиной - "я его слепила из того, что было, а потом, что было - то и полюбила".
18 дек 18, 20:21    [21767671]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Charles Weyland
Далее в PowerBI вычитаю из первого второе и получаю прибыль и т.д.

А потом выяснится, что суммы по заказам без учета российского НДС, суммы по оплатам с учетом НДС и так далее. И что нельзя вычитать и делить ничего ни с чем.

Вы таки знаете, я Вам скажу, только Вы не обижайтесь. Российский IT настолько бессмысленный и беспощадный, что прежде всего поражает воображение исполнителей - а потом уже по цепочке заказчиков таких "учетно"-"отчетных" систем и их пользователей.
18 дек 18, 20:25    [21767675]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4331
Andy_OLAP
Charles Weyland
Далее в PowerBI вычитаю из первого второе и получаю прибыль и т.д.

А потом выяснится, что суммы по заказам без учета российского НДС, суммы по оплатам с учетом НДС и так далее. И что нельзя вычитать и делить ничего ни с чем.

Вы таки знаете, я Вам скажу, только Вы не обижайтесь. Российский IT настолько бессмысленный и беспощадный, что прежде всего поражает воображение исполнителей - а потом уже по цепочке заказчиков таких "учетно"-"отчетных" систем и их пользователей.

да с чего ж обижаться.
Всё верно описываете. Вся подробная информация о движении товара, с IDшниками и прочим - лежит в своих таблицах. В нужных валютах, с нужными примечаниями и прочим.

У описанной выше таблицы есть конкретная задача, не привязанная к конкретным заказам.
И суть состоит в том, что нужно просто посмотреть эффективность работы сотрудников и подразделений. Запланировано, что он сможет выполнить на одну сумму, поступает ему задач на другую, выполняет на третью. Нет задачи следить за конкретным заказчиком.
18 дек 18, 20:37    [21767687]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Charles Weyland
Запланировано, что он сможет выполнить на одну сумму, поступает ему задач на другую, выполняет на третью. Нет задачи следить за конкретным заказчиком.

А когда заказы по факту обслуживает другой менеджер по продажам? Первый ушел на больничный, а второй бегает по подразделениям, контролирует отгрузку, оплату, созванивается, когда будет возврат забракованного, и так далее.

У Вас сейчас будет несколько таких "таблиц фактов", каждая из которых намертво привязана к одной дате. А далее заказчик системы вспомнит небольшой нюанс и будет просить сделать "мааааленькую переделку". Теперь представьте, как все таблицы фактов переделаются, как все формулы в отчетах PowerBI "едут" напрочь.

Резюмирую - я не верю ни в "серебряную пулю", ни в единую таблицу фактов для любой, самой мелкой задачи, когда сравниваются план и факт из разных календарных периодов.
18 дек 18, 20:47    [21767701]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
PizzaPizza
Member

Откуда:
Сообщений: 309
Charles Weyland,

для меня непонятно, как вы эту одну таблицу наполнять собираетесь.

если чисто для консолидированных или обзорных отчетов я б сделал вьюшку/ки и из неё/них уже делал бы
Charles Weyland
какую-то аналитику вроде "сумма фактов в третьей таблице разделить на сумму фактов в первой" и т.д.
18 дек 18, 20:54    [21767709]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2374
Charles Weyland,

cделайте olap кубик, на который и натравите ваш powerBI
19 дек 18, 10:34    [21768013]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4331
PizzaPizza
Charles Weyland,

для меня непонятно, как вы эту одну таблицу наполнять собираетесь.

если чисто для консолидированных или обзорных отчетов я б сделал вьюшку/ки и из неё/них уже делал бы

да никак

К сообщению приложен файл. Размер - 15Kb
19 дек 18, 11:28    [21768102]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4331
StarikNavy
Charles Weyland,

cделайте olap кубик, на который и натравите ваш powerBI


Charles Weyland
В кубах подобные задачи решаются. Но ничего не могу поделать - нет тут сервера кубов у меня.
19 дек 18, 11:28    [21768105]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4331
Andy_OLAP
А когда заказы по факту обслуживает другой менеджер по продажам?

Вопросы здравые.
Но цель годы делать сложную идеальную систему, которую не потребуется менять десятками лет - задача неправильная.
Тут всё проще: наклепал запрос, загнал во вьюшку, сделал отчёт, народ посмотрел, подумал - нравится/не нравится. Забраковали, сделали другое решение.

Подход к анализу данных всегда выбирается исходя из реалий и с определённой степенью достоверности.
Яркий пример - значения "среднее" и "медиана" - тоже во многом бессмысленные и не точные и критиковать анализ "средних значений" можно бесконечно. Но всё равно востребованы, позволяют понять некоторые тенденции измеряемых величин. И ими совершенно нормально пользуются, держа в голове особенности исходных данных.

Да, заказ взят в январе, а выполнен в марте. Но вычисления будут производиться "мартовский доход"/"мартовский объём заказа".
Это во многом дебильно.
Но заказчик хочет посмотреть на практике, насколько это отражает реальность, ведь многие заказы на самом деле закрываются в тот же день.
ЗАКАЗЧИК
"Давай для начала сделаем хоть что-то, покажи для начала хотя бы это решение, и уже на практике поговорим, почему оно имеет недостаточную достоверность и решим, как её улучшить".

Статистика - вообще вещь такая: как повернёшь, то и получишь. Вот, люди хотят и так повертеть, и иначе повертеть.
19 дек 18, 11:46    [21768143]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
aleks222
Member

Откуда:
Сообщений: 847
Charles Weyland
да никак

Ну сделай генератор вьюшки из упрощенного шаблона.
Null вставит железный механизм.
19 дек 18, 15:44    [21768498]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7383
Charles Weyland,

Создайте сбор данных select-insert, не мучайте представления.
19 дек 18, 18:20    [21768725]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
PizzaPizza
Member

Откуда:
Сообщений: 309
Charles Weyland
PizzaPizza
Charles Weyland,

для меня непонятно, как вы эту одну таблицу наполнять собираетесь.

если чисто для консолидированных или обзорных отчетов я б сделал вьюшку/ки и из неё/них уже делал бы

да никак


если проблема именно с нуллами, то

SELECT ФИО, Офис, a.A_fact, b.B_fact, c.C_fact ...
FROM a
видимо LEFT JOIN b ON (id or key or ФИО Офис)
LEFT JOIN c ON (id or key or ФИО Офис)
...
19 дек 18, 22:01    [21768935]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4370
Charles Weyland
PizzaPizza
Charles Weyland,

Колонки - это атрибуты, а у вас получается, что колонки - это сущности.

Но задача в целом мне непонятна. С примером было бы гораздо нагляднее.

Все столбцы, начиная с четвёртого, - в рублях.
Исполнитель_ID Офис_ID Дата Запланировано дохода Объём поступившего заказа(руб) объём(кол-во) отгружено(руб) отгружено(кол-во)
11 1.01.2018 400nullnull
11 1.02.2018 600nullnull
11 1.03.2018 600nullnull
11 24.01.2018 null 80 5 null null
11 26.01.2018 null 120 3 null null
11 31.01.2018 null 110 4 null null
11 4.02.2018 null 50 3 null null
11 6.02.2018 null 220 6 null null
11 15.02.2018 null 90 6 null null
11 25.02.2018 null 190 9 null null
11 4.02.2018 null 230 4 null null
11 26.01.2018 null null null 40 2
11 29.01.2018 null null null 140 2
11 3.02.2018 null null null 130 4
11 27.02.2018 null null null 100 6
11 7.02.2018 null null null 300 6

Таблица - результат объёдинения нескольких таблиц (планы, доходы, заказы). Это позволяет мне сделать ту самую пресловутую схему "звезда", зафигачив все факты в одну таблицу. И таким образом выбрав конкретного исполнителя, работающего в конкретном офисе, могу узнать его эффективность работы в январе, феврале, марте и суммарную, сравнить "Ожидания и реальность" и т.д.


Вопрос в глобальной архитектуре.

ЕСЛИ СДЕЛАТЬ ПРАВИЛЬНО, ТО

Не надо объединять в одну таблицу.
1) Держите разные таблицы,
2) Сделайте дату гранулярной до дня.
3) Сделайте куб и по каждой таблице группу мер в кубе
4) Они вертикально сопоставятся по дате и другим совпадающим измерениям
В SSAS это уже проработали

ЕСЛИ ДЕЛАТЬ ПО БЫСТРОМУ, ТО ПОЛОЖИТЕ ЭТО В COLUMNSTORE и не парьтесь

Вопрос в том, что вы пытаетесь реализовать самопальный OLAP, а можно воспользоваться готовым
20 дек 18, 12:11    [21769335]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
Владислав Колосов
Member

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

какой куб? У автора нет SSAS.
20 дек 18, 13:02    [21769420]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4370
Владислав Колосов
a_voronin,

какой куб? У автора нет SSAS.


Так в том и вопрос, что может это сделать через куб/SSAS.
20 дек 18, 13:31    [21769467]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4370
Владислав Колосов,

Все подключается

https://docs.microsoft.com/en-us/power-bi/desktop-ssas-multidimensional
20 дек 18, 13:33    [21769471]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сделать таблицу фактов?  [new]
Charles Weyland
Member

Откуда: Feorina "Fury" 161
Сообщений: 4331
a_voronin
Владислав Колосов,

Все подключается

https://docs.microsoft.com/en-us/power-bi/desktop-ssas-multidimensional


вот странный ты человек. Говоришь, что "всё" подключается, а сам даёшь ссылку на статью, где чёрным по белому сказано, что не всё подключается.

Есть у меня куб, не могу я его подключить. Пока что. Потом закупят - тогда смогу. Так что описанная выше задача носит исключительно временное решение.

К сообщению приложен файл. Размер - 23Kb
20 дек 18, 14:34    [21769594]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить