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

Забележка: Бихме искали да ви осигурим най-новото помощно съдържание възможно най-бързо на вашия собствен език. Тази страница е преведена чрез автоматизация и може да съдържа граматически грешки и несъответствия. Нашата цел е това съдържание да ви бъде полезно. Можете ли да ни кажете дали информацията е била полезна за вас, в дъното на тази страница? Ето статията на английски за бърза справка.

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

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

ИД_клиент

Име

Имейл

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

ИД_поръчка

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

Продукт

Количество

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 може да открие релациите между таблиците на базата на колоните, които са в [скоби], и може да възпроизведе тези релации в модел на данни, който се изгражда зад сцени. За повече информация вижте автоматично откриване и подразбиране на релациите в тази статия. Ако импортирате таблици от няколко източника, можете ръчно да създадете релации, както е описано в Създаване на релация между две таблици.

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

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

Въпреки че има много видове ключове, това са най-важните за нашата цел тук:

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

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

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

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

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

Тази таблица показва релациите между трите таблици (клиенти, CustomerDiscountsи поръчки):

Релация

Тип

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

Колона

Customers-CustomerDiscounts

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

Customers.CustomerID

CustomerDiscounts.CustomerID

Customers-Orders

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

Customers.CustomerID

Orders.CustomerID

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

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

Даден модел на данни може да съдържа няколко релации между две таблици. За да се изградят точни изчисления, Excel се нуждае от един път от една таблица към следващата. Затова само една релация между всяка двойка от таблици е активна по едно и също време. Въпреки че другите са неактивни, можете да зададете неактивна релация във формули и заявки.

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

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

Критерии

Описание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Уверете се, че името на колоната за ключове в много страни е подобно на името на колоната за ключове в справочната таблица. Имената не трябва да бъдат еднакви. Например в бизнес настройка често имате вариации на имената на колоните, които съдържат по същество едни и същи данни: ЕМП ИД, EmployeeID, ИД на служителя, EMP_IDи т. н. Алгоритъмът открива подобни имена и присвоява по-голяма вероятност за тези колони, които имат подобни или точно съответстващи имена. Следователно, за да увеличите вероятността за създаване на релация, можете да опитате да преименувате колоните в данните, които импортирате, в нещо подобно на колоните в съществуващите таблици. Ако Excel открие множество възможни релации, то не създава релация.

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

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

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

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

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

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

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

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

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

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

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

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

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

×