Почему нужно избавляться от связи многие ко многим

Связь многие-ко-многим, OLAP и MS SQL Server Analysis Services

Для начала немного о том, как я к этому подошел. Начальство сказало, что на имеющейся базе данных неплохо бы развернуть какую-то аналитику. Проведя небольшой гуглопоиск выяснилось, что отлично подойдет технология OLAP. А так как на сервере компании развернут MS SQL Server, то еще более отлично подойдет тамошний компонент Analysis Services.

Радостно потирая руки, я схватил майкрософтовский учебник, который идет в комплекте с SQL Server’ом. А через два дня я был уверен, что мне все по плечу. Но, не тут то было… В имеющейся базе данных большинство связей между таблицами оказались связями многие-ко-многим, что поначалу не предвещало никаких осложнений. Но на практике выясняется, что без дополнительных весьма осмысленных маханий руками не обойтись, т.к. в противном случае кубик просто выдает некорректную информацию.

В рунете найти информацию по данной тематике оказалось задачей не из легких. То ли это настолько само собой разумеющееся для всех дело, то ли лыжи не едут. Однако мною таки был найден очень классный большой английский мануал по данной теме. Собственно тем, кто отлично понимает английский или же хочет очень хорошо разобраться в теме, предлагается дальше не читать, а уйти по ссылке: http://www.sqlbi.com/articles/many2many/

Те же, кто, как и я ранее, не имеет времени читать английские учебники, читают далее. Пост не представляет собой перевод данного мануала: скорее некое суммирование его первых примеров + немного основ из майкрософтовского упомянутого учебника.

Собственно в чем проблема. Если читающий знакомился с данным постом: http://habrahabr.ru/post/67272/ , то знает что стандартными схемами для Olap являются схемы «Звезда» и «Снежинка». А что если нас кругом обложили связями многие-ко-многим?

Самый простой вариант: уйти от связи многие-ко-многим с помощью представлений – это положительно скажется на скорости обработки запросов. Невозможно уйти? Давайте разбираться.

Постановка задачи. Интернет-магазин. MS SQL Server, на нем база данных с пресловутыми связями M2M, которая выглядит следующим образом: Таблица идентификаторов покупок, к ней привязаны таблица категории (еда, спорт, другое) и таблица аккаунтов. Усложним задачу: пусть теперь одним аккаунтом могут пользоваться сразу несколько человек (например, муж и жена делают покупку на дом), соответственно через M2M связана таблица персон. И чтоб совсем не сахар: пусть к таблице персон через M2M привязана таблица категорий персон. И нас интересует: какие категории людей, какие покупки чаще делают, и когда они это делают.

Пример надуманный, но все же видна проблема: как связать аж через две связи M2M будущее измерение с таблицей фактов? Все просто, мы подскажем SSAS, куда нужно смотреть.
Выполнив предварительные действия по созданию измерений (Types, Dates, Categories, Persons, Accounts)(см. http://habrahabr.ru/post/67272/), пытаемся создать куб на мере Sales (количество строк). По умолчанию Visual Studio предложит нам только три меры (Types, Accounts, Dates) – ведь только они связаны напрямую с нашей мерой. Создав куб, руками добавляем оставшиеся две меры. Плюс создаем еще две вспомогательные меры внутри куба, которые будут отвечать за обработку связи M2M: Bridge Accounts Persons и Bridge Persons Categories (обе – количество строк в понятно каких таблицах).
Таким образом, будем иметь следующую картинку:

Видим много серых боксов и то, что Visual Studio уже обработала одну связь M2M: между вспомогательной мерой Bridge Persons Categories и измерением Accounts. И это хорошо, но не достаточно. Если прямо сейчас попросить наш куб что-нибудь выдать, мы не получим ничего хорошего. Нет, измерения Dates и Accounts все сделают правильно, но вот с Persons и Categories увы.

Чтобы исправить сие недоразумение подскажем SSAS, где искать информацию для обработки наших запросов. Для этого заполним те серые боксы на вкладке «Использование измерений» следующим образом: щелкаем на серые бокс-> многоточие-> тип связи выбираем «Многие ко многим»-> Выбираем промежуточную группу мер, как написано на картинке:

Вуаля! Все работает. Можно убедиться на очередной картинке:

В чем магия? Мы указали SSAS, где и как искать информацию о связях. Обратите внимание: невозможно, например, правильно заполнить пересечение «измерение Categories и мера Sales» до того как вы правильно заполните «измерение Categories и мера Bridge Account Persons». В вариантах выпадает только Bridge Persons Categories, т.к. иного пути Visual Studio просто не знает. Но ведь вспомогательные меры – тоже меры. И пути для них надо указывать так же, как и для обычных (целевых) мер. По мере заполнения таблицы Visual Studio набирается знаний и предлагает больше вариантов.

Теперь сформулируем мнемоническое правило о том, как надо заполнять таблицу связей: «Между целевой мерой и целевым измерением выбирай ближайшую таблицу мер к целевой мере». Таким образом, и получалось, что для измерений Type и Date во второй столбце будет полноценная, интересная мера Sales, а в третьем вспомогательная Bridge Accounts Persons. Аналогично и для измерения Categories.

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

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

• SQL
• Microsoft SQL Server

Урок по структуризации и проектированию баз данных

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

Читается за 15 мин.

Хотите создать схему базы данных самостоятельно? Попробуйте Lucidchart! Быстро, удобно и совершенно бесплатно.

Процесс проектирования базы данных

Хорошо структурированная база данных:

• экономит место на диске, так как не содержит лишней информации
• поддерживает целостность и точность данных
• обеспечивает удобный доступ к данным

Проектирование содержательной и эффективной базы данных — вопрос выполнения надлежащей процедуры, в которую входят следующие фазы:

1. Анализ требований, или выявление цели базы данных
2. Организация данных в таблицы
3. Указание первичных ключей и анализ связей
4. Нормализация и стандартизация таблиц

Давайте подробнее рассмотрим каждый из этих этапов. Но сначала хотим обратить ваше внимание на то, что данный урок построен на примере модели реляционной базы данных Эдгара Кодда, написанной на SQL (в противопоставление иерархической, сетевой или информационной модели). Подробнее ознакомиться с разными моделями баз данных можно в нашем руководстве по этой ссылке.

Анализ требований: выявление цели создания базы данных

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

Вот несколько способов сбора информации для подготовки базы данных:

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

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

• Имя
• Адрес
• Город, регион, почтовый индекс
• Адрес электронной почты

• Имя
• Цена
• Количество на складе
• Количество в заказе

• Номер заказа
• Консультант
• Дата
• Товар(ы)
• КоличествоS
• Цена
• Общая стоимость

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

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

Структура базы данных: строительные кирпичики

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

Схожие данные в пределах базы группируются в таблицы, каждая из которых состоит из строк (или кортежей) и столбцов.

Чтобы преобразовать списки данных в таблицы, в первую очередь создайте таблицу по каждому типу сущности, (товар, продажа, клиенты и так далее). Вот пример:

Каждая строка таблицы называется записью. Записи содержат информацию о людях и объектах, например, о конкретном клиенте компании. В отличие от них, столбцы (которые также называют полями или атрибутами) содержат информацию одного типа, которая присутствует в каждой записи, например, адреса всех клиентов, перечисленных в таблице.

Имя	Фамилия	Возраст	Почтовый индекс
Роджер	Уильямс	43	34760
Джеррика	Йоргенсен	32	97453
Саманта	Хопкинс	56	64829

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