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

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

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

Макар че можете лесно да създавате огромни модели на данни в Excel, има няколко причини да не го направите. Първо, големи модели, които съдържат множества от таблици и колони, са Overkill за повечето анализи и правят за необременителен списък с полета. Второ, големите модели използват ценна памет, като влияят отрицателно върху други приложения и отчети, които споделят един и същ системен ресурс. И накрая, в 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 Run" се създават, когато данните са заредени. Никоя от тези колони не е необходима за модела и трябва да бъде отказана, когато импортирате данни.

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

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

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

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

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

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

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

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

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

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

Какво ще кажете за филтриране само на необходимите редове?

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

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

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

Какво става, ако имаме нужда от колоната; Можете ли все още да намалим разходите за мястото му?

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

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

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

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

  • Имам ли нужда от частта за час?

  • Имам ли нужда от частта за час на нивото на часовете? минути? Секунди? милисекунди?

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

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

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

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

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

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

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

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

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

За разлика от това, ако сте импортирали таблица изцяло, без да премахнете отметката от която и да е колона или да приложите филтър, ще видите заявката като "изберете * от", което ще е по-трудно за модифициране:

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

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

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

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

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

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

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

    "SELECT CAST (DBO. Bigtable. [Date Time] As date) AS [Date Time]) "

  3. Ако имате колона за дата и час, наречена DBO. Bigtable. [Дата и час] имате нужда и от двете части на дата и час, като използвате няколко колони в SQL заявката вместо единичната колона от дата за изказване:

    "SELECT CAST (DBO. Bigtable. [Date Time] As date) AS [Date Time],

    Datepart (чч, DBO. Bigtable. [Дата и час]) като [час за дати],

    Datepart (MI, DBO. Bigtable. [Дата и час]) като [минути за време],

    Datepart (СС, DBO. Bigtable. [Дата и час]) като [секунди за дата и час],

    Datepart (MS, DBO. Bigtable. [Дата и час]) като [дата за време милисекунди] "

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

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

    Timefromparts (Datepart (HH; DBO. Bigtable. [Дата час]), Datepart (мм; DBO. Bigtable. [Дата час])) As [Date Time HourMinute]

  5. Ако имате две колони за дата и час, като например [начален час] и [End Time], и това, от което имате нужда, е разликата във времето за секунди като колона, наречена [продължителност], премахнете и двете колони от списъка и добавете:

    "datediff (SS, [Start Date], [End Date]) As [продължителност]"

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

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

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

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

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

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

"Общо продажби: = sumx (" таблица на продажбите ";" таблица на продажбите "[Единична цена] *" таблица на продажбите "[количество])"

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

Заключение

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

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

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

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

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

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

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

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

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

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

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

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

Нуждаете се от още помощ?

Разширете уменията си в Office
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

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

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

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

×