Сегодня мы остановимся на сценарии, с которым столкнулся каждый из вас, однако использовали разные подходы. Хорошо ли иметь слишком много таблиц измерений? Можете ли вы объединить некоторые из этих таблиц вместе, чтобы построить одну таблицу измерений? Сколько нужно выравнивать их? Должны ли вы в конечном итоге получить одну огромную таблицу, включающую все? В этой статье мы ответим на все эти вопросы и объясним сценарии объединения измерений в Power BI.

Образец набора данных

Набор данных для этой модели - это файл Excel AdventureWorksDW2012, который можно загрузить здесь. Для этого примера нам нужны эти таблицы: DimProduct, DimProductCategory, DimProductSubcategory и FactInternetSales.

В наборе данных AdventureWorksDW у нас есть три таблицы, связанные с информацией о продукте; У DimProduct есть детали каждого продукта:

DimProductCategory содержит информацию обо всех категориях товаров:

DimProductSubCategory - это второй уровень иерархии, где у нас есть подкатегории в каждой категории:

Как видите, каждая запись в таблице подкатегорий принадлежит категории или, скажем, имеет значение в столбце ProductCategoryKey, который является ключом для поиска категории более высокого уровня в таблице DimProductCategory. Если мы хотим разработать иерархию продуктов, это будет выглядеть так:

Проблемы проектирования

Допустим, мы загружаем все таблицы выше (плюс таблицу FactInternetSale, которая является нашей таблицей транзакционных продаж) в модель Power BI. Вот как выглядит диаграмма отношений:

Обратите внимание, что Power BI автоматически создает пару из этих отношений, но вам нужно создать ту, которая выделена желтым цветом самостоятельно, подключите ProductSubcategoryKey из DimProduct к ProductSubcategoryKey в таблице DimProductSubcategory.

Периодическая необходимость двусторонних отношений

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

Однако как насчет следующего: мы хотим отфильтровать более высокий уровень иерархии, используя более низкий уровень! Например, мы хотим знать, к какой товарной категории относятся горные велосипеды?

Как видите, приведенное выше изображение не помогает.  ProductSubcategory, не может отфильтровать таблицу ProductCategory, и причина очевидна: связь между этими таблицами является односторонней от категории в подкатегорию.

Иногда, в зависимости от требований, приведенная выше конструкция приводит к необходимости двусторонних отношений, что не рекомендуется.

Создание иерархии всех трех таблиц

Допустим, вы хотите сделать дизайн отчета согласованным и хотите создать иерархию всех этих трех полей (категория, подкатегория и продукт), а затем использовать ее во всех визуальных элементах.

* Обратите внимание, что можно создать иерархию полей непосредственно в каждом визуале, перетаскивая и опуская поля друг за другом, однако создание иерархии в модели делает этот процесс более согласованным, а затем вы можете просто перетаскивать иерархию в каждом желаемом визуале.

Проблема в том, что вы не можете создать иерархию моделей между полями, которые не находятся в одной таблице! Вам может потребоваться создать вычисляемые столбцы в таблице Product с помощью функции RELATED DAX, чтобы выполнить этот процесс. Это очень сложный процесс для достижения чего-то действительно простого.

Вы не можете создавать иерархии моделей между полями из нескольких таблиц.

Дополнительные отношения! Зачем?!

Следующая проблема существующей модели состоит в том, что у нас так много отношений, чтобы охватить что-то действительно простое. В этой модели у нас пока есть три отношения. В реальных сценариях у вас нет только четырех таблиц, у вас будет сотни таблиц, и если вы будете следовать этому подходу, у вас будут сотни отношений! Отношения будут стоить вычислительной мощности, когда дело доходит до нарезки. Вы не заметите это для маленьких моделей с несколькими таблицами, но вы увидите разницу, когда модель вырастет. Если вы не слишком задумываетесь о своей модели данных и просто добавляете в нее таблицы и связи, то вскоре у вас получится гораздо более сложная модель, чем эта:

Подготовка данных - самый важный, первый шаг моделирования данных.

Модель сбивает с толку

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

Откуда взялась эта концепция проектирования?

Таким образом, вышеприведенная разработка имеет много проблем. Давайте посмотрим, откуда приходит концепция дизайна? Проект, который вы видите, который имеет одну таблицу на атрибут или функцию, исходит из дизайна транзакционной базы данных. В мире транзакционных баз данных важно спроектировать базу данных таким образом, чтобы вы могли легко и быстро применять операции CRUD (создание, обновление, извлечение и удаление). Один из лучших способов сделать это состоит в том, чтобы отделить каждую сущность в виде таблицы: категория в виде таблицы, подкатегория в качестве другой таблицы, цвет продукта в виде таблицы, бренд в качестве другой таблицы и т. д. В таком дизайне, вероятно, у вас будет более десяти таблиц, в которых хранится различная информация о самом продукте. Этот дизайн отлично подходит для транзакционных систем, но он не работает для систем отчетности. Вам нужен другой дизайн для отчетности.

Сглаживание таблиц измерений

В модели данных отчетности ваш подход должен заключаться в разработке звездообразной схемы и наличии достаточного количества таблиц измерений. Ваши таблицы измерений могут содержать все об этом объектеи. Например; если у вас есть измерение Product, оно может включать цвет, марку, размер, название продукта, подкатегорию и категорию продукта, все в одной таблице. Такой способ дизайна позволит избежать всех проблем, о которых мы упоминали ранее в этом посте, а также многих других проблем.

Таким образом, решение нашей задачи проектирования состоит в том, чтобы сгладить таблицу Product, комбинируя с ней DimProductCategory и DimProductSubCategory. Давайте посмотрим, как это возможно;

Объединение или сглаживание таблиц в одном измерении

Существуют различные методы, которыми вы можете осуществить этот процесс сглаживания, вы можете сделать это с помощью T-SQL, если у вас есть реляционные базы данных в качестве источника, вы можете использовать DAX для этого, или, альтернативно, вы можете сделать это с помощью Power Query.

Перейдите к Edit Queries в Power BI Desktop:

В окне редактора Power Query щелкните таблицу Product и в разделе Combine выберите Merge Queries;

В окне Merge Queries убедитесь, что DimProduct является первой таблицей, затем выберите ProductSubcategoryKey из нее, затем выберите DimProductSubcategory в качестве второй таблицы, а также столбец ProductSubcategoryKey и нажмите кнопку ОК.

Таблица слияния - это операция, которая выравнивает две таблицы на основе совпадающих полей. Мы подробно описали, что такое merge и чем отличается от append, а также какие существуют разные типы соединений в операциях слияния. В этой операции у нас будет работать тип соединения по умолчанию, но обязательно прочитайте разницу между всеми видами объединений и их выходными примерами.

Операция слияния создаст новый столбец в таблице DimProduct (в конце всех столбцов), который затем может быть расширен до деталей, поступающих из DimProductSubcategory. Таким образом, мы объединяем товар и подкатегорию вместе;

Мы выбрали только EnglishProductSubcategoryName и ProductCategoryKey (этот нужен для объединения его на следующем шаге с DimProductCategory). вот результат:

Как видите, два столбца выше теперь являются частью таблицы Product.

Мы выполняем операцию слияния еще раз, и на этот раз мы выберем DimProductCategory в качестве второй таблицы, используя ProductCategoryKey в обеих таблицах для слияния.

А затем расширим его до деталей DimProductCategory, которая в данном случае является только ProductSubcategoryName

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

Не загружайте промежуточные таблицы

Важно установить свойство “enable load” двух промежуточных таблиц (DimProductCategory и DimProductSubcategory), чтобы теперь оно не проверялось. Эти две таблицы не нужно загружать непосредственно в модель Power BI. Они подают данные в DimProduct, и это единственная таблица, которая нам нужна в модели.

Теперь, если вы загрузите данные в модель Power BI, у вас будет простая модель вроде этой:

Вы можете легко получить нарезку данных, работая правильно:

Вы также можете построить иерархию моделей и использовать ее в любом месте отчета:

Как насчет других таблиц? Свести все измерения воедино?

Вышеописанный метод был полезен, так что вы можете подумать, что давайте сделаем это для других таблиц. Почему бы не объединить таблицы Client и Product в качестве примера! Ну, у выравнивания есть золотое правило: выравнивайте это, пока у объектов есть значимые отношения друг с другом! Категория продукта и продукт имеют значимые отношения, каждый товар относится к категории. Тем не менее, продукт и клиент не имеют отношений, если не совершены сделки купли-продажи! У нас уже есть таблица транзакций продаж, которая является сердцем этой модели, и сведение в нее таблиц измерений сделает ее огромной, а также уменьшит нашу гибкость, если мы захотим позже подключить другую таблицу к продукту или клиенту. Так в этом сценарии; мы не будем выравнивать клиента и продукт. Мы сохраним их как их собственные измерения с отношением к таблице фактов; построение звездной схемы.

Резюме

Сглаживание таблиц измерений позволит избежать многих проблем в будущем в системе отчетности. Рекомендуется объединить атрибуты в одно большое измерение для каждого объекта. Измерение продукта может содержать всю информацию о цвете, размере, бренде, номере продукта, категории и т. д., а измерение клиента может содержать все о названии должности, образовании, возрасте и т. д. Вы узнали из этой статьи, каковы возможные проблемы, когда измерения не выровнены, и  как использовать команду Power Query Merge для их объединения. 

No Comments