В Excel можете да създавате модели на данни, съдържащи милиони редове, и след това да извършвате сложен анализ на данните спрямо тези модели. Модели на данни могат да бъдат създавани със или без добавката Power Pivot, за да поддържат произволен брой обобщени таблици, диаграми и визуализации на Power View в една и съща работна книга.
Въпреки че можете лесно да създавате модели на данни с огромни размери в Excel, има няколко причини да не го правите. Първо, големите модели, които съдържат множество таблици и колони, са излишни за повечето анализи и създават тромав списък с полета. Второ, големите модели използват ценна памет, което се отразява негативно на други приложения и отчети, които споделят същите системни ресурси. И накрая, в Microsoft 365 както SharePoint Online, така и Excel Web App ограничават размера на файл на Excel до 10 МБ. За модели на данни на работни книги, които съдържат милиони редове, доста бързо ще стигнете до ограничението от 10 МБ. Вижте спецификациите и ограниченията на модела на данни.
В тази статия ще научите как да създадете плътно изграден модел, който е по-лесен за работа и който използва по-малко памет. Ако отделите време, за да научите най-добрите практики за ефективно проектиране на модели, ще се отплати с всеки модел, който създавате и използвате, независимо дали го разглеждате в Excel, Microsoft 365 SharePoint Online, Office Web Apps Server, или в SharePoint.
Помислете и за използване на оптимизатора на размера на работни книги. Той анализира вашата работна книга на Excel и ако е възможно, я компресира допълнително. Изтеглете оптимизатора на размера на работни книги.
В тази статия
Нищо не може да се сравни с несъществуваща колона за ниско използване на паметта
Какво ще стане, ако имаме нужда от колоната; Можем ли все пак да намалим разходите за пространство?
Коефициенти на компресия и системата за анализ в паметта
Моделите на данни в Excel използват системата за анализ в паметта, за да съхраняват данни в паметта. Двигателят прилага мощни техники за компресия, за да намали изискванията за съхранение, свивайки набора от резултати, докато стане част от първоначалния му размер.
Можете да очаквате средно един модел на данни да бъде от 7 до 10 пъти по-малък от същите данни в точката на произход. Ако например импортирате 7 МБ данни от база данни на SQL Server, моделът на данните в Excel лесно може да бъде 1 МБ или по-малко. Действително постигнатата степен на компресия зависи предимно от броя на уникалните стойности във всяка колона. Колкото повече уникални стойности, толкова повече памет е необходима за съхраняването им.
Защо говорим за компресия и уникални стойности? Защото при създаването на ефективен модел, който намалява използването на паметта, всичко е свързано с максимизиране на компресията, а най-лесният начин да направите това е да се отървете от колоните, от които в действителност нямате нужда, особено ако тези колони съдържат голям брой уникални стойности.
Забележка
Разликите в изискванията за съхранение за отделни колони могат да бъдат огромни. В някои случаи е по-добре да имате няколко колони с малък брой уникални стойности, вместо една колона с голям брой уникални стойности. Разделът за оптимизиране на дата/час разглежда тази техника в детайли.
Нищо не може да се сравни с несъществуваща колона за ниско използване на паметта
Колоната с най-ефективно използване на паметта е тази, която никога не сте импортирали. Ако искате да създадете ефективен модел, погледнете всяка колона и се запитайте дали тя допринася за анализа, който искате да извършите. Ако не е така или не сте сигурни, оставете го. Винаги можете да добавите нови колони по-късно, ако имате нужда от тях.
Два примера на колони, които винаги трябва да бъдат изключени
Първият пример се отнася до данни, които произхождат от хранилище за данни. В един склад за данни е обичайно да намерите артефакти на ETL процеси, които зареждат и обновяват данните в склада. Колони като "дата на създаване", "дата на актуализиране" и "изпълнение на ETL" се създават, когато данните се зареждат. Нито една от тези колони не е необходима в модела и избирането й трябва да бъде премахнато, когато импортирате данни.
Вторият пример включва изпускане на колоната за първичен ключ, когато импортирате таблица с факти.
Много таблици, включително таблиците с факти, имат първични ключове. За повечето таблици, като например тези, които съдържат данни за клиенти, служители или продажби, ще искате първичния ключ на таблицата, за да можете да го използвате за създаване на релации в модела.
Таблиците с факти са различни. В таблицата с факти първичният ключ се използва за еднозначно идентифициране на всеки ред. Въпреки че е необходимо за целите на нормализирането, то е по-малко полезно в модел на данни, където искате да се използват само тези колони за анализ или за установяване на релации между таблиците. Поради тази причина, когато импортирате от таблица с факти, не включвайте нейния първичен ключ. Първичните ключове в таблица с факти заемат огромно място в модела, но не осигуряват никаква полза, тъй като не могат да се използват за създаване на релации.
Забележка
В складовете за данни и многомерните бази данни големите таблици, състоящи се предимно от числови данни, често се наричат "таблици с факти". Таблиците с факти обикновено включват данни за бизнес производителност или транзакции, като например точки с данни за продажби и разходи, които са агрегирани и съобразени с организационните единици, продукти, пазарни сегменти, географски региони и т.н. Всички колони в таблица с факти, които съдържат бизнес данни или които могат да се използват за кръстосана препратка към данни, съхранени в други таблици, трябва да бъдат включени в модела, за да се поддържа анализа на данни. Колоната, която искате да изключите, е колоната с първичен ключ на таблицата с факти, която се състои от уникални стойности, които съществуват само в таблицата с факти и никъде другаде. Тъй като таблиците с факти са толкова огромни, някои от най-големите печалби в ефективността на моделите се получават от изключването на редове или колони от таблиците с факти.
Как да изключите ненужните колони
Ефективните модели съдържат само тези колони, които действително ще ви трябват в работната книга. Ако искате да контролирате кои колони са включени в модела, ще трябва да използвате съветника за импортиране на таблици в добавката Power Pivot, за да импортирате данните , а не диалоговия прозорец "Импортиране на данни" в Excel.
Когато стартирате съветника за импортиране на таблици, вие избирате кои таблици да импортирате.
За всяка таблица можете да щракнете върху бутона "Визуализация & филтриране" и да изберете частите от таблицата, от които наистина имате нужда. Препоръчваме ви първо да изчистите отметките от всички колони и след това да преминете към проверка на желаните колони, след като прецените дали са задължителни за анализа.
Какво ще кажете за филтриране само на необходимите редове?
Много таблици в корпоративните бази данни и складове за данни съдържат хронологични данни, натрупани за дълги периоди от време. Освен това можете да установите, че таблиците, които ви интересуват, съдържат информация за области от бизнеса, които не са необходими за вашия конкретен анализ.
Като използвате съветника за импортиране на таблици, можете да филтрирате хронологични или несвързани данни и по този начин да спестите много място в модела. В изображението по-долу филтър за дата се използва за извличане само на редове, които съдържат данни за текущата година, с изключение на хронологичните данни, които няма да са необходими.
Какво ще стане, ако имаме нужда от колоната; Можем ли все пак да намалим разходите за пространство?
Има няколко допълнителни метода, които можете да приложите, за да направите една колона по-добър кандидат за компресиране. Не забравяйте, че единствената характеристика на колоната, която влияе върху компресията, е броят на уникалните стойности. В този раздел ще научите как някои колони могат да бъдат модифицирани, за да се намали броят на уникалните стойности.
Промяна на колони за дата и час
В много случаи колоните за дата и час заемат много място. За щастие има няколко начина да се намалят изискванията за съхранение за този тип данни. Техниките ще се различават в зависимост от начина, по който използвате колоната, и от нивото ви на комфорт при създаването на SQL заявки.
Колоните за дата и час съдържат част за дата и час. Когато се питате дали имате нужда от колона, задайте един и същ въпрос няколко пъти за колона за дата/час:
- Трябва ли ми частта за време?
- Трябва ли ми частта за час на ниво часове? , минути? , Секунди? , милисекунди?
- Дали имам няколко колони за дата и час, защото искам да изчисля разликата между тях, или просто за да агрегирам данните по години, месеци, тримесечия и т. н.
Начинът, по който отговаряте на всеки от тези въпроси, определя възможностите ви за работа с колоната за дата и час.
Всички тези решения изискват промяна на SQL заявка. За да улесните промяната на заявката, трябва да филтрирате поне една колона във всяка таблица. Чрез филтриране навън на колона можете да промените конструкцията на заявката от съкратен формат (SELECT *) в команда SELECT, която включва пълни имена на колони, които са много по-лесни за модифициране.
Нека разгледаме заявките, които се създават за вас. От диалоговия прозорец "Свойства на таблицата" можете да превключите към редактора на заявки и да видите текущата SQL заявка за всяка таблица.
От "Свойства на таблицата" изберете Редактор на заявки на Power Query.
Редактор на заявки показва 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. Голяма маса. [Date Time] as date ) AS [Date Time],
DatePart(hh; dbo. Голяма маса. [Дата и час]) като [дата и час часове],
DatePart(MI, dbo. Голяма маса. [Дата и час]) като [дата и час минути],
DatePart(SS; dbo. Голяма маса. [Дата и час]) като [дата, час и секунди],
DatePart(MS, dbo. Голяма маса. [Дата и час]) като [дата, час, милисекунди]"
Използвайте толкова колони, колкото са ви необходими, за да съхранявате всяка част в отделни колони. - Ако имате нужда от часове и минути и предпочитате да са заедно като една колона за време, можете да използвате синтаксиса:
Timefromparts(datepart(hh, dbo. Голяма маса. [Date Time]), datepart(mm, dbo. Голяма маса. [Дата Час])) as [Date, Time, HourMinute] - Ако имате две колони за дата и час, като например [Начален час] и [Краен час], и това, което наистина ви трябва, е времевата разлика между тях в секунди като колона, наречена [Продължителност], премахнете и двете колони от списъка и добавете:
"datediff(ss,[начална дата],[крайна дата]) като [продължителност]"
Ако използвате ключовата дума ms вместо ss, ще получите продължителността в милисекунди
Използване на изчисляеми мерки на DAX вместо колони
Ако сте работили с езика за изрази DAX преди, може би вече знаете, че изчисляемите колони се използват за извличане на нови колони въз основа на някоя друга колона в модела, докато изчисляемите колони се дефинират веднъж в модела, но се изчисляват само когато се използват в обобщена таблица или друг отчет.
Един метод за пестене на памет е заместването на обикновените или изчисляемите колони с изчисляеми мерки. Класическият пример е "Единична цена", "Количество" и "Общо". Ако имате и трите, можете да спестите място, като поддържате само две и изчислявате третото с помощта на DAX.
Кои 2 колони трябва да запазите?
В горния пример запазете "Количество" и "Единична цена". Тези две имат по-малко стойности от общата сума. За да изчислите общата сума, добавете изчисляема мярка, като например:
"ОбщоПродажби:=sumx('Таблица на продажби';'Таблица на продажби';'Единична цена]*'Таблица на продажби'[Количество])"
Изчисляемите колони са като обикновените колони по това, че и двете заемат място в модела. За разлика от тях, изчислените мерки се изчисляват в движение и не заемат място.
Заключение
В тази статия говорихме за няколко подхода, които могат да ви помогнат да изградите модел, който е по-ефективен по отношение на паметта. Начинът да намалите изискванията за размер на файла и паметта на модела на данни е да намалите общия брой колони и редове и броя на уникалните стойности, които се появяват във всяка колона. Ето някои техники, които разгледахме:
- Премахването на колони, разбира се, е най-добрият начин да спестите място. Решете кои колони наистина ви трябват.
- Понякога можете да премахнете колона и да я заместите с изчисляема мярка в таблицата.
- Възможно е да нямате нужда от всички редове в таблица. Можете да филтрирате редове в съветника за импортиране на таблици.
- По принцип разделянето на една колона на няколко отделни части е добър начин да намалите броя на уникалните стойности в колона. Всяка от частите ще има малък брой уникални стойности и общата сума ще бъде по-малка от първоначалната обединена колона.
- В много случаи имате нужда също и от различните части, които да използвате като сегментатори във вашите отчети. Когато е подходящо, можете да създадете йерархии от части като часове, минути и секунди.
- Много често колоните съдържат повече информация, отколкото ви е необходима. Да предположим например, че една колона съхранява десетични дроби, но сте приложили форматиране, за да скриете всички десетични знаци. Закръгляването може да бъде много ефективно за намаляване на размера на числова колона.
Сега, след като сте направили всичко възможно, за да намалите размера на вашата работна книга, помислете и за използване на оптимизатора на размера на работни книги. Той анализира вашата работна книга на Excel и ако е възможно, я компресира допълнително. Изтеглете оптимизатора на размера на работни книги.
Сродни връзки
Спецификации и ограничения на модела на данни
Оптимизатор на размера на работни книги
Power Pivot: Мощен анализ на данни и моделиране на данни в Excel