Hogyan nézhetem meg az Excel által generált MDX-et?

Például az OLAP PivotTable Extensions nevű bővítménnyel. És ha nincs ilyen a gépen? Akkor természetesen PowerShell-el :)

1. add-type -assemblyname "Microsoft.Office.Interop.Excel";  
2. $xls = [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application");  
3. $xls.workbooks.item(1).Worksheets.item(2).PivotTables(1).MDX;  

Ha változik a pivot, csak az utolsó sort kell újra meghívni. Viszont a kimenet típusa betűleves, ezzel még kezdeni kell valamit.

Egy egyszerű megoldás lehet az MDX Studio Online (by Mosha Pasumansky), vagy elcsenhetjük a fent említett add-in trükkjét, ami Nick Medveditskov webservice-ét használja. Ezzel öt sorosra bővül a szkript:

1. add-type -assemblyname "Microsoft.Office.Interop.Excel";  
2. $xls = [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application");  
3. $mdx = $xls.Workbooks.item(1).Worksheets.item(2).PivotTables(1).MDX;  
4. $mdxformatter = new-WebServiceProxy -uri http://formatmdx.msftlabs.com/formatter.asmx;  
5. $mdxformatter.FormatAsPlainText($mdx);  

Na, így mindjárt más.

Megjegyzés: a Workbooks és a Worksheets után nem lehet elhagyni az Item elemet, az indexek 1-től kezdődnek.

Package helyett Project

Elkészült SSIS csomagjainkat eddig kissé nehézkesen juttathattuk rendeltetési helyükre – az Analysis Services-hez vagy a Reporting Services-hez hasonlítva mindenképpen. Születtek izgalmas handmade megoldások (pl. kötegelt package export SSIS-szel vagy akár PowerShell-el), de azért hiányzott egy igazán korrekt megoldás. Szerencsére ezt a Microsoftnál is érzékelték, ezért a 2012-es Integration Services-ben megjelent a Project Deployment Model.

Koen Verbeeck szerint, amikor az SSAS-csapat előállt a Tabular Model-lel, az SSIS fejlesztőknél betelt a pohár: ”Mindenki az SSAS-re figyel majd! Hát egy frászt! Mostantól az SSIS-ben is lesz egy extra fejlesztési modell!” És lőn. J

A legfontosabb újdonságok:

• Alapvető változás, hogy nem egy package-et, hanem egy egész project-et kezel egy egységként.
• Az elkülönülő konfigurációk helyett paramétereket használhatunk, ezek a project részeként kerülnek telepítésre.
• Közvetlenül egy SQL Server instance-ra kerül a project, az Integration Services Catalogs alá.
• A végrehajtás közvetlenül az SQL Serveren történik, nincs szükség a DTExec utilra.
• A végrehajtás során az eseményeket automatikusan kezeli és tárolja, ezek később T-SQL-el lekérdezhetők.
• Verziókövetés az SQL Serveren. Minden project-telepítés során automatikusan létrejön egy új verzió, és pár kattintással vissza lehet állni egy előző verzióra. Ez jól hangzik, ugye?
• Microsoft.SqlServer.Management.IntegrationServices. Egy teljesen új assembly az új struktúrához.

A Project Deployment Model-ben tehát már nem egy SSIS instance-ra kerülnek a csomagok, hanem az SQL Server-re, az Integration Services Catalogs node alá. Itt először létre kell hoznunk egy katalógust: jobb klikk, majd Create Catalog.... A felugró ablakban engedélyeznünk kell a CLR integrációt, és kötelezően meg kell adnunk egy jelszót.

Jelenleg egyetlen, SSISDB nevű katalógust hozhatunk itt létre, amivel automatikusan létrejön egy ugyanilyen nevű adatbázis is (és két login, plusz egy SSIS Server Maintenance nevű job is, csak a teljesség kedvéért).

Ha már létezik az SSISDB, nyissunk meg az SSDT-ben (Sql Server Data Tools, a BIDS utódja) egy Integration Services projectet. A Solution Explorer-ben kattintsunk jobb gombbal a projectre és a megjelenő menüből válasszuk a Deploy-t. Ezzel elindítunk egy varázslót, ami pár lépésben végigvisz a telepítésen. A kihelyezett projectet már az SSMS-ből tudjuk menedzselni.

Akár a Deploy, akár a Build parancsot választjuk, automatikusan létrejön egy .ispac kiterjesztésű fájl (a helyét a project tulajdonság-lapján adhatjuk meg). Dupla kattintásra az SSDT-ben megismert Deployment Wizard indul el, a project így is telepíthető.

Változás esetén az új verziót ugyanígy élesíthetjük, az új telepítés automatikusan felülírja a régebbit. (Egy apró betűs figyelmeztetés jelenik meg csupán a Deployment Wizard ablakának alján.)

Nagyot nem hibázhatunk, mivel SQL Serveren bármelyik telepített project verzióra visszaállhatunk. Az SSMS Object Explorerében kattintsunk jobb gombbal a project nevére, és a helyi menüben válasszuk a Versions…-t. 

A felugró ablakban megjelenő verziók közül pedig jelöljük be azt, amelyiket használni akarjuk, szabadon léphetünk előre-hátra a verziók között.

(NEM a Current mezőt kell kipipálni, hanem csak rá kell állni az adott sorra, aztán katt a Restore to Selected Version gombra.)

Az új rendszer mindezek mellett még számos újdonságot tartogat. Új táblák, nézetek és tárolt eljárások, valamint igen részletes beépített riportok segítik a munkát.

Az új Project Deployment Model mellett a korábbi telepítési modell is elérhető még, Package Deployment Model néven találhatjuk meg (néhol Legacy Deployment Model-ként is hivatkoznak rá). A SSIS fejlesztői környezetben már az új modell az alapértelmezett, de a korábbit is lehet használni, ha átkonvertáljuk a projectet.

SSIS csomagok listája 1.

Az Addictive Measures blogon találtam nemrég egy hasznos T-SQL szkriptet, ami kilistázza az MSDB alá telepített SSIS csomagokat. Hasznos cucc, rajtam viszont egyre jobban elharapózik a PowerShell mánia (bárcsak időm is lenne rá), ezért kicsit utánajártam, vajon nem lehet ezt összehozni PS-sel is? Hát persze hogy de :)

A GetDtsServerPackageInfos eljárásnak egy szerver és egy mappa nevét kell megadnunk, és már ontja is az információkat. A szerver azonosításánál pontos nevet, vagy ˝.˝-t használjunk, a ˝(local)˝ vagy ˝localhost˝ nem működik.

1. param([string]$folder = "\", [string]$server = ".", $drill = 0);  
2.   
3. # Get assembly (this version come with SQL Server 2012 RC0)  
4. $dll = "C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\v4.0_11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ManagedDTS.dll"  
5. add-type -path $dll;  
6.   
7. $ssisapp = new-object Microsoft.SqlServer.Dts.Runtime.Application;  
8. $packages = $ssisapp.GetDtsServerPackageInfos($folder, $server);  
9.   
10. # Get output  
11. $packages `  
12.     | select name, description, flags, folder, creationdate, packagedatasize, `  
13.     @{name="version";expression={[string]$_.versionmajor + "." + [string]$_.versionminor + "." + [string]$_.versionbuild}}, `  
14.     versioncomments `  
15.     |sort name | format-table -autosize;  
16.   
17. # Drill down if it's needed  
18. if ($drill -eq 1)  
19.     {  
20.     foreach ($pkg in $packages)  
21.         {  
22.         if ($pkg.flags -eq "Folder")  
23.             {  
24.             $subfolder = $folder + "\" + $pkg.name; 
25.             $subfolder = $subfolder -replace "\\\\", "\";  
26.               
27.             .\pkg_list $subfolder $server 1;  
28.             }  
29.         }  
30.     };  

(A syntax highlighter egy kicsit belezavarodott a backslash-ekbe. A kód helyes, csak a színezés nem. Sorry.)


update: a fenti szkript akkor fut hibátlanul, ha pkg_list.ps1 néven mentjük el, és abból a könyvtárból futtatjuk, ahová tettük. Ha a $folder mappa alatti almappák tartalma is kell, akkor $drill=1 paraméterrel hívjuk meg. Pl.: .\pkg_list "MSDB" "." 1. Ilyenkor saját magát hívja, pkg_list néven. Már a következő poszton járt az eszem. :)

A GetDtsServerPackageInfos meghívásához a Microsoft.SqlServer.ManagedDTS assembly-re van szükségünk. Csakhogy ennek a dll-nek az SQL Server 2012 RC0-val már a .NET 4-es verziója érkezik. Mivel a PowerShell a .NET 2-re van felkészítve, ez gondot jelent. Két lehetőségünk van:

1. Ha a gépen fut SQL 2005/2008/2008R2, akkor előkereshetjük egy korábbi verzióját. Például: C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll
2. Engedélyezzük a .NET 4-et a PowerShell környezetünkben egy config fájl segítségével. Erről bővebben pl. itt vagy itt lehet infót találni.

Az SSIS 2012-es kiadásával azonban egy teljesen új csomag-telepítő metódus (deploy mode) érkezik, ami átveszi a helyét a korábbi megoldásoknak. A kompatibilitás miatt ugyan továbbra is választható a korábbi módszer, és ugyanezért megmaradtak az msdb.dbo.sysssis... táblák is, de az új módszerhez egy teljesen új struktúra tartozik.

A következő poszt erről fog szólni. Maradjanak velünk, a szünet után folytatjuk! ;)