Mark Aslan Kuschels Blog

SQL, Business Intelligence, Microsoft, .Net

Ermitteln der Measurenutzung in SQL Server Analysis Services

Business Intelligence Projekte erzeugen in der Regel einen oder mehrere Cubes. Jeder kennt die Problematik, dass es für den internen oder externen Kunden sehr schwierig ist seine Anforderungen derart zu abstrahieren, dass sich Measures im Vorfeld genau definieren lassen. Manchmal versteht der Kunde sogar erst während des Projektes, wie die Technologie wirklich funktioniert.
Die Folge: Anforderungen werden über den Haufen geworfen und das System wird an diversen stellen erweitert – sowohl noch im Projekt, als auch später durch die Wartung. Über die Jahre hinweg werden die Strukturen des Cubes immer komplexer. Doch wie ermittelt man, welche wirklich noch gebraucht werden?

Der Klassiker: OLAPQueryLog

In Analysis Services ist ein entsprechendes Werkzeug theoretisch bereits eingebaut. Bei der Entdeckung des QueryLogs mögen sicher schon viele Jubelschreie von sich gegeben haben, denn das QueryLog ermöglicht es Anfragen an die Analysis Services aufzuzeichnen. Beim Ausprobieren zeigt sich jedoch, dass neben dem Namen der AS-Datenbank, der verwendeten Measuregruppe nur noch ein kryptisches Dataset mit aufgezeichnet wird.

image_thumb

Enthält die Measuregruppe mehr als ein Measure, was nicht gerade unüblich ist, ist diese Information unscharf und somit nur begrenzt hilfreich.

Ein Versuch: SQL Server Profiler

Etwas detaillierte Daten lassen sich ermitteln, indem man den SQL Server Profiler laufen lässt. Der Profiler klinkt sich direkt in den SQL Server ein und zeichnet die gewählten Ereignisse mit allen Details auf. Beim Erstellen des Traces ist darauf zu Achten, dass alle Ereignisse, außer “Query End” abgewählt werden. Ebenso ist als Ziel eine Tabelle anzugeben, damit die Daten später auch ausgewertet werden können.
Zum Profiler muss man jedoch immer sagen, dass der Einsatz Auswirkungen auf die Performance des Systems hat. In Produktivumgebungen sollte man daher immer prüfen, ob es nicht noch einen anderen Weg gibt.

image_thumb4image_thumb5

Als Ergebnis stehen in nun in der Tabelle einige Spalten, interessant ist hier die Spalte TextData, die enthält die komplette MDX Abfrage und somit auch alle benutzten Measures!
Jetzt fehlt nur noch ein Weg aus der Abfrage eine Liste der Measures zu erzeugen.

Um dies zu lösen habe ich ein SSIS Paket geschrieben und mit dem folgenden Skript Task eine Liste von Measures aus dem Text extrahiert. Kernstück ist der reguläre Ausdruck, über den die Abfrage aufgeteilt wird. Das Ergebnis ist eine Komma-separierte Liste mit den verwendeten Measures.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    Dim _strMdx As String

    If (Row.TextDataString_IsNull = False) AndAlso Row.TextDataString.IndexOf("SELECT") > -1  
     Then

        _strMdx = Row.TextDataString.Substring(Row.TextDataString.IndexOf("SELECT"))

 

        Dim _strEscaped As String

        Dim _strMatch As MatchCollection

        Dim _strLine As String

        Dim usedMeasures As String = String.Empty


        _strEscaped = Regex.Escape(_strMdx)

        _strMatch = Regex.Matches(_strEscaped, "\[Measures.*?(,|\)|\})")

 

        For Each _line As Match In _strMatch

            _strLine = _line.ToString.Substring(0, _line.ToString.Length - 1).Trim()

            usedMeasures &= Regex.Unescape(_strLine) & ","

        Next

 

        If usedMeasures.Length > 1 Then

            Row.Measures = usedMeasures.Substring(0, usedMeasures.Length - 1)

        Else

            Row.Measures = String.Empty

        End If

    Else

        Row.Measures = String.Empty

    End If

End Sub


 

In meinem Beispiel wird die Liste erst in eine temporäre Tabelle geschrieben und dann Schlussendlich werden die einzelnen Measures in die Zieltabelle überführt.

image16_thumb
Dies ist die temporäre Tabelle

image_thumb11
Und dies die Zieltabelle

Eine einfache Abfrage zeigt nun welches Measure wie oft genutzt wurde:
SELECT [Measure],

      [DatabaseName],

      COUNT(*)

  FROM [log_olap_trace_measures]

  GROUP BY [Measure],

      [DatabaseName]

image24_thumb

 

Neue Welten: Extended Events

Seit SQL Server 2012 hat Microsoft den SQL um Extended Events erweitert. Im Gegensatz zum Profiler läuft bei Extended Events nicht permanent ein Trace mit, sondern nur bei bestimmten ausgewählten Ereignissen wird etwas protokolliert. Dies erfolgt mit einem nur recht geringen Overhead und kann daher auch auf einem Produktivsystem ohne Probleme eingesetzt werden.

Für die Datenbankengine ist es möglich Extended Event-Sitzungen direkt im Objekt Explorer des Management Studios anzulegen:
image_thumb15

Bei Analysis Services müssen Extended Events per XMLA definiert werden.
Ein sehr guter Artikel findet sich dazu im Blog von Bill Anton.
Um unser Profiler-Beispiel mit Extended Events nachzubauen kann das XMLA Skript aus Bills Post verwendet werden. Hier ist dann die Zeile mit dem Event “QueryEnd” auszukommentieren und ein gültiger Dateipfad muss angegeben werden. Das Schreiben in eine Tabelle ist von den Extended Events aus leider nicht möglich.

Glücklicherweise kann die xel Datei per T-SQL ausgelesen werden. Jedoch gibt es auch hier eine weitere Hürde, die für uns relevante MDX-Abfrage verbirgt sich in der letzten XML Spalte, diese enthält das Element “TextData”. Das Schreiben eines XML-Parsers oder das Laden dieser Daten in ein XML Objekt über Integretation Services ist zum Glück nicht nötig. Das Zauberwort heißt hier XQuery!
Zugegeben, XQuery ist sehr gewöhnungsbedüftig und ich tue mich mit der Syntax etwas schwer, die Mühe wird aber am Ende belohnt. Nach etwas Experimentieren habe ich folgende Abfrage erstellt:
SELECT  *,
       CAST(event_data AS XML).query('(/event/data[@name="TextData"]/value)[1]') AS 'event_data_XML'
FROM   sys.fn_xe_file_target_read_file(
                    'D:\Trace\OlapTrace_0_130334860612250000.xel'
                    ,NULL
                    ,NULL
                    ,NULL
             )

Die Spalte event_data_XML enthält nun die MDX-Abfrage, eingekapselt in <value></value>-Tags. Das braucht uns bei der weiteren Verwendung für den vorhandenen ETL-Prozess nicht zu stören, da der reguläre Ausdruck diesen Abschnitt ignoriert.
image_thumb2

Und nun wünsche ich viel Freude beim Analysieren der Nutzung Ihres Cubes!

Weitere Informationen

Artikel im Blog der PTS Group AG
Blog von Bill Anton
MSDN: Query
MSDN: Extended Events

SSAS_MeasureTracking.zip (24,00 kb)

Kommentar schreiben

Loading