A Környezet lehetővé teszi dinamikus elemzés elvégzését, amelynek során egy képlet eredménye az aktuális sor- vagy cellakijelölésnek és az esetleges kapcsolódó adatoknak megfelelően módosulhat. A szövegkörnyezet megértése és hatékony használata rendkívül fontos a nagy teljesítményű képletek és dinamikus elemzések létrehozásához, valamint a képletekben előforduló problémák elhárításához.
Ez a szakasz a környezetek különböző típusait határozza meg: sorkörnyezet, lekérdezési környezet és szűrőkörnyezet. Ismerteti, hogy a program hogyan értékeli ki a számított oszlopokban és a kimutatásokban szereplő képletek kontextusát.
A cikk utolsó része olyan részletes példákra hivatkozik, amelyek bemutatják, hogyan változnak a képletek eredményei a környezetnek megfelelően.
A környezet értelmezése
A Power Pivot képleteire hatással lehetnek a kimutatásokban alkalmazott szűrők, a táblázatok közötti kapcsolatok, valamint a képletekben használt szűrők. A kontextus az, ami lehetővé teszi a dinamikus elemzés elvégzését. A szövegkörnyezet megértése fontos a képletek felépítése és a hibák elhárítása során.
Különböző típusú környezetek léteznek: sorkörnyezet, lekérdezéskörnyezet és szűrőkörnyezet.
A sorkontextusra úgy is tekinthetünk, mint "az aktuális sorra". Ha számított oszlopot hozott létre, a sorkörnyezet az egyes sorok értékeiből és az aktuális sorhoz kapcsolódó oszlopok értékeiből áll. Vannak olyan függvények is (EARLIER és EARLIEST), amelyek az aktuális sorból nyernek egy értéket, majd felhasználják egy művelet végrehajtása során egy teljes táblán.
A lekérdezési környezet a kimutatás minden egyes cellájához a sor- és oszlopfejlécektől függő, implicit módon létrehozott adatok részhalmazára utal.
A szűrőkörnyezet az egyes oszlopokban engedélyezett értékek halmaza, amely a sorra alkalmazott vagy a képletben található szűrőkifejezésekkel meghatározott szűrési korlátozásokon alapul.
Sorkörnyezet
Ha egy számított oszlopban hoz létre képletet, a képlet sorkörnyezete az aktuális sor összes oszlopának értékeit tartalmazza. Ha a tábla egy másik táblához kapcsolódik, a tartalom a másik tábla aktuális sorhoz kapcsolódó összes értékét is tartalmazza.
Tegyük fel például, hogy létrehoz egy számított oszlopot (=[Fuvardíj] + [Adó], amely összead két oszlopot ugyanabból a táblázatból. Ez a képlet úgy viselkedik, mint az Excel-táblázatokban lévő képletek, amelyek automatikusan hivatkoznak az ugyanabból a sorból származó értékekre. Tartsa észben, hogy a táblázatok különböznek a tartományoktól: nem hivatkozhat az aktuális sor előtti sor értékére tartományjelöléssel, és nem hivatkozhat egyetlen tetszőleges értékre sem egy táblázatban vagy cellában. Mindig táblázatokkal és oszlopokkal kell dolgoznia.
A sorkörnyezet automatikusan a táblák közötti kapcsolatokat követve határozza meg, hogy a kapcsolódó táblák mely sorai vannak az aktuális sorhoz társítva.
A következő képlet például a RELATED függvény használatával lekér egy adóértéket egy kapcsolódó táblából a rendelés szállítási régiója alapján. Az adó értékét a program úgy határozza meg, hogy az aktuális táblában a régió értékét használja, megkeresi a régiót a kapcsolódó táblában, majd a kapcsolódó táblából beolvassa az adott régió adókulcsát.
= [Fuvardíj] + RELATED('Régió'[AdóMérték])
Ez a képlet egyszerűen lekéri az aktuális régió adókulcsát a Régió táblázatból. Nem kell tudnia vagy megadnia a táblákat összekötő kulcsot.
Többsoros környezet
A DAX emellett olyan függvényeket is tartalmaz, amelyek számításokat végeznek táblázaton. Ezek a függvények több aktuális sorral és aktuális sorkörnyezettel rendelkezhetnek. Programozási szempontból olyan képleteket hozhat létre, amelyek egy belső és egy külső hurokban ismétlődnek.
Tegyük fel például, hogy a munkafüzet tartalmaz egy Termékek és egy Értékesítés táblát. Célszerű lehet végignézni a teljes értékesítés táblázatot, amely tele van több terméket érintő tranzakciókkal, és megkeresni az egyes termékekhez rendelt legnagyobb mennyiséget bármely tranzakcióban.
Az Excelben ez a számítás köztes összegzések sorozatát igényli, amelyeket újra kell építeni, ha megváltoznak az adatok. Ha Ön az Excel hozzáértő felhasználója, olyan tömbképleteket készíthet, amelyek elvégzik ezt a feladatot. Másik lehetőségként a relációs adatbázisokban írhat beágyazott részkijelöléseket is.
A DAX segítségével azonban létrehozhat egyetlen olyan képletet, amely a helyes értéket adja vissza, és az eredmények automatikusan frissülnek, valahányszor adatokat vesz fel a táblázatokba.
=MAXX(SZŰRŐ(Értékesítés,[Termékkulcs]=KORÁBBAN([Termékkulcs])),Értékesítés[RendelésMennyiség])
A képlet részletes ismertetését a EARLIER függvény tartalmazza.
Röviden, a EARLIER függvény az aktuális műveletet megelőző művelet sorkörnyezetét tárolja. A függvény mindig két környezethalmazt tárol a memóriában: az egyik környezetkészlet a képlet belső ciklusának aktuális sorát, egy másik környezethalmaz pedig a képlet külső hurokának aktuális sorát képviseli. A DAX automatikusan betölti az értékeket a két hurok közé, így összetett összesítéseket hozhat létre.
Lekérdezési környezet
A lekérdezési környezet a képlethez implicit módon beolvasott adatok részhalmazára utal. Amikor egy mértéket vagy más értékmezőt egy kimutatás cellájába húz, a Power Pivot motor a sor- és oszlopfejlécek, a szeletelők és a jelentésszűrők segítségével állapítja meg a környezetet. Ezután a Power Pivot elvégzi a kimutatás celláinak kitöltéséhez szükséges számításokat. A visszaolvasott adathalmaz az egyes cellák lekérdezési környezete.
Mivel a kontextus változhat attól függően, hogy hol helyezi el a képletet, az eredménye is változik attól függően, hogy a képletet számos csoportosítást és szűrőt tartalmazó kimutatásban vagy szűrők nélküli és minimális környezetű számított oszlopban használja.
Tegyük fel például, hogy létrehozza ezt az egyszerű képletet, amely összeadja az Értékesítés tábla Nyereség oszlopában lévő értékeket:
=SZUM('Értékesítés'[Nyereség])
Ha ezt a képletet a Értékesítés táblázat egyik számított oszlopában használja, a teljes tábla esetében ugyanaz lesz a képlet eredménye, mivel a képlet lekérdezési környezete mindig a Forgalom tábla teljes adathalmaza. Az eredmények nyereséget mutatnak az összes régióra, minden termékre, minden évre és így tovább.
Általában azonban nem azt az eredményt szeretné több százszor látni, ehelyett egy adott év, egy adott ország vagy régió, egy adott termék vagy ezek valamilyen kombinációjának nyereségét szeretné megkapni, majd a végösszeget szeretné megkapni.
A kimutatásokban könnyen módosíthatja a környezetet oszlop- és sorfejlécek hozzáadásával vagy eltávolításával, illetve szeletelők hozzáadásával vagy eltávolításával. Létrehozhat egy képletet, mint amilyen a fenti, egy mértékben, majd egy kimutatásba húzhatja. Amikor oszlop- vagy sorfejléceket ad a kimutatáshoz, azzal módosítja azt a lekérdezési környezetet, amelyben a mértéket kiértékeli. A szeletelési és szűrési műveletek a környezetet is befolyásolják. Ezért a kimutatásban használt ugyanazt a képletet minden cella esetében más lekérdezési környezetben értékeli a program.
Szűrőkörnyezet
Szűrőkörnyezetet akkor ad hozzá a rendszer, ha egy oszlopban vagy táblázatban engedélyezett értékhalmazra vonatkozó szűrési korlátozásokat ad meg argumentumok használatával egy képletben. A szűrőkörnyezet más környezeteken, például sor- vagy lekérdezéskörnyezeten felül alkalmazható.
Egy kimutatás például a sor- és oszlopfejlécek alapján számítja ki az egyes cellák értékeit, a lekérdezéskörnyezetről szóló előző szakaszban leírt módon. A kimutatáshoz adott mértékekben vagy számított oszlopokban azonban szűrőkifejezéseket adhat meg, amelyekkel szabályozhatja a képletben használt értékeket. Szelektíven törölheti is egyes oszlopok szűrőit.
A képletszűrők létrehozásáról a Szűrőfüggvények című témakörben olvashat bővebben.
Az ALL függvénynél példát talál arra, hogy miként lehet a szűrők törlésével végösszeget kiszámítani.
A szűrők képleteken belüli szelektív törlésére és alkalmazására az ALLEXCEPT függvényben talál példákat.
Ezért át kell néznie a kimutatásokban használt mértékek vagy képletek definícióját annak érdekében, hogy tisztában legyen a szűrőkontextussal a képletek eredményeinek értelmezésekor.
Kontextus meghatározása képletekben
Képletek létrehozásakor az Excelhez készült PowerPivot először ellenőrzi az általános szintaxist, majd összeveti a megadott oszlopok és táblázatok nevét az aktuális környezetben lehetséges oszlopokkal és táblázatokkal. Ha a Power Pivot nem találja a képletben megadott oszlopokat és táblázatokat, hibaüzenet jelenik meg.
A környezet meghatározása az előző szakaszokban leírt módon történik a munkafüzetben rendelkezésre álló táblák, a táblák közötti kapcsolatok és az alkalmazott szűrők alapján.
Ha például éppen most importált néhány adatot egy új táblába, és nem alkalmazott szűrőket, a táblázat összes oszlopa a jelenlegi környezet részét képezi. Ha több táblája is van, amelyek kapcsolatok alapján össze vannak kötve, és oszlopfejlécek hozzáadásával és szeletelőkkel szűrt kimutatást használ, a környezet magában foglalja a kapcsolódó táblázatokat és az adatokon végrehajtott esetleges szűrőket.
A környezet egy hatékony fogalom, amely megnehezítheti a képletekkel kapcsolatos hibák elhárítását. Azt javasoljuk, hogy kezdje egyszerű képletekkel és kapcsolatokkal, hogy lássa, hogyan működik a kontextus, majd kísérletezzen egyszerű képletekkel a kimutatásokban. A következő szakaszban azt is bemutatjuk, hogy a képletek hogyan használnak különböző környezeteket az eredmények dinamikus visszaadására.
Példák képletek kontextusára
- A RELATED függvény kiterjeszti az aktuális sor környezetét, hogy egy kapcsolódó oszlop értékeit is magába foglalja. Ez lehetővé teszi a keresések végrehajtását. A témakörben szereplő példa a szűrés és a sorkörnyezet kölcsönhatását szemlélteti.
- A SZŰRŐ függvénnyel megadhatja az aktuális környezetbe belefoglalandó sorokat. A témakörben szereplő példák azt is szemléltetik, hogy miként ágyazhatók be szűrők összesítést végző egyéb függvényekbe.
- Az ALL függvény beállítja a képleten belüli kontextust. Használatával felülbírálhatja a lekérdezési környezet eredményeként alkalmazott szűrőket.
- Az ALLEXCEPT függvénnyel egy megadott kivételével minden szűrőt eltávolíthat. Mindkét témakör tartalmaz olyan példákat, amelyek végigvezetik Önt a képletek felépítésén és a bonyolult környezetek megértésén.
- A EARLIER és a LEGKORÁBBI függvénnyel számításokat végezhet a táblázatok között, miközben egy belső hurokból hivatkozik egy értékre. Ha ismeri a rekurzió fogalmát, valamint a belső és külső hurkokat, értékelni fogja a EARLIER és a EARLIER függvény nyújtotta teljesítményt. Ha még nem ismeri ezeket a fogalmakat, akkor érdemes figyelmesen követnie a példában leírt lépéseket, hiszen láthatja, hogy a számítások során miként használja fel a belső és külső összefüggéseket.
Hivatkozási integritás
Ez a szakasz néhány speciális fogalmat ismertet a kapcsolatok által összekötött Power Pivot-táblázatok hiányzó értékeivel kapcsolatban. Ez a szakasz akkor lehet hasznos, ha több táblázatot és összetett képleteket tartalmazó munkafüzetei vannak, és segítségre van szüksége az eredmények megértéséhez.
Ha még nem ismeri a relációs adatok fogalmát, javasoljuk, hogy először olvassa el a Kapcsolatok áttekintése című bevezető témakört.
A hivatkozási integritás és a Power Pivot-kapcsolatok
A Power Pivot nem követeli meg a két tábla közötti hivatkozási integritás megőrzését ahhoz, hogy érvényes kapcsolat létrejöjjön. Ehelyett a program egy üres sort hoz létre minden egyes egy-a-többhöz kapcsolat "egy" végén, és ezzel kezeli a kapcsolódó tábla összes nem egyező sorát. Gyakorlatilag külső SQL-illesztésként működik.
Ha a kimutatásokban a kapcsolat egy oldala szerint csoportosítja az adatokat, a kapcsolat "több" oldalán lévő nem egyező adatok egy csoportba kerülnek, és az üres sorfejlécjel kerülnek az összegbe. Az üres fejléc nagyjából megfelel az "ismeretlen tag" fejlécnek.
Az ismeretlen tag ismertetése
Az ismeretlen tag fogalma valószínűleg ismerős Önnek, ha dolgozott már többdimenziós adatbázis-rendszerekkel, például az SQL Server Analysis Services-szel. Ha a kifejezés még nem ismert Önnek, az alábbi példa ismerteti, hogy mi az ismeretlen tag, és ez hogyan befolyásolja a számításokat.
Tegyük fel, hogy olyan számítást szeretne létrehozni, amely összegzi az egyes áruházak havi értékesítését, de a Sales táblázat egyik oszlopából hiányzik egy érték az üzlet nevéhez. Mivel az Áruház és az Értékesítés táblázatot az üzlet neve köti össze, mi várható a képletben? Hogyan csoportosítsa vagy jelenítse meg a kimutatás azokat az értékesítési adatokat, amelyek nem kapcsolódnak egy meglévő üzlethez?
Ez a probléma gyakori az adatraktárakban, ahol a tényadatokat tartalmazó nagy táblázatoknak logikailag kapcsolódniuk kell az adattárolókról, régiókról, valamint a tények kategorizálásához és kiszámításához használt egyéb attribútumokról információkat tartalmazó dimenziótáblákkal. A probléma megoldása érdekében a rendszer ideiglenesen hozzárendel minden olyan új tényt, amely nem kapcsolódik egy meglévő entitáshoz. Ezért jelennek meg a nem kapcsolódó tények egy kimutatásban egy üres cím alatt csoportosítva.
Az üres értékek kezelése és az üres sor kezelése
Az üres értékek nem ugyanazok, mint az ismeretlen tag elhelyezésére felvett üres sorok. Az üres érték egy speciális érték, amely null értékeket, üres karakterláncokat és más hiányzó értékeket jelöl. Az üres értékről, valamint a többi DAX-adattípusról az Adattípusok az adatmodellekben című témakörben talál további információt.