Създаване на ефективен по отношение на паметта модел на данни чрез Excel и добавката Power Pivot

Важно : Тази статия е преведена машинно – вижте отказа от отговорност. Английската версия на тази статия за справка можете да намерите тук .

В Excel 2013 или по-късно можете да създадете модели на данни, които съдържат милиони редове и след това извършете мощен анализ на данни срещу тези модели. Модели на данни могат да бъдат създадени със или без Power Pivot добавка, за да поддържа произволен брой обобщени таблици, диаграми и визуализации на Power View в същата работна книга.

Забележка : Тази статия описва модели на данни в Excel 2013. Въпреки това същите данни моделиране и Power Pivot функции, въведени в Excel 2013 приложат също и за Excel 2016. Има ефективно малка разлика между тези версии на Excel.

Макар че можете лесно да изградите огромни модели на данни в Excel, има няколко причини да не го правите. Първо, големите модели, съдържащи множество таблици и колони, поставят под въпрос ефективността на повечето анализи и водят до претрупан списък на полетата. Второ, големите модели използват ценна памет, като влияят отрицателно на други приложения и отчети, които споделят същите системни ресурси. Накрая, в Office 365 както SharePoint Online, така и Excel Web App ограничават размера на файловете на Excel до 10 МБ. За модели на данни на работна книга, които съдържат милиони редове, ще достигнете лимита от 10 МБ доста бързо. Вж. Спецификации и ограничения на модела на данни.

В тази статия ще научите как да изградите сбит модел, с който се работи по-лесно и който използва по-малко памет. Времето, което отделите, за да се запознаете с най-добрите практики за ефективно проектиране на модели, ще ви се отплати впоследствие при всеки модел, който създавате или използвате, независимо дали го преглеждате в Excel 2013, в SharePoint Online в Office 365, в Office Web Apps Server, или в SharePoint 2013.

Помислете и за използване на оптимизатора на размера на работни книги. Той анализира вашата работна книга на Excel и ако е възможно, я компресира допълнително. Изтеглете оптимизатора на размера на работни книги.

В тази статия

Коефициенти на компресиране и аналитичното във вътрешната памет ядро

Нищо удара несъществуваща колона за ниска памет

Два примера за колони, които винаги трябва да бъдат изключвани

Как се изключват ненужни колони

Възможно ли е филтриране само на необходимите редове?

Какво става, ако трябва колона; да ние все още се намали пространството, използвано?

Промяна на колони за дата и час

Промяна на SQL заявката

Използване на изчисляеми DAX мерки вместо колони

Кои две колони трябва да запазите?

Заключение

Сродни връзки

Коефициенти на компресиране и аналитичното ядро във вътрешната памет

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

Най-често, можете да очаквате модел на данни да бъде от седем до десет пъти по-малък от същите данни в техния източник. Например ако импортирате данни с размер 7 МБ от база данни на SQL Server, моделът на данните в Excel може лесно да стане 1 МБ или по-малко. В действителност постигнатото ниво на компресиране зависи основно от броя на уникалните стойности във всяка колона. Колкото повече са уникалните данни, толкова повече памет е необходима за тяхното съхраняване.

Защо говорим за компресиране и за уникални стойности? Защото изграждането на ефективен модел, който свежда до минимум използването на памет, се основава на максимизиране на компресирането, а най-лесният начин за това е да премахнете колоните, които не са ви необходими, особено ако тези колони включват голям брой уникални стойности.

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

Нищо не е по-ефективно по отношение на използването на памет от една несъществуваща колона

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

Два примера за колони, които би трябвало винаги да бъдат изключвани

Първият пример се отнася за данни, които произлизат от хранилище за данни. В хранилище за данни обикновено могат да бъдат намерени артефакти на ETL процеси, които зареждат и обновяват данните в хранилището. Колони като "create date", "update date" и "ETL run" се създават, когато данните се зареждат. Тези колони не са необходими в модела и трябва да бъдат изключени от селекцията, когато импортирате данните.

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

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

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

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

Как се изключват ненужни колони

Ефективните модели съдържат само колоните, които ще са ви необходими във вашата работна книга. Ако искате да контролирате кои колони да бъдат включени в модела, ще трябва да използвате съветника за импортиране на таблици на добавката Power Pivot, за да импортирате данните, вместо диалоговия прозорец "Импортиране на данни" на Excel.

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

Съветник за импортиране на таблици в добавката PowerPivot

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

Екран за визуализация в съветника за импортиране на таблици

Възможно ли е филтриране само на необходимите редове?

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

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

Екран за филтриране в съветника за импортиране на таблици

Възможно ли е да се намали мястото, използвано от колона, която е необходима?

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

Промяна на колони за дата и час

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

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

  • Необходима ли ми е частта за час?

  • На какво ниво ми е необходима частта за час: часове, минути, секунди, милисекунди?

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

Отговорите на тези въпроси определят опциите ви за обработка на колоната за дата и час.

Всички тези решения изискват промяна на SQL заявка. За да улесните промяната на заявката, трябва да филтрирате поне една колона във всяка таблица. Чрез филтрирането на колона вие променяте структурата на заявката от съкратен формат (SELECT *) в команда SELECT, включваща пълните имена на колоните, които могат да бъдат променени много по-лесно.

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

Лента в прозореца на PowerPivot, показваща командата ''Свойства на таблица''

От "Свойства на таблица" изберете Редактор на заявки.

Отваряне на редактора на заявки от диалоговия прозорец за свойства на таблица

Редакторът на заявки показва SQL заявката, използвана за попълване на таблицата. Ако сте филтрирали колона по време на импортирането, вашата заявка ще включва пълните имена на колоните:

SQL заявка, използвана за извличане на данните

За разлика от това, ако сте импортирали таблица в нейната цялост, без да изключвате колони и без да прилагате филтър, ще видите заявката като "Select * from", която би била много по-трудна за промяна:

SQL заявка, използваща по-краткия синтаксис, който е зададен по подразбиране

Промяна на SQL заявката

Сега, след като знаете как да намерите заявката, можете да я промените, за да намалите още повече размера на своя модел.

  1. За колони, които съдържат данни за валутата или дробната част, ако нямате нужда от дробните части, използвайте следния синтаксис, за да ги премахнете:

    "SELECT ROUND([Decimal_column_name];0)… ."

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

  2. Ако имате колона за дата и час, наречена dbo.Bigtable.[Date Time], и нямате нужда от частта за час, използвайте следния синтаксис, за да премахнете часа:

    "SELECT CAST (dbo.Bigtable.[Date time] as date) AS [Date time])"

  3. Ако имате колона за дата и час, наречена dbo.Bigtable.[Date Time], и имате нужда както от частта за дата, така и от частта за час, използвайте няколко колони в SQL заявката вместо общата колона за дата и час:

    "SELECT CAST (dbo.Bigtable.[Date Time] as date ) AS [Date Time],

    datepart(hh, dbo.Bigtable.[Date Time]) as [Date Time Hours],

    datepart(mi, dbo.Bigtable.[Date Time]) as [Date Time Minutes],

    datepart(ss, dbo.Bigtable.[Date Time]) as [Date Time Seconds],

    datepart(ms, dbo.Bigtable.[Date Time]) as [Date Time Milliseconds]”

    Използвайте толкова колони, колкото са необходими, за да съхраните всяка част в отделна колона.

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

    Timefromparts(datepart(hh, dbo.Bigtable.[Date Time]), datepart(mm, dbo.Bigtable.[Date Time])) as [Date Time HourMinute]

  5. Ако имате две колони за дата и час, например [Start Time] (Начало) и [End Time] (Край), и това, което действително ви интересува, е времевата разлика между тях в секунди, представена в колона с име [Duration] (Продължителност), премахнете и двете колони от списъка и добавете:

    "datediff(ss,[Start Date],[End Date]) as [Duration]"

    Ако използвате ключовата дума ms вместо ss, ще получите продължителността в милисекунди

Използване на изчисляеми DAX мерки вместо колони

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

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

Кои две колони трябва да запазите?

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

"Общо_продажби:=sumx("Таблица на продажбите";"Таблица на продажбите"[Unit Price]*"Таблица на продажбите"[Quantity])"

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

Заключение

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

  • Премахването на колони, разбира се, е най-добрият начин за пестене на пространство. Решете кои колони са ви необходими наистина.

  • Понякога можете да премахнете колона и да я заместите с изчисляема мярка в таблицата.

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

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

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

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

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

Сродни връзки

Спецификации и ограничения на модела на данни

оптимизатора на размера на работни книги

Power Pivot: Мощен анализ на данни и моделиране на данни в Excel

Забележка : Отказ от отговорност за машинен превод: Тази статия е преведена от компютърна система без човешка намеса. Microsoft предлага тези машинни преводи, за да помогне на потребителите, които не говорят английски, да се възползват от съдържанието за продукти, услуги и технологии на Microsoft. Тъй като статията е преведена машинно, е възможно да съдържа грешки в речника, синтаксиса и граматиката.

Споделяне Facebook Facebook Twitter Twitter Имейл Имейл

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

Чудесно! Друга обратна връзка?

Как можем да подобрим това?

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

×