Table of Contents
|
Áttekintés
Az az oldal a Microsoft SQL Server Analysis Services nevű termékről szól. Ennek a szokásos rövidítése SSAS; ez az oldal is így hivatkozik rá a továbbiakban. Ahogy a nevéből is következik, egy Microsoft megoldásról van szó. Nem ismerek ezzel kompatibilis, vagy legalább hasonló rendszert a Microsoft világon kívül.
Az alap probléma, melyre megoldást nyújt, az alábbi:
- Van egy (esetleg több) tényeket (angolul fact) tartalmazó tábla. Ez általában nagy, és többnyire folyamatosan növekszik. Egy példa: internetes eladások.
- Vannak dimenziókat (angolul is dimension) tartalmazó táblák, melyek általában csillagszerű elrendezésben kapcsolódnak a tény táblához vagy táblákhoz. A fenti példát folytatva: ilyen lehet például a vásárolt termék egyéb adatai, a vásárló személyével kapcsolatos adatok (pl. hol lakik), vagy mondjuk az eladás időpontja.
- A tény táblákon alapuló, különböző dimenziók mentén összegzett mértékeket (angolul measure) szeretnénk kiszámolni, például hogy országra és negyedévre lebontva melyik termék kategóriából összesen mennyi értékesítettek, vagy mekkora volt a legnagyobb értékű értékesítés.
- A mértékeket tovább gondolva: fő teljesítménymutatókat (angolul Key Performance Indicator, gyakori rövidítéssel KPI) hozhatunk létre. Az eladási példát folytatva: tegyük fel, hogy az eladás tábla (vagy valamelyik kapcsoló táblája) az előállítási költséget is tartalmazza, melynek segítségével meg tudjuk határozni a nyereséget, tehát pl. azt, hogy az eladási ár hány százaléka a nyereség. Meghatározhatunk cél értéket, valamint piros (rossz), sárga (elfogadható) és zöld (jó) értékeket. A fenti összegzésben nem az abszolút értékekre vagyunk kíváncsiak, hanem a KPI értékekre.
Az SSAS fő feladata ezek biztosítása. Noha mindez megvalósítható normál SQL lekérdezésekkel is, annak számos hátránya van az SSAS-szel szemben:
- Az adat, amiből az SSAS "táplálkozik", általában egyfajta konszolidált nyers adat, tehát olyan, ami tipikusan túl sok adatot tartalmaz. Az SSAS leszűkíti az adatmennyiséget, ideális esetben annyira, hogy a kliens csak annyi információt kapjon, ami feltétlenül szükséges.
- Az SSAS több adatforrásból is táplálkozhat, pl. adatbázisból és Excel táblákból egyszerre.
- A fenti említett mértékek jó része (pl. összegzés, maximum stb.) része az SSAS-nek, és továbbiakat is létre tudunk hozni.
- Új számított oszlopot adhatunk a táblázathoz.
- Az SSAS-ben az adatok a memóriában vannak, így sokkal gyorsabb a lekérdezés.
- Az SSAS üzemeltetése általában olcsóbb mint egy adatáruházé.
Az SSAS-nek viszont számos hátránya is van: elég hosszú a betanulási görbe, összetettek a folyamatok, számos hiba van benne, és kevesen értenek hozzá.
Szoftverek telepítése
A rendszer használatához számos szoftvert kell telepítenünk, melyek nem egyszerűek. Sok idő, elszántság és kitartás kell hozzá, nagyon sok a buktató, így nélkülözhetetlen, hogy az embernek kötélből legyenek az idegei. A teljes környezet kialakítása egy középkategóriás horrorfilm feszültségével vetekszik.
Microsoft SQL Server
Az SSAS a Microsoft SQL Server része. Ez tartalmazza a hagyományos Microsoft SQL szervert, az SSAS-t, és még számos még szolgáltatást. A példákhoz telepítsük fel a Microsoft SQL szervert, valamint az SSAS-t.
- Töltsük le a telepítőt a https://www.microsoft.com/en-us/sql-server/sql-server-downloads oldalról. Válasszuk ki fejlesztői (Developer) változatot; az ingyenes, és tartalmaz mindent, amire nekünk szükségünk van.
- A letöltött telepítő csak egy pár megabájtos fájl, ami indítás után letölti a tulajdonképpeni SQL szervert. Ez eltart egy jó ideig, mivel a mérete gigabájtos nagyságrendű.
- Indítsuk el a telepítőt, és ne lepődjünk meg azon, ha nem megy elsőre. Győződjünk meg arról, hogy az SQL szervert és az SSAS-t is kiválasztottuk!
A telepítés eléggé komplikált; leírok néhány olyan problémát, mellyel én találkoztam.
Több adatbázis telepítése
Lehetséges, viszont az első sikeres lesz az alapértelmezett. Nekem úgy sikerült telepítenem, hogy az alapértelmezett a klasszikus SQL szerver lett, az SSAS pedig a - nem túl fantáziadús - SSAS nevet kapta, így a localhost\SSAS címen érhető el.
Autentikációs módok
Az SQL szervernek két autentikációs módja van: Windows és vegyes; ez utóbbi tartalmazza a Windows mellett a hagyományos SQL azonosító-jelszó módszert is. A programozó ez utóbbihoz szokott, így a vegyes módot kell választanunk. Sajnos az alapértelmezett a Windows mód. Ha a telepítés során elrontottuk, akkor a következőt tehetjük.
- A következő szakaszban bemutatandó SSMS nevű alkalmazással kapcsolódjunk az adatbázishoz.
- A szerver neve lehet a ., ami a localhost rövidítése, ill. ha nem ez az alapértelmezett, akkor vissza per jel után a megadott nevet.
- Adjuk meg a Windows azonosítónkat és jelszavunkat. (Az azonosítóhoz a domain előtag is kellhet, ami után vissza per jel következik.)
- Kattintsunk jobb egérgombbal az adatbázis szerveren, és kattintsunk a Properties menüpontra.
- A Security fülön felül be tudjuk állítani az autentikációs módot
SSAS módjai
A telepítés során vegyük figyelembe azt hogy kétféle módon lehet telepíteni az SSAS-t:
- Multidimenziós: ez volt előbb. Fő fogalma a kocka (cube), ami tények, dimenziók és mértékek halmazát jelenti. A lekérdezési nyelve az MDX, ami az SQL-ből nőtte ki magát. Erről ebben a leírásban nem lesz szó; aki érdeklődik a téma iránt, annak jó kiinudlópont lehet ez a leírás: https://www.sqlshack.com/build-cube-scratch-using-sql-server-analysis-services-ssas/.
- Táblázatos: célja az, hogy az informatikához, azon belül az Excelhez értő, ám nem programozó gazdasági emberek felé nyisson. A lekérdező nyelve a DAX, ami az Excel függvényekre hasonlít.
Válasszuk a táblázatos módot! Ha véletlenül elrontottuk, akkor nem találtam "legális" módját annak, hogy ezt megváltoztassuk. (Ellenőrzés: jobb kattintás a lent bemutatandó SSMS program segítségével a szerveren → Properties → Server mode). A kézenfekvő törlést és újratelepítést csak akkor válasszuk, ha időmilliomosok vagyunk, mert a tapasztalatom szerint egy ilyen művelet akár órákig is eltarthat. Viszont van egy trükk, amit a https://www.sqlservercentral.com/articles/how-to-change-an-analysis-services-instance-to-tabular-mode oldalon találtam:
- Keressük meg a konfigurációs fájlt, melynek neve msmdsrv.ini. Igazából nem lesz egyszerű, mert egy teljesen logikátlan könyvtárszerkezet legalján van, mégpedig úgy, hogy az azt tartalmazó könyvtárba már csak rendszer adminisztrátori jogosultságokkal tudunk belépni, így normál esetben rá se lehet keresni. Egy tipp: nálam itt található: c:\Program Files\Microsoft SQL Server\MSAS14.SSAS\OLAP\Config\msmdsrv.ini.
- Ez egy többé-kevésbé XML-re hasonlító konfigurációs fájl, a szabványok laza figyelembevételével. Találunk benne egy <DeploymentMode> tag-et, ahol egy számot találunk. Az 1-es jelenti a multidimenzionális módot, a 2-es a táblázatosat.
- Helyben nem tudjuk módosítani Másoljuk át valahova, ott módosítsuk ezt az egyetlen számjegyet, majd másoljuk vissza.
- Indítsuk újra az SSAS-t: SSMS-ben jobb kattintás a szerver nevén → Restart.
Microsoft SQL Server Management Studio (SSMS)
Az SQL szervert, az SSAS-t és még pár szolgáltatást a Microsoft SQL Server Management Studio nevű program segítségével tudjuk kezelni. Ennek szokásos rövidítése SSMS; annyira, hogy ha telepítés után ezt a 4 betűt beírjuk a Windows start menü keresőjébe, akkor ezt a programot indítja el. A telepítéshez töltsük le a telepítőt a https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms oldalról.
A kapcsolódásnál vegyük figyelembe az alábbiakat:
- A szerver neve lehet ., ami az adott gépre mutat.
- Ha megadtunk a telepítés során valamilyen adatbázis szerver nevet, akkor azt a következőképpen kell megadnunk: .\SSAS, ha a név SSAS volt.
- Alapértelmezésben a Windows azonosítónkat és jelszavunkat adjuk meg.
Teszt adatbázis
Az SSAS tanfolyamok kedvenc teszt adatbázisa az Adventure Works, ami internet eladásokat tartalmaz. Ezt a Microsoft oldaláról letölthetjük: https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure. Egy Data Warehouse változatot válasszunk. A telepítése nagyon nem logikus:
- Kapcsolódjunk az adatbázis szerverhez, és jobb egérgombbal kattintsunk a Databases-re.
- Válasszuk ki a Restore Database… menüpontot.
- A Source legyen Device.
- Válasszuk ki a letöltött .bak kiterjesztésű fájlt, majd kattintsunk az OK-ra.
Microsoft Visual Studio és SQL Server Data Tools (SSDT)
Telepítsük fel a Microsoft Visual Studio fejlesztő keretrendszert az SQL Server Data Tools kiegészítővel (mostantól: SSDT). Ezzel is eléggé meggyűlt a bajom, így leírom, hogy mi volt az egyetlen megoldás, ami nálam működött:
- A Visual Studio-t ne töltsük le, ne telepítsük fel!
- Töltsük le az SSDT telepítőt a https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt oldalról. A 2017-es verziót válasszuk; a 2019-es verziót még nem láttam működni.
- Indítsuk el. Észlelni fogja, hogy még nincs felepítve Visual Studio, és felkínálja annak telepítését. Ezt fogadjuk el.
- Az Analysis Service-s mindenképpen telepítsük fel, de feltelepíthetjük a Reporting Services és Integration Services komponenseket is.
Excel
Az Excel egy igen hasznos jószág az SSAS kipróbálásához. Persze csak miután azt is megfelelően beállítottuk.
- Szükség van a PowerPivot-ra. Ez meglepő módon alapértelmezésben telepítve van minden Excel rendszeren, de nincs engedélyezve. Az engedélyezéshez hajtsuk végre az alábbi lpépseket:
- File → Options → Add-ins
- Alul: Manage COM Add-ins
- Go…
- Itt kattintsuk be a Microsoft Power Pivor for Excel-t.
- Telepítenünk kell 3 kiegészítőt. Ezeket az alábbi oldalról tudjuk letölteni: https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-data-providers. Közvetlen linkek:
- Ha proxy szerver mögött vagyunk, az SSAS szerver egy távoli gép, és valami miatt ki kell kapcsolnunk a proxy-t, akkor nem elég az Internet Options ablakban kikapcsolni. A rendszerproxy-t is ki kell kapcsolni mert az Excel azt használja. Mivel hasznos lehet, ide írom, hogy hogyan kell:
- Rendszer proxy kikapcsolása (rendszer adminisztrátori jogosultságok kellenek): netsh winhttp reset proxy
- Visszakapcsolása: netsh winhttp set proxy proxy-server="http=[server]:[port];https=[server]:[port]" bypass-list="*.[domain]"
Power BI
A Power BI egy üzleti intelligencia (business intelligence) eszköz a Microsofttól, mellyel riportokat tudunk készíteni. Erről részletesen a Üzleti intelligencia oldalamon olvashatunk. Töltsük le és telepítsük fel a Power BI Desktop programot a https://powerbi.microsoft.com/hu-hu/desktop/ oldalról.
Egyebek
Elképzelhető, hogy egyéb szerverekre (pl. NET keretrendszer) is szükség van, ami nálam vagy alapból fel volt telepítve, vagy az SSAS használata előtt feltelepítettem.
Hello, SSAS World!
Ha sikeresen túléltük a szükséges szoftverek telepítését, nem kaptunk idegösszeroppanást, elkészíthetjük az első SSAS projektünket. Nem lesz egyszerű!
Projekt létrehozása
Indítsuk el az SSDT kiegészítővel ellátott Visual Studio-t. Hozzunk létre egy új projektet:
- File → New → Project…
- Installed → Analysis Services → Tabular
- Itt adjunk meg egy nevet, pl. AdventureWorks, majd kattintsunk az OK-ra.
A rendszer felkínálja, hogy a memóriában tárolja-e az adatokat, vagy egy SSAS példányban. Igazából bármelyiket választhatjuk, nem ez lesz a végleges. Ha az SSAS-t választjuk, akkor automatikusan létrehoz egy SSAS adatbázist. Ezt a következőképpen tudjuk leellenőrizni:
- Az SSMS segítségével csatlakozzunk az SSAS szerverhez: az Object Explorerben Connect → Analysis Services…
- Server name: .\SSAS (ill. amit adtunk telepítéskor)
- Authentication: Windows Authentication. Ez esetben nincs szükség azonosítóra és jelszóra.
- Connect
- Kattintsunk a + jelre, ami az SSAS adatbázis szerver nevétől balra található.
- Nyissuk le a Databases-t. Esetleg frissítsük a következőképpen: jobb kattintás a Databases feliraton → Refresh.
- Ha mindent jól csináltunk, látni fogunk egy olyan adatbázist, ami a projekt nevével kezdődik (AdventureWorks), tartalmazza az azonosítónkat és egy generált UUID-t.
Adatok importálása
Az adatok importálásához a Visual Studio-ban hajtsuk végre a következő lépéseket:
- Jobb oldalon látunk egy panelt Tabular Model Explorer címmel. Ha nem lenne lenyitva, nyissuk le.
- Jobb kattintás a Data Sources fülön → Import From Data Source…
- Válasszuk ki ezt: Database → SQL Server database
- Kattintsunk a Connect-re.
- Server: . (vagy más, ha nem ez az alapértelmezett, pl. .\MYDB)
- Minden mást hagyjunk alapértelmezett állapotban, és kattintsunk az OK-ra.
- Kérni fogja az azonosítónkat. Ha beállítottunk külön adatbázis azonosítót, akkor a Database lehetőséget bal oldalon kiválasztva megadhatjuk azt. Egyébként használhatjuk a saját Windows azonosítónkat. Ekkor a bal oldalon hagyjuk az alapértelmezett Windows-on, az Impersonate Mode maradjon Impersonate Account, és adjuk meg az Windows azonosítónkat (elképzelhető, hogy kell a domain is az azonosító elé, \ jellel elválasztva) és a jelszónkat, majd kattintsunk a Connect-re.
- Kiírhat egy bosszantó figyelmeztetést, hogy nem tudja titkosítani a kapcsolatot. Kattintsunk az OK-ra.
- Válasszuk ki a kívánt adatbázist, pl. AdventureWorksDW2017, és kattintsunk az OK-ra.
- Válasszuk ki az importálni kívánt táblákat. Ne válasszuk ki az összeset, csak az alábbiakat: FactInternetSales, DimDate, DimProduct, DimProductCategory, DimProductSubcategory, DimSalesTerritory.
- Kattintsunk a Load-ra.
Itt a betöltéskor az adatokat is feldolgozza; ez eltart valamennyi ideig (kb. egy percig), majd ha minden sikeres volt (zöld pipa és egy nagybetűs Success jelzi), akkor kattintsunk a Close gombra. Mentsük le a projektet (Ctrl + S). Lassuk, mit kaptunk:
- Középen megjelenik egy nagy táblázat az adatokkal. Alul tudunk táblát választani.
- Jobb oldalon a Tabular Model Explorer kicsit kibővült: megjelentek a táblanevek és a kapcsolatok.
- Ennek a lapnak a jobb alsó sarkában van két ikon: Grid (rács) és Diagram. Az alapértelmezett a Grid. Ha a Diagram-ra kattintsunk, akkor láthatjuk az adatbázis sémát a kapcsolatokkal.
Kapcsolatok kialakítása táblák között
Remélhetőleg a betöltés során automatikusan sikerült rendesen beállítani a kapcsolatokat. Ha véletlenül mégsem, akkor ezt kézzel kell megoldanunk a következőképpen: a Diagram nézetben kattintsunk jobb egérgombbal az egyik táblán, válasszuk ki a Create Relationship… menüpontot, ott válasszuk ki a másik táblát, és adjuk meg az irányt és a kapcsolat kardinalitását is. Az alábbi kapcsolatokat kell látnunk:
- FactInternetSales [OrderDateKey] - DimDate [DateKey]: Cardinality: *:1, Filter Direction: To FactInternetSales
- FactInternetSales [SalesTerritoryKey] - DimSalesTerritory [SalesTerritoryKey]: Cardinality: *:1, Filter Direction: To FactInternetSales
- FactInternetSales [ProductKey] - DimProduct [ProductKey]: Cardinality: *:1, Filter Direction: To FactInternetSales
- DimProduct [ProductSubcategoryKey] - DimProductSubcategory [ProductSubcategoryKey]: Cardinality: *:1, Filter Direction: To DimProduct
- DimProductSubcategory [ProductCategoryKey] - DimProductCategory [ProductCategoryKey]: Cardinality: *:1, Filter Direction: To DimProductSubcategory
- A többit (pl. a FactInternetSales és DimDate közöttieket) töröljük ki.
Rendezzük el a táblákat úgy, hogy a FactInternetSales legyen középen, a többi pedig csillagszerűen vegye ezt körül. Ezt a diagram kialakítást csillag diagramnak hívjuk.
Oszlopok törlése
A táblák számát már sikerült kezelhetőre redukálnunk, viszont még így is nagyon sok oszlopunk maradt. Töröljük ki a felesleget! Ezt talán legegyszerűbben a következőképpen tudjuk megtenni:
- Váltsunk rács (Grid) nézetre.
- Jelöljük ki a törölni kívánt oszlopot az oszlop nevére kattintva.
- Jobb kattintással az oszlopon válasszuk ki a Delete Columns menüpontot.
Úgy módosítsuk a táblákat, hogy az alábbi oszlopok maradjanak meg (célszerű átrendezni a megadott sorrendbe):
- FactInternetSales: OrderDateKey, SalesTerritoryKey, ProductKey, ProductStandardCost és SalesAmount
- DimDate: DateKey, CalendarYear, MonthNumberOfYear, EnglishMonthName
- DimProduct: ProductKey, ProductSubcategoryKey, EnglishProductName
- DimProductSubcategory: ProductSubcategoryKey, ProductCategoryKey, EnglishProductCategoryName
- DimProductCategory: ProductCategoryKey, EnglishProductCategoryName
- DimSalesTerritory: SaleyTerritoryKey, SalesTerritoyGroup, SalesTerritoryCountry, SalesTerritoryRegion
Oszlopok elrejtése
A kulcsok a kapcsolótáblákhoz szükségesek, azonban a klienseknek nem kell tudniuk. Rejtsük el azokat az oszlopokat, amelyek arra végződnek, hogy Key. Ezt a következőképpen tudjuk megtenni:
- Jobb oldalon a Tabular Model Explorerben nyissuk le a Tables mappát, azon belül nyissunk le egy táblát, és nyissuk le a Columns mappát is.
- Kattintsunk az elrejteni kívánt oszlop nevére.
- Alul a Properties ablakban megjelennek a tulajdonságai. A Basic → Hidden alapértelmezett értéke False, azt llítsuk át True-ra.
Ezt a műveletet hajtsuk végre az összes kulcson.
Mérték létrehozása
Ahhoz, hogy használni tudjuk a riportot, mértéket kell létrehoznunk. Például hozzunk létre egy olyan mértéket, amely meghatározza az eladás teljes összegét! Ehhez hajtsuk végre a következő lépéseket:
- Rács (Grid) nézetben jelöljük ki a FactInternetSales táblát.
- A SalesAmount oszlop alatt, az értékeke alatt kattintsunk egy üres szürke cellába.
- Fent, közvetlenül a menü alatt van egy ikon sor. A jobboldal tájt van egy szumma jel. Arra kattintsunk rá.
- Megjelenik az alábbi: Sum of SalesAmount:=SUM([SalesAmount])
Tesztelés Visual Studio segítségével
A Visual Studio önmagában kínál pár alap tesztelési lehetőséget. Pl. mutatja a táblázat adatait, vagy az imént létrehozott mértéket kiszámolja. Talán az egyik leggyakrabban használt lehetőség az Excel megjelenítés: felül, az ikon sorban, középen van egy zöld Excel ikon, arra kattintsunk rá. Fogadjuk el az alapértelmezetten felkínált Windows felhasználót. Ez a módszer működik akkor is, ha az adat a Visual Studio memóriájában van, és akkor is, ha egy ideiglenes SSAS adatbázisban. Később majd látni fogjuk, hogy hogyan lehet Excellel rácsatlakozni egy SSAS adatbázisra; ennél a két kattintásnál azért bonyolultabb.
Játszunk el egy kicsit az imént felépített rendszerrel! Jobb oldalon csak a megmaradt táblák megmaradt és látható oszlopai találhatóak, valamint az egy szem létrehozott mérték. Tegyük pl. a következőt:
- Kattintsunk a FactInternetSales → Sum of SalesAmount mérték előtti négyzetre! Megjelenik a teljes összeg.
- Hasonló módon válasszuk ki a DimDate → CalendarYear oszlopnevet! Automatikusan a sorok (ROWS) alá kerül. A táblázat megváltozott oly módon, hogy 2 oszlopa lett: az egyik az év, a másik az összeg; a sorok az egyes éveket és az adott évben eladott áruk árának összegét mutatja , valamint láthatjuk alul a teljes összeget. Ezt amiatt tudtuk egyetlen kattintással megtenni, mert az SSAS-ben össze van kapcsolva a két tábla.
- Jelöljük ki a DimSalesTerritory → SalesTerritoryGroup oszlopot! Ez is a sorok alá került, és a táblázatban lenyitható/becsukható módon az évekre felfűzve jelenik meg.
- Húzd és vidd technikával fordítsuk meg a sorrendet! A táblázatban ennek következtében a fő kategória a kontinens lett, és az alábontás lett az év.
- Húzd és vidd technikával vigyük át az oszlopok (COLUMNS) alá! Egy olyan táblázatot kapunk, melynek sorai az évek, oszlopai a kontinensek, az értékei pedig az abban az évben adott kontinensen eladott áruk árának összege.
- A harmadik dimenzió az áru kategória lesz. Valójában sem a sorokba, sem az oszlopokba nem illik, oda majd megfelelő hierarchiákat fogunk létrehozni. 3 dimenziót jól áttekinthető módon egy táblázatban elég nehézkes megjeleníteni, de van még egy lehetőségünk: a szűrés! Húzd és vidd technikával dobjuk bele a DimProductCategory → EnglishProductCategoryName-et a szűrők (FILTERS) alá. Az első sorban megjelenik egy szűrő (a B1 cellában), amit egy tölcsér jelez. Kattintsunk rá, alul válasszuk ki a Select Multiple Items melletti négyzetet, és válasszunk ki egy elemet, pl. a kerékpárokat (Bikes). Így a táblázatban a kerékpárokra vonatkozó eladási összegeket láthatjuk.
Játsszunk el bátran itt a lehetőségekkel!
Séma telepítése
Ha elégedettek vagyunk az eredménnyel, akkor telepítsük! (Ne feledjük, hogy eddig a pillanatig még csak vagy a memóriában, vagy ideiglenes adatbázisban van az adat!) A Visual Studio-ban hajtsuk végre a következő lépéseket:
- Jobb oldalon a Tabular Model Explorer-ről váltsunk át Solution Explorer-re.
- Kattintsunk jobb egérgombbal a projekt nevén (pl. AdventureWorks; ez nem a legfelső, ami tartalmazza azt is, hogy Solution, hanem a második.)
- Válasszuk ki a Build vagy Rebuild műveletet.
- Ugyanúgy a context menüben válasszuk ki a Properties-t.
- Procession Options: itt azt tudjuk kiválasztani, hogy a telepítés után végrehajtódjon-e az adatokkal való feltöltés. A Full mindent újraszámol (ha már nagy a rendszerünk, akkor ezt kerüljük), a Do not process csak a sémát telepíti, a Default nem tudom, pontosan mit csinál (feltételezem, hogy ha már volt telepítés, akkor csak a különbözetet számolja ki). Válasszunk ki egy tetszőleges megoldást.
- Ellenőrizzük a szervert. Itt jó eséllyel a helyi gépre telepített SSAS példány lesz beállítva (pl. localhost\SSAS). Ha nem erre szeretnénk telepíteni, akkor írjuk be a megfelelőt.
- Hajtsuk végre ugyanonnan elindítva a Deploy műveletet.
Adatok feltöltése
Az adatok feltöltését (tehát amikor az adatforrásból az SSAS-be kerül az adat) processzálásnak hívjuk. A dimenzió táblákat általában egyszerre processzáljuk, a tény táblákat pedig lehet particionálni, pl. egy partíció tartalmazhat egy hónapnyi adatot.
A processzálás általában drága művelet, hosszú ideig tart, és komoly az erőforrás igénye. Elkerülhető a közvetlen lekérés (DirectQuery) technológiával; ez esetben az SSAS csak átfordítja a bejövő kérést az adatforrás felé. Előnye, hogy nem kell processzálni, nem kell a memóriában tárolni semmilyen adatot, és a végfelhasználó mindig a legújabb adatokat látja. Ez viszont lassú, nagyobb adatbázisok esetén elfogadhatatlanul lassú reakcióidőt eredményez.
Létezik egy köztes mód is: az inkrementális processzálás, amikor gyakran (pl. 10-15 percenként) processzáljuk a különbözetet. Tehát azzal, hogy csak 10-15 percnyi adatot kell feltölteni, az erőforrásban is elfogadható, és a felhasználó is közel valós idejű adatokat kap. Persze mind mindennek, ennek is meg van az ára: nem szabad számolt oszlopot használni (legalábbis a tény táblán), mert az minden processzálás után teljes egészében újraszámolódik.
Elképzelhető, hogy a telepítéskor már megtörtént a processzálás. Ha mégse (ill. újra szeretnénk processzálni), akkor a következő lépéseket hajtsuk végre:
- Indítsuk el az SSMS-t.
- Kapcsolódjunk a szerverhez: Connect → Analysis Services → adjuk meg a szerver nevét (pl. .\SSAS), majd Connect.
- Nyissuk le az adatbázisokat, frissítsük, ha kell (jobb kattintás, majd Refresh), majd nyissuk le az imént létrehozott adatbázis tábláit.
- Kattintsunk jobb egérgombbal akármelyik táblán, és válasszuk ki a Process Table műveletet.
- A Mode legye Full, és jelöljük be az összes táblát a legfelső négyzetre kattintva. Ezt a későbbiekben finomhangolhatjuk.
- Kattintsunk az OK-ra.
Lekérdezés SSMS-sel
Lássuk, hogy sikerült-e a művelet! Először próbáljuk ki az SSMS-szel, ha már itt vagyunk! Bár ezzel pont nem egyszerű a művelet (ami azt jelenti, hogy semmivel sem egyszerű, de ezzel kifejezetten bonyolult). Egy kisebb táblát, mondjuk a DimSalesTerritory-t fogjuk megjeleníteni.
- Jobb gombbal kattintsunk az adatbázis nevére, és ott válasszuk ki a New Query → MDX menüpontot. Itt egy csalást hajtunk végre. Mivel Tabular módban telepítettük, a DAX nyelvet tudjuk használni. Viszont MDX módban is ki tudunk adni DAX parancsokat, és az az MDX lekérdező ablak kicsit többet mutat, célszerű azt választani.
- A sémát az ablak bal oldalán látjuk.
- Hajtsuk végre a következő parancsot: EVALUATE(DimSalesTerritory)
Ha mindent jól csináltunk, a tejes tábla megjelenik (a rejtett oszlopok is).
Kapcsolódás Excel-lel
Excellel már csatlakoztunk fent, Visual Studio-ból; most tegyük meg ismét, kívülről!
- Indítsuk el az Excel-t, és nyissunk egy üres lapot.
- Válasszuk ki a következőt: Data → From Other Sources → From Analysis Services
- A felugró ablakban adjuk meg a szerver nevét, pl. .\SSAS. Az autentikáció maradjon Windows.
- Next után a legördülő menüből válasszuk ki a fent létrehozott adatbázist (AdventureWorks).
- Ismételt Next után kattintsunk a Finish-re.
- Az Import Data ablakban mindent hagyjunk alapértelmezett értéken, és kattintsunk az OK-ra.
Ezen a ponton ugyanazt tudjuk tenni, mint fent.
PowerBI riport készítése
Az SSAS-ben összegyűjtött adatokból általában riportot készítünk. Az egyik ilyen eszköz a Power BI. Az Üzleti Intelligencia oldalon részletes leírást találunk erről a rendszerről. Készítsünk egy egyszerű riportot a fentiekből! Az ott leírtak alapján állítsuk be a Power BI Desktopot, majd hajtsuk égre az alábbi műveleteket:
- Home → Get Data → More… → Database → SQL Server Analysis Services database → Connect
- Adjuk meg a szerver nevét (pl. .\SSAS), a többit hagyjuk alapértelmezett értéken, majd kattintsunk az OK-ra.
- Válasszuk ki a fenti adatbázist, és kattintsunk az OK-ra.
- Válasszuk ki a Stacked Bar Chart vizuális elemet.
- A Value legyen Sum of SalesAmount (bár vegyük észre, hogy a számot tartalmazó oszlopokból automatikusan készített nekünk a Power BI mértékeket). Ezzel megjelenik a teljes összeg.
- Az Axis legyen CalendarYear. Így év szerinti bontásban láthatjuk az eladások összegét.
- A Legend legyen SalesTerritoryGroup. Ezáltal az oszlopok alá lesznek bontva kontinensre.
- Hozzunk létre egy új vizuális elemet, melynek típusa Slicer legyen.
- A Field legyen EnglishProductCategoryName.
- Válasszunk ki egyet, pl. Bikes.
Ha mindent jól csináltunk, kaptunk egy riportot, mely két elemet tartalmaz: egy oszlopdiagramot és egy szűrőfeltételt.
Fejlesztés
A bevezető példában elég sok mindent érintettünk; ebből is látható, hogy igen hosszú a rendszer betanulási görbéje. Most megnézünk pár technikát, amit el tudunk készíteni; ezek között lesznek újat és olyanok is, amelyek a fentiek ismétlése, további lehetőségek bemutatása.
Adattáblák
Amint azt már láthattuk, az adattáblák az SSAS-ben - az SQL-hez hasonlóan - alapvető fontosságúak. Van még néhány fontos művelet még ezzel kapcsolatban,a melyek sajnos nem nyilvánvaló helyen "el vannak rejtve". Ezek az alábbiak:
- Új táblák importálása: a Tables küröl keresnénk, de nem ott van! A Tabular Model Explorer-ben Models → AdventireWorks → Data Sources → jobb kattintás az adatforrás nevén → Import New Tables, majd itt ki tudjuk választani az importálandó táblákat.
- Új oszlop importálása adott táblához: ez még nehézkesebb. Szintén a Tabular Model Explorer-ben keressük meg a módosítani kívánt táblát, kattintsunk a nevére jobb egérgombbal → Table Properties… → Design… (nyomógomb alul). Itt elvileg ki kell jelölni az importálni kívánt oszlopot, majd az Import-ra kattintani (nyomógomb a bals felső sarokban). A tapasztalatom szerint a gyakorlatban az történik, hogy minden oszlopot importál, nemcsak a kiválasztottakat. A Tabular Model Explorer-ben a tábla nem frissül, és nem is lehet frissíteni (vagy nagyon elrejtették); a változást középen, rács nézetben láthatjuk.
Táblák és oszlopok átnevezése
A táblákat és oszlopokat átnevezhetjük akár a rács-, akár a diagram nézetben, ha duplán kattintunk a nevére. Ez nem befolyásolja az eredeti tábla- ill. oszlopnevet.
Kapcsolatok
A fenti példában automatikusan megtalálta a kapcsolatokat, mezőnév és típus szerint. Kézzel is be tudjuk állítani. Most lássunk a fentitől eltérő módszert!
- Table → Create Relationships… → itt válasszuk ki az összekötni kívánt táblát, a kapcsolat irányát és típusát.
- Table → Manage Relationships…: itt a már létező kapcsolatokat tudjuk áttekinteni, módosítani.
Számított oszlopok
A meglevő oszlopokból tudunk számított oszlopokat létrehozni. Ezt nyilván a forrás oldalon és kliens oldalon is megtehetnénk, mégis, valószínűleg itt a legjobb. Ha a forrás oldalon tárolnánk, akkor az redundáns lenne. Ha a kliensre bíznánk, akkor az lassítaná a lekérdezést. Az SSAS ezzel szemben előre kiszámolja, szükség esetén (processzáláskor) újra számolja, és az eredményt a memóriában tartja. Így nincs redundáns információ tárolás sem, de nem is kell a felhasználónak várnia a számolás eredményére.
Példaként hozzunk létre a a tény táblában egy olyan számított oszlopot, ahol a nyereséget tároljuk.
- Nyissuk meg a FactInternetSales táblát a rács nézetben.
- Felül az oszlopnevek felsorolása után van egy olyan oszlop, melynek a címe helyén azt látjuk, hogy Add Column. Kattintsunk rá duplán!
- Adjunk neki nevet, pl. Gain (jelentése: haszon), és nyomjunk Enter-t.
- A felső beviteli mezőbe írjuk be a következőt: =[SalesAmount]-[ProductStandardCost]. Egy trükk: kattintsunk a beviteli mezőbe, győződjünk meg, hogy ott villog a kurzor, majd kattintsunk a SalestAmount oszlopnévre, utána írjuk be a mínuszt (-) végül kattintsunk a ProductStandardCost oszlopnévre.
Ha mindent jól csináltunk, megjelenik az új oszlop az új értékekkel. Próbáljuk ki!
- Ehhez hozzunk létre egy új mértéket! Kattintsunk az új oszlop alatti üres szürke cellába, majd kattintsunk a szumma ikonra, mint fent tettük. A következőnek kell megjelennie felül: Sum of Gain:=SUM([Gain]), lent pedig az eredménynek.
- Kattintsunk az Excel ikonra felül. Ott hozzunk létre egy táblázatot, melynek oszlopai tartalmazzák az eladás összegét és a nyereséget, a sorai pedig az évet.
Ha mindent jól csináltunk, a nyereséget kell látunk éves bontásban. (Az SSAS-nek ezt a tulajdonságát egyébként még mindig bámulatosnak tartom.)
Számított táblák
A számított oszlopokhoz hasonlóan egy teljes tábla is lehet számított. Például készítsünk külön táblát a drága áruk számra, ahol az eladási ár eléri az 1000 dollárt!
- Készítsünk egy új számított táblát a következőképpen: Table → New Calculated Table, vagy rács nézetben a jobb alsó sarokban a plusz jelre kattintva.
- Adjunk neki alul egy nevet, pl. ExpensiveSales.
- Felül adjunk meg egy DAX formulát! Pl. a =FILTER('FactInternetSales', [SalesAmount] >= 1000) létrehoz egy olyan táblát, ami ugyanazokat az oszlopokat tartalmazza mint a FactInternetSales, viszont csak azokat a sorokat, amelyben a SalesAmount értéke legalább 1000.
Egy másik példa: ha csak bizonyos oszlopokat szeretnénk kiválasztani, akkor azt pl. a következőképpen tudjuk megtenni: =SELECTCOLUMNS(FactInternetSales, "Sales", [SalesAmount], "Cost", [ProductStandardCost]). Valójában itt tetszőleges olyan DAX képletet megadhatunk, melynek az eredménye egy táblázat.
Ahhoz, hogy a fenti módon működjön ez is, a megfelelő kapcsolatokat a FactInternetSales mintájára itt is létre kell hozni, valamint itt is létre kell hozni a mértékeket, pl. Sum of Expensive Sales Amount:=SUM([SalesAmount]). Az Excel-ben össze tudjuk hasonlítani a két értéket:
Mértékek
Az eddigi példákban már hoztunk létre mértékeket; most vessük ezeket jobban szemügyre!
- A FactInternetSales táblában a SalesAmount oszlop alatt kattintsunk egy üres cellába!
- Kattintsunk a szumma jel melletti kis háromszögre! Ott láthatunk egyéb lehetőségeket is. Válasszuk ki a Count-ot!
- Eredményül kiírja a nem üres sorok számát, viszont sajnos furcsa formában: dollárjellel és két tizedes formában, noha egészről van szó (darabszám). Jobbra lent a Properties panelen a formátumot (Format) állítsuk át általánosra (General). A mérték nevét is módosítsuk alatta (Measure Name), pl. erre: Internet Sales Count.
Ezzel pillanatok alatt össze tudunk dobni egy táblázatot, melyben az eladások darabszáma látható éves és kontinens szerinti bontásban.
Mértékként tetszőleges olyan DAX formula megadható, ami egy számot ad eredményül.
Sorrend
A fentiek alapján hozzuk létre a következő Excel táblázatot:
- VALUES: Sum of SalesAmount
- ROWS: CalendarYear, EnglishMonthName
Nyissuk le az egyik évet. Az eredmény nem pont az, amit elvártunk:
Jó lenne, ha a hónapokat nem ábécé sorrendben írná ki, hanem ahogyan a naptárban egymás után következnek. Ennek a megoldásához tegyük a következőket:
- Akár a rács-, akár diagram nézetben válasszuk ki a DimDate tábla EnglishMonthName oszlopát.
- A jobb alsó sarokban aProperties panelen görgessünk le, és a Sort By Column melletti legördülő mezőben válasszuk ki a MonthNumberOfYear oszlopot.
Ezzel beállítottuk azt, hogy ne ábécésorrendben jelenjenek meg a hónapok. Frissítsük az adatokat és győződjünk meg róla!
Hierarchiák
Ahogyan azt már láthattuk, bizonyos oszlopok hierarchiákat alkotnak. Ezt formálissá is tehetjük, gyakorlatilag csökkentve a dimenziók számát. Lássunk erre három példát!
Terület
Készítsünk terület hierarchiát!
- Diagram nézetben keressük meg a DimSalesTerritory osztályt!
- Kattintsunk a tábla jobb felső sarkában található bal oldali ikonra (Create Hierarchy)!
- Adjunk neki rendes nevet, pl. azt, hogy Territory.
- Húzd és vidd technikával dobjuk be alá az alábbi 3 oszlopot, ebben a sorrendben: SalesTerritoryGroup, SalesTerritoryCountry, SalesTerritoryRegion.
- Átnevezhetjük az oszlopokat a hierarchián belül, az nem befolyásolja az eredeti oszlopnevet.
Próbáljuk ki Excelben! Az oszlophoz a hierarchiát húzzuk be!
Termék kategória
Ez a hierarchia amiatt jelent kihívást, mert 3 különböző táblában szerepel, de hierarchiát csak táblán belül tudunk létrehozni. Ehhez tegyük a következőt:
- Rács nézetben nyissuk meg a DimProduct osztályt!
- Adjuk hozzá a fent megadott módon az alábbi két számított oszlopot (egyúttal megismerjük a RELATED DAX függvényt)!
- SubCategory: =RELATED(DimProductSubcategory[EnglishProductSubcategoryName])
- Category: =RELATED(DimProductCategory[EnglishProductCategoryName])
- A fen megadott módon hozzuk létre a DimProduct táblában a Product hierarchiát az alábbi tartalommal: Category, SubCategory, EnglishProductName.
Az eredmény az Excelben az elvárt:
Dátum kategória
A fentiek alapján az év-hónap hierarchia (CalendarYear - EnglishMonthName) már nem szabad, hogy nehézséget okozzon. Az egyedüli említésre méltó dolog az az, hogy a hónap sorrendje örökli a fent beállítottat.
Particionálás
Egy-egy nagyobb méretű tény tábla processzálása sok időt vehet igénybe, esetleg idővel a régi adatoktól meg szeretnénk szabadulni. Ezek miatt célszerű ezeket a táblákat partciókra osztani. Például a FactInternetSales táblát az alábbi módon tudjuk év szerint felosztani kisebb darabokra:
- Visual Studio-ban a Tabular Model Explorerben kattintsunk jobb egérgombbal a FactInternetSales alatti Partitions feliratra!
- Kattintsunk a New-ra, adjunk neki nevet (pl. 2010), majd alul kattintsunk a Design-ra.
- Válasszuk ki azt az oszlopot, ami alapján fel szeretnénk osztani a táblát, pl. legyen az OrderDateKey.
- Kattintsunk a kiválasztott oszlop melletti lefele mutató háromszögre.
- Válasszuk ki ezt: Number Filters → Between…
- A mód maradjon Basic, az "is greater than or equal to" értéke legyen 20100101, az "is less or equal to" 20101231, közöttük a kapcsolat maradjon And, végül kattintsunk az OK-ra.
- Kattintsunk bal felső sarokban az Import-ra.
Elvileg elkészítette, a gyakorlatban nálam nem frissült. Újranyitáskor már látható volt, hogy ott van. A következőképpen kell kinéznie:
let
Source = #"SQL/ ;AdventureWorksDW2017",
dbo_FactInternetSales = Source{[Schema="dbo",Item="FactInternetSales"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] >= 20100101 and [OrderDateKey] <= 20101231)
in
#"Filtered Rows"
Folytassuk!
- Készítsük el a 2011, 2012, 2013 és 2014 éveket is! Elég a Partition Manager-ben Copy-val másolatot készíteni, nem kell végrehajtani az összes fenti lépést.
- Hajtsuk égre a Deploy folyamatot, a fent leírtaknak megfelelően!
- Az SSMS-ben jobb kattintással a FactInernetSales táblán válasszuk ki a Partitions…-t!
- Válasszunk ki egy hónapot, és kattintsunk a Process ikonra (felül jobbról a második)!
- Felül meghatározhatjuk a processzálás módját, alul pedig kiválaszthatjuk a ténylegesen processzálandó éveket.
- Kattintsunk az OK-ra a processzálás megkezdéséhez.
Jogosultság kezelés
A felhasználók tipikusan valamilyen azonosítóval és jelszóval tudnak az elemző szolgáltatáshoz kapcsolódni. Ez alapján kezelhetjük a jogosultságot: ki mit láthat. Ezt a Model → Roles… ablakban tudjuk kezelni, ahol megadhatunk csoportokat és jogosultságokat. Például létrehozhatunk egy Users táblát, ahol a felhasználóneveket hozzárendelhetjük mondjuk az országokhoz, a USERNAME() függvénnyel kérhetjük le a bejelentkezett felhasználó nevét, és a kapcsolótábla alapján a megfelelő oszlopon beállíthatjuk azt, hogy pl. mindenki csak a saját országára vonatkozóstatisztikát láthassa. Ennek a létrehozása túlmutat a leírás keretein; erről többet a https://docs.microsoft.com/en-us/analysis-services/tabular-models/roles-ssas-tabular oldalon olvashatunk.
Végeredmény
A végeredmény az alábbi: AdventureWorks.zip.
A DAX lekérdező nyelv
A DAX a Data Analysis Expressions (adatelemző kifejezések) rövidítése. Ez valójában az Excelben kifejlődött függvénytár továbbgondolása, mely használható az Excelen kívül az SSAS-ben és a Power BI-ban is.
Források
Ennek a leírásnak nem célja, hogy részletesen bemutassa a DAX nyelvet. Az alábbi oldalakt hasznosnak gondolom a témában:
- https://docs.microsoft.com/en-us/dax/, ill. magyarul https://docs.microsoft.com/hu-hu/dax/: a hivatalos DAX dokumentáció
- https://docs.microsoft.com/en-us/dax/dax-function-reference, ill. magyarul https://docs.microsoft.com/hu-hu/dax/dax-function-reference: DAX függvény referencia
- https://dax.guide/: ez is DAX függvény referencia
- https://docs.microsoft.com/en-us/analysis-services/tabular-models/understanding-dax-in-tabular-models-ssas-tabular: SSAS specifikus DAX leírás
- https://www.tutorialspoint.com/dax_functions/: referencia szinten érinti a DAX függvényeket
- https://www.sqlservercentral.com/articles/dax-query-basics-1: alapvető SQL lekérdezése lefordítása DAX-ra.
- https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/: táblák összekapcsolása DAX-ban.
Példák
Lássunk néhány példát, amit SSMS-ben ki tudunk próbálni.
Sorok számának lekérdezése
Ez az SQL SELECT COUNT(*) FROM FactInternetSales-nak felel meg
EVALUATE(ROW("Count", COUNTROWS('FactInternetSales')))
Tábla teljes tartalmának kiírása
SQL-ben így írnánk: SELECT * FROM DimProductSubcategory
EVALUATE('DimProductSubcategory')
Tábla teljes tartalmának kiírása adott sorrendben
Az alábbi képlet SQL megfelelője: SELECT * FROM DimProductSubcategory ORDER BY EnglishProductSubcategoryName
EVALUATE('DimProductSubcategory') ORDER BY [EnglishProductSubcategoryName]
Csak kiválasztott oszlopok megjelenítése
SQL megfelelője: SELECT ProductStandardCost, SalesAmount FROM FactInternetSales
EVALUATE(SELECTCOLUMNS('FactInternetSales',
"ProductStandardCost", [ProductStandardCost],
"SalesAmount", [SalesAmount])
)
Adott oszlopban előforduló különböző értékek megjelenítése
SQL megfelelője: SELECT DISTINCT SalesAmount FROM FactInternetSales
EVALUATE(VALUES('FactInternetSales'[SalesAmount]))
Különböző értékek gyakorisága
SQL: SELECT SalesAmount, COUNT(*) FROM FactInternetSales GROUP BY SalesAmount
EVALUATE(SUMMARIZE('FactInternetSales', [SalesAmount], "Count", COUNT('FactInternetSales'[SalesAmount])))
Szűrés sorokra
SQL: SELECT * FROM DimSalesTerritory WHERE SalesTerritoryGroup = 'Europe'
EVALUATE(FILTER('DimSalesTerritory', [SalesTerritoryGroup] = "Europe"))
Két tábla összekapcsolása
SQL: SELECT SalesTerritoryCountry, COUNT(*) FROM DimSalesTerritory dst INNER JOIN FactInternetSales fis ON dst.SalesTerritoryKey = fis.SalesTerritoryKey GROUP BY dst.SalesTerritoryCountry
EVALUATE(ADDCOLUMNS(
SUMMARIZE('DimSalesTerritory', [SalesTerritoryCountry]),
"Sales Count", CALCULATE(COUNT('FactInternetSales'[SalesAmount]))
))
Skaláris érték megjelentése
Az egyik legzavaróbb hiányossága az SSAS-nek az, hogy képtelen megbirkózni azzal, ha az eredmény egy skaláris érték, és nem tábla. A következőt szeretnénk valahogy DAX-ban kifejezni: SELECT SUM(SalesAmount) FROM FactInternetSales. Adná magát a következő megoldás: EVALUATE(SUM('FactInternetSales'[SalesAmount])), viszont a következő hibaüzenetet kapjuk: Query (1, 1) The expression specified in the query is not a valid table expression. A következőképpen kell átalakítanunk, hogy működjön:
EVALUATE(SUMMARIZE('FactInternetSales', "Sum of sales amount", SUM('FactInternetSales'[SalesAmount])))