A dátumtáblázatok a Power Pivotban nélkülözhetetlenek az adatok időbeli változásának böngészéséhez és kiszámításához. Ez a cikk alaposan megismeri a dátumtáblázatokat, és hogy hogyan hozhatja létre őket a Power Pivot bővítményben. A cikk különösen a következőket ismerteti:
- Miért fontos a dátumtáblázat a dátumok és időpontok szerinti adatok böngészéséhez és kiszámításához?
- Dátumtáblázat felvétele az adatmodellbe a Power Pivot használatával.
- Új dátumoszlopok (például Év, Hónap és Időszak) létrehozása egy dátumtáblázatban.
- Dátumtáblázatok és ténytáblák közötti kapcsolatok létrehozásának módja.
- Hogyan dolgozzunk az idővel.
Ez a cikk a Power Pivot új felhasználóinak készült. Fontos azonban, hogy már jól ismeri az adatok importálását, a kapcsolatok létrehozását, valamint a számított oszlopok és mértékek létrehozását.
Ez a cikk nem ismerteti , hogyan használhatók a DAX Time-Intelligence függvényei mértékképletekben. A mértékek DAX időintelligencia-függvényekkel való létrehozásáról további információt az Időintelligencia az Excel Power Pivot bővítményében című témakörben talál.
Megjegyzés
A Power Pivotban a "mérték" és a "számított mező" név szinonima. A cikkben végig a mérték nevet használjuk. További információ: Mértékek a Power Pivot programban.
Tartalom
A dátumtáblázatok ismertetése
Szinte minden adatelemzés magában foglalja a dátum- és időadatok böngészését és összehasonlítását. Előfordulhat például, hogy össze szeretné összegezni az elmúlt pénzügyi negyedév értékesítési összegeit, majd össze szeretné hasonlítani ezeket más negyedévekkel, vagy ki szeretné számítani egy számla hónap végi záró egyenlegét. A fenti esetek mindegyikében dátumok használatával csoportosítja és összesíti egy adott időszak értékesítési tranzakcióit vagy egyenlegeit.
Power View-jelentés
Egy dátumtáblázat a dátumok és időpontok számos különböző ábrázolását tartalmazhatja. Egy dátumtáblázatban például gyakran szerepelnek olyan oszlopok, mint például a Pénzügyi év, a Hónap, a Negyedév vagy az Időszak, amelyeket kiválaszthat mezőknek egy mezőlistából, amikor kimutatásokban vagy Power View nézetet tartalmazó jelentésekben szeleteli és szűri az adatokat.
Power View nézetbeli mezőlista
Ahhoz, hogy az Év, a Hónap és a Negyedév dátumoszlopok a megfelelő tartományon belül az összes dátumot tartalmazzák, a dátumtáblázatnak legalább egy oszlopban szerepelnie kell egy összefüggő dátumkészlettel. Ez azt jelenti, hogy az oszlopnak a dátumtáblázatban szereplő minden évben minden naphoz külön sornak kell tartoznia.
Ha például a keresett adatok között 2010. február 1. és 2012. november 30. közötti dátumok szerepelnek, és Ön egy naptári évre vonatkozik, akkor olyan dátumtartományt szeretne létrehozni, amely legalább a 2010. január 1. és 2012. december 31. közötti dátumtartományt tartalmazza. A dátumtáblázatban minden évnek tartalmaznia kell az egyes évek összes napját. Ha rendszeresen frissíteni fogja az adatait újabb adatokkal, érdemes lehet a záró dátumot egy vagy két évvel későbbre hozni, hogy ne kelljen frissítenie a dátumtáblázatot az idő múlásával.
Összefüggő dátumkészletet tartalmazó dátumtáblázat
Ha pénzügyi évre készít jelentést, létrehozhat egy dátumtáblázatot az egyes pénzügyi évekre vonatkozóan összefüggő dátumkészlettel. Ha például az Ön pénzügyi éve március 1-jén kezdődik, és a 2010-es pénzügyi évre vonatkozóan az aktuális dátumig (például a 2013-as pénzügyi évig) vannak adatai, létrehozhat egy dátumtáblázatot, amely 2009. március 1-jén kezdődik, és a 2013-as pénzügyi év utolsó napjáig az összes pénzügyi év legalább minden napját tartalmazza.
Ha a naptári évet és a pénzügyi évet is megjeleníti, nem kell külön dátumtáblákat létrehoznia. Egyetlen dátumtáblázat egy naptári év, egy pénzügyi év vagy akár egy tizenhárom négyhetes időszak szerinti naptárat is tartalmazhat. A fontos dolog az, hogy a dátumtáblázat folyamatosan tartalmazza a dátumokat az összes évre vonatkozóan.
Dátumtáblázat hozzáadása az adatmodellhez
Dátumtáblázatot többféleképpen is hozzáadhat az adatmodellhez:
- Importálhat relációs adatbázisból vagy más adatforrásból.
- Hozzon létre egy dátumtáblázatot az Excelben, majd másoljon vagy csatoljon egy új táblázatot a Power Pivot programban.
- Importálás a Microsoft Azure Piactérről.
Vizsgáljuk meg ezeket közelebbről.
Importálás relációs adatbázisból
Ha adattárházból vagy más típusú relációs adatbázisból importálja az adatai egy részét vagy egészét, akkor valószínűleg már létezik dátumtáblázat, illetve kapcsolatok vannak a táblázat és a többi importált adat között. A dátumok és a formátum valószínűleg megegyezik a tényadatok között szereplő dátumokkal, és valószínűleg jóval a múltban kezdődnek és messzire nyúlnak vissza. Az importálni kívánt dátumtáblázat nagyon nagy lehet, és az adatmodellben szükségesnél több dátumot tartalmazhat. A Power Pivot táblaimportáló varázslójának speciális szűrőfunkcióival kiválaszthatja csak azokat a dátumokat és oszlopokat, amelyekre valóban szüksége van. Ez jelentősen csökkentheti a munkafüzet méretét és javíthatja a teljesítményt.
Tábla importálása varázsló
A legtöbb esetben nem lesz szükség további oszlopok létrehozására (például Pénzügyi év, Hét, Hónap neve stb.), mert ezek már léteznek az importált táblában. Bizonyos esetekben azonban, miután a dátumtáblázatot importálta az adatmodellbe, szükség lehet további dátumoszlopok létrehozására egy adott jelentési igénytől függően. Szerencsére a DAX használatával ez könnyen elvégezhető. Dátumtábla mezők létrehozásáról a későbbiekben tudhat meg többet. Minden környezet más és más. Ha nem biztos abban, hogy az adatforrásokhoz tartozik-e kapcsolódó dátum- vagy naptártábla, forduljon az adatbázisgazdához.
Dátumtáblázat létrehozása az Excelben
Létrehozhat egy dátumtáblázatot az Excelben, majd átmásolhatja egy új táblázatba az adatmodellben. Ez valóban elég könnyű és nagy rugalmasságot biztosít.
Az Excelben a dátumtáblázat létrehozásakor egyetlen oszlopból kell kiindulnia, amely egy összefüggő dátumtartománnyal rendelkezik. Ezután további oszlopokat (például Év, Negyedév, Hónap, Pénzügyi év, Időszak stb.) hozhat létre az Excel-munkalapon Excel-képletek használatával, vagy a táblázat adatmodellbe másolása után számított oszlopként is létrehozhatja őket. Megtudhatja, hogy miként hozhat létre további dátumoszlopokat a Power Pivotban a jelen cikk későbbi, Új dátumoszlopok hozzáadása a dátumtáblázathoz című szakaszában.
Útmutató: Dátumtáblázat létrehozása az Excelben és másolása az adatmodellbe
Az Excel egy üres munkalapjának A1 cellájába írjon be egy oszlopfejlécnevet a dátumtartomány azonosításához. Ez általában a következőhöz hasonló: Dátum, DátumIdő vagy DátumKulcs.
Írjon be egy kezdő dátumot az A2 cellába. Például: 2010.01.01.
Kattintson a kitöltőjelre, és húzza lefelé a záró dátumot tartalmazó sorszámhoz. Például: 2016.12.31.
Jelölje ki a Dátum oszlop összes sorát (beleértve az A1 cellában lévő fejlécnevet is).
A Stílusok csoportban kattintson a Formázás táblázatként lehetőségre, majd válasszon egy stílust.
A Formázás táblázatként párbeszédpanelen kattintson az OK gombra.
Másolja a vágólapra az összes sort, beleértve a fejlécet is.
A Power Pivot Kezdőlap lapján kattintson a Beillesztés gombra.
A Beillesztés előnézete> táblázatneve mezőbe írjon be egy nevet (például Dátum vagy Calendar). Hagyja bejelölve az Első sor oszlopfejlécként jelölőnégyzetet, majd kattintson az OK gombra.
Az új dátumtáblázat (a példában a neve Calendar) a Power Pivotban így néz ki:
Megjegyzés
A Hozzáadás az adatmodellhez paranccsal is létrehozhat csatolt táblát. Emiatt azonban a munkafüzet mérete szükségtelenül nagy, mivel a munkafüzet két verziót tartalmaz a dátumtáblázatból; egyet az Excelben, egyet pedig a Power Pivotban.
Megjegyzés
A dátum név egy kulcsszó a Power Pivotban. Ha a Power Pivotban létrehozott táblázatnak Dátum nevet ad, akkor a táblázatnevet szimpla idézőjelek közé kell tennie minden olyan DAX-képletben, amely argumentumában hivatkozik rá. A cikkben példaképek és képletek mindegyike a Power Pivotban létrehozott Calendar nevű dátumtáblázatra hivatkozik.
Ekkor egy dátumtáblázat szerepel az adatmodellben. A DAX használatával új dátumoszlopokat (például Év, Hónap stb.) adhat hozzá.
Új dátumoszlopok hozzáadása a dátumtáblázathoz
Egy dátumtáblázat olyan táblázat, amelyben egyetlen dátumoszlop van, és minden év minden napja egy sort tartalmaz, egy dátumtartomány összes dátumának meghatározása szempontjából fontos. Emellett ez szükséges a ténytábla és a dátumtábla közötti kapcsolat létrehozásához is. Ez az egyetlen dátumoszlop azonban, amelyben minden nap egy sor szerepel, nem hasznos, ha kimutatásokban vagy Power View nézetet tartalmazó jelentésekben dátum szerint elemezni az adatokat. Ha azt szeretné, hogy a dátumtáblázat olyan oszlopokat tartalmazzon, amelyek segítségével összesítheti egy dátumtartományra vagy dátumcsoportra vonatkozó adatokat. Előfordulhat például, hogy havi vagy negyedéves értékesítési összegeket szeretne összegezni, vagy olyan mértéket szeretne létrehozni, amely éves növekedést számol. Minden ilyen esetben a dátumtáblázatnak év, hónap vagy negyedév oszlopokra van szüksége, amelyek lehetővé teszik az adott időszakra vonatkozó adatok összesítését.
Ha a dátumtáblázatot relációs adatforrásból importálta, előfordulhat, hogy az már tartalmazza a kívánt különféle típusú dátumoszlopokat. Bizonyos esetekben szükség lehet az oszlopok némelyikének módosítására, vagy további dátumoszlopok létrehozására. Különösen igaz ez akkor, ha saját dátumtáblázatot hoz létre az Excelben, és bemásolja az adatmodellbe. Szerencsére a DAX Dátum és idő függvényeivel meglehetősen egyszerűen hozhatók létre új dátumoszlopok a Power Pivotban.
Tipp:
Ha még nem használta a DAX szolgáltatást, a rövid útmutató nagyszerű kiindulópont a tanulás megkezdéséhez : A DAX alapjai 30 perc alatt elsajátításaOffice.com.
A DAX dátum- és időfüggvényei
Ha használt valaha dátum- és időfüggvényeket Excel-képletekben, akkor valószínűleg ismeri a dátum- és időfüggvényeket. Bár ezek a függvények hasonlítanak az Excelben használt megfelelőikre, van néhány fontos különbség is:
- A DAX dátum- és időfüggvényei datetime adattípust használnak.
- Argumentumként felvehetnek értékeket egy oszlopból is.
- Ezek dátumértékek visszaadására és/vagy módosítására használhatók.
Ezek a függvények gyakran használatosak egyéni dátumoszlopok létrehozásakor a dátumtáblázatokban, ezért fontos megérteni őket. E függvények némelyikét használjuk például az Év, a Negyedév, a Pénzügyi hónap stb. oszlopainak létrehozásához.
Megjegyzés
A DAX rendszerben használt dátum- és időfüggvények nem egyeznek meg az időintelligencia-függvényekkel. További információ az időintelligenciáról az Excelhez készült Power Pivotban.
A DAX az alábbi dátum- és időfüggvényeket tartalmazza:
- DÁTUM
- DÁTUMÉRTÉK
- NEXTDAY
- KALK.DÁTUM
- HÓNAP.UTOLSÓ.NAP
- HOUR
- MINUTE
- MONTH
- MOST
- SECOND
- TIME
- IDŐÉRTÉK
- MA
- HÉT.NAPJA
- HÉT.SZÁMA
- YEAR
- TÖRTÉV
A DAX számos egyéb függvényét is használhatja képleteiben. Az itt ismertetett képletek közül például számos matematikai és trigonometrikus függvényt (például MOD és CSONC), logikai függvényt ( HA) és szöveges függvényt (például FORMAT ) használ A többi DAX-függvényről a jelen cikk későbbi További források című szakaszában talál további információt.
Képletpéldák egy naptári évre
Az alábbi példák olyan képleteket mutatnak be, amelyekkel további oszlopokat lehet létrehozni egy Calendar nevű dátumtáblázatban. Az egyik Dátum nevű oszlop már létezik, és a 2010. 01. 01. és 2016. 12. 31. közötti dátumtartományt tartalmazza.
Év
=ÉV([dátum])
Ebben a képletben az ÉV függvény az évet adja vissza a Dátum oszlopban lévő értékből. Mivel a Dátum oszlop értéke datetime adattípusú, az ÉV függvény tudja, hogy miként adja meg belőle az évet.
Hónap
=HÓNAP([dátum])
Ebben a képletben, hasonlóan az ÉV függvényhez, egyszerűen a HÓNAP függvénnyel visszaadhatunk egy hónapértéket a Dátum oszlopból.
Negyedév
=INT(([Hónap]+2)/3)
Ebben a képletben az INT függvényt használjuk, hogy egy dátumértéket egész számként adjon vissza. Az INT függvényhez megadott argumentum a Hónap oszlopban szereplő érték, amelyhez hozzáadunk 2-t, majd elosztjuk 3-mal, és megkapjuk a negyedév értékét (1–4).
Hónap neve
=FORMAT([dátum];"mmmm")
Ebben a képletben a hónap nevét a FORMAT függvénnyel alakítjuk át szöveggé a Dátum oszlop egyik numerikus értékén. Első argumentumként megadjuk a Dátum oszlopot, majd a formátumot; Azt szeretnénk, hogy a hónap neve minden karaktert tartalmazzon, ezért az "MMMM" karaktert használjuk. Az eredmény így néz ki:
Ha a hónap nevét három betűre rövidítve szeretnénk visszaadni, a formátumargumentumban az "mmm" kifejezést kell használni.
A hét napja
=FORMAT([dátum];"nn")
Ebben a képletben a FORMAT függvényt használjuk a nap nevének megállapítására. Mivel csak egy rövidített napnevet szeretnénk megadni, a "nnn" értéket kell megadni a formátum argumentumban.
Kimutatásminta
Ha rendelkezik dátummezőkkel (például év, negyedév, hónap stb.), felhasználhatja őket egy kimutatásban vagy jelentésben. Az alábbi képen például a Sales fact table SalesAmount (ÉrtékesítésiMennyiség) mezője látható a VALUES (VALUES) területen, valamint a Year (Év) és a Negyedév mező a Calendar dimenziótáblázatból a SOROK esetén. A SalesAmount összesítése évre és negyedévre vonatkozó kontextusban történik.
Képletpéldák egy pénzügyi évre
Pénzügyi év
=HA([Hónap]<= 6,[Év],[Év]+1)
Ebben a példában a pénzügyi év július 1-jén kezdődik.
Nincs olyan függvény, amely pénzügyi évet tudna kinyerni egy dátumértékből, mivel a pénzügyi év kezdő és záró dátuma gyakran eltér a naptáriaktól. A pénzügyi év meghatározásához először egy HA függvénnyel vizsgáljuk meg, hogy a Hónap argumentum értéke kisebb-e 6-nál, vagy egyenlő-e vele. A második argumentumban, ha a Hónap argumentum értéke 6 vagy annál kisebb, akkor az Év oszlopból adja vissza az értéket. Ha nem, akkor a Year (Év) értéket adja vissza, és adjon hozzá 1-et.
A pénzügyi év végi havi értékek úgy is megadhatók, hogy egyszerűen megadja a hónapot megadó mértéket. Például: FYE:=6. Ezután a hónap száma helyett hivatkozhat a mérték nevére. Például: =HA([Hónap]<=[ÉV];[Év];[Év]+1). Ez nagyobb rugalmasságot biztosít, amikor a pénzügyi év végi hónapra több különböző képlettel hivatkozik.
Pénzügyi hónap
=HA([Hónap]<= 6, 6+[Hónap], [Hónap]- 6)
Ebben a képletben megadjuk, hogy ha a [Hónap] értéke kisebb 6-nál vagy azzal egyenlő, akkor vegye a 6-ot, és adja hozzá a Hónap értékét, ellenkező esetben vonjon ki 6-ot a [Hónap] értékéből.
Pénzügyi negyedév
=INT(([FiscalMonth]+2)/3)
A pénzügyi negyedévhez használt képlet nagyjából ugyanaz, mint a naptári év negyedévében a negyedévre vonatkozó. Az egyetlen különbség az, hogy a [Hónap] helyett a [FiscalMonth] értéket adjuk meg.
Ünnepnapok vagy különleges dátumok
Érdemes lehet felvenni egy dátumoszlopot, amely azt jelzi, hogy bizonyos napok ünnepnapok vagy más különleges dátumok. Előfordulhat például, hogy az Ünnepnapok mező kimutatáshoz, szeletelőként vagy szűrőként való hozzáadásával össze szeretné összegezni az Ünnepnapok mezőt. Más esetekben azonban célszerű lehet kizárni ezeket a dátumokat más dátumoszlopokból vagy egy mértékből.
Az ünnepnapok és a különleges napok felvétele meglehetősen egyszerű. Az Excelben létrehozhat egy táblázatot, amely tartalmazza a dátumokat. Ezután a másolással vagy a Hozzáadás az adatmodellhez lehetőséggel csatolt táblaként felveheti azt az adatmodellbe. A legtöbb esetben nem szükséges kapcsolatot létrehozni a tábla és a Calendar tábla között. Az arra hivatkozó képletek a LOOKUPVALUE függvénnyel visszaadhatnak értékeket.
Az alábbi táblázatban egy Excelben létrehozott táblázat látható, amely a dátumtáblázatba felvenni kívánt ünnepnapokat tartalmazza:
| Dátum | Ünnepnap |
|---|---|
| 1/1/2010 | Szilveszter |
| 11/25/2010 | Hálaadás |
| 12/25/2010 | Karácsony |
| 2011.01.01. | Szilveszter |
| 11/24/2011 | Hálaadás |
| 12/25/2011 | Karácsony |
| 2012.01.01 | Szilveszter |
| 2012.11.22. | Hálaadás |
| 12/25/2012 | Karácsony |
| 1/1/2013 | Szilveszter |
| 11/28/2013 | Hálaadás |
| 12/25/2013 | Karácsony |
| 11/27/2014 | Hálaadás |
| 12/25/2014 | Karácsony |
| 2014.01.01. | Szilveszter |
| 11/27/2014 | Hálaadás |
| 12/25/2014 | Karácsony |
| 1/1/2015 | Szilveszter |
| 11/26/2014 | Hálaadás |
| 12/25/2015 | Karácsony |
| 2016.01.01. | Szilveszter |
| 11/24/2016 | Hálaadás |
| 12/25/2016 | Karácsony |
A dátumtáblázatban létrehozunk egy Ünnepnapok nevű oszlopot, és egy, az alábbihoz hasonló képletet használunk:
=LOOKUPVALUE(Holidays[Holiday];Holidays[date];Calendar[date])
Vizsgáljuk meg közelebbről ezt a képletet.
A LOOKUPVALUE függvény segítségével nyerjük ki az Ünnepnapok tábla Ünnepnapok oszlopának értékeit. Az első argumentumban azt az oszlopot adjuk meg, amelyben az eredmény lesz. Az Ünnepnapok táblában az Ünnepnapok oszlopot adja meg, mert azt szeretnénk visszakapni.
=LOOKUPVALUE(Holidays[Holiday];Holidays[date];Calendar[date])
Ezután megadjuk a második argumentumot, azt a keresési oszlopot, amely a keresendő dátumokat tartalmazza. Az Ünnepnapok táblázat Dátum oszlopát az alábbiak szerint adjuk meg:
=LOOKUPVALUE(Holidays[Holiday];Holidays[date];Calendar[date])
Végezetül megadjuk a Calendar táblának azt az oszlopát, amely az Ünnepnapok táblában keresni kívánt dátumokat tartalmazza. Ez természetesen a Calendar táblázat Dátum oszlopa.
=LOOKUPVALUE(Holidays[Holiday];Holidays[date];Calendar[date])
Az Ünnepnapok oszlop minden olyan sor esetében megjeleníti az ünnepnap nevét, amelynek dátumértéke megegyezik az Ünnepnapok táblázat egyik dátumával.
Egyéni naptár – tizenhárom négy hetes időszak
Egyes szervezetek, például a kiskereskedelmi vagy az étkeztetési szolgáltatók, gyakran különböző időszakokra számolnak be, például tizenhárom négy hetes időszakokról. Tizenhárom négyhetes naptárral mindegyik időszak 28 nap; ezért minden időszak négy hétfőt, négy keddet, négy szerdát és így tovább tartalmaz. Minden időszak ugyanannyi napot tartalmaz, és az ünnepnapok általában minden évben ugyanabba az időszakba esnek. Az időszakot a hét bármely napján megkezdheti. A naptári vagy pénzügyi év dátumaihoz hasonlóan a DAX segítségével is létrehozhat további oszlopokat egyéni dátumokkal.
Az alábbi példákban az első teljes időszak a pénzügyi év első vasárnapján kezdődik. Ebben az esetben a pénzügyi év 7/1-én kezdődik.
Hét
Ez az érték adja meg a hét számát a pénzügyi év első teljes hetével kezdődően. Ebben a példában az első teljes hét vasárnap kezdődik, így a Calendar táblában szereplő első pénzügyi év első teljes hete valójában 2010. 07. 04-én kezdődik, és a Calendar táblában szereplő utolsó teljes hétig tart. Habár ez az érték önmagában nem annyira hasznos elemzéskor, szükséges számítást végezni más 28 napos periódusú képletekben való használatához.
=INT([dátum]-40356)/7)
Vizsgáljuk meg közelebbről ezt a képletet.
Először hozzon létre egy képletet, amely a Dátum oszlop értékeit adja vissza egész számként, például:
=INT([dátum])
Ezután az első pénzügyi év első vasárnapját szeretnénk megkeresni. Látjuk, hogy 2010/7/4 van.
Most ebből az értékből ki kell vonni a 40356-ot (a 2010. 06. 27. szám, az előző pénzügyi év utolsó vasárnapjának egész száma), hogy megkapja a Calendar táblázatban lévő napok kezdete óta eltelt napok számát, a következőképpen:
=INT([dátum]-40356)
Ezután ossza el az eredményt 7-tel (a hét napjaival) a következő módon:
=INT(([dátum]-40356)/7)
Az eredmény így néz ki:
Period
Ebben az egyéni naptárban az időszak 28 napot tartalmaz, és mindig vasárnap kezdődik. Ez az oszlop az első pénzügyi év első vasárnapjával kezdődő időszak számát adja eredményül.
=INT(([Hét]+3)/4)
Vizsgáljuk meg közelebbről ezt a képletet.
Először hozzunk létre egy képletet, amely visszaad egy értéket a Hét oszlopból egész számként, a következőképpen:
= INT([Hét])
Ezután adjon hozzá hármat ehhez az értékhez a következőképpen:
=INT([Hét]+3)
Ezután ossza az eredményt 4-gyel a következő módon:
=INT(([Hét]+3)/4)
Az eredmény így néz ki:
Időszak Pénzügyi év
Ez az érték az adott időszakra vonatkozó pénzügyi évet adja eredményül.
=INT(([Period]+12)/13)+2008
Vizsgáljuk meg közelebbről ezt a képletet.
Először hozzon létre egy képletet, amely a Pont értékét adja vissza, és 12-t ad hozzá:
=([Pont]+12)
Az eredményt elosztjuk 13-mal, mivel a pénzügyi évben tizenhárom 28 napos időszak van:
=(([Pont]+12)/13)
A 2010. évet adja hozzá, mivel ez az első év a táblázatban:
=(([Időszak]+12)/13)+2010
Végül az INT függvénnyel eltávolítjuk az eredmény tetszőleges törtjét, és 13-mal elosztva egész számot adunk vissza a következőképpen:
= INT(([Időszak]+12)/13)+2010
Az eredmény így néz ki:
Időszak a pénzügyi évben
Ez az érték az időszak számát adja vissza (1 és 13 között) minden pénzügyi év első teljes időszakával kezdve (vasárnap kezdődően).
=HA(MARADÉK([Pont];13), MARADÉK([Pont],13),13)
Ez a képlet kicsit összetettebb, ezért először egy jobban érthető nyelven írjuk le. Ez a képlet a következőt jelenti: a [Pont] értékét el kell osztani 13-mal, hogy megkapja az év időszakszámát (1-13). Ha ez a szám 0, akkor a visszaadott érték 13.
Először hozzon létre egy képletet, amely 13-mal adja vissza a Periódus értékének maradékát. A MOD-ot (matematikai és trigonometrikus függvényeket) így használhatjuk:
= MARADÉK([pont];13)
Ez az érték többnyire a kívánt eredményt adja, kivéve abban az esetben, ha a Időszak mező értéke 0, mivel ezek a dátumok nem esnek az első pénzügyi évbe, mint a példában szereplő Calendar dátumtáblázat első öt napjába. Erről gondoskodni tudunk egy HA függvénnyel. Ha az eredmény 0, akkor 13-at adunk vissza, az alábbiak szerint:
= HA(MARADÉK([Pont];13);MARADÉK([Pont];13);13)
Az eredmény így néz ki:
Kimutatásminta
Az alábbi képen egy kimutatás látható, amelyben a SalesAmount mező a Sales fact table (VALUES), a PeriodFiscalYear és PeriodInFiscalYear mező pedig a Calendar date dimension table ROWS (SOROK) értékkel rendelkezik. A SalesAmount összesítése pénzügyi év és a pénzügyi év 28 napos időszaka szerint történik.
Kapcsolatok
Miután létrehozott egy dátumtáblázatot az adatmodellben, a kimutatásokban és jelentésekben való böngészéshez és a dátumdimenziós táblázat oszlopai alapján összesítheti az adatokat, kapcsolatot kell létrehoznia a tranzakciós adatokkal rendelkező ténytábla és a dátumtábla között.
Mivel dátumokon alapuló kapcsolatot kell létrehoznia, ügyeljen arra, hogy a dátum adattípusú oszlopok között hozza létre a kapcsolatot.
A ténytábla összes dátumértékéhez a dátumtáblázat kapcsolódó keresési oszlopának egyező értékeket kell tartalmaznia. Például az Értékesítési adatok tábla azon sorának (tranzakciórekordjának), amelynek a DateKey oszlopában 08/15/2012 12:00 AM az érték, rendelkeznie kell megfelelő értékkel a (Calendar) nevű tábla kapcsolódó Dátum oszlopában. Ez az egyik legfontosabb oka annak, hogy azt szeretné, hogy a dátumtáblázat dátumoszlopa olyan összefüggő dátumtartományt tartalmazzon, amely a ténytábla bármely lehetséges dátumát tartalmazza.
Megjegyzés
Bár a két táblázatban a dátumoszlopnak azonos adattípusúnak kell lennie (Date), az egyes oszlopok formátuma nem számít.
Megjegyzés
Ha a Power Pivot nem engedi, hogy kapcsolatokat hozzon létre a két tábla között, előfordulhat, hogy a dátummezők nem ugyanolyan pontossággal tárolják a dátumot és az időt. Az oszlop formázásától függően az értékek ugyanúgy nézhetnek ki, de másképpen tárolódnak. További információ az idő használatáról
Megjegyzés
Kerülje az egész helyettesítő kulcsok használatát a kapcsolatokban. Amikor adatokat importál relációs adatforrásból, a dátum- és időoszlopokat gyakran egy helyettesítő kulcs jelöli, amely egy egyedi dátumot jelölő egész szám oszlop. A Power Pivotban kerülje a kapcsolatok létrehozását egész dátum/idő kulcsok használatával, és helyette használjon egyedi adatokat tartalmazó, dátum adattípusú oszlopokat. Bár a helyettesítő kulcsok használata ajánlott eljárásnak számít a hagyományos adattárakban, az egész számok használatára nincs szükség a Power Pivotban, és megnehezíthetik a kimutatásokban lévő értékek különböző dátumidőszakok szerinti csoportosítását.
Ha kapcsolat létrehozásakor típuseltérésre utaló hibaüzenetet kap, annak valószínűleg az az oka, hogy a ténytábla oszlopa nem Dátum adattípusú. Ez akkor fordulhat elő, ha a Power Pivot nem tudja automatikusan átalakítani a nem dátum típusú (általában szöveges adattípusú) adattípusokat. Továbbra is használhatja az oszlopot a ténytáblában, de az adatokat egy DAX-képlettel egy új számított oszlopba kell konvertálnia. A szöveges adattípusú dátumok konvertálása dátumadattípussá című szakaszt a függelék későbbi részében találja.
Több kapcsolat
Bizonyos esetekben szükség lehet több kapcsolat vagy dátumtábla létrehozására. Ha például több dátummező is található az Értékesítés adatait tartalmazó táblában (például DateKey, ShipDate és ReturnDate), ezek mindegyike kapcsolódhat a Calendar date tábla Date mezőjéhez, de ezek közül csak az egyik lehet aktív kapcsolat. Ebben az esetben, mivel a DateKey a tranzakció dátumát jelöli, és így a legfontosabb dátumot, ez a leginkább aktív kapcsolat. A többiek kapcsolata inaktív.
Az alábbi kimutatás pénzügyi év és pénzügyi negyedév szerinti összes értékesítést számítja ki. A Total Sales (Összes eladás) mérték (képlete Total Sales:=SUM([SalesAmount])) a VALUES függvénybe kerül, a FiscalYear és a FiscalQuarter mező pedig a Calendar date táblázatból a SOROK függvénybe kerül.
Ez az egyszerű kimutatás azért működik helyesen, mert a teljes értékesítést a DateKey tranzakció dátuma szerint szeretnénk összesíteni. A Total Sales mérték a DateKey dátumait használja, és pénzügyi év és pénzügyi negyedév szerint van összegezve, mivel kapcsolat van az Értékesítés táblázatban szereplő DateKey és a Calendar date táblázat Date oszlopa között.
Inaktív kapcsolatok
De mi van akkor, ha a teljes értékesítést nem a tranzakció dátuma, hanem a szállítás dátuma alapján szeretnénk összesíteni? Kapcsolatot kell létesíteni az Értékesítés tábla SzállításiDátum oszlopa és a Calendar tábla Dátum oszlopa között. Ha nem jön létre ilyen kapcsolat, az összesítések mindig a tranzakció dátumán alapulnak. Ugyanakkor több kapcsolatunk is lehet, annak ellenére, hogy csak egy lehet aktív, és mivel a tranzakció dátuma a legfontosabb, az aktív kapcsolatot a Calendar táblával kapja meg.
Ebben az esetben a ShipDate oszlopnak inaktív kapcsolata van, ezért az adatok szállítási dátumok alapján történő összesítésére létrehozott mértékképletnek meg kell adnia az inaktív kapcsolatot a USERELATIONSHIP függvénnyel.
Mivel például inaktív kapcsolat van az Értékesítés tábla SzállításiDátum oszlopa és a Calendar tábla Dátum oszlopa között, létrehozhatunk egy olyan mértéket, amely a szállítási dátum szerint összegzi a teljes értékesítést. A használandó kapcsolatot a következő képlettel határozzuk meg:
Teljes értékesítés szállítási dátum szerint:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
Ez a képlet egyszerűen a következőt jelenti: Számítsa ki az összeget a SalesAmount oszlophoz, de a szűrést az Sales tábla ShipDate oszlopa és a Calendar tábla Date oszlopa közötti kapcsolat alapján végezze el.
Ha most létrehozunk egy kimutatást, és a Szállítási dátum szerinti összes eladás mértéket az ÉRTÉKEK területen, a Pénzügyi év és pénzügyi negyedév mezőt pedig a SOROK oszlopba helyezzük, ugyanazt a végösszeget látjuk, de a pénzügyi évre és a pénzügyi negyedévre vonatkozó összes többi összeg különbözik, mivel a szállítási dátumon alapulnak, nem pedig a tranzakció dátumán.
Az inaktív kapcsolatok használata lehetővé teszi, hogy csak egy dátumtáblázatot használjon, de azt megköveteli, hogy bármely mérték (például a Szállítási dátum szerinti teljes értékesítés) hivatkozzon az inaktív kapcsolatra a képletében. Van egy másik alternatíva is, vagyis használjon több dátumtáblázatot.
Több dátumtáblázat
A ténytáblában úgy is dolgozhat több dátumoszloppal, hogy több dátumtáblázatot hoz létre, és különálló aktív kapcsolatokat hoz létre közöttük. Nézzük meg újból az Értékesítés táblázat példáját. A következő három oszlopban lévő dátumok találhatók, amelyekről érdemes lehet összesíteni az adatokat:
- Egy DateKey az egyes tranzakciók eladási dátumával.
- A ShipDate – az a dátum és időpont, amikor az eladott cikkeket kiszállították a vevőnek.
- A ReturnDate – az a dátum és időpont, amikor egy vagy több visszaküldött tétel érkezett.
Ne feledje, hogy a tranzakció dátumát tartalmazó DateKey mező a legfontosabb. Az összesítések nagy részét ezen dátumok alapján fogjuk elvégezni, ezért mindenképpen szeretnénk kapcsolatot létrehozni ez és a Calendar táblázat Dátum oszlopa között. Ha nem szeretne inaktív kapcsolatot létesíteni a SzállításiDátum és a VisszaküldésiDátum mező, valamint a Calendar tábla Dátum mezője között, ami speciális mértékképleteket igényel, létrehozhat további dátumtáblázatokat a szállítási dátumhoz és a visszaküldés dátumához. Ezután aktív kapcsolatokat hozhatunk létre közöttük.
Ebben a példában létrehoztunk egy másik dátumtáblázatot, melynek neve ShipCalendar. Ez természetesen további dátumoszlopok létrehozását is jelenti, és mivel ezek a dátumoszlopok egy másik dátumtáblázatban helyezkednek el, szeretnénk úgy elnevezni őket, hogy megkülönböztetjük őket a Calendar táblázat azonos oszlopaitól. Létrehoztunk például egy oszlopot ShipYear, ShipMonth, ShipQuarter és így tovább.
Ha létrehozzuk a kimutatást, és a teljes értékesítési mértéket a VALUES táblába, a ShipFiscalYear és a ShipFiscalQuarter értéket pedig a SOROK oszlopba helyezzük, ugyanazokat az eredményeket kapjuk, mint amikor egy inaktív kapcsolatot és egy speciális Teljes értékesítés a szállítási dátum szerint számított mezőt hoztunk létre.
E megközelítések mindegyike alapos megfontolást igényel. Ha több kapcsolatot használ egyetlen dátumtáblával, előfordulhat, hogy olyan speciális mértékeket kell létrehoznia, amelyek a USERELATIONSHIP függvény használatával továbbítják az inaktív kapcsolatokat. Több dátumtáblázat létrehozása azonban zavaró lehet a mezőlistában, és mivel több táblázatot tartalmaz az adatmodell, több memóriát igényel. Kísérletezzen azzal, ami a legmegfelelőbb az Ön számára.
Date Table tulajdonság
A Date Table (Dátumtáblázat) tulajdonság beállítja a Time-Intelligence függvények, például a TOTALYTD, a PREVIOUSMONTH és a DATESBETWEEN megfelelő működéséhez szükséges metaadatokat. Amikor egy számítást e függvények valamelyikével futtat, a Power Pivot képletmotorja tudja, hogy hol kell keresnie a szükséges dátumokat.
Figyelmeztetés
Ha ez a tulajdonság nincs beállítva, előfordulhat, hogy a DAX Time-Intelligence függvényeket használó mértékek nem helyes eredményeket adnak.
A Dátumtábla tulajdonság beállításakor megad egy dátumtáblázatot és egy Dátum (datetime) adattípusú dátumoszlopot.
Útmutató: A Date Table tulajdonság beállítása
- A PowerPivot ablakban válassza a Calendar táblát.
- A Tervezés lapon kattintson a Megjelölés dátumtáblázatként parancsra.
- A Megjelölés dátumtáblázatként párbeszédpanelen jelöljön ki egy egyedi értékeket tartalmazó oszlopot és a Dátum adattípust.
Munka az idővel
Az Excelben és az SQL Server-ben minden Dátum adattípusú dátumérték valójában szám. Ez a szám egy adott időpontra utaló számjegyeket is tartalmaz. Sok esetben ez az idő minden egyes sorban éjfél. Ha például egy Értékesítés adattábla DateTimeKey mezőjének értékei közé tartozik a következőhöz hasonló érték: 10/19/2010 12:00:00 AM, az azt jelenti, hogy az értékek napi pontossággal érnek el. Ha a DateTimeKey mezőértékek tartalmaznak időt, például 2010/10/19 8:44:00 AM, az azt jelenti, hogy az értékek percpontossági szintűek. Az értékek lehetnek az óra vagy akár a másodperc szintű pontossággal is. Az időérték pontossági szintje jelentős hatással lesz a dátumtáblázat létrehozásának módjára, valamint a táblázat és a ténytábla közötti kapcsolatokra.
El kell döntenie, hogy napi vagy időbeli pontossággal összegzi-e az adatokat. Más szóval előfordulhat, hogy a dátumtáblázat oszlopait (például a Reggel, a Délután és az Óra oszlopokat) egy kimutatás sor-, oszlop- vagy szűrőterületén időpontdátum-mezőként szeretné használni.
Megjegyzés
A nap az a legkisebb időegység, amellyel a DAX időintelligencia-funkciói használhatók. Ha nem kell időértékeket használnia, akkor csökkentse az adatok pontosságát, hogy minimális mértékegységként napokat használjon.
Ha az idő szerint szeretné összesíteni az adatokat, akkor a dátumtáblázatnak szüksége lesz egy időt is tartalmazó dátumoszlopra. Valójában szüksége lesz egy dátumoszlopra, amelyben a dátumtartomány minden órájához, vagy akár minden percéhez egy sor szerepel. Ennek az az oka, hogy a ténytábla DateTimeKey oszlopa és a dátumtáblázat dátumoszlopa közötti kapcsolat létrehozásához egyező értékekre van szükség. Ahogy el lehet képzelni, ha sok évet is beleszámítunk, akkor ez egy nagyon nagy dátumtáblázat lehet.
A legtöbb esetben azonban célszerű csak a napi adatokat összesíteni. Más szóval az oszlopokat, például az Év, a Hónap, a Hét vagy a Hét napja oszlopokat fogja használni a kimutatás sor-, oszlop- vagy szűrőterületének mezőjeként. Ebben az esetben a dátumtáblázat dátumoszlopának csak egy sort kell tartalmaznia az év minden napjához, ahogy azt korábban leírtuk.
Ha a dátumoszlop egy adott időszinttel rendelkezik pontossággal, de az összesítést csak a nap szintjére szeretné elvégezni, a ténytábla és a dátumtábla közötti kapcsolat létrehozásához lehet, hogy módosítania kell a ténytáblát egy új oszlop létrehozásával, amely a dátumoszlop értékeit egy napi értékre csonkítja. Más szóval alakítsa át a 10/19/2010 8:44:00AM formátumot 10/19/2010 12:00:00 AM formátumra. Ezt követően létrehozhatja a kapcsolatot az új oszlop és a dátumtáblázat dátumoszlopa között, mert az értékek megegyeznek.
Lássunk egy példát. Ezen a képen egy DateTimeKey oszlop látható az értékesítési adatokat tartalmazó táblázatban. A táblában szereplő adatok összes összesítésének csak a nap szintjén kell történnie, a Calendar dátumtáblázat oszlopait (Év, Hónap, Negyedév stb.) használva. Az értékben szereplő idő nem lényeges, csak a tényleges dátum.
Mivel nem kell időszinten elemeznünk ezeket az adatokat, nincs szükség, hogy a Calendar date táblázat Date oszlopa minden év minden napjának órájához és minden percéhez egy sort tartalmazzon. A dátumtáblázat Dátum oszlopa tehát így néz ki:
Ha kapcsolatot kíván létrehozni az Értékesítés tábla DateTimeKey oszlopa és a Calendar tábla Date oszlopa között, létrehozhat egy új számított oszlopot az Értékesítési adatok táblázatban, és a TRUNC függvénnyel csonkolhatja a DateTimeKey oszlopban lévő dátum- és időértéket egy olyan dátumértékké, amely megegyezik a Calendar táblázat Dátum oszlopának értékeivel. A képletünk a következő:
=CSONK([DateTimeKey];0)
Ez létrehoz egy új oszlopot (a DateKey nevet adtuk), amely a DateTimeKey oszlopban lévő dátumot tartalmazza, és minden sorhoz egy 12:00:00 időpontot:
Most kapcsolatot hozhatunk létre az új (DateKey) oszlop és a Calendar táblázat Date oszlopa között.
Hasonlóképpen, létrehozhatunk egy számított oszlopot az Értékesítés táblázatban, amely a DateTimeKey oszlop időpontosságát az óra pontossági szintre csökkenti. Ebben az esetben a CSONK függvény nem fog működni, de a DAX többi dátum- és időfüggvényével továbbra is használhatunk egy új értéket egy óra pontosságú összefűzéséhez. A következő képletet használhatjuk:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
Az új oszlop így néz ki:
Amennyiben a dátumtáblázat Dátum oszlopa óra pontosságú értékekkel rendelkezik, kapcsolatot hozhatunk létre közöttük.
Dátumok használhatóbbá tétele
A dátumtáblában létrehozott dátumoszlopok közül sok szükséges más mezőkhöz, de elemzésben nem igazán hasznos. Például az Értékesítés tábla DateKey mezője, amelyre a jelen cikkben hivatkoztunk és bemutattuk, azért fontos, mert a rendszer minden tranzakciót rögzít úgy, mint amely egy adott napon és időpontban történt. Elemzési és jelentéskészítési szempontból azonban nem annyira hasznos, mert nem használhatjuk sor-, oszlop- vagy szűrőmezőként egy kimutatásban vagy jelentésben.
Hasonlóképpen, példánkban a Calendar táblázat Dátum oszlopa nagyon hasznos, valójában kritikus fontosságú, de kimutatásokban nem használhatja dimenzióként.
Ahhoz, hogy a táblázatok és a bennük lévő oszlopok a lehető leghasznosabbak maradjanak, és hogy a kimutatások vagy Power View-jelentések mezőlistáiban könnyebben navigálhasson, fontos, hogy elrejtse a felesleges oszlopokat az ügyféleszközök elől. Egyes táblákat is elrejthet. A korábban látható Ünnepnapok táblázat a Calendar táblázat egyes oszlopaiban fontosnak tartott ünnepnapokat tartalmazza, de maguk az Ünnepnapok tábla Dátum és Ünnepnapok oszlopa nem használható mezőként egy kimutatásban. A mezőlistákban való könnyebb navigálás érdekében itt is elrejtheti a teljes Ünnepnapok táblát.
A dátumokkal végzett munka során fontos szempont is az elnevezési konvenciók. A Power Pivotban a táblázatoknak és az oszlopoknak tetszés szerinti nevet adhat. Ne feledje azonban, hogy különösen ha a munkafüzetet másokkal is meg fogja osztani, egy jó elnevezési konvenció megkönnyíti a táblázatok és a dátumok azonosítását nemcsak a mezőlistákban, hanem a Power Pivotban és a DAX-képletekben is.
Ha már van egy dátumtáblázat az adatmodellben, elkezdhet olyan mértékek létrehozására, amelyek segítenek a legtöbbet kihozni az adataiból. Lehetnek olyan egyszerűek, mint az aktuális évre vonatkozó értékesítési összegek összegzése, mások azonban bonyolultabbak, ahol egyedi dátumok adott tartományára kell szűrni. További információért olvassa el a Mértékek a Power Pivotban és az időintelligencia-függvényekben című témakört.
Függelék
Szöveges adattípusú dátumok konvertálása dátumadattípussá
Egyes esetekben a tranzakciós adatokat tartalmazó adattábla szöveges adattípusú dátumokat is tartalmazhat. Ez azt jelenti, hogy a 2012-12-04T11:47:09 formátumú dátum valójában egyáltalán nem dátum, vagy legalábbis nem az a típusú dátum, amelyet a Power Pivot meg tud érteni. Ez valójában csak szöveg, amely úgy néz ki, mint egy dátum. Ahhoz, hogy kapcsolatot lehessen létesíteni a ténytábla dátumoszlopa és egy dátumtáblázat dátumoszlopa között, mindkét oszlopnak Dátum adattípusúnak kell lennie.
Ha egy szöveges adattípusú dátumoszlop adattípusát dátum adattípusúra próbálja módosítani, a Power Pivot képes a dátumokat értelmezni, és automatikusan igaz dátumadattípussá alakítani. Ha a Power Pivot nem képes adattípus-konverziót végezni, típuseltérési hiba lép fel.
A dátumokat azonban átalakíthatja valódi dátumadattípussá. Létrehozhat egy új számított oszlopot, és egy DAX-képlettel elemezheti a szöveges karakterláncokból az évet, a hónapot, a napot, az időt stb., majd újra összefűzheti úgy, ahogyan a Power Pivot valódi dátumként képes olvasni.
Az alábbi példában egy Értékesítés nevű adattáblát importáltunk a Power Pivot bővítménybe. Ez tartalmaz egy DateTime nevű oszlopot. Az értékek a következőképpen jelennek meg:
Ha megnézzük az Adattípus a Power Pivot Kezdőlap lapjának Formázás csoportjában, láthatja, hogy az Szöveg adattípusú.
Nem tudjuk kapcsolatot létrehozni a dátumtáblázat Dátum és Dátum oszlopa között, mert az adattípusok nem egyeznek. Ha megpróbáljuk az adattípust Dátum típusra módosítani, típuseltérési hiba jelenik meg:
Ebben az esetben a Power Pivot nem tudta átalakítani az adattípust szövegből dátummá. Ez az oszlop továbbra is használható, de ahhoz, hogy valódi dátumadattípust alakítsunk ki, létre kell hoznunk egy új oszlopot, amely elemzi a szöveget, majd újra létrehozza egy olyan értékké, amelyet a Power Pivot Dátum adattípusúvá tud alakítani.
Ne feledje a cikk korábbi, Munka az idővel című szakaszából; Hacsak nem lényeges, hogy az elemzés napszak szerinti pontossággal történjen, a ténytáblában lévő dátumokat napi pontosságúra kell átalakítania. Ezt szem előtt tartva azt szeretnénk, ha az új oszlop értékei a nap pontossági szinten lennének (az idő kivételével). A következő képlettel átalakíthatjuk a DátumIdő oszlop értékeit dátum adattípusúvá, és eltávolíthatjuk a pontosság időszintjét:
=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))
Ez egy új oszlopot eredményez (ebben az esetben a Dátum nevet). A Power Pivot még azt is észleli, hogy az értékek dátumok, és automatikusan Dátum értékre állítja az adattípust.
Ha meg akarjuk őrizni a pontosság időszintjét, egyszerűen bővítse a képletet az órára, a percre és a másodpercre.
=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2)) +
TIME(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))
Most, hogy már van egy Dátum típusú Dátum típusú oszlopunk, kapcsolatot hozhatunk létre közte és a dátum dátumoszlopával.
További erőforrások
QuickStart útmutató: A DAX alapjai 30 percben