Attribute Keys für Analysis Services optimieren mit Hilfe von Dense_Rank (Coaches' Diary)
Vor einigen Tagen habe ich einen Tipp veröffentlicht mit dem herausgefunden werden kann welche Attribute innerhalb einer SSAS Dimension einen String als Key haben. Das gilt es ja in einem SSAS Projekt aus diversen Gründen (einer ist Performance und ich glaube der zweite Grund war Performance, auf den Dritten komme ich noch…) zu vermeiden.
Nun kam die Frage auf, wie denn nun mit einfachen Mitteln dafür gesorgt werden kann, dass denn ein Integer Key auch immer verfügbar ist für das Attribut? Sofern das nicht über die Datenbewirtschaftung eines Data Warehouses gelöst wird (größeres Projekt…), dann muss das halt beim Aufbereitungsprozess für das Star-Schema eines “Cubes” (ich spreche ja eigentlich immer von SSAS Datenbanken) passieren.
Häufig sind diese Aufbereitungsprozesse aber direkt an das Quellsystem gebunden und die Aufbereitung wird über Sichten (Views) realisiert. Wo ist da noch Platz für die Zuordnung von Integer Keys? Diese Herausforderung habe ich in der Praxis schon häufig vorgefunden und damit auch eine ganze Reihe von Lösungen, welche sich der jeweilige Kunde dazu überlegt hatte. Da wird mit gespeicherten Prozeduren gearbeitet, da werden Tabellen umkopiert und mit Update-Statements aufgefüllt und natürlich spielt das Distinct Argument dabei häufig eine wichtige Rolle, um überhaupt die eindeutigen Werte des Attributes zu finden.
Hier möchte ich einen Lösungsansatz zeigen, welchen ich gerne verwende. Dazu nutze ich eine sogenannte Fensterrangfunktionen. Und zwar: DENSE_RANK(). Was steht zu dieser Funktion in den BOL?
“Gibt den Rang von Zeilen innerhalb der Partition eines Resultsets ohne Lücken in der Rangfolge an. Der Rang einer Zeile ist 1 plus die Anzahl der Ränge vor der fraglichen Zeile.”
Ok, klar! Also genau was wir brauchen, oder? Nun ja, der Sinn und ganz besonders der Nutzen erschließt daraus nicht auf den ersten Blick. Daher ein Beispiel.
Nehmen wir von der guten alten AdventureWorks2008R2 die Dimension DimProduct. In dieser Dimension gibt es eine ganze Reihe von Attributen für welche nur Keys als String bereitstehen. Zum Beispiel Color und es ist recht wahrscheinlich, dass unsere Anwender nach Color den Cube schneiden wollen. Also muss eine Optimierung des Schlüssels her.
Nur wie kommen wir jetzt mit ohne Umweg an einen eindeutigen Integer Key für Color? Hier ein erstes Beispiel für DENSE_RANK().
select
color,
DENSE_RANK() OVER (ORDER BY COLOR) as Color_Key
from dbo.DimProduct;
Hier nutzen wir die Rangfunktion, um für die Spalte Color den Rang für jeweils eine sogenannte Partition zu ermitteln. Diesen Rang nennen wir Color_Key. Natürlich geht das nicht nur mit einem Attribute in einer Abfrage, sondern auch mit weiteren Attributen!
select
*,
DENSE_RANK() OVER (ORDER BY COLOR) as Color_Key,
DENSE_RANK() OVER (ORDER BY SIZE) as Size_Key,
DENSE_RANK() OVER (ORDER BY SizeRange) as SR_Key
from dbo.DimProduct;
Wir sehen, dass wir in unsere Abfrage ohne weiteres eine Funktion einbauen können, welche uns für unsere Attribute die “notwendigen” Keys liefert. Damit kann also ohne größeren Umweg oder Aufwand unser Analysis Services Cubes optimiert werden.
