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

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

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

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

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

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

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

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

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

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

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

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

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

• Трябва ли ми частта от часа?

• Нуждая ли се от част от часа на ниво часове? Минути? Секунди? Милисекунди?

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

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

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

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

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

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

Модифициране на SQL заявката

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

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

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

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

2. Ако имате колона за дата и час с име dbo. Голяма маса. [Дата и час] и не ви е необходима частта Час, използвайте синтаксиса, за да се отървете от часа:

   "SELECT CAST (dbo. Голяма маса. [Date time] as date) AS [Date time]) "

3. Ако имате колона за дата и час с име 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]"

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

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

   Timefromparts(datepart(hh, dbo. Голяма маса. [Date Time]), datepart(mm, dbo. Голяма маса. [Дата и час])) as [Date Time HourMinute]

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

   "datediff(ss;[Начална дата];[Крайна дата]) като [Продължителност]"

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

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

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

"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"

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