Táblaszerű Excel pivot táblák optimalizálása

Az üzleti felhasználók sok esetben a pivot tábla lefúrható megjelenítése helyett táblázatos formában szeretnék megjeleníteni az adatokat. Mutatom:

Ehelyett:

Így szeretnék megjeleníteni ugyanazokat az adatokat:

Miért?

• Mert sokszor a pivot tábla adatait csak forrásként akarják használni más táblázatokban
• Mert ki akarják exportálni a pivot tábla adatait és ehhez a táblázatos elrendezés a legjobb
• vagy csak egész egyszerűen mert így szokták meg

Meg tudjuk ezt csinálni az Excellel?

Igen. Át tudjuk formázni a kimutatáseszközök/tervezés fülön a kimutatást tábla kinézetűvé, csakhogy ez a fajta elrendezés borzalmasan lassú lesz.

Miért lesz lassú?

Mert hiába kapcsoljuk a részösszegek, végösszegek megjelenítését, az Excel ettől függetlenül lekérdezi azt az adatbázisból. Megjeleníteni nem jeleníti meg, de az adatbázis kezelővel kiszámítatja.

Vegyünk egy tízezer elemű vevő dimenziót. Ha ennek ki szeretnénk tenni egymás mellé 5 attribútumát, akkor ez azt jelenti, hogy az adatbázis kezelőre bíz tízmillió milliárd részösszeg számítást. Konkrétan ennyit: 10 000 000 000 000 000. Ebbe pedig szegénykém belehal (És a tízezres vevőtörzs még nem is számít nagynak…)

Mit tudunk tenni, hogy az Excel ne számítassa ki az adatbázis kezelőkkel a részösszegeket? Úgy húzzuk össze a Pivot táblát, hogy ne tudjon az Excel olyan lekérdezést generálni, ami kiszámítatja a részösszegeket az adatbázis kezelővel. Erre gyakorlatilag 4 lehetőségünk van, de ezek közül a legegyszerűbb és az üzleti felhasználók által a legkönnyebben megvalósítható a nevesített halmazokkal (Named Sets) történő kiváltás.

Ehhez azt kell csinálnunk hogy létre kell hoznunk egy új nevesített halmazt (Kimutatás eszközök menü, tervezés fül, mezők elemek és halmazok gomb, halmaz létrahozása parancs, új, halmaz létrehozása az MDX segítségével)

úgy hogy a megjeleníteni kívánt oszlopokat felsoroljuk alábbi szintaktika szerint:

[Dátum].[Dátum].[Dátum].members *

[Régió].[Országrész].[Országrész].members *

[Régió].[Régió].[Régió].members *

[Régió].[Megye].[Megye].members *

[Gyógyszertörzs].[ATC1].[ATC1].members

Az eredmény:

További optimalizációs lehetőségek, ha táblaszerűen akarjuk megjeleníteni az OLAP/PowerPivot adatbázis adatait a Pivot táblában:

• Member Properties: Ez csak OLAP adatbázis esetén működik, PowerPivot adatbázis esetén nem, mert a PowerPivot oszlopalapú adatbázis kezelőjében nincsenek dimenzióelemek és ennek következtében nincs tulajdonságuk sem. De OLAP adatbázis esetén remekül működik. Annyit kell csak csinálni, hogy ezeket a tulajdonságokat kell kirakni a riportra. Úgy ahogy itt le van írva: Analysis Services Member Properties and Excel
• Drillthrough lekérdezés: Erre itt talál példát: PowerPivot mutatószámok dokumentálása
• Connection Stringbe ágyazott lekérdezés. Erről bővebben itt: Binding an Excel table to the results of an MDX query

További hasznos cikkek a témában:

• Introduction to MDX for PowerPivot Users, Part 3: The Members() and Crossjoin() functions
• Dynamic DAX Query Tables in Excel 2013