dimensional data model data warehouse tutorial with examples
Този урок обяснява предимствата и митовете на размерния модел на данните в хранилището на данни. Също така научете за таблиците с размери и таблиците с факти с примери:
Тестване на хранилището на данни беше обяснено в предишния ни урок, в това Склад за обучение на хранилище за данни за всички .
Огромни данни са организирани в хранилището на данни (DW) с техники за размерно моделиране на данни. Тези техники за размерно моделиране на данни правят работата на крайните потребители много лесна за справяне с бизнес данните. Този урок обяснява всичко за моделите с размерни данни в DW.
Целева аудитория
- Хранилище за данни / ETL разработчици и тестери.
- Професионалисти в базата данни с основни познания за концепции за бази данни.
- Администратори на бази данни / експерти за големи данни, които искат да разберат концепциите за Data warehouse / ETL.
- Завършили колеж / Преподаватели, които търсят работа в склад за данни.
Какво ще научите:
Модели с размерни данни
Размерните модели на данни са структурите от данни, които са на разположение на крайните потребители в ETL поток, за заявка и анализ на данните. Процесът ETL завършва с зареждане на данни в целевите модели с размерни данни. Всеки модел с размерни данни е изграден с таблица с факти, заобиколена от множество таблици с измерения.
Стъпки, които трябва да се следват при проектирането на Модел на измерените данни:
Ползи от моделирането на размерни данни
По-долу са изброени различните предимства на моделирането на измерените данни.
- Те са защитени, за да използват непрекъснато променящите се DW среди.
- Огромни данни могат лесно да бъдат изградени с помощта на модели с размерни данни.
- Данните от размерните модели данни са лесни за разбиране и анализ.
- Те са бързо достъпни от крайните потребители за заявки с висока производителност.
- Моделите с размерни данни ни позволяват да разгърнем (или) навиваме данните йерархично.
ER моделиране срещу моделиране на размерни данни
- ER моделирането е подходящо за операционни системи, докато моделното моделиране е подходящо за хранилището на данни.
- ER моделирането поддържа подробни текущи транзакционни данни, докато измереното моделиране поддържа обобщението както на текущите, така и на историческите транзакционни данни.
- ER моделирането има нормализирани данни, докато измереното моделиране има денормализирани данни.
- ER моделирането използва повече съединения по време на извличане на заявка, докато моделирането на измерения използва по-малък брой съединения, поради което производителността на заявката е по-бърза при моделирането на размери.
Митове за моделиране на размерни данни
По-долу са дадени някои от съществуващите митове за моделиране на размерни данни.
- Размерните модели данни се използват само за представяне на обобщението на данните.
- Те са специфични за отделите в дадена организация.
- Те не поддържат мащабируемост.
- Те са предназначени да служат за целите на отчетите и заявките за крайни потребители.
- Не можем да интегрираме моделите с размерни данни.
Таблици с размери
Таблиците с размери играят ключова роля в DW системата, като съхраняват всички анализирани метрични стойности. Тези стойности се съхраняват под лесно избираеми размерни атрибути (колони) в таблицата. Качеството на DW системата зависи най-вече от дълбочината на атрибутите на размерите.
Следователно трябва да се опитаме да предоставим много атрибути заедно със съответните им стойности в таблиците с измерения.
Нека изследваме структурата на таблиците с измерения !!
# 1) Ключ на таблицата с размери: Всяка таблица с измерения ще има някой от своите атрибути на измерения като първичен ключ за уникално идентифициране на всеки ред. Следователно различните числови стойности на този атрибут могат да действат като първични ключове.
Ако стойностите на атрибута във всеки случай не са уникални, тогава можете да разгледате последователно генерирани системни номера като първични ключове. Те също се наричат сурогатни ключове.
Размерните модели данни трябва да имат референтно ограничение на целостта за всеки ключ между измерения и факти. По този начин таблиците с факти ще имат препратка към външен ключ за всеки първичен / заместващ ключ в таблицата с измерения, за да се поддържа референтна цялост.
Ако е неуспешно, тогава съответните данни от таблицата с факти не могат да бъдат извлечени за този размери ключ.
# 2) Масата е широка: Можем да кажем, че таблиците с размери са широки, тъй като можем да добавим произволен брой атрибути към таблица с размери във всяка точка от цикъла на DW. DW архитект ще поиска от екипа на ETL да добави съответните нови атрибути към схемата.
В сценарии в реално време можете да видите таблици с размери с 50 (или) повече атрибута.
# 3) Текстови атрибути: Атрибутите на размерите могат да бъдат от всякакъв тип като за предпочитане текстов (или) цифров. Текстовите атрибути ще имат реални бизнес думи, а не кодове. Таблиците с размери не са предназначени за изчисления, поради което числовите стойности рядко се използват за атрибути на размерите.
# 4) Атрибутите може да не са пряко свързани: Всички атрибути в таблица с измерения може да не са свързани един с друг.
# 5) Не нормализирано: Нормализирането на таблица с размери вкарва повече междинни таблици в картината, което не е ефективно. По този начин таблиците с размери не се нормализират.
Атрибутите с размери могат да действат като източник за ограничения в заявките и могат да се показват и като етикети в отчетите. Заявките ще се изпълнят ефективно, ако директно изберете атрибут от таблицата с измерения и се обърнете директно към съответната таблица с факти, без да докосвате други междинни таблици.
# 6) Пробиване и навиване: Атрибутите на измерение имат способността да пробиват (или) навиват данните, когато е необходимо.
# 7) Множество йерархии: Таблицата с едно измерение с множество йерархии е много често срещана. Таблица с измерения ще има проста йерархия, ако съществува само един път от долното ниво до върха. По същия начин той ще има множество йерархии, ако има множество пътища, които да достигнат от долното ниво до горното.
# 8) Малко записи: Таблиците с измерения ще имат по-малък брой записи (в стотици) от таблиците с факти (в милиони). Въпреки че са по-малки от фактите, те предоставят всички данни за таблиците с факти.
Ето пример за таблица с измерения на клиента:
Разбирайки горните концепции, можете да решите дали полето с данни може да действа като атрибут на измерение (или), докато не извличате данните от самия източник.
Основният план за натоварване за измерение
Размерите могат да бъдат създадени по два начина, т.е. Чрез извличане на данните за измерения от системи с външни източници (или) Системата ETL може да изгради размерите от етапиране, без да включва външни източници. Въпреки това, ETL система без външна обработка е по-подходяща за създаване на таблици с размери.
По-долу са описани стъпките, включени в този процес:
най-добре конвертирате YouTube видео в mp3
- Почистване на данни: Данните се почистват, валидират и се прилагат бизнес правила преди зареждане в таблицата с измерения, за да се поддържа последователност.
- Съответствие на данните: Данните от други части на хранилището за данни трябва да бъдат правилно обобщени като единична стойност по отношение на всяко поле от таблицата с измерения.
- Споделете същите домейни: След като данните се потвърдят, те се съхраняват отново в подреждащи таблици.
- Доставка на данни: Накрая всички стойности на размерните атрибути се зареждат с присвоени първични / заместващи ключове.
Видове размери
Различните видове Размери са изброени по-долу за справка.
Да започваме!!
# 1) Малки размери
Малките размери в хранилището за данни действат като справочни таблици с по-малък брой редове и колони. Данните в малки размери могат лесно да се зареждат от електронни таблици. При необходимост малки размери могат да се комбинират като супер измерение.
# 2) Съобразено измерение
Съобразеното измерение е измерение, което може да бъде посочено по същия начин с всяка таблица с факти, с която е свързано.
Измерението дата е най-добрият пример за съответстващо измерение, тъй като атрибутите на измерението дата, като година, месец, седмица, дни и т.н., съобщават едни и същи данни по един и същи начин за произволен брой факти.
Пример за съобразено измерение.
# 3) Нежелана величина
Малко атрибути в таблица с факти като флагове и индикатори могат да бъдат преместени в отделна таблица с нежелани измерения. Тези атрибути също не принадлежат към други съществуващи таблици с измерения. Като цяло стойностите на тези атрибути са просто „да / не“ (или) „вярно / невярно“.
Създаването на ново измерение за всеки отделен атрибут на флага го прави сложен чрез създаване на повече на брой чужди ключове към таблицата с факти. В същото време запазването на всички тези знамена и информация за индикатора в таблици на факти също увеличава количеството данни, съхранявани във факти, което по този начин влошава производителността.
Следователно най-доброто решение за това е създаването на едно нежелано измерение, тъй като нежеланото измерение може да съдържа произволен брой индикатори „да / не“ или „вярно / невярно“. Въпреки това, нежеланите размери съхраняват описателни стойности за тези показатели (да / не (или) вярно / невярно) като активни и чакащи и т.н.
Въз основа на сложността на таблицата с факти и нейните показатели, таблицата с факти може да има едно или повече измерения на боклука.
Пример за Junk Dimension.
# 4) Ролево измерение
Единично измерение, което може да бъде посочено за множество цели в таблица с факти, е известно като измерение на ролевите игри.
Най-добрият пример за ролево измерение отново е таблица с измерения на дата, тъй като същият атрибут дата в дадено измерение може да се използва за различни цели в един факт като дата на поръчка, дата на доставка, дата на транзакция, дата на анулиране, и т.н.
Ако е необходимо, можете да създадете четири различни изгледа в таблицата с измерения на датата по отношение на четири различни атрибута на дата на таблица с факти.
Пример за ролево измерение.
# 5) Дегенерирани размери
Може да има малко атрибути, които не могат да бъдат нито измерения (метрики), нито факти (мерки), но се нуждаят от анализ. Всички такива атрибути могат да бъдат преместени в изродени измерения.
Например, можете да разгледате номера на поръчката, номера на фактурата и т.н. като дегенерирани атрибути на измерението.
Пример за изродено измерение.
# 6) Бавно променящи се размери
Бавно променящата се величина е вид, при който данните могат да се променят бавно по всяко време, а не на периодични редовни интервали. Модифицираните данни в таблиците с размери могат да се обработват по различни начини, както е обяснено по-долу.
Можете да изберете типа SCD, за да реагирате на промяна индивидуално за всеки атрибут в таблица с размери.
(i) Тип 1 SCD
- При тип 1, когато има промяна в стойностите на атрибутите на измеренията, съществуващите стойности се заменят с ново модифицираните стойности, което не е нищо друго освен актуализация.
- Старите данни не се поддържат за исторически справки.
- Миналите отчети не могат да бъдат регенерирани поради несъществуването на стари данни.
- Лесна за поддръжка.
- Въздействието върху таблиците с факти е по-голямо.
Пример за тип 1 SCD:
(Ii) Тип 2 SCD
- При тип 2, когато има промяна в стойностите на атрибутите с измерения, ще се вмъкне нов ред с променените стойности, без да се променят данните от стария ред.
- Ако има някаква препратка към външен ключ, която съществува към стария запис във всяка от таблиците с факти, тогава старият сурогатен ключ се актуализира навсякъде с нов сурогатен ключ автоматично.
- Въздействието върху промените в таблицата с факти е много по-малко с горната стъпка.
- След промените никъде не се разглеждат стари данни.
- При тип 2 можем да проследим всички промени, които се случват с атрибутите на измеренията.
- Няма ограничение за съхранение на исторически данни.
- При тип 2 добавянето на няколко атрибута към всеки ред, като например променена дата, ефективна дата-час, крайна дата-час, причината за промяната и текущия флаг не е задължително. Но това е важно, ако бизнесът иска да знае броя на промените, направени през определен период от време.
Пример за тип 2 SCD:
(III) Тип 3 SCD
- При тип 3, когато има промяна в стойностите на атрибутите с измерения, новите стойности се актуализират, но старите стойности все още остават валидни като втората опция.
- Вместо да се добавя нов ред за всяка промяна, ще се добави нова колона, ако не е съществувала преди.
- Старите стойности се поставят в добавените по-горе атрибути и данните на основния атрибут се заменят с променената стойност, както при тип 1.
- Има ограничение за съхранение на исторически данни.
- Въздействието върху таблиците с факти е по-голямо.
Пример за тип 3 SCD:
(iv) SCD от тип 4
- При тип 4 текущите данни се съхраняват в една таблица.
- Всички исторически данни се поддържат в друга таблица.
Пример за тип 4 SCD:
(v) Тип 6 SCD
- Таблицата с размери също може да има комбинация от трите типа SCD 1, 2 и 3, която е известна като хибриден тип 6 (или) бавно променящ се размер.
Таблици с факти
Фактическите таблици съхраняват набор от количествено измерени стойности, които се използват за изчисления. Стойностите на таблицата с факти се показват в бизнес отчетите. За разлика от текстовия тип данни на таблиците с размери, типът данни на таблиците с факти е значително числов.
Фактическите таблици са дълбоки, докато таблиците с размери са широки, тъй като фактическите таблици ще имат по-голям брой редове и по-малък брой колони. Първичен ключ, дефиниран в таблицата с факти, е преди всичко да идентифицира всеки ред поотделно. Първичният ключ се нарича още Комбиниран ключ всъщност таблица.
Ако съставният ключ липсва в таблица с факти и ако всеки два записа имат едни и същи данни, е много трудно да се направи разлика между данните и да се препратят данните в таблици с измерения.
Следователно, ако подходящ уникален ключ съществува като съставния ключ, тогава е добре да се генерира пореден номер за всеки запис на таблица с факти. Друга алтернатива е да се формира свързан първичен ключ. Това ще бъде генерирано чрез обединяване на всички посочени първични ключове на таблици с размери по ред.
Една таблица с факти може да бъде заобиколена от таблици с множество измерения. С помощта на външните ключове, които съществуват всъщност таблици, съответният контекст (подробни данни) на измерените стойности може да бъде посочен в таблиците с измерения. С помощта на заявки потребителите ще извършват ефективно пробиване и навиване.
Най-ниското ниво на данни, които могат да се съхраняват в таблица с факти, е известно като Гранулираност. Броят на таблиците с измерения, свързани с таблица с факти, е обратно пропорционален на детайлността на данните от тази таблица с факти. т.е.на най-малката измервателна стойност са необходими повече таблици с размери, за да бъдат посочени
В размерния модел таблиците с факти поддържат връзка много към много с таблиците с размери.
Пример за таблица с факти за продажбите:
Заредете план за таблици с факти
Можете да заредите ефективно данни от таблица с факти, като вземете предвид следните указатели:
# 1) Пускане и възстановяване на индекси
Индексите в действителност таблици са добри ускорители на производителността при заявка на данните, но те разрушават производителността, докато зареждат данните. Следователно, преди да заредите огромни данни в таблици с факти, първо пуснете всички индекси в тази таблица, заредете данните и възстановете индексите.
# 2) Отделни вложки от актуализации
Не обединявайте вмъкване и актуализиране на записи, докато се зареждате в таблица с факти. Ако броят на актуализациите е по-малък, тогава обработвайте вмъкванията и актуализациите отделно. Ако броят на актуализациите е повече, препоръчително е да съкратите и презаредите таблицата с факти за бързи резултати.
# 3) Разделяне
Направете разделянето физически на таблица с факти на мини таблици за по-добро изпълнение на заявките за данни от масивна таблица с факти. С изключение на DBA и екипа на ETL, никой няма да е наясно с разделянето на фактите.
Като пример , можете да разделяте таблица месечно, четвърт, годишно и т.н. Докато правите заявка, се вземат предвид само секционираните данни, вместо да се сканира цялата таблица.
# 4) Заредете паралелно
най-добрият безплатен изтеглящ видео за YouTube за Windows 10
Сега имаме представа за дяловете на таблици с факти. Разделянето на факти също е полезно, докато зареждате огромни данни във факти. За да направите това, първо разбийте данните логически в различни файлове с данни и стартирайте ETL заданията, за да заредите всички тези логически части от данни паралелно.
# 5) Помощна програма за насипно натоварване
За разлика от други RDBMS системи, ETL системата не се нуждае от изрично поддържане на дневници за връщане обратно за грешки в средата на транзакциите. Тук „насипни товари“ се случват във факти, вместо в „SQL вложки“, за да заредят огромни данни. Ако в случай, че едно зареждане не успее, тогава всички данни могат лесно да бъдат презаредени (или), те могат да продължат от мястото, където са спрени с груповия товар.
# 6) Изтриване на фактически запис
Изтриването на запис от таблица с факти се случва само ако бизнесът изрично иска. Ако има данни от таблица с факти, които вече не съществуват в системите източници, тогава съответните данни могат да бъдат изтрити физически (или) логически.
- Физическо изтриване: Нежеланите записи се премахват завинаги от таблицата с факти.
- Логично изтриване: Към таблицата с факти ще бъде добавена нова колона, като например „изтрит“ от битов (или) булев тип. Това действа като флаг за представяне на изтритите записи. Трябва да сте сигурни, че не избирате изтритите записи, докато заявявате данни от таблицата с факти.
# 7) Последователност за актуализации и изтриване в таблица с факти
Когато има някакви данни, които трябва да се актуализират, таблиците с измерения трябва да се актуализират първо, последвано от актуализиране на заместващите ключове в справочната таблица, ако е необходимо и след това съответната таблица с факти се актуализира. Изтриването става обратно, защото изтриването на всички нежелани данни от таблици с факти улеснява изтриването на свързаните нежелани данни от таблиците с измерения.
Трябва да следваме горната последователност и в двата случая, защото таблиците с размери и таблиците с факти поддържат референтна цялост през цялото време.
Видове факти
Въз основа на поведението на данни от таблици с факти те се категоризират като таблици с факти за транзакции, таблици с фактически моментни снимки и натрупани таблици с фактически моментни снимки. Всички тези три типа следват различни функции с различни стратегии за зареждане на данни.
# 1) Таблици с факти за транзакции
Тъй като името посочва таблиците с факти за транзакции, съхраняват данни на ниво транзакция за всяко събитие, което се случва. Такъв вид данни е лесен за анализ на самото ниво на таблицата с факти. Но за по-нататъшен анализ можете да се обърнете и към свързаните измерения.
Например, всяка продажба (или) покупка, която се случва от уебсайт за маркетинг, трябва да бъде заредена в таблица с факти за транзакции.
Пример за таблица с факти за транзакции е показан по-долу.
# 2) Таблици с периодични моментни снимки
Тъй като името показва данните в периодичната таблица с фактически снимки се съхраняват под формата на моментни снимки (снимки) на периодични интервали, като например за всеки ден, седмица, месец, тримесечие и т.н., в зависимост от бизнес нуждите.
Така че е ясно, че това е съвкупност от данни през цялото време. Следователно фактите за моментна снимка са по-сложни в сравнение с таблиците с факти за транзакции. Например, всички данни за приходите от изпълнение могат да се съхраняват в таблици с факти за моментна снимка за лесна справка.
Пример за таблица с периодични моментни снимки е показан по-долу.
# 3) Натрупване на фактически таблици за моментна снимка
Натрупващите моментни таблици с факти ви позволяват да съхранявате данни в таблици за целия живот на продукта. Това действа като комбинация от горните два типа, при които данните могат да бъдат вмъкнати от всяко събитие по всяко време като моментна снимка.
При този тип допълнителни колони за дата и данни за всеки ред се актуализират с всеки етап от този продукт.
Пример за натрупваща се таблица с фактически моментни снимки.
В допълнение към горните три типа, ето и няколко други типа таблици с факти:
# 4) Фактически таблици с факти: Фактът е съвкупност от мерки, докато фактът по-малко улавя само събития (или) условия, които не съдържат никакви мерки. Таблица с факти без факти се използва главно за проследяване на система. Данните в тези таблици могат да бъдат анализирани и използвани за отчитане.
Например, можете да потърсите подробности за служител, който е ползвал отпуск и вида на отпуска за една година и т.н. Включвайки всички тези неясни подробности за факта в даден факт, таблицата определено ще увеличи размера на фактите.
Пример за Фактическа таблица с факти е показан по-долу.
# 5) Съответстващи таблици с факти: Съобразеният факт е факт, който може да бъде посочен по същия начин с всеки март, с който е свързан.
Спецификации на таблица с факти
По-долу са дадени спецификациите на таблица с факти.
- Име на факта: Това е низ, който описва накратко функционалността на таблицата с факти.
- Бизнес процес: Разговорите за бизнеса трябва да бъдат изпълнени от тази таблица с факти.
- Въпроси: Споменава списък на бизнес въпроси, на които ще отговори тази таблица с факти.
- Зърно: Показва най-ниското ниво на детайлност, свързано с данните от тази таблица с факти.
- Размери: Избройте всички таблици с измерения, свързани с тази таблица с факти.
- Мерки: Изчислените стойности се съхраняват в таблицата с факти.
- Честота на натоварване Представлява интервалите от време за зареждане на данни в таблицата с факти.
- Начални редове: Обърнете се към първоначалните данни, попълнени за първи път в таблицата с факти.
Пример за моделиране на размерни данни
Можете да получите представа за това как таблиците с размери и таблиците с факти могат да бъдат проектирани за система, като разгледате диаграмата за моделиране на данни по-долу за продажби и поръчки.
Заключение
Досега трябваше да придобиете отлични познания за техниките за моделиране на размерни данни, техните предимства, митове, таблици с измерения, таблици с факти, заедно с техните типове и процеси.
Вижте нашия предстоящ урок, за да научите повече за схемите за съхранение на данни !!
=> Посетете тук, за да научите съхранението на данни от нулата.
Препоръчително четене
- Урок за тестване на хранилище на данни с примери | Ръководство за тестване на ETL
- Примери за извличане на данни: Най-често срещаните приложения на извличането на данни 2021
- Урок за Python DateTime с примери
- Основи за съхранение на данни: Крайно ръководство с примери
- Урок за тестване на обем: Примери и инструменти за тестване на обем
- Топ 10 на популярните инструменти за съхранение на данни и технологии за тестване
- Извличане на данни: процес, техники и основни проблеми при анализа на данните
- Как да извършите тестване на данни в SoapUI Pro - Урок SoapUI # 14