Урок: Разширяване на релациите в модела на данни чрез Excel, Power Pivot и DAX

Резюме: Това е вторият урок от поредицата. В първия урок "Импортиране на данни и създаване на модел на данни", беше създадена работна книга на Excel, като се използват данни, импортирани от множество източници.

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

Разделите в този урок са следните:

• Добавяне на релация с помощта на изгледа на диаграма в Power Pivot
• Разширяване на модела на данни с помощта на изчисляеми колони
• Create a hierarchy
• Използване на йерархии в обобщени таблици
• Контролна точка и тест

В края на този урок има тест, с който можете да проверите какво сте научили.

Тази серия използва данни, описващи олимпийски медали, страни домакини и различни олимпийски спортни състезания. Серията включва следните уроци:

1. Импортиране на данни в Excel и създаване на модел на данните
2. Разширяване на релациите в модел на данни с помощта на Excel, Power Pivot и DAX
3. Създаване на базирани на карта отчети на Power View
4. Включване на данни от интернет и задаване на настройки по подразбиране за отчетите на Power View
5. Помощ за Power Pivot
6. Създаване на удивителни отчети на Power View – част 2

Препоръчваме ви да следвате уроците подред.

Тези уроци използват Excel 2013 с активиран Power Pivot. За повече информация за Excel 2013 щракнете тук. За указания как да активирате Power Pivot, щракнете тук.

Добавяне на релация с помощта на изгледа на диаграма в Power Pivot

В този раздел използвате добавката Microsoft Office Power Pivot в Excel 2013, за да разширите модела. Използване на изгледа на диаграма в Microsoft SQL Server Power Pivot за Excel улеснява създаването на релации. Първо трябва да се уверите, че добавката Power Pivot е разрешена.

Забележка: Добавката Power Pivot в Microsoft Excel 2013 е част от Office Professional Plus. Вижте стартирането на добавката Power Pivot в Microsoft Excel 2013 за повече информация.

Добавете Power Pivot към лентата на Excel, като разрешите добавката Power Pivot

Когато Power Pivot е разрешен, виждате раздел на лентата в Excel 2013, наречен POWER PIVOT. За да разрешите Power Pivot, изпълнете стъпките по-долу.

1. Отидете на "ФАЙЛ > Опции > ", добавки.
2. В полето "Управление " в долната част щракнете върху "COM добавки> Go".
3. Отметнете квадратчето Microsoft Office Power Pivot в Microsoft Excel 2013 и след това щракнете върху OK.

Сега на лентата на Excel има раздел POWER PIVOT .

Добавяне на релация с помощта на изгледа на диаграма в Power Pivot

Работната книга на Excel съдържа таблица, наречена "Домакини". Импортирахме хостове, като ги копирахме и поставихме в Excel, след което форматирахме данните като таблица. За да добавим таблицата Hosts към модела на данни, трябва да установим релация. Нека използваме Power Pivot, за да представим визуално релациите в модела на данни, и след това да създадем релацията.

1. В Excel щракнете върху раздела "Домакини ", за да го направите активния лист.

2. На лентата изберете "POWER PIVOT > Таблици > – Добавяне към модела на данни". Тази стъпка добавя таблицата Hosts към модела на данни. Тя отваря и добавката Power Pivot, която използвате за извършване на оставащите стъпки в тази задача.

3. Обърнете внимание, че прозорецът на Power Pivot показва всички таблици в модела, включително "Хостове". Щракнете върху няколко таблици. В Power Pivot можете да видите всички данни, които съдържа вашият модел, дори ако те не се показват в работни листове в Excel, като например данните за дисциплините, състезанията и медалите по-долу, както и S_Teams, W_Teams и спорт.

   

4. В прозореца на Power Pivot, в секцията " Изглед " щракнете върху "Изглед на диаграма".

5. Използвайте лентата за слайдове, за да преоразмерите диаграмата, така че да можете да виждате всички обекти в нея. Пренаредете таблиците, като плъзнете заглавната лента, така че да са видими и разположени една до друга. Обърнете внимание, че четири таблици не са свързани с останалите таблици: "Домакини", "Събития", "W_Teams" и "S_Teams".

   

6. Ще забележите, че и таблицата "Медали", и таблицата "Състезания" имат поле, наречено "ДисциплинаСъбитие". При по-нататъшно разглеждане ще определите, че полето DisciplineEvent в таблицата "Събития " се състои от уникални, неповтарящи се стойности.

1. Създайте релация между таблицата "Медали" и таблицата "Състезания". Докато сте в изгледа на диаграма, плъзнете полето DisciplineEvent от таблицата Events в полето DisciplineEvent в Medals. Между тях се появява линия, което показва, че е установена връзка.

2. Щракнете върху линията, която свързва състезания и медали. Осветените полета дефинират релацията, както е показано на следващия екран.

   

3. За да свържем Hosts към модела на данни, ни трябва поле със стойности, които еднозначно идентифицират всеки ред в таблицата Hosts . След това можем да търсим в нашия модел на данни, за да видим дали същите данни съществуват в друга таблица. Гледането в изгледа на диаграма не ни позволява да направим това. Когато сте избрали "Хостове ", превключете обратно към "Изглед на данни".

4. След като разгледахме колоните, разбираме, че в "Домакини " няма колона с уникални стойности. Ще трябва да я създадем с помощта на изчисляема колона и изрази за анализ на данни (DAX).

Хубаво е, когато данните във вашия модел на данни имат всички полета, необходими за създаване на релации и смесване на данни, за визуализиране в Power View или обобщени таблици. Но таблиците не винаги са толкова кооперативни, така че следващият раздел описва как да създадете нова колона с помощта на DAX, която да може да се използва за създаване на релация между таблиците.

Разширяване на модела на данни с помощта на изчисляеми колони

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

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

Като прегледаме таблицата Hosts , а след това прегледаме други таблици от модела на данни, намираме добър кандидат за уникално поле, което можем да създадем в Hosts, и след това да го асоциираме с таблица в модела на данни. И двете таблици ще изискват нова изчисляема колона, за да отговарят на изискванията, необходими за установяване на релация.

В "Домакини" можем да създадем уникална изчисляема колона, като комбинираме полето "Издание" (годината на олимпийското състезание) и полето "Сезон" (лято или зима). В таблицата Medals има също поле Edition и поле Season, така че ако създадем изчисляема колона във всяка от тези таблици, която комбинира полетата Edition и Season, можем да установим връзка между домакини и медали. Следващият екран показва таблицата Hosts с избрани полета Edition и Season

        

Създаване на изчисляеми колони с помощта на DAX

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

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

1. В Power Pivot изберете НАЧАЛО > Изглед > на данни , за да сте сигурни, че е избран изглед на данни, а не изглед на диаграма.

2. Изберете таблицата Hosts в Power Pivot. До съществуващите колони има празна колона, озаглавена " Добавяне на колона". Power Pivot предоставя тази колона като контейнер. Има много начини за добавяне на нова колона в таблица в Power Pivot, един от които е просто да изберете празната колона със заглавието "Добавяне на колона".

   

3. Въведете следната DAX формула в лентата за формули. Функцията CONCATENATE комбинира две или повече полета в едно. Докато въвеждате, автодовършването ви помага да въведете пълните имена на колоните и таблиците и показва списък на функциите, които са налични. Използвайте Tab, за да изберете предложения за автодопълване. Можете също да щракнете върху колоната, докато въвеждате формулата, и Power Pivot вмъква името на колоната във формулата.

   =CONCATENATE([Edition],[Season])

4. Когато приключите със създаването на формулата, натиснете Enter, за да я приемете.

5. Стойностите се попълват за всички редове в изчисляемата колона. Ако превъртите надолу през таблицата, виждате, че всеки ред е уникален – затова успешно създадохме поле, което еднозначно идентифицира всеки ред в таблицата Hosts . Такива полета се наричат първичен ключ.

6. Нека преименуваме изчисляемата колона на EditionID. Можете да преименувате всяка колона, като щракнете двукратно върху нея или като щракнете с десния бутон върху колоната и изберете "Преименуване на колона". Когато приключи, таблицата Hosts в Power Pivot изглежда като следващия екран.

   

Таблицата Hosts е готова. След това нека създадем изчисляема колона в Medals , която съответства на формата на колоната EditionID, която създадохме в Hosts, така че да можем да създадем релация между тях.

1. Започнете със създаване на нова колона в таблицата "Медали ", както направихме за "Домакини". В Power Pivot изберете таблицата Medals и щракнете върху Design > Columns > Add. Обърнете внимание, че е избрано "Добавяне на колона ". Това има същия ефект като простото избиране на "Добавяне на колона".

2. Колоната "Издание" в Medals има различен формат от колоната Edition в Hosts. Преди да комбинираме или конкатенираме колоната Edition с колоната Season, за да създадем колоната EditionID, трябва да създадем междинно поле, което въвежда Edition в правилния формат. В лентата за формули над таблицата въведете следната DAX формула.

   = YEAR([Edition])
   
   

3. Когато приключите със създаването на формулата, натиснете клавиша Enter. Стойностите се попълват за всички редове в изчисляемата колона на базата на въведената от вас формула. Ако сравните тази колона с колоната "Издание" в "Хостове", ще видите, че тези колони са с еднакъв формат.

4. Преименувайте колоната, като щракнете с десния бутон върху CalculatedColumn1 и изберете "Преименуване на колона". Въведете Year и след това натиснете клавиша Enter.

5. Когато сте създали нова колона, Power Pivot е добавил друга колона-контейнер, наречена "Добавяне на колона". След това искаме да създадем изчисляемата колона EditionID, затова избираме "Добавяне на колона". В лентата за формули въведете следната DAX формула и натиснете клавиша Enter.

   =CONCATENATE([Year],[Season])

6. Преименувайте колоната, като щракнете двукратно върху CalculatedColumn1 и въведете EditionID.

7. Сортирайте колоната във възходящ ред. Таблицата Medals в Power Pivot сега изглежда като следващия екран.

   

Забележете, че много стойности се повтарят в полето EditionID на таблицата Medals . Това е добре и очаквано, тъй като по време на всяко издание на Олимпийските игри (сега представено със стойността EditionID) бяха връчени много медали. Уникалното в таблицата с медали е всеки награден медал. Еднозначният идентификатор за всеки запис в таблицата Medals и неговият определен първичен ключ е полето MedalKey.

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

Създаване на релация с помощта на изчисляеми колони

След това нека използваме изчислените колони, които създадохме, за да установим връзката между домакини и медали.

1. В прозореца на Power Pivot изберете "Изглед на начален >> изглед " от лентата. Можете също да превключвате между изглед на мрежа и изглед на диаграма с помощта на бутоните в дъното на прозореца на PowerView, както е показано на следващия екран.

   

2. Разгънете "Домакини", така че да можете да виждате всичките му полета. Създадохме колоната EditionID, която да действа като първичен ключ на таблицата Hosts (уникално, неповтарящо се поле), и създадохме колона EditionID в таблицата Medals , за да направим възможно установяването на релация между тях. Трябва да ги намерим и двете и да създадем връзка. Power Pivot предоставя функция " Търсене " в лентата, така че да можете да търсите съответни полета във вашия модел на данни. Следващият екран показва прозореца "Търсене на метаданни", с EditionID, въведен в полето "Търси".
   

3. Позиционирайте таблицата "Домакини ", така че да е до таблицата "Медали".

4. Плъзнете колоната EditionID в Medals в колоната EditionID в Hosts. Power Pivot създава релация между таблиците въз основа на колоната EditionID и начертава линия между двете колони, указваща релацията.

   

В този раздел научихте нова техника за добавяне на нови колони, създадохте изчисляема колона с помощта на DAX и използвахте тази колона, за да създадете нова релация между таблиците. Таблицата Hosts сега е интегрирана в модела на данни, а данните й са достъпни за обобщената таблица в Лист1. Можете също да използвате свързаните данни за създаване на допълнителни обобщени таблици, обобщени диаграми, отчети на Power View и много други.

Create a hierarchy

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

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

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

Създаване на йерархия в Sport

1. В Power Pivot превключете към изглед на диаграма. Разширете таблицата "Събития ", за да можете по-лесно да виждате всичките й полета.

2. Натиснете и задръжте Ctrl и щракнете върху полетата Sport, Discipline и Event. Когато сте избрали тези три полета, щракнете с десния бутон и изберете Създаване на йерархия. В дъното на таблицата се създава възел на родителската йерархия, Йерархия 1, и избраните колони се копират в йерархията като дъщерни възли. Уверете се, че Sport се появява първо в йерархията, след това Discipline, след това Event.

3. Щракнете двукратно върху заглавието "Йерархия1" и въведете SDE , за да преименувате новата си йерархия. Сега имате йерархия, която включва "Спорт", "Дисциплина" и "Състезание". Сега таблицата "Събития" изглежда като следващия екран.

   

Създаване на йерархия на местоположението

1. Докато сте още в изглед на диаграма в Power Pivot, изберете таблицата "Хостове " и щракнете върху бутона "Създаване на йерархия" в заглавката на таблицата, както е показано на следващия екран.
   

   Празен родителски възел на йерархията се появява в дъното на таблицата.

2. Въведете "Местоположения " като име за вашата нова йерархия.

3. Има много начини за добавяне на колони към йерархия. Плъзнете полетата за сезон, град и NOC_CountryRegion върху името на йерархията (в този случай "Местоположения"), докато името на йерархията не се освети, след което освободете, за да ги добавите.

4. Щракнете с десния бутон върху EditionID и изберете "Добави към йерархията". Изберете местоположения.

5. Уверете се, че дъщерните възли на йерархията са в ред. Отгоре-надолу редът трябва да бъде: Season, NOC, City, EditionID. Ако дъщерните ви възли не са подредени, просто ги плъзнете в подходящия ред в йерархията. Вашата таблица трябва да изглежда както на следващия екран.
   

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

Използване на йерархии в обобщени таблици

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

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

1. Върнете се в Excel.
2. В Лист1 премахнете полетата от областта "РЕДОВЕ" на "Полета на обобщената таблица", след което премахнете всички полета от областта "КОЛОНИ". Уверете се, че е избрана обобщената таблица (която сега е доста малка, така че можете да изберете клетка A1, за да сте сигурни, че е избрана обобщената таблица). Единствените оставащи полета в обобщената таблица са Medal в областта "ФИЛТРИ" и "Count of Medal" в областта "СТОЙНОСТИ". Вашата почти празна обобщена таблица трябва да изглежда както на следващия екран.
   
3. От областта "Полета на обобщената таблица" плъзнете SDE от таблицата "Събития " в областта "РЕДОВЕ". След това плъзнете "Местоположения" от таблицата "Домакини" в областта "КОЛОНИ". Просто като плъзнете тези две йерархии, вашата обобщена таблица се попълва с множество данни, всички от които са подредени в йерархията, която дефинирахте в предишните стъпки. Екранът ви трябва да изглежда като следващия екран.
   
4. Нека филтрираме малко тези данни и да видим само първите десет реда на събитията. В обобщената таблица щракнете върху стрелката в "Етикети на редове", щракнете върху (Избери всички), за да изчистите всичко избрано, след което щракнете върху квадратчетата до първите десет вида спорта. Сега обобщената таблица изглежда както на следващия екран:
   
5. Можете да разгънете всеки от тези спортове в обобщената таблица, която е най-високото ниво на йерархията на SDE, и да видите информацията в следващото ниво надолу в йерархията (дисциплината). Ако за тази дисциплина съществува по-ниско ниво в йерархията, можете да разширите дисциплината, за да видите нейните събития. Можете да направите същото за йерархията на местоположенията, чието най-горно ниво е Season, което в обобщената таблица се показва като Summer и Winter. Когато разширим спорта по водни спортове, виждаме всички негови елементи на детската дисциплина и техните данни. Когато разширим дисциплината Diving под Aquatics, виждаме и нейните дъщерни събития, както е показано на следващия екран. Можем да направим същото за водната топка и да видим, че има само едно събитие.
   

Плъзгайки тези две йерархии, бързо създавате обобщена таблица с интересни и структурирани данни, които можете да задълбочавате, филтрирате и подреждате.

Нека сега да създадем същата обобщена таблица, без преимуществата от йерархиите.

1. В областта "Полета на обобщената таблица" премахнете "Местоположения" от областта "КОЛОНИ". След това премахнете SDE от областта РЕДОВЕ. Връщате се към основната обобщена таблица.
2. От таблицата Hosts плъзнете Season, City, NOC_CountryRegion и EditionID в областта COLUMNS и ги подредете в този ред, от горе надолу.
3. От таблицата Events плъзнете Sport, Discipline и Event в областта РЕДОВЕ и ги подредете в този ред, от горе надолу.
4. В обобщената таблица филтрирайте етикетите на редовете до десетте най-добри спорта.
5. Свийте всички редове и колони, след това разгънете Водни спортове, след това Гмуркане и Водна топка. Работната книга изглежда както на следващия екран.
   

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

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

Контролна точка и тест

Преглед на наученото

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

Научихте, че създаването на йерархии ви позволява да зададете присъщата структура на вашите данни и бързо да използвате йерархични данни във вашите отчети.

В следващия урок от тази серия създавате визуално завладяващи отчети за олимпийски медали с помощта на Power View. Освен това извършвате повече изчисления, оптимизирате данните за бързо създаване на отчети и импортирате допълнителни данни, за да направите тези отчети още по-интересни. Ето една връзка:

Урок 3: Създаване на базирани на карта отчети на Power View

ТЕСТ

Искате да проверите какво сте запомнили от изучаваното? Ето вашия шанс. Следващият тест набляга на функции, възможности или изисквания, с които се запознахте в този урок. Най-долу на страницата ще намерите отговорите. Успех!

Въпрос 1: Кои от следните изгледи ви позволяват да създадете релации между две таблици?

О: Създавате релации между таблиците в Power View.

Б: Създавате релации между таблиците с помощта на изгледа за проектиране в Power Pivot.

В: Създавате релации между таблиците с помощта на изгледа на мрежа в Power Pivot

Г: Всички от горните

Въпрос 2: TRUE или FALSE: Можете да установявате релации между таблиците въз основа на уникален идентификатор, създаден с помощта на формули на DAX.

О: TRUE

Б: НЕВЯРНО

Въпрос 3: В кое от следните можете да създадете DAX формула?

А: В областта за изчисляване на Power Pivot.

Б: В нова колона в Power Pivotf.

В: В произволна клетка в Excel 2013.

Г: И А, и Б.

Въпрос 4: Кое от следните е вярно за йерархиите?

А: Когато създавате йерархия, включените полета вече не са налични поотделно.

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

В: Когато създавате йерархия, базовите данни в модела на данни се комбинират в едно поле.

Г: Не можете да създавате йерархии в Power Pivot.

Отговори на теста

1. Правилен отговор: Г
2. Правилен отговор: А
3. Правилен отговор: Г
4. Правилен отговор: Б