Релации между таблици в модел на данни

Вашият браузър не поддържа видео. Инсталирайте Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.
Видео: Релации в Power View и Power Pivot

Направете анализа на данни по-задълбочен, като създадете релации между данните в отделните таблици. Релация означава връзка между две таблици с данни, базирани на по една колона от двете таблици. За да видите защо релациите са полезни, представете си, че проследявате данни за поръчки на клиенти на вашия бизнес. Може да проследите всички данни в една единствена таблица, която има структура като следната:

ИД_клиент

Име

Имейл

Процент_отстъпка

ИД_поръчка

Дата_поръчка

Продукт

Количество

1

Христов

plamen.hristov@contoso.com

0,05

256

07.01.2010

Компактни цифрови

11

1

Христов

plamen.hristov@contoso.com

0,05

255

03.01.2010

Огледално-рефлексни фотоапарати

15

2

Димитров

borislav.dimitrov@contoso.com

0,10

254

03.01.2010

Бюджетни видеокамери

27

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

Клиенти

[ИД_клиент]

Име

Имейл

1

Христов

plamen.hristov@contoso.com

2

Димитров

borislav.dimitrov@contoso.com

Отстъпки_клиент

[ИД_клиент]

Процент_отстъпка

1

0,05

2

0,10

Поръчки

[ИД_клиент]

ИД_поръчка

Дата_поръчка

Продукт

Количество

1

256

07.01.2010

Компактни цифрови

11

1

255

03.01.2010

Огледално-рефлексни фотоапарати

15

2

254

03.01.2010

Бюджетни видеокамери

27

Релации съществуват в модел на данни, създаден от вас или създаден от Excel от ваше име, когато импортирате няколко таблици едновременно. Освен това можете да използвате добавката Power Pivot, за да създадете или управлявате модела. За повече подробности вж. Създаване на модел на данни в Excel.

Ако използвате добавката на Power Pivot, за да импортирате таблици от същата база данни, Power Pivot може да открие релациите между таблиците, базирани на колоните, които са в [скоби], и да възпроизведе тези релации в модела на данни, който бива създаден на заден план. За повече информация вж. Автоматично откриване и подразбиране на релациите в тази статия. Ако импортирате таблици от няколко източника, може ръчно да създадете релации, както е описано в Създаване на релация между две таблици.

Най-горе на страницата

В тази статия

Колони и ключове

Типове релации

Релации и представяне

Множество релации между таблици

Изисквания към релация на таблица

Не се поддържа в релация на таблица

Комбинирани ключове и справочни колони

Релации тип "много към много"

Рефлексивни съединения и цикли

Автоматично откриване и подразбиране на зависимости в Power Pivot

Автоматично откриване на наименувани набори

Подразбиране на релации

Колони и ключове

Релациите са базирани на колони във всяка таблица, която съдържа еднакви данни. Например таблици "Клиенти" и "Поръчки" може да се свържат една с друга, тъй като и двете съдържат колона с клиентския ИД. В примера имената на колоните са същите, но това не е задължително. Едната може да се казва CustomerID, а другата CustomerNumber, стига всички редове в таблица "Поръчки" да съдържат ИД като този в таблица "Клиенти".

В една релационна база данни има няколко типа ключове, които обикновено представляват колони със специални свойства. Познанията относно предназначението на всеки ключ може да ви помогнат да управлявате модел на данни, съдържащ няколко таблици, който предоставя данни на обобщена таблица, обобщена диаграма или отчет на Power View.

Следните ключове са от най-голяма важност за нашите цели:

  • Основен ключ: уникално идентифицира ред в таблица, като например CustomerID в таблица "Клиенти".

  • Алтернативен ключ (или ключ кандидат): колона, различна от основния ключ, който е уникален. Например таблица "Служители" може да съхранява ИД на служителите и техния социално-осигурителен номер, като и двата са уникални.

  • Чужд ключ: колона, която се обръща към уникална колона в друга таблица, като например CustomerID в таблица "Поръчки", която се обръща към CustomerID в таблица "Клиенти".

В модела на данни основният ключ или алтернативният ключ се наричат свързана колона. Ако една таблица съдържа основен и алтернативен ключ, може да използвате който и да е от двата като основа за релация на таблица. Чуждият ключ се нарича колона източник или просто колона. В нашия пример зависимост ще се дефинира между CustomerID в таблица "Поръчки" (колоната) и CustomerID в таблица "Клиенти" (справочната колона). Ако импортирате данни от релационни база данни, Excel по подразбиране избира чуждия ключ от едната таблица и съответстващия основен ключ от другата таблица. Вие обаче можете да използвате всяка колона, която съдържа уникални стойности за справочната колона.

Избира диапазона на показваните дати

Релацията между "Клиенти" и "Поръчки" е релация от тип "един към много". Всеки клиент има няколко поръчки, но една поръчка не може да има няколко клиента. Другите видове релации са тип "един към един" и "много към много". Таблицата CustomerDiscounts, която дефинира една отстъпка за всеки клиент, е свързана с таблица "Клиенти" със релация тип "един към един".

Следната таблица показва релациите между трите таблици:

Релация

Тип

Справочна колона

Колона

Customers-CustomerDiscounts

"един към един"

Customers.CustomerID

CustomerDiscounts.CustomerID

Customers-Orders

"един към много"

Customers.CustomerID

Orders.CustomerID

Забележка :  Релациите тип "много към много" не се поддържат в модел на данни. Пример за релация тип "много към много" е пряката релация между "Продукти" и "Клиенти", в която един клиент може да купи много продукти и един и същ продукт да бъде продаден на много клиенти.

Релации и представяне

След създаването на релация обикновено Excel трябва да пресметне отново всички формули, които използват колони от таблиците в новосъздадената релация. Обработването може да отнеме известно време в зависимост от обема на данните и сложността на релациите. За повече подробности вж. Преизчисляване на формули.

Множество релации между таблици

Даден модел на данни може да съдържа няколко релации между две таблици. За да създаде верни изчисления, Excel се нуждае от единствен път от една таблица до друга. Затова само една релация между всяка двойка от таблици е активна по едно и също време. Останалите не са активни, но може да указвате неактивна релация във формули и заявки. В изглед "Диаграма" активната релация се отбелязва с непрекъсната линия, а неактивните – с прекъсната. Например в AdventureWorksDW2012 таблица DimDate съдържа колона DateKey, която се обръща към три различни колони в таблица FactInternetSales: OrderDate, DueDate и ShipDate. Ако активната релация е между DateKey и OrderDate, тя ще бъде релацията, използвана по подразбиране във формули, освен ако не укажете друго.

Най-горе на страницата

Изисквания към релация на таблица

Дадена релация може да бъде създадена, ако бъдат изпълнени следните изисквания:

Критерии

Описание

Уникален идентификатор за всяка таблица

Всяка таблица трябва да има една колона, която уникално да идентифицира всеки ред в тази таблица. Колоната често се нарича основен ключ.

Уникални справочни колони

Стойностите на данните в справочната колона трябва да бъдат уникални. С други думи, колоната не може да съдържа дублиращи се стойности. В модела на данни нулите и празните низове са равностойни на празни стойности, които представляват различни стойности на данни. Това означава, че не може да има множество нули в справочната колона.

Съвместими типове данни

Типовете данни в колоната източник и справочната колона трябва да са съвместими. За повече информация относно типовете данни вж. Типове данни, поддържани в моделите на данни.

Не се поддържа в релация на таблица

В модела на данни не може да се създава релация на таблица, ако ключът е комбиниран. Освен това сте ограничени при създаването на релации от типа "един към един" и "един към много". Не се поддържат други типове релации.

Комбинирани ключове и справочни колони

Комбинираният ключ е съставен от повече от една колона. Моделите на данни не могат да използват комбинирани ключове; една таблица винаги трябва да съдържа точно една колона, която уникално да идентифицира всеки ред в таблицата. Ако импортирате таблици, които съдържат съществуваща релация, базирани на комбиниран ключ, съветникът за импортиране на таблици в Power Pivot ще игнорира релацията, тъй като тя не може да бъде създадена в модела.

За да създадете релация между две таблици, които съдържат множество колони, дефиниращи основния и чуждия ключ, първо комбинирайте стойностите, за да създадете една колона с ключ, преди да създадете релацията. Може да направите това, преди да импортирате данните или като създадете изчисляема колона в модела на данни, използвайки добавката Power Pivot.

Релации тип "много към много"

Модел на данни не може да съдържа релации тип "много към много". Не може просто да добавите съединителни таблици в модела. Въпреки това може да използвате функции DAX, за да моделирате релации тип "много към много".

Рефлексивни съединения и цикли

Рефлексивните съединения не са позволени в модела на данни. Рефлексивно съединение представлява рекурсивна релация между една таблица и нея самата. Рефлексивните съединения често се използват за дефиниране на йерархии от тип родител-наследник. Например може да съедините таблица "Служители" с нея самата, за да получите йерархия, която да показва управленската верига в даден бизнес.

Excel не позволява създаването на цикли между релации в една работна книга. С други думи, следният набор от релации е забранен.

  • Таблица 1, колона а   към   Таблица 2, колона f

  • Таблица 2, колона f   към   Таблица 3, колона n

  • Таблица 3, колона n   към   Таблица 1, колона а

Ако опитате да създадете зависимост, която ще създаде цикъл, се генерира грешка.

Най-горе на страницата

Автоматично откриване и подразбиране на релации в Power Pivot

Едно от предимствата на импортирането на данни посредством добавката Power Pivot е, че понякога Power Pivot може да открива релации и да създава нови такива в модела на данни, който създава в Excel.

Когато импортирате множество таблици, Power Pivot автоматично открива всички съществуващи релации между таблиците. Също така, когато създавате обобщена таблица, Power Pivot анализира данните в таблиците. Той открива възможни релации, които не са били дефинирани, и предлага подходящи колони, които да се включат в тези релации.

Алгоритъмът за откриване използва статистически данни за стойностите и метаданните на колоните, за да направи изводи за вероятността на релациите.

  • Типовете данни във всички свързани колони трябва да са съвместими. За автоматично откриване се поддържат само цели числа и текстови типове данни. За повече информация относно типове данни вж. Типове данни, поддържани в модели на данни.

  • За да бъде открита една релация успешно, броят на уникалните ключове в справочната колона трябва да бъде по-голям от стойностите в таблицата в страна "много". С други думи, ключовата колона в страна "много" на зависимостта не трябва да съдържа стойности, които не са в ключовата колона на справочната таблица. Например представете си, че имате таблица, която изброява продукти с техните ИД (справочната таблица) и таблица с продажби, която изброява продажбите за всеки продукт (страна "много" на релацията). Ако списъкът с продажби съдържа ИД на продукта, който няма съответстващ ИД в таблица "Продукти", релацията не може да бъде създадена автоматично, а вие вероятно ще трябва да я създадете ръчно. За да може Excel да открие релацията, трябва първо да актуализирате справочна таблица "Продукти" с ИД на липсващите продукти.

  • Убедете се, че името на ключовата колона в страна "много" е подобно на името на ключовата колона в справочната таблица. Имената не трябва да бъдат напълно еднакви. Например в бизнес-среда често трябва да има вариации на имената на колоните, които съдържат общо взето едни и същи данни: Emp ID, EmployeeID, Employee ID, EMP_ID и т.н. Алгоритъмът открива подобни имена и назначава по-голяма вероятност към тези колони, които имат подобни или едни и същи имена. Затова, за да увеличите вероятността за създаване на релация, може да опитате да преименувате колоните в данните, които импортирате, с нещо, което е подобно на колоните във вече съществуващите таблици. Ако Excel открие множество възможни релации, тогава не създава релация.

Тази информация може да ви помогне да разберете защо не всички релации се откриват или как промените в метаданните – като имена на полета и типове данни – могат да подобрят резултатите от автоматичното откриване на релации. За повече информация вж. Отстраняване на неизправности при релации.

Автоматично откриване на наименувани набори

Релациите не се откриват автоматично между наименувани набори и свързани полета в обобщена таблица. Може да създадете тези релации ръчно. Ако искате да използвате автоматичното откриване на релации, премахнете всеки наименуван набор и добавете индивидуални полета от наименувания набор директно към обобщената таблица.

Подразбиране на релации

В някои случаи релациите между таблиците се свързват автоматично. Например ако създадете релация между първите два набора от таблиците по-долу, подразбира се, че съществува релация между другите две таблици и релацията се създава автоматично.

  • Продукти и категория – създадена ръчно

  • Категория и подкатегория – създадена ръчно

  • Продукти и подкатегория – зависимостта се подразбира

За да се свържат автоматично релациите, те трябва да вървят последователно, както е указано по-горе. Ако първоначалните релации бяха, например между "Продажби" и "Продукти" и "Продажби" и "Клиенти", релация няма да бъде подразбрана. Това е така, защото релацията между "Продукти" и "Клиенти" е релация от тип "много към много".

Най-горе на страницата

Разширете уменията си
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

Беше ли полезна тази информация?

Благодарим ви за обратната връзка!

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×