Adatelemzési kifejezések a Power Pivot programban

A Data Analysis Expressions (DAX) első pillantásra ijesztően hangzik, de ne hagyja, hogy megtévesszen a név. A DAX alapjai valóban könnyen megérthetők. A legfontosabb tudnivalók – a DAX NEM egy programozási nyelv. A DAX egy képletnyelv. A DAX használatával egyéni számításokat definiálhat számított oszlopokhoz és mértékekhez (más néven számított mezőkhöz). A DAX magában foglalja az Excel-képletekben használt függvények némelyikét, valamint további függvényeket, amelyeket relációs adatokkal való használatra és dinamikus aggregálás végrehajtására terveztek.

A DAX-képletek ismertetése

A DAX-képletek nagyon hasonlítanak az Excel-képletekre. A létrehozásához írjon be egy egyenlőségjelet, majd a függvény nevét vagy kifejezését, majd a szükséges értékeket és argumentumokat. Az Excelhez hasonlóan a DAX is számos függvényt kínál karakterláncokkal való munkához, dátumok és időpontok használatával számítások végrehajtásához, illetve feltételes értékek létrehozásához.

A DAX-képletek azonban a következő fontos módokon különböznek egymástól:

• Ha a számításokat soronként szeretné testre szabni, a DAX olyan függvényeket tartalmaz, amelyek lehetővé teszik az aktuális sor vagy egy kapcsolódó érték használatát a környezettől függően változó számítások elvégzéséhez.
• A DAX tartalmaz egy olyan típusú függvényt, amely eredményként egy táblát ad vissza egyetlen érték helyett. Ezek a függvények más függvények bemenetét is lehetővé teszik.
• A DAX időintelligencia-függvényeilehetővé teszik dátumtartományok használatával végzett számításokat, és összehasonlíthatják az eredményeket párhuzamos időszakokban.

Hol használhatók a DAX-képletek?

A Power Pivotban képleteket számított oszlopokban és számított mezőkben is létrehozhat.

Számított oszlopok

A számított oszlop olyan oszlop, amelyet egy meglévő Power Pivot-táblázathoz adhat hozzá. Az oszlopértékek beillesztése vagy importálása helyett létrehozhat egy DAX-képletet, amely meghatározza az oszlopértékeket. Ha a PowerPivot-táblázatot is felveszi egy kimutatásba (vagy kimutatásdiagramba), a számított oszlop ugyanúgy használható, mint bármely más adatoszlop.

A számított oszlopokban lévő képletek nagyon hasonlítanak az Excelben készített képletekre. Az Exceltől eltérően azonban nem hozhat létre eltérő képletet egy táblázat különböző soraihoz; Ehelyett a DAX-képletet automatikusan a teljes oszlopra alkalmazza a program.

Amikor egy oszlop képletet tartalmaz, az értéket minden sorra kiszámítja. A program a képlet létrehozásakor azonnal kiszámítja az oszlop eredményeit. Az oszlopértékek újraszámítása csak a kapcsolódó adatok frissítésekor, illetve manuális újraszámítás esetén történik meg.

Létrehozhat mértékeken és más számított oszlopokon alapuló számított oszlopokat is. Kerülje azonban, hogy egy számított oszlophoz és mértékhez ugyanazt a nevet használja, mert ez zavaros eredményekhez vezethet. Oszlopokra való hivatkozáskor célszerű teljesen minősített oszlophivatkozást használni, nehogy véletlenül egy mértéket hívjon elő.

További információ: Számított oszlopok a Power Pivotban.

Mértékek

A mérték olyan képlet, amelyet kifejezetten Power Pivot-adatokat használó kimutatásokban (vagy kimutatásdiagramokban) való használatra hoztak létre. A mértékek alapulhatnak szabványos aggregálási függvényeken (például COUNT vagy SUM), de saját képletet is definiálhat a DAX használatával. A mérték a kimutatások Értékek területén használható. Ha a kiszámított eredményeket a kimutatás másik részén szeretné elhelyezni, használjon inkább számított oszlopot.

Amikor egy explicit mértékhez definiál egy képletet, semmi nem történik, amíg a mértéket hozzá nem adja egy kimutatáshoz. Amikor hozzáadja a mértéket, a program a kimutatás Értékek területén lévő minden cellára kiértékeli a képletet. Mivel a sor- és oszlopfejlécek minden kombinációjához külön eredmény jön létre, a mérték eredménye minden cellában eltérő lehet.

A program a létrehozott mérték definícióját a forrásadattáblával együtt menti. Megjelenik a kimutatás mezőlistájában, és a munkafüzet minden felhasználója számára elérhető.

További információ: Mértékek a Power Pivot programban.

Képletek létrehozása a szerkesztőlécen

A Power Pivot – akárcsak az Excel – tartalmaz szerkesztőlécet a képletek létrehozásának és szerkesztésének megkönnyítéséhez, valamint automatikus kiegészítési funkciót is biztosít a beírási és szintaktikai hibák minimalizálása érdekében.

Tábla nevének beírása Kezdje el beírni a táblázat nevét. Az automatikus képletkiegészítési funkció egy legördülő listát biztosít a megadott betűkkel kezdődő érvényes nevekkel.

Oszlop nevének beírása Írjon be egy szögletes zárójelet, majd válassza ki az oszlopot az aktuális táblázat oszloplistájából. Egy másik táblázatból származó oszlop esetén kezdje el beírni a táblázatnév első betűit, majd válassza ki az oszlopot az Automatikus kiegészítés legördülő listából.

További részletekért és a képletek felépítésének menetét a Képletek létrehozása számításokhoz a Power Pivot bővítményben című témakörben találja.

Tippek az automatikus kiegészítés használatához

Az automatikus képletkiegészítési funkciót beágyazott függvényeket tartalmazó meglévő képlet közepén is használhatja. A közvetlenül a beszúrási jel előtti szöveg jeleníti meg az értékeket a legördülő listában, a beszúrási jelet követő teljes szöveg pedig változatlan marad.

Az állandókhoz létrehozott definiált nevek nem jelennek meg az Automatikus kiegészítés legördülő listájában, beírhatja azonban őket.

A Power Pivot nem veszi fel a függvények záró zárójelét, és nem egyezteti automatikusan a zárójeleket. Győződjön meg arról, hogy minden függvény szintaktikailag helyes, különben nem tudja menteni vagy használni a képleteket. 

Több függvény használata egy képletben

A függvények egymásba ágyazhatók, azaz az egyik függvény eredményei egy másik függvény argumentumaként használhatók. A számított oszlopokba legfeljebb 64 szinten ágyazhatók be függvények. A beágyazás azonban megnehezítheti a képletek létrehozását és a hibák elhárítását.

Számos DAX-függvény kizárólag beágyazott függvényként való használatra készült. Ezek a függvények egy táblát adnak vissza, amelyet így nem lehet közvetlenül menteni; Ezt egy TABLE függvény bemeneteként kell megadni. A SZUMX, az ÁTLAGX és a MINX függvény első argumentuma például táblázatot használ.

A DAX-függvények és az Excel-függvények összehasonlítása

A DAX függvénytár az Excel függvénytárán alapul, de a függvénytárakban sok különbség van. Ez a szakasz az Excel-függvények és a DAX-függvények közötti különbségeket és hasonlóságokat foglalja össze.

• Sok DAX-függvény neve és általános működése megegyezik az Excel-függvények nevével és működésével, de úgy módosították őket, hogy különböző bemeneti típusokat fogadjanak, és bizonyos esetekben más adattípusokat adjanak vissza. Általában nem használhatók DAX-függvények Excel-képletekben, illetve Excel-képletek a Power Pivotban módosítás nélkül.
• A DAX-függvények sohasem használnak cellahivatkozást vagy tartományt, hanem oszlopot vagy táblázatot használnak hivatkozásként.
• A DAX dátum- és időfüggvényei egy datetime adattípust adnak vissza. Ezzel szemben az Excel dátum- és időfüggvényei olyan egész számot adnak eredményül, amely a dátumot számszámként jelenti.
• Számos új DAX-függvény vagy értéktáblázatot ad vissza, vagy számításokat végez egy értékek táblázata alapján, bemenetként. Ezzel szemben az Excelben nincsenek olyan függvények, amelyek táblázatot adnak vissza, de néhány függvény használható tömbökkel. A Power Pivot új funkciója, hogy egyszerűen lehet teljes táblázatokra és oszlopokra hivatkozni.
• A DAX új keresési függvényeket tartalmaz, amelyek hasonlóak az Excel tömb- és vektorkeresési függvényeihez. A DAX függvényei azonban megkövetelik a táblák közötti kapcsolat létrehozását.
• Az oszlopokban szereplő adatoknak mindig azonos adattípusúaknak kell lenniük. Ha az adatok nem azonos típusúak, a DAX a teljes oszlopot arra az adattípusra módosítja, amely a legjobban alkalmas az összes értékre.

DAX-adattípusok

A Power Pivot-adatmodellekbe számos olyan adatforrásból importálhat adatokat, amelyek más adattípusokat támogathatnak. Amikor importálja vagy betölti az adatokat, majd számításokban vagy kimutatásokban használja fel őket, a program a Power Pivot-adattípusok egyikévé alakítja az adatokat. Az adattípusok listáját az Adatmodellek adattípusai című témakörben találja.

A tábla adattípus a DAX egy új adattípusa, amely számos új függvény bemeneteként vagy kimeneteként használható. A SZŰRŐ függvény például egy táblázatot fogad bemenetként, és egy olyan táblát ad kimenetként, amely csak a szűrési feltételeknek megfelelő sorokat tartalmazza. A táblázatfüggvények és az aggregálási függvények kombinálásával komplex számításokat végezhet dinamikusan meghatározott adathalmazokon. További információ: Összesítések a Power Pivotban.

A képletek és a relációs modell

A Power Pivot ablak egy olyan terület, ahol több adattáblázattal dolgozhat, és relációs modellben kapcsolhatja össze a táblázatokat. Ebben az adatmodellben a táblázatok kapcsolatokon keresztül kapcsolódnak egymáshoz, ami lehetővé teszi, hogy korrelációkat hozzon létre más táblázatok oszlopaival, és érdekesebb számításokat hozzon létre. Létrehozhat például olyan képleteket, amelyek összeadják egy kapcsolódó tábla értékeit, majd ezt az értéket egyetlen cellába mentik. Vagy a kapcsolódó tábla sorainak szabályozásához szűrőket alkalmazhat a táblákra és az oszlopokra. További információt az Adatmodellben szereplő táblázatok közötti kapcsolatok című témakörben talál.

Mivel a táblákat kapcsolatok segítségével kapcsolhatja össze, a kimutatások több különböző táblázatból származó oszlopokból származó adatokat is tartalmazhatnak.

Mivel azonban a képletekkel egész táblázatok és oszlopok is használhatók, a számításokat másképpen kell megtervezni, mint az Excelben.

• Általában az oszlopban található DAX-képletet a program mindig az oszlop összes értékhalmazára alkalmazza (sohasem csak néhány sorra vagy cellára).
• A Power Pivot-táblázatok minden sorában mindig ugyanannyi oszlopnak kell szerepelnie, és az oszlopok minden sorának ugyanolyan adattípust kell tartalmaznia.
• Amikor a táblákat kapcsolat köti össze, meg kell győződnie arról, hogy a kulcsként használt két oszlop értékei többségében megegyeznek. Mivel a Power Pivot nem tartja tiszteletben a hivatkozási integritást, előfordulhat, hogy egy kulcsoszlopban nem egyező értékek is létrejönnek kapcsolatok. Az üres vagy nem egyező értékek jelenléte azonban hatással lehet a képletek eredményére és a kimutatások megjelenésére. További információt a Keresések a Power Pivot-képletekben című témakörben talál.
• Amikor kapcsolatok használatával kapcsol össze táblázatokat, megnöveli a képletkiértékelés hatókörét vagy környezetét. Egy kimutatás képleteire például hatással lehet bármilyen szűrő, illetve a kimutatásban szereplő oszlop- és sorfejlécek. Írhat olyan képleteket, amelyek módosítják a kontextust, de a kontextus is okozhatja az eredmények váratlan változását. További információ: Környezet a DAX-képletekben.

Képletek eredményének frissítése

Az adatfrissítés és az újraszámítás két különálló, egymással összefüggő művelet, amelyeket meg kell értenie, ha összetett képleteket, nagy mennyiségű adatot vagy külső adatforrásokból nyert adatokat tartalmazó adatmodellt tervez.

Az adatok frissítése során a külső adatforrásból származó új adatokkal frissíti a munkafüzetben lévő adatokat. Az adatokat manuálisan is frissítheti a megadott időközönként. Ha a munkafüzetet egy SharePoint-webhelyen tette közzé, akkor ütemezheti a külső forrásból történő automatikus frissítést.

Az újraszámítás az a folyamat, amelynek során a képletek eredménye frissül, hogy tükrözze a képletek esetleges változásait, illetve hogy azok tükrözzék a módosításokat a mögöttes adatokban is. Az újraszámítás az alábbi módokon befolyásolhatja a teljesítményt:

• Számított oszlop esetén a képlet módosítása esetén mindig újra kell számítani a képlet eredményét a teljes oszlopban.
• Mérték esetében a képlet eredményének kiszámítása csak akkor történik meg, ha a mértéket a kimutatás vagy kimutatásdiagram környezetébe helyezi. A program akkor is újraszámítja a képletet, ha olyan sor- vagy oszlopfejlécet módosít, amely hatással van az adatszűrőkre, illetve amikor manuálisan frissíti a kimutatást.

Képletek hibaelhárítása

Hibák képletek írásakor

Ha hiba történik egy képlet definiálásakor, a képlet szintaktikai,szemantikai hibát vagy számítási hibát tartalmazhat.

A szintaktikai hibákat a legkönnyebb megoldani. Jellemzően hiányzó zárójelről vagy vesszőről van szó. Az egyes függvények szintaxisával kapcsolatos segítségért lásd a DAX függvényeinek ismertetését.

A másik hibatípus akkor fordul elő, ha a szintaxis megfelelő, de a hivatkozott érték vagy oszlop nem értelmezhető a képlet kontextusában. Az ilyen szemantikai és számítási hibákat az alábbi problémák bármelyike okozhatja:

• A képlet nem létező oszlopra, táblázatra vagy függvényre hivatkozik.
• A képlet látszólag helyes, de amikor az adatmotor beolvassa az adatokat, típuseltérést talál, és hibát jelez.
• A képlet helytelen számú vagy típusú paramétert ad át egy függvénynek.
• A képlet egy hibás oszlopra hivatkozik, ezért az értékei érvénytelenek.
• A képlet egy még nem feldolgozott oszlopra hivatkozik, ami azt jelenti, hogy rendelkezik metaadatokkal, de nem rendelkezik számításokhoz felhasználható tényleges adatokkal.

Az első négy esetben a DAX az érvénytelen képletet tartalmazó teljes oszlopot megjelöli. Az utolsó esetben a DAX kiszürkíti az oszlopot, ezzel jelezve, hogy az oszlop feldolgozatlan állapotban van.

Helytelen vagy szokatlan eredmények az oszlopértékek rangsorolásakor vagy rendezésekor

NaN (Nem szám) értéket tartalmazó oszlop rangsorolásakor vagy rendezésekor hibás vagy váratlan eredményeket kaphat. Ha egy számítás például elosztja a 0-t 0-val, NaN eredményt ad vissza.

Ennek az az oka, hogy a képletszerkesztő a numerikus értékek összehasonlításával végzi el a rendezést és a rangsorolást; a NaN azonban nem hasonlítható össze az oszlop többi számával.

A helyes eredmény érdekében feltételes utasításokkal és a HA függvénnyel tesztelheti a NaN-értékeket, és numerikus 0 értéket adhat eredményül.

Kompatibilitás az Analysis Services táblázatos modelljeivel és a DirectQuery móddal

A Power Pivotban készített DAX-képletek általában teljesen kompatibilisek az Analysis Services táblázatos modelljeivel. Ha azonban áttelepíti a Power Pivot-modellt egy Analysis Services-példányra, majd DirectQuery módban telepíti a modellt, tapasztal bizonyos korlátozásokat.

• Egyes DAX-képletek eltérő eredményeket adhatnak, ha a modellt DirectQuery módban telepíti.
• Egyes képletek érvényesítési hibákat okozhatnak a modell DirectQuery módban való üzembe helyezésekor, mert a képlet olyan DAX-függvényt tartalmaz, amelyet relációs adatforrás nem támogat.

További információt az Analysis Services táblázatos modellezési dokumentációjában talál az SQL Server 2012 BooksOnline szolgáltatásban.