OLAP kostka v Excel - teorie

Úvodem do teorie OLAP kostek v Excel

To že Excel umí pracovat s OALP kostkami (krychlemi) je všeobecně známo, bohužel informací v ČR (i ve světě) je zatím málo. Proto jsem se rozhodl něco napsat (na druhou stranu budu mít podkladové materiály nejen pro školení). V tomto článku se podíváme na teorií, která bude rozdělena do samostatných kapitol. Ať je článek přehlednější pro případné doplňování a vylepšování:

• Křížové tabulky
• OLAP kostka - základ
• Základní pojmy - fakta, dimenze..
• Operace s OLAP kostkou:
  • Krájení kostky - SLICE podle dimenzí
  • Kostkování DICE
  • Roll up a drill down
  • Pivotování
• OLAP - schémata
  • Star - hvězda
  • Snowflake - sněhová vložka
• V přípravě
  • načítání OLAP
  • CUBE funkce
  • MDX
  • ...

Křížové tabulky

Začneme od základu, kdy máte tabulky, které znáte (řádky, sloupce a hodnoty). Vysvětlím na fiktivní firmě prodávající auta (několik let na třech pobočkách od tří automobilek).

První údaj počet prodaných automobilů v této firmě (někdy údaj postačí, ale pro další úvahy o směřování firmy již ne).

Druhá data budete mít seskupené přes jednu dimenzi (například celkové prodeje dle automobilek, které se prodávají).

Třetí se můžete na tyto prodeje podívat přes dvě dimenze. Automobilka a jak se daří dle jednotlivých prodejen.

Vy ale víte, že prodáváte několik let, tak potřebujete třetí údaj (ten rok, roky ve kterých již prodáváte). Co s tím, tři tabulky pro každý rok samostatnou (rok je třetí dimenze) a každá z tabulek bude mít dvě dimenze (automobilka a misto prodejny).

Teď tabulky jakoby naskládejte za sebe. Co vám to začíná připomínat? Krádr, neboli - onu kostku.

Samozdřejmě že tabulky mohou mít více dimenzí, ale z důvodu přehledmosti zůstanu u těch tří.

OLAP kostka

Když to zjednoduším, tak OLAP kostka je vlastně rozšíření křížové tabulky do prostoru.

OLAP kostka

S kostkou se dají provádět různé operace (pokud vám připomíná Rubikovou, nejste daleko od pravdy a některé operace budou to hodně podobné s operacemi s Rubikovou kostkou).

Základní pojmy

Než se podíváme na operace s kostkou, tak si pro sebe uvedu pár pojmů.

Faktová tabulka

Každé databázové schéma (datový sklad) tvoří jedna (mnohdy několik) faktových tabulek. Ve faktových tabulkách jsou uložena vlastní data (neboli údaje, hodnoty, které potřebujete analyzovat).

Faktové tabulky obsahují hodnoty vzniklé sloučením zdrojových dat. Napojují se na dimenze pro možnost třídení (kategorizace).

Dimenze (Číselník)

Popisy objektů, které se váží na konkrétní fakt. Neboli obsahuje seznamy hodnot sloužící ke třídění (kategorizaci dat ve faktových tabulkách). Faktové tabulky jsou pak pomocí cizích klíčů napojeny na tyto dimenze.

Jiným pohledem (slovem) jde o číselník, podle kterého data budete analyzovat (například město Brno..., datum - rok 2018...).

Pro datum můžou být dimenze:

• Rok
• Měsíc
• Den
• Týden
• Kvartál

Další ukázky dimenzí:

• Město
• Región

Navíc podle způsobu napojení dimenzí na faktovou tabulku existuji různá schémata (hvězda/sněhová vločka). O těch, ale v další samostatné kapitole.

Granualita

Určuje do jaké hloubky/úrovně jsou data ve faktové tabulce uvedena. Pokud se podíváme na datum, může být zaznamenán jen rok prodeje. Případně máte uveden ro a měsíce prodeje. Nebo můžete mít uveden nejen konkretní den, ale prodej mít včetně času do poslední sekundy (tčeba jako na účtenkách EET).

Krájení kostky SLICE

Jak vypadá OLAP kostka víte, teď se podíváme na nějaké operace. Začneme krájením. Neboli některou dimenzi omezíte na jeden prvek (dostanete se na křížovou tabulku). Podíváme se na tři případy. Pro tři různé uživaatele:

• Manager produktu - BMW
• Obchodní zástupce - Zlín
• Finanční manažer - 2016

Aneb každý se na data potřebuje podívat jinak (dle oddělení z kterého danná osoba je), neboli získá onu křížovou tabulku ve které se rychle orientuje.

Krájení kostky - automobilka BMW

Produktový manager se bude chcít podívat na data dle automobilek. Nebo je tento produktový manager odpovědná za konkretní značku, tak ho zajímají jen data o této značce.

Krájení kostky - město Zlín

Regionální zástupce chce mít data o svém regiónu (nebo naopak jako analytik, nechcete dávat svým obchodním zástupcům data o jiných regionech, kromě toho za které jsou zodpovědní).

Krájení kostky - rok 2016

Finanční manažer se může podívat jak se dařilo v konkretním roce atd.).

Kostkování - DICE

Omezení některé dimenze (některých dimenzí) na podmnožinu o dvou a vice prvcích.

Z velké kostky uděláte menší ;)

Roll up a drill down

Jak se pohybovat v rámci dimenze (dimenze může mít podrobnější granualitu). Lze se "pohybovat" dvěma směry od celku k detailu a od detailu k celku.

Drill Up

Pohyb v rámci hierarchie dimenze od detailů k celku. U datumu se jde ze den na měsíc na kvartál na rok.

Drill Down

Pohyb v rámci dimenze od celku k detailům. Pokud jsme stále u datumu tak se jde od roku na čtvrtletí, měsíce, dny...

Pivotování

U OLAP kostky nejde o statický stav, můžete ji kromě řezání (kostkování) atd. také "natáčet" neboli měnit úhel pohledu na data.

Star - hvězda

Základ o kostkách máte teď krátká zmínka o napojení dimenzí na faktovou tabulku. Co pojmy znamenají jsme se pokusil zmínit v úvodu.

Jak mohou být dimenze napojeny na faktovou tabulku. První možností se říká hvězda. Je popsána v této kapitole. Další základní je sněhová vločka popsána v následující.

Výhody schématu Star - hvězda

V přípravě.

Snowflake - sněhová vložka

Hvězdu máte za sebou. ;)

Druhý základní způsob, jak mohou být dimenze napojeny na faktovou tabulku. Jedná se o sněhovou vločku, která je popsána v této kapitole nebo v předchozí jsem popsal hvězdu.

Výhody schématu Snowflake - sněhová vložka

V přípravě.

Související články

V přípravě. Toto je zatím první z nové série o OLAP kostkách v Excel, když o tom nikdo uceleně nepíše, tak začnu, třeba se někdo přidá.

Závěrem

Článek rozpracovaný. Potřebuji si někde zesumarizovat poznámky k OLAP kostkám, tak proč je nemít veřejně. Budu rád za tipy a náměty na doplnění.

Článek byl aktualizován: 19.09.2020 11:02

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.