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

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

• Добавяне на релация с помощта на изглед на диаграма в 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 добавки> Започване.

3. Поставете отметка в квадратчето Microsoft Office Power Pivot в Microsoft Excel 2013 и след това щракнете върху OK.

Лентата на Excel вече има раздел POWER PIVOT .

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

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

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

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

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

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

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

6. Забелязвате, че както таблицата Medals , така и таблицата Events имат поле, наречено DisciplineEvent. След допълнителна проверка вие определяте, че полето DisciplineEvent в таблицата Events се състои от уникални, неповтарящи се стойности.

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

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

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

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

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

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

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

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

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

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

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

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

В 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 комбинира две или повече полета в едно. Докато въвеждате, автодовършването ви помага да въведете напълно квалифицираните имена на колоните и таблиците, както и да изреждате наличните функции. Използвайте раздела, за да изберете предложения за автодовършване. Можете също просто да щракнете върху колоната, докато въвеждате формулата, и Power Pivot вмъква името на колоната във формулата.
   
   =CONCATENATE([Edition],[Season])

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

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

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

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

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

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

   = YEAR([Edition])
   

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

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

5. Когато сте създали нова колона, Power Pivot добавили друга колона контейнер, наречена Добавяне на колона. След това искаме да създадем изчисляемата колона EditionID, така че изберете Добавяне на колона. В лентата за формули въведете следната DAX формула и натиснете клавиша Enter.
   
   =CONCATENATE([Year],[Season])

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

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

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

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

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

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

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

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

3. Позиционирайте таблицата Hosts , така че да е до Medals.

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

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

Create a hierarchy

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

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

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

Създаване на йерархия "Спорт"

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Върнете се в Excel.

2. В Лист1 премахнете полетата от областта РЕДОВЕ на Полета на обобщената таблица, след което премахнете всички полета от областта КОЛОНИ. Уверете се, че обобщената таблица е избрана (която сега е доста малка, така че можете да изберете клетка A1, за да сте сигурни, че обобщената таблица е избрана). Единствените оставащи полета в полетата на обобщената таблица са Medal в областта FILTERS и Брой медали в областта СТОЙНОСТИ. Вашата почти празна обобщена таблица трябва да изглежда като следващия екран.

   

3. От областта Полета на обобщената таблица плъзнете SDE от таблицата Events в областта РЕДОВЕ. След това плъзнете Местоположения от таблицата Hosts в областта КОЛОНИ . Просто чрез плъзгане на тези две йерархии обобщената таблица се попълва с много данни, всички от които са подредени в йерархията, която дефинирахте в предишните стъпки. Екранът ви трябва да изглежда като следващия екран.
   

4. Нека филтрираме данните малко и просто видим първите десет реда със събития. В обобщената таблица щракнете върху стрелката в Етикети на редове, щракнете върху (Избери всички), за да премахнете всички селекции, след което щракнете върху полетата до първите десет Sports. Обобщената таблица сега изглежда като следващия екран.
   

5. Можете да разширите всяко от тези "Спорт" в обобщената таблица, което е най-високото ниво на SDE йерархията, и да виждате информацията на следващото ниво надолу в йерархията (дисциплина). Ако съществува по-ниско ниво в йерархията за тази дисциплина, можете да разширите дисциплината, за да видите нейните събития. Можете да направите същото за йерархията "Местоположение", чието най-високо ниво е Season, което се показва като Лято и Зима в обобщената таблица. Когато разширим водния спорт, виждаме всички елементи на детската дисциплина и техните данни. Когато разширим дисциплината "Гмуркане" под "Водни спортове", ще видим и детските събития, както е показано на следващия екран. Можем да направим същото за Water Polo и да видим, че има само едно събитие.
   

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

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

1. В областта Полета на обобщената таблица премахнете Местоположения от областта КОЛОНИ. След това премахнете SDE от областта РЕДОВЕ. Връщате се към основна обобщена таблица.

2. От таблицата Hosts плъзнете Season, City, NOC_CountryRegion и EditionID в областта COLUMNS и ги подредете в този ред, отгоре надолу.

3. От таблицата Events плъзнете Sport, Discipline и Event в областта ROWS и ги подредете в този ред– отгоре надолу.

4. В обобщената таблица филтрирайте етикетите на редовете до десетте най-високи sports.

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

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

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

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

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

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

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

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

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

ТЕСТ

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

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

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

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

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

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

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

A: TRUE

Б: НЕВЯРНО

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

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

Б: В нова колона в Power Pivot f.

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

Г: И A, и B.

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

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

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

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

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

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

1. Правилен отговор: Г

2. Правилен отговор: A

3. Правилен отговор: Г

4. Правилен отговор: Б