Как смоделировать, когда таблица заголовков имеет значения?
У меня есть следующие таблицы:
Таблица заголовка заказа: ordnum, orddate, customerID, Cost_of_packing, Cost_of_delivery
Таблица строк заказа: ordnum, ordlinenum, ProductID, количество, цена.
Столбец Ordnum — это общее поле между двумя вышеуказанными таблицами.
Клиент: идентификатор клиента, имя клиента
Продукт: идентификатор продукта, название продукта.
Как смоделировать эти данные?
Если я объединим таблицы заголовков заказов и строк строк заказа, то есть объединим их в одну таблицу фактов, тогда стоимость упаковки и стоимость доставки будут дублироваться для каждой строки.
Если я сохраню их как две отдельные таблицы фактов, я не смогу соединить их в столбце ordnum, поскольку это нарушает структуру звездообразной схемы. Таким образом, я не могу сообщить заголовок и детали заказа.
Ответы 2
Соедините их в одну таблицу и затем используйте DISTINCT(). Такова природа структур заголовок/детализация в звездообразных схемах. Альтернативой является подключение к таблицам фактов, и когда вы соединяете две очень большие таблицы мощности, производительность остальной части модели будет ужасной.
Объедините таблицу заголовка и сведений в одну большую таблицу. У вас будут повторяющиеся значения для каждой строки заголовка.
Хорошо, в этом случае значения стоимости упаковки и стоимости доставки будут дублироваться для каждой позиции детализации заказа. Как предоставить эти значения конечному пользователю? Например, я не могу использовать AVG или MAX, поскольку это даст неверное значение. Я вижу, вы упомянули об использовании DISTINCT. Как именно использовать это для моей цели?
Вам нужно будет написать показатели так же, как вы это делаете для продаж. Продажи — это просто SUM ( table[column] ), тогда как поставки будут выглядеть примерно так: SUMX(VALUES(table[ordnum], Cost_of_delivery). Здесь есть очень хорошая статья, в которой объясняется, почему слияние — это правильно, исходя из звездообразной схемы. перспектива sqlbi.com/articles/….
Как насчет этого: скажем, я храню заголовок и детали в виде отдельных таблиц фактов. И создайте новую таблицу измерений, содержащую отдельный список OrdNum. И подключите этот дим к обеим таблицам фактов.
Кстати, разве ваша приведенная выше формула не должна быть такой: SUMX(VALUES(table[ordnum]), CALCULATE(MAX(cost_of_delivery)))
На самом деле, вероятно, это должно быть что-то вроде этого: SUMX(SUMMARIZE('Таблица', 'Таблица'[Заказ], 'Таблица'[Стоимость доставки]), 'Таблица'[Стоимость доставки])
Вы можете попробовать создать измерение, но при дальнейшем создании показателей или визуализаций вы, вероятно, столкнетесь с некоторыми дополнительными проблемами. Я обнаружил, что SQLBI обычно является лучшей практикой.
какие проблемы вы видите в наличии такого измерения, пожалуйста?
Это действительно зависит от вашей модели. Попробуйте создать прототип того, что вы хотите, и если он не работает, вы можете переделать его.
Дэвид, на основании чего ты решаешь, должен ли я использовать SUMX(VALUES(table[ordnum]), CALCULATE(MAX(cost_of_delivery))) или SUMX(SUMMARIZE('Table', 'Table'[Order], 'Table'[Delivery Cost]), 'Table'[Delivery Cost]). Я прочитал статью о sqlbi, которой вы поделились, но не понимаю, как писать DAX, когда таблица заголовков/подробностей объединена в одну таблицу.
Оба в порядке. Обычно в DAX существует множество способов решения одной и той же проблемы, и проблемы не проявляются, пока вы не протестируете свою конкретную модель. Я бы в первую очередь выбрал первый вариант.
Под первым, я полагаю, вы имеете в виду мою версию? Кроме того, не могли бы вы указать мне какие-нибудь статьи о том, как написать такой DAX для обработки таких вещей. Я знаю DAX на среднем уровне, но мне нужно изучить его под этим углом. Пожалуйста, дайте мне несколько советов.
Да, ваша версия. Промежуточного DAX должно быть достаточно. Я не знаю конкретных статей, но обычно в SQLBI есть все, что касается DAX. Видео на YouTube для SQLBI также превосходны.
Если вы застряли, просто опубликуйте новый вопрос с проблемой
Полностью избавьтесь от таблицы заголовков заказов. Перенесите даты заказов и идентификаторы клиентов в таблицу сведений о заказе и распределите затраты по строкам.
Если вам нужно выполнить расчеты на уровне строки, вам придется определить правила распределения затрат. Например, выразите затраты как % от общей стоимости заказа, а затем умножьте на суммы строк. Это сделает затраты полностью аддитивными.
Если затраты никогда не учитываются на уровне строки, просто разместите их в первой строке каждого заказа и оставьте пробелы во всех остальных строках.
Редактировать:
Я смоделировал ваши данные и модель, чтобы показать, как это будет выглядеть на практике.
Модель данных:
Макет таблицы Orders с номерами верхнего уровня, выделенными для первой строки заказа:
Итоговый отчет выглядит так:
Подробный отчет о заказе с детализацией продуктов:
Меры DAX:
линейный уровень:
Order Qty = SUM(Orders[Qty])
Order Amount = SUMX(Orders, Orders[Price]*Orders[Qty])
верхний уровень:
Order Delivery Cost =
CALCULATE (
SUM ( Orders[Delivery Cost] ),
REMOVEFILTERS ( Orders[Product ID] )
)
Комбинированные расчеты верхнего и линейного уровня:
Order Total = [Order Amount] + [Order Delivery Cost]
Как видите, модель проста, а расчеты интуитивно понятны и производительны. Единственное, что выглядит немного странно, — это пробел в стоимости доставки, но это цена, которую вам придется заплатить за нераспределение.
К сожалению, не существует четкого правила распределения затрат на уровне строки.
В этом корень вашей проблемы: номера уровня строки и номера уровня заказа находятся на разном уровне детализации. Решать это приходится так или иначе — либо свертывать номера строкового уровня в верхний уровень, либо распределять вниз. Любое «решение», игнорирующее это несоответствие, скрывает реальную проблему под ковриком. Если у вас нет правила распределения, установите его.
А как насчет того, чтобы сохранить их в отдельных таблицах фактов и создать измерение с отдельным OrdNum. Затем создайте отношение 1:M между этим тусклым изображением и каждой таблицей фактов. Таким образом, пользователь может анализировать заголовок и детали, а также переходить от заголовка к деталям с помощью общего измерения.
Проблема с этим дизайном заключается в том, что другие измерения не будут использоваться совместно (продукт, дата, клиент). Это сделает анализ и код DAX очень громоздкими и неэффективными. Кроме того, он по-прежнему не учитывает уровень несоответствия детализации. Например, предположим, что бизнес задает вам вопрос: покажите мне чистую прибыль по продуктам. Как бы вы это сделали? В предложенном вами дизайне вы не сможете отвечать на подобные вопросы.
Если предположить, что нет необходимости показывать профиль сети по продуктам, то является ли это хорошей моделью? Я уверен, что это распространенный сценарий, когда значения уровня заказа не могут быть присвоены уровню детализации. Как разработчики моделей справляются с таким сценарием?
Делать порядок измерением — плохая практика. Заказы по сути являются транзакциями, а это означает, что они являются фактами, а не измерениями. На практике размер заказа будет постоянно расти и в конечном итоге станет очень высоким. Схема «Правильное начало» требует, чтобы измерения были короткими и широкими, а таблица фактов — высокой и узкой (высокие размеры снижают производительность запросов). Лучше всего распределять значения уровня заголовка до уровня строки (прочитайте этот технический документ от Кимбалла (файл в формате pdf): kimballgroup.com/wp-content/uploads/2013/08/…
В идеале бизнес должен предоставить вам правильные правила распределения. Если нет, подделайте их. В худшем случае просто присвойте значения заголовка первой строке и null остальным. Чтобы получить стоимость доставки, достаточно просто суммировать (если вы не фильтруете отчет по продукту - если это так, используйте «удалить фильтры» для продукта). Такая модель превосходит описанную вами во всех аспектах.
Как насчет присвоения значений заголовка каждой строке, а затем я использую SUMX(VALUES(table[ordnum]), CALCULATE(MAX(cost_of_delivery))) для значения, поэтому у меня есть одна таблица фактов.
Конечно, но почему вы хотите усложнить задачу? Итератор с CALCULATE означает цикл с переходом контекста, что очень дорого. При распределении это просто СУММА (стоимость доставки).
Я просто не могу распределить. В лучшем случае я понимаю вашу точку зрения, что мне нужно разместить все это в одной строке подробностей. Я думаю об этом.
если я распределяю все значение заголовка продажи одной из подробных записей, то в чем именно заключается ограничение такой модели?
Единственное ограничение — это продукт. Без правильного распределения ваш анализ выручки будет неправильным, если продукты будут фильтроваться. Вы можете решить эту проблему, создав специальный отчет для расчетов выручки или используя REMOVEFILTERS для продукта в показателях, которые включают цифры выручки. . В любом случае, это ничем не отличается от других рассмотренных вами моделей — у них всех одна и та же проблема, плюс для них потребуется запутанный и медленный DAX.
@variable: Я отредактировал свой ответ, чтобы показать вам, как это выглядит на практике.
Спасибо, что нашли время для подробного ответа, внесшего ясность. Таким образом, функция удаления фильтров полезна только в том случае, если показатель используется вместе с продуктом и деталями не верхнего уровня.
Да, а также если вы поместите продукт в срез, который фильтрует отчет (поскольку, если продукт выбран, общие затраты будут неверными).
Хорошая работа, RADO – проголосовал за.
@rado - когда я выделяю значения уровня заголовка одной из строк сведений, тогда другим строкам должно быть присвоено значение 0 или пустое/нулевое?
Либо один хорошо; Я предпочитаю использовать пробелы, потому что отчеты выглядят чище.
Что вы подразумеваете под
connect them into a single table?