Урок: Разширяване на релациите в модела на данни чрез 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 OfficePower Pivot в Microsoft Excel 2013, след което щракнете върху OK.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

   = YEAR([Edition])
   

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3. Щракнете двукратно върху заглавието, Hierarchy1, след което въведете SDE, за да преименувате своята нова йерархия. Вече имате йерархия, която включва Sport, Discipline и Event. Таблицата Events вече изглежда като на екрана по-долу.
   
   

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

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

2. Въведете Locations за име на вашата нова йерархия.

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

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

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

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

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

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

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

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

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

   

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

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

5. Можете да разширите някои от тези спортове в обобщената таблица, която е най-горното ниво на йерархията на SDE, и посочва в следващото ниво надолу в йерархията (дисциплина). Ако по-ниско ниво в йерархията на съществува за тази дисциплина, можете да разширите дисциплина, за да видите своите събития. Можете да направите същото местоположение йерархия, най-горното ниво на което е Season, който се показва като лято и зима в обобщената таблица. Когато разширяваме олимпийски спортове, ние виждаме всичките му дъщерни елементи дисциплина и техните данни. Когато разширяваме гмуркане дисциплина под водни, ние виждаме неговите дъщерни събития също така, както е показано на следващия екран. Да Направете същото за вода Поло и вижте дали има само едно събитие.
   

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

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

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

2. От таблицата Hosts плъзнете Season, City, NOC_CountryRegion и EditionID в областта КОЛОНИ, след което ги подредете в този ред – от горе надолу.

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: ВЯРНО или ГРЕШНО: Можете да установявате релации между таблици въз основа на еднозначен идентификатор, който е създаден с помощта на DAX формули.

А: ВЯРНО

Б: НЕВЯРНО

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

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

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

В: Във всяка клетка на Excel 2013.

Г: И "А", и "Б".

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

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

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

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

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

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

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

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

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

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