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