В Excel 2013 г. или по-нова версия можете да създадете модели на данни, съдържащи милиони редове, и след това да извършите мощен анализ на данни спрямо тези модели. Моделите на данни могат да бъдат създадени със или без добавката Power Pivot, за да се поддържа произволен брой обобщени таблици, диаграми и визуализации на Power View в една и съща работна книга.
Забележка: Тази статия описва модели на данни в Excel 2013. Обаче същите модели на данни и функции на Power Pivot, въведени в Excel 2013, са в сила и за Excel 2016. Има ефективно малка разлика между тези версии на Excel.
Въпреки че можете лесно да създавате огромни модели на данни в Excel, има няколко причини да не го направите. Първо, големите модели, които съдържат множество таблици и колони, са препълнени за повечето анализи и правят за тромава списък с полета. Второ, големите модели използват ценна памет, която оказва отрицателно въздействие върху други приложения и отчети, които споделят едни и същи системни ресурси. И накрая, Microsoft 365, както SharePoint Online, така и Excel Web App ограничават размера на Excel до 10 МБ. За модели на данни на работна книга, които съдържат милиони редове, ще се натвъртате в ограничението от 10 МБ доста бързо. Вижте Спецификация и ограничения на модела на данни.
В тази статия ще научите как да създадете плътно изграден модел, който е по-лесен за работа и използва по-малко памет. Отделяйки време, за да научите най-добрите практики в ефективния модел на модела, ще се отплати надолу за всеки модел, който създавате и използвате, независимо дали го преглеждате в Excel 2013 г., Microsoft 365 SharePoint Online, на сървър на Office Web Apps или в SharePoint 2013 г.
Помислете и за използване на оптимизатора на размера на работни книги. Той анализира вашата работна книга на Excel и ако е възможно, я компресира допълнително. Изтеглете оптимизатора на размера на работната книга.
В тази статия
Съотношения на компресиране и аналитичната машина в паметта
Моделите на данни Excel използват ядрото за анализ в паметта, за да съхраняват данни в паметта. Двигателят прилага мощни техники за компресиране, за да намали изискванията за съхранение, като свива набор от резултати, докато не стане част от първоначалния му размер.
Средно можете да очаквате моделът на данни да бъде от 7 до 10 пъти по-малък от същите данни в мястото му на произход. Ако например импортирате 7 МБ данни от база данни на SQL Server, моделът на данни в Excel може лесно да бъде 1 МБ или по-малко. Степента на действително постигната компресия зависи предимно от броя на уникалните стойности във всяка колона. По-уникалните стойности, толкова повече памет е необходима, за да ги съхранявате.
Защо говорим за компресиране и уникални стойности? Тъй като изграждането на ефективен модел, който намалява използването на паметта, е всичко за увеличаване на компресирането и най-лесният начин да направите това е да се отървете от колоните, които не ви трябват, особено ако тези колони включват голям брой уникални стойности.
Забележка: Разликите в изискванията за съхранение за отделни колони могат да бъдат огромни. В някои случаи е по-добре да имате няколко колони с малък брой уникални стойности, а не една колона с голям брой уникални стойности. Секцията за оптимизации на дата и час описва подробно тази техника.
Нищо не би побеждавало несъ съществуваща колона за слабо използване на паметта
Най-ефективната за паметта колона е тази, която никога не сте импортирали на първо място. Ако искате да създадете ефективен модел, погледнете всяка колона и се запитайте дали допринася за анализа, който искате да извършите. Ако не е така или не сте сигурни, оставете го. Винаги можете да добавяте нови колони по-късно, ако имате нужда от тях.
Два примера за колони, които винаги трябва да бъдат изключени
Първият пример се отнася до данни, които произхождат от склад за данни. В склад за данни е често срещано да намирате артефакти на ETL процеси, които зареждат и обновяват данни в склада. Колони като "дата на създаване", "дата на актуализиране" и "Изпълнение на ETL" се създават, когато данните се заредят. Никоя от тези колони не е необходима в модела и трябва да бъде отменена, когато импортирате данни.
Вторият пример включва пропускане на колоната за първичен ключ при импортиране на таблица с факти.
Много таблици, включително таблици с факти, имат първични ключове. За повечето таблици, като например тези, които съдържат данни за клиенти, служители или продажби, ще искате първичният ключ на таблицата, така че да можете да я използвате за създаване на релации в модела.
Таблиците с факти са различни. В таблицата с факти първичният ключ се използва за еднозначно идентифициране на всеки ред. Въпреки че е необходимо за целите на нормализирането, той е по-малко полезен в модел на данни, където искате да се използват само тези колони за анализ или за установяване на релации между таблици. Поради тази причина, когато импортирате от таблица с факти, не включвайте неговия първичен ключ. Първичните ключове в таблица с факти използват огромни количества място в модела, но не предоставят полза, тъй като те не могат да се използват за създаване на релации.
Забележка: В складовете за данни и многомерните бази данни големите таблици, които се състоят предимно от числови данни, често се наричат "таблици с факти". Таблиците с факти обикновено включват данни за бизнес производителност или транзакция, като например точки от данни за продажби и разходи, които са агрегирани и подравнени на организационни единици, продукти, пазарни сегменти, географски региони и т.н. Всички колони в таблица с факти, които съдържат бизнес данни или които могат да се използват за кръстосани препратки, съхранявани в други таблици, трябва да бъдат включени в модела, за да се поддържа анализ на данни. Колоната, която искате да изключите, е колоната за първичен ключ на таблицата с факти, която се състои от уникални стойности, които съществуват само в таблицата с факти и никъде другаде. Тъй като таблиците с факти са толкова огромни, някои от най-големите печалби в ефективността на модела се извличат от изключването на редове или колони от таблици с факти.
Как да изключите ненужни колони
Ефективните модели съдържат само тези колони, които всъщност ще ви трябват във вашата работна книга. Ако искате да контролирате кои колони са включени в модела, ще трябва да използвате съветника за импортиране на таблици в добавката Power Pivot, за да импортирате данните, а не в диалоговия прозорец "Импортиране на данни" в Excel.
Когато стартирате съветника за импортиране на таблици, изберете кои таблици да импортирате.
За всяка таблица можете да щракнете върху бутона Визуализация & филтър и да изберете частите от таблицата, от които наистина се нуждаете. Препоръчваме първо да премахнете отметката от всички колони и след това да продължите, за да проверите колоните, които искате, след като обмислите дали са необходими за анализа.
Какво ще кажете за филтрирането само на необходимите редове?
Много таблици в корпоративни бази данни и складове за данни съдържат исторически данни, натрупани за дълги периоди от време. Освен това може да откриете, че таблиците, които ви интересуват, съдържат информация за области от бизнеса, които не са необходими за вашия конкретен анализ.
С помощта на съветника за импортиране на таблици можете да филтрирате исторически или несвързани данни и по този начин да запишете много място в модела. В изображението по-долу се използва филтър за дата, за да се извличат само редове, които съдържат данни за текущата година, с изключение на исторически данни, които няма да са необходими.
Какво става, ако се нуждаем от колоната; можем ли все още да намалим цената на пространството?
Има няколко допълнителни техники, които можете да приложите, за да направите колоната по-добра кандидатка за компресиране. Не забравяйте, че единствената характеристика на колоната, която засяга компресирането, е броят на уникалните стойности. В този раздел ще научите как някои колони могат да бъдат променяни, за да се намали броят на уникалните стойности.
Промяна на колони за дата и час
В много случаи колоните за дата и час отнемат много място. За щастие има редица начини за намаляване на изискванията за съхранение за този тип данни. Техниките ще варират в зависимост от начина, по който използвате колоната, и нивото на удобство в SQL заявки.
Колоните за дата и час включват част от датата и час. Когато се запитате дали имате нужда от колона, задайте един и същ въпрос няколко пъти за колона "Дата и час":
-
Трябва ли ми времената?
-
Имам ли нужда от времената част на нивото на часовете? , минути? , Секунди? , милисекунди?
-
Имам ли няколко колони за дата и час, защото искам да изчисля разликата между тях или просто да сумия данните по година, месец, тримесечие и т. н.
Как да отговорите на всеки от тези въпроси определя опциите ви за работа с колоната "Дата и час".
Всички тези решения изискват промяна на SQL заявка. За да направите промяната на заявката по-лесна, трябва да филтрирате поне една колона във всяка таблица. Като филтрирате колона, можете да промените структурата на заявките от съкратен формат (SELECT *) в команда SELECT, която включва напълно квалифицирани имена на колони, които са много по-лесни за промяна.
Нека да разгледаме заявките, които са създадени за вас. От диалоговия прозорец Свойства на таблицата можете да превключите към редактора на заявки и да видите текущата SQL заявка за всяка таблица.
От Свойства на таблица изберете Редактор на заявки.
Редакторът на заявки показва SQL, използвана за попълване на таблицата. Ако сте филтрирали някоя колона по време на импортирането, вашата заявка включва напълно квалифицирани имена на колони:
|
За разлика от това, ако сте импортирали цяла таблица, без да махнете отметката от някоя колона или да приложите филтър, ще видите заявката като "Избор * от ", което ще бъде по-трудно за модифициране:
|
Промяна на заявката SQL данни
Сега, след като знаете как да намерите заявката, можете да я промените, за да намалите допълнително размера на вашия модел.
-
За колони, съдържащи валутни или десетични данни, ако не ви трябват десетичния знак, използвайте този синтаксис, за да се отървете от десетичния знак:
"SELECT ROUND([Decimal_column_name]]0)... .”
Ако ви трябват цента, но не и дроби от цента, заместете 0 с 2. Ако използвате отрицателни числа, можете да закръглите до единици, десетки, стотици и т.н.
-
Ако имате колона datetime с име dbo. Голяма таблица. [Дата час] и не ви трябва частта "Час", използвайте синтаксиса, за да се отървете от часа:
"SELECT CAST (dbo. Голяма таблица. [Дата час] като дата) AS [Дата час]) "
-
Ако имате колона datetime с име dbo. Голяма таблица. [Дата и час] и ви трябват както частите "Дата", така и "Час", използвайте няколко колони в заявката SQL вместо единичната колона "Дата и час":
"SELECT CAST (dbo. Голяма таблица. [Дата час] като дата ) AS [Дата час]
datepart(hh, dbo. Голяма таблица. [Дата и час]) като [Часове за дата]
datepart(mi, dbo. Голяма таблица. [Дата и час]) като [Минути за дата и час]
datepart(ss, dbo. Голяма таблица. [Дата и час]) като [Секунди за дата и час]
datepart(ms, dbo. Голяма таблица. [Дата и час]) като [Милисекунди за дата и час]"
Използвайте толкова колони, колкото е необходимо, за да съхранявате всяка част в отделни колони.
-
Ако ви трябват часове и минути и ги предпочитате заедно като една колона, можете да използвате синтаксиса:
Timefromparts(datepart(hh, dbo. Голяма таблица. [Дата час]), datepart(mm, dbo. Голяма таблица. [Дата и час])) като [Час за дата Минути]
-
Ако имате две колони за дата и час, като например [Начален час] и [Краен час] и това, което наистина ви трябва, е разликата във времето между тях в секунди като колона, наречена [Продължителност], премахнете и двете колони от списъка и добавете:
"datediff(ss;[Начална дата][Крайна дата]) като [Продължителност]"
Ако използвате ключовата дума ms вместо ss, ще получите продължителността в милисекунди
Използване на изчисляеми мерки за DAX вместо колони
Ако преди сте работили с езика на израза DAX, може вече да знаете, че изчисляемите колони се използват за извличане на нови колони въз основа на друга колона в модела, докато изчисляемите мерки се дефинират веднъж в модела, но се оценяват само когато се използват в обобщена таблица или друг отчет.
Една техника за пестене на памет е да заместите обикновени или изчисляеми колони с изчисляеми мерки. Класическият пример е Единична цена, Количество и Обща сума. Ако имате и трите, можете да спестите място, като запазите само две и изчислите третата с помощта на DAX.
Кои 2 колони трябва да запазите?
В горния пример запазете Количество и Единична цена. Тези две стойности имат по-малко стойности от общата сума. За да изчислите общата сума, добавете изчисляема мярка като:
"TotalSales:=sumx('Таблица продажби';'Таблица за продажби'[Единична цена]*'Таблица продажби'[Количество])"
Изчисляемите колони са като обикновени колони, в които и двете имат място в модела. За разлика от това изчисляемите мерки се изчисляват в движение и не отнемат място.
Заключение
В тази статия говорихме за няколко подхода, които могат да ви помогнат да създадете по-ефективен от паметта модел. Начинът за намаляване на размера на файла и изискванията към паметта на модела на данни е да се намали общият брой колони и редове и броят на уникалните стойности, които се показват във всяка колона. Ето някои техники, които сме покрили:
-
Премахването на колони, разбира се, е най-добрият начин да спестите място. Решете кои колони наистина ви трябват.
-
Понякога можете да премахнете колона и да я заместите с изчисляема мярка в таблицата.
-
Може да не ви трябват всички редове в таблица. Можете да филтрирате редове в съветника за импортиране на таблици.
-
По принцип разделянето на една колона на няколко отделни части е добър начин за намаляване на броя на уникалните стойности в колона. Всяка от частите ще има малък брой уникални стойности, а общата сума ще бъде по-малка от първоначалната обединена колона.
-
В много случаи ви трябват и отделните части, които да използвате като сегментатори във вашите отчети. Когато е подходящо, можете да създавате йерархии от части като Часове, Минути и Секунди.
-
Много пъти колоните съдържат повече информация, отколкото ви трябват. Да предположим например, че една колона съхранява десетичния знак, но сте приложили форматиране, за да скриете всички десетични знаци. Закръгляване може да бъде много ефективно за намаляване на размера на числова колона.
Сега, след като сте направили това, което можете, за да намалите размера на работната книга, обмислете също да изпълните оптимизатора на размера на работната книга. Той анализира вашата работна книга на Excel и ако е възможно, я компресира допълнително. Изтеглете оптимизатора на размера на работната книга.
Сродни връзки
Спецификации и ограничения на модела на данни
Оптимизатор на размера на работната книга
Power Pivot: Мощен анализ на данни и моделиране на данни в Excel
