SQLShack

Варіант багатовимірного куба Служб аналізу легко обробляв взаємозв'язки "багато-до-багатьох" для багатьох версій до 2016 року. Таблиця мала роботу із використанням формул DAX до виходу SQL Server 2016. Є ще деякі обмеження для багатьох - до багатьох у табличній таблиці, але, звичайно, є деякі “хитрощі”, щоб подолати обмеження. Але відносини «багато-до-багатьох» будуть знаходитись у даних про бізнес на довгі роки. Потрібно надати рішення, що стосується баз даних Служби аналізу.

Перш ніж перейти до розробки та підтримки багато-до-багатьох у службах аналізу, ETL програми звітності має відформатувати дані в таблицях, що сприяють підтримці цієї опції. Це може називатися мостовим столом. Дані в таблиці транзакцій “пов’язані” з багатьма можливими підтримуючими категоріями. Дані Adventure Works DW є чудовим прикладом багатьох причин продажу, пов’язаних з багатьма позиціями в Інтернеті.

На малюнку 1 наведено приклад конструкції цих таблиць.

Рисунок 1: Відносини багато-до-багатьох у кубі

Редагувати взаємозв'язок на малюнку 1 показує у кубі взаємозв'язок між 2 таблицями з декількома стовпцями - SalesOrderNumber (Номер замовлення) та SaleOrderLineNumber (Номер рядка замовлення). Ця мостова таблиця, FactInternetSalesReason, мостує Причини продажів із таблиці розмірів DimSalesReason до таблиці фактів FactinternetSales за допомогою цих 2 стовпців. У таблиці FactInternetSalesReason може бути кілька записів для одного номера замовлення плюс номера рядка замовлення. Приклад у таблиці нижче показує 2 різні номери замовлення на продаж із 3 різними причинами продажу.

NumberOrderNumber	SalesReasonName
SO51214	Про просування
SO51214	Інший
SO51214	Ціна
SO51298	Про просування
SO51298	Інший
SO51298	Ціна

Таблиця 1: Кілька причин продажу

На малюнку 2 показано Аналіз у Excel, коли немає взаємозв’язку між Причиною продажу та Інтернет-продажами для куба. Це той самий результат, який спочатку був отриманий і з таблицею. Відносини багато-до-багатьох не призначаються автоматично при побудові куба або табличної моделі за допомогою майстрів. Після завершення роботи майстра буде зроблено більше роботи. Міри підрахунку продажів та суми продажу підсумовуються у всіх рядках, а не в зрізі причин продажів, використаному на малюнку 2.

Рисунок 2: Аналіз у початковому кубі Excel

Відносини не є звичайними відносинами для Вимірювальних відносин у Куб. Але перед тим, як встановлювати взаємозв'язок, потрібно створити розміри Причини продажів (таблиця DimSalesReason) та Фактичні продажі через Інтернет (таблиця FactInternetSales) плюс група вимірювань для таблиці фактів FactInternetSalesReason. Міра може бути підрахунком рядків і прихована через властивість Visible міри. Після їх створення зв'язок "багато-до-багатьох" може бути створений у взаємозв'язку "Вимір куба" між виміром "Причина збуту" та групою "Інтернет-показник продажів", як на малюнку 3.

Рисунок 3: Відносини "багато-до-багатьох" з причини збуту

Нижче відношення Причина продажу до групи вимірювань Інтернет-продаж знаходиться Фактовий зв’язок між виміром Фактичний продаж Інтернету та групою вимірювань Інтернет-продаж. Таблиці фактів можуть мати розміри в службі аналізу та містити такі атрибути, як номер замовлення або вантажна компанія. Його також можна створити для цього відношення і зробити у кубі Visible = False. Причини збуту та факти Продажі в Інтернеті пов’язані з новою групою мір за допомогою регулярних зв’язків.

Рисунок 4: Аналіз у Excel - виправлено

На рисунку 4 тепер показано правильний підрахунок та суми в доларах під час аналізу в Excel. Загальна сума продажів як загальна сума - це правильна сума всіх відфільтрованих продажів. Але сума підсумків причин продажів у зведеній таблиці буде більше загальної суми через продажі, що мають кілька причин продажу. Навчання кінцевих користувачів про цю варіацію є важливим.

Таблична модель служб аналізу використовує нову функцію в SQL Server 2016, яка називається двонаправленою фільтрацією. Двоспрямована фільтрація використовується для багатьох-до-багатьох. Створення двонаправленої фільтрації призначене для фільтрації через одну таблицю, скажімо таблицю фактів, до агрегації в таблиці вимірів. Деякі можуть активувати це у будь-яких стосунках, але Microsoft попереджає не робити цього. Просто впроваджуйте там, де це потрібно.

Єдине багато-до-багатьох, з яким це буде працювати, це 3 таблиці багато-до-багатьох. На рисунку 5 показано Фактичні Інтернет-продажі, Фактичні Інтернет-продажі та Причини продажів у табличній моделі. Не використовуйте це, коли більше 3 таблиць задіяні у взаємозв'язку багато-до-багатьох.

Рисунок 5: Таблична причина "багато-до-багатьох"

У цьому прикладі не видно стовпців, що використовуються для відношення InternetSalesReason до InternetSales. Зв'язки в табличній моделі (і в Power BI) можуть складати лише один стовпець. Отже, у цьому прикладі використовується обчислюваний стовпець у таблиці InternetSales та InternetSalesReason. На рисунку 6 показано це співвідношення.

Рисунок 6: Взаємозв'язок "багато-до-багатьох" через Інтернет-продаж

Крім того, у спадному меню "Напрямок фільтра" відображається "До обох таблиць", а не до багатьох сторін відносин. Це варіант двонаправленої фільтрації. Стовпець, який використовується для зв’язку двох таблиць, називається AltKey. На рисунку 7 показано розрахунковий стовпець у таблиці InternetSalesReason.

Рисунок 7: Обчислюваний стовпець AltKey

Обчислюваний стовпець AltKey використовує цю логіку –InternetSalesReason [SalesOrderNumber] & “-” & InternetSalesReason [SalesOrderLineNumber]. NumberOrderNumber об'єднується з SalesOrderLineNumber з тире між двома значеннями. Цей стовпець створює альтернативний ключ для таблиць. Оскільки він створений як в InternetSales, так і в InternetSalesReason, їх можна використовувати для об'єднання 2 таблиць фактів. Пам’ятайте, таблиця InternetSalesReason - це міст між DimSalesReason та FactInternetSales.

Двонаправлену фільтрацію можна використовувати по-іншому. Говорить, що кінцевий користувач хоче переглянути кількість різних продуктів, пов’язаних зі списком продажів, за певний рік. Звичайні стосунки багато до одного не зможуть цього показати. На малюнку 8 показано нормальне відношення DimProduct до InternetSales.

Рисунок 8: Взаємозв'язок товару з InternetSales

На рисунку 9 показано виразний підрахунок товарів із фільтром за роком для Інтернет-продажів.

Рисунок 9: Чіткий рахунок за роком Інтернет-продажів

Виразний рахунок однаковий протягом усіх років, навіть років без будь-яких продажів. Зміна співвідношення у вигляді діаграми табличної моделі вирішить цю проблему, як на малюнку 10.

Рисунок 10: Двонаправлений фільтр для вимірювання продукту

На рисунку 11 показано результати аналізу в Excel за допомогою двонаправленої фільтрації у взаємозв'язку виробничої таблиці з таблицею InternetSales.

Рисунок 11: Аналіз двонаправленого фільтра в Excel

Служби Analysis Services змогли обробляти таку бізнес-логіку як MDX та DAX. Але користувачів більше цікавлять ці логічні бізнес-правила, які мають бути впроваджені в базу даних. Таблична модель починає все більше нагадувати Багатовимірний куб з кінцевим результатом, а не методом отримання результату. На щастя, в спільноті Microsoft Data Technology є багато користувачів, які бажають показати, як це працює. Слідкуйте за новинками, що з’являються в SQL Server 2017, і не відставайте від змін.