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.
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.
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.
Dies ist die temporäre Tabelle
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]
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:
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.
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)