[PL] Metadane baz OLAP w SQL Server 2005 i nowszych

Jakiś czas temu zmieniłem mój zawodowy profil zainteresowań i z rasowego programisty SQL stałem się kimś pomiędzy analitykiem a programistą Business Intelligence. W nowej roli przydają się czasem moje umiejętności programistyczne. Dowodem na to jest przykład, który opisuję poniżej.

W swojej pracy, oprócz typowych zadań programisty BI, wykonuję także zadania adminitracyjne. Jednym z takich zadań jest monitorowanie baz danych. O ile bazy relacyjne (głównie hurtownie danych) daję radę ogarnąć dość sprawnie (widoki systemowe, DMVs, …), o tyle z bazami OLAP jest w SQL Server 2005/2008/2008 R2 już nieco gorzej, głównie z uwagi na dwie rzeczy: po pierwsze – SQL Server Management Studio nie oferuje tylu fajnych “zabawek” do administrowania bazami OLAP i – po drugie – sam silnik OLAP w prosty sposób nie chce odpowiadać na moje, najprostsze nawet, pytania.

Przykład konkretny: chcę wylistować bazy danych OLAP z mojego serwera oraz sprawdzić, ile miejsca zajmuje każda z nich. Typowe zadanie administracyjne – pilnuję, jak rosną moje bazy. Wydawałoby się, że powinien być jakiś prosty mechanizm, który takie informacje mi udostępni. A jednak nie znalazłem takowego (może poza raportem zwracającym właściwości pojedynczej bazy danych, który mogę sobie wyświetlić przez kliknięcie prawym przyciskiem myszy na konkretnej bazie OLAP w oknie Object Explorer w narzędziu SQL Server Management Studio). Dodatkowo, chciałbym raportowanie rozmiarów baz OLAP dołączyć do całej reszty raportów administracyjnych (a te w pocie czoła tworzę w T-SQL). Problem w tym, że nie udało mi się z poziomu kodu T-SQL (próbowałem głównie funkcji OPENROWSET) wykonać na serwerze SSAS kodu ASSL z wywołaniem metody Discover (OPENROWSET wszystko, co wysyłamy do SSAS od razu pakuje do metody Execute).

Rozwiązanie:
1. Pobrać i zainstalować przykłady dla SQL Servera (szukaj tam pozycji “command-line utility”), a w nich narzędzie ascmd.exe (odpowiednik sqlcmd.exe dla Analysis Services).
2. Stworzyć skrypt Discover.xmla zawierający następujący kod ASSL:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
  <RequestType>DISCOVER_XML_METADATA</RequestType>
  <Restrictions>
    <RestrictionList>
      <ObjectExpansion>ExpandFull</ObjectExpansion>
    </RestrictionList>
  </Restrictions>
  <Properties>
    <PropertyList>
      <Format>Tabular</Format>
      <Content>Data</Content>
    </PropertyList>
  </Properties>
</Discover>

Tu adnotacja: próbowałem różnych opcji w sekcji Restrictions i nie udało mi się osiągnąć zamierzonego efektu bez umieszczenia tam opcji ExpandFull, która co prawda powoduje, że skryptowane są wszystkie informacje o serwerze SSAS i bazach OLAP, ale umożliwia zdobycie tych informacji, na których mi zależy.

3. Stworzyć plik .bat / .cmd, który będzie wykonywał skrypt z kroku 2. na serwerze SSAS. Zawartość takiego skryptu może wyglądać tak:

“C:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Administrator\ascmd\CS\ascmd\bin\Debug\ascmd.exe” -tc 3600 -S localhost -i “C:\Scripts\Discover.xmla” -o “C:\Scripts\Discover.xml”

Czyli wykonujemy skrypt, a wynik (informacje o bazach OLAP i serwerze SSAS) zapisujemy do pliku Discover.xml.

4. Napisać kawałek kodu T-SQL, który wydobędzie niezbędne dane i zaprezentuje je w postaci tabelarycznej.

Przykład takiego kodu:

DECLARE @cmd nvarchar(4000);
DECLARE @discover xml;

SET @cmd = '"C:\Scripts\Discover.bat"';
EXEC master.dbo.xp_cmdshell @cmd, no_output; -- generujemy plik z wynikiem metody Discover

SET @discover = (SELECT * FROM OPENROWSET(BULK N'C:\Scripts\Discover.xml', SINGLE_BLOB) AS Q);
WITH XMLNAMESPACES (
  DEFAULT 'urn:schemas-microsoft-com:xml-analysis',
  'urn:schemas-microsoft-com:xml-analysis:rowset' AS x,
  'http://www.w3.org/2001/XMLSchema-instance' AS xsi,
  'http://www.w3.org/2001/XMLSchema' AS xsd,
  'http://schemas.microsoft.com/analysisservices/2003/engine/2' AS ddl2,
  'http://schemas.microsoft.com/analysisservices/2003/engine/2/2' AS ddl2_2,
  'urn:schemas-microsoft-com:xml-analysis:rowset' AS xars,
  'http://schemas.microsoft.com/analysisservices/2003/engine' AS xars2
)
SELECT
  Dbs.Db.value('(xars2:Name/text())[1]', 'sysname') AS DATABASE_NAME,
  Dbs.Db.value('(xars2:EstimatedSize/text())[1]', 'bigint')/(1024.*1024) AS ESTIMATED_SIZE_MB
FROM @discover.nodes('/DiscoverResponse/return/x:root/x:row/xars:METADATA/
  xars2:Server/xars2:Databases/xars2:Database') AS Dbs(Db);

Tylko tyle i aż tyle. W podobny sposób mogę uzyskiwać wszystkie informacje o bazach OLAP – o ich strukturze (kostki, miary, wymiary, …) i właściwościach. Cały mechanizm można “ubrać” w procedury składowane czy joby SQL Server Agenta.

W SQL Server 2008 i nowszych wersjach do wydobywania metadanych baz OLAP w jakimś zakresie można wykorzystać widoki systemowe SSAS (ale z tego, co wiem, ilość metadanych zwracanych przez te widoki nie powala na kolana) lub PowerShell (zamierzam się w wolnym czasie trochę przyjrzeć tej opcji), ale aktualnie muszę takie zadania realizować także na SQL Server 2005, stąd podejście takie, a nie inne. Jeśli ktoś zna lepszą metodę dobierania się do tych metadanych, chętnie poczytam.