Gelegentlich trifft man in der BI-Welt auf die Anforderung, dass nicht alle dargestellten Kennzahlen auch für alle Nutzer sichtbar sein sollen. Zur Implementierung von Nutzerberechtigungen auf OLAP-Cubes stellt Microsoft in Analysis Services zwei Verfahren zur Verfügung: die Dimensionssicherheit und die Zellsicherheit. Die Dimensionssicherheit regelt den Zugriff der Nutzer über die in einer Dimension enthaltenen Elemente. Die (mächtigere) Zellsicherheit wird normalerweise nur benötigt, wenn die Steuerung der Nutzerrechte anhand der Elemente (nur) einer Dimension nicht mehr ausreicht. Der Einsatz der Zellsicherheit hat allerdings auch einige Tücken. Erstens bleiben weiterhin alle Elemente der Dimensionen sichtbar. Nur die Kennzahlen auf den nicht-berechtigten Elementen werden mit SEC überschrieben. Zweitens werden ohne Sonderbehandlung die Nutzungsrechte (nach oben) auf die Aggregate vererbt. Das heißt, hat ein Nutzer das Recht mindestens ein Element einer bestimmten Ebene zu sehen, so sieht er auch das echte Aggregat aller Elemente dieser Ebene, unabhängig davon, ob einzelne Elemente dieser Ebene per SEC geschützt sind.
Dieser Blogbeitrag stellt eine elegante Alternative zum Einsatz der Zellsicherheit vor.
Ausgangssituation
Wir stellen uns bei unserem fiktiven Stühle-Hersteller „Chair“ ein Szenario vor, in dem die Vertriebsmitarbeiter Zugriff auf die Kennzahlen anhand einer Kombination von Elementen aus zwei Dimensionen, beispielsweise Vertriebsleiter und Stoffgruppe, erhalten sollen. Das heißt, die Berechtigungen zum Lesen einer Kennzahl sollen an den Schnittpunkten der Elemente aus den beiden Dimensionen erfolgen. Dies illustriert folgender Bericht, in dem die beiden Dimensionen miteinander verschachtelt wurden.
Abbildung 1: Zwei verschachtelte Dimensionen ohne Rechte-Beschränkungen
Problematik
Das obige Szenario ist ein Musterbeispiel für den Einsatz der Zellsicherheit. Auf der Ebene der berechtigten Elemente klappt dies auch sehr gut und der Nutzer kann nur die Elemente sehen, für die er die Berechtigung besitzt. Auf der Ebene der Aggregate aber darf er alle Aggregate sehen, die wenigstens aus einem Element bestehen, für welches er die Berechtigung besitzt. Und da die Berechnung der Aggregate im Cube bereits stattgefunden hat, wenn über die Anzeige der Kennzahlen anhand von Berechtigungen entschieden wird, sieht der Nutzer auch die echten Aggregate. Das ist eine sehr unschöne Tatsache. Besitzt eine Ebene nur wenige Elemente, kann leicht ein Rückrechnen auf die gesperrten Elemente erfolgen. Selbst bei mehreren Elementen lässt das Aggregat über die Anzahl der Elemente einen Vergleich mit einem Durchschnitt zu. Beides lässt sich mit der Anforderung nach nutzergefilterten Teilsichten auf den Cube meist nicht vereinbaren. Zwar gäbe es als Workaround die Möglichkeit, per Cubescript für die betroffenen Dimensionen Scopes anzulegen, in denen die Aggregate im Cube nach der Berücksichtigung der Nutzerrechte nachberechnet würden, aber diese Möglichkeit ist mitunter aufwendig zu implementieren und verschlechtert gerade bei größeren Cubes die Performance. Außerdem würden die Nachberechnungen des Cubes auch die Nutzer anderer Rollen wie etwa die der Admin-Rolle betreffen. Es ließe sich auch hier wieder eine weitere Behandlung einbauen, aber die Komplexität würde dadurch deutlich größer und die Performance nicht besser.
Lösungsidee
Statt die Zellsicherheit zu verwenden und alle damit verbundenen Probleme behandeln zu müssen, bauen wir einfach eine neue Dimension, deren Elemente aus der Kombination der Elemente der ersten Dimension mit den Elementen der zweiten Dimension bestehen.
Wie in der Ausgangssituation beschrieben, lautet die Anforderung, Nutzerberechtigungen an die Schnittpunkte der Elemente der ersten Dimension mit denen aus der zweiten Dimension zu hängen. Unsere neue Dimension stellt genau alle diese Schnittpunkte dar und bildet damit ein sogenanntes kartesisches Produkt. An ihre Elemente können wir direkt die Berechtigungen hängen und dadurch statt der Zellsicherheit gleich die Dimensionssicherheit verwenden.
Im Gegensatz zur Zellsicherheit stellt uns die Dimensionssicherheit die Option „Enable Visual Totals“ zur Verfügung. Diese stellt sicher, dass auf den Ebenen der Aggregate nur die Summen der für den jeweiligen Nutzer sichtbaren Werte angezeigt werden. Damit sind die Anforderungen des Szenarios erfüllt und alle etwaigen Probleme vermieden.
Implementierung Schritt für Schritt
Folgende Schritte sind für die Implementierung der Dimensionssicherheit über zwei Dimensionen durchzuführen:
- Datengrundlage der neuen Kombi-Dimension schaffen und Faktentabelle(n) anbinden
- Tabelle zur Aufnahme der zugriffsberechtigten Nutzer erstellen
- Kombi-Dimension modellieren und an die MeasureGroup(s) anbinden
- Cube erstellen
- Dimensionssicherheit im Cube einrichten
Kombi-Dimension im Management Studio erstellen
Im SQL Server Management Studio erstellen wir eine View, die der neuen Dimension als Datengrundlage dient. Diese View verbindet die beiden betroffenen Dimensionen in einem kartesischen Produkt. In unserem Beispiel werden also alle möglichen Kombinationen aus Vertrieb und Stoffgruppe gebildet.
CREATE VIEW V_Import_DIM_StoffgruppeVertrieb AS SELECT s.StoffgruppenID + '_' + v.VertriebID StoffgruppeVertriebID ,s.StoffgruppenBEZ_DE + '_' + v.VertriebBEZ_DE StoffgruppeVertriebBEZ ,s.StoffgruppenID ,s.StoffgruppenBEZ_DE StoffgruppeBEZ ,v.VertriebID ,v.VertriebBEZ_DE VertriebBEZ FROM V_Import_Dim_Stoffgruppen s, V_Import_Dim_Vertrieb v
Als gemeinsamer Schlüssel wird eine Verbindung des Schlüssels der ersten Dimension mit dem Schlüssel der zweiten Dimension, hier StoffgruppeVertriebID, verwendet. Die weiteren Spalten dienen lediglich der Übersichtlichkeit und könnten auch weggelassen werden.
Die relationale Faktentabelle sollte auch gleich an die neue Dimension angebunden werden. Hierfür erweitern wir die Fact-View um eine Spalte, welche einfach aus der Konkatenation der beiden bereits existierenden Dimensionsschlüssel StoffgruppenID und VertriebID besteht. Wie in der Dimension selbst heißt diese dann StoffgruppeVertriebID.
ALTER VIEW V_Import_Fact_Deckungsbeitragsrechnung AS SELECT db.MONTH Periode, db.Valuetype WertartID, 1 PeriodenansichtID, 1 KumulationID, 1 WertanzeigeID, db.Color StoffgruppenID, db.Product ProduktID, db.Customer KundeID, db.Sales VertretergruppeID, '1' AS GesellschaftID, db.Color + '_' + db.Sales AS StoffgruppeVertriebID, -- Measures db.Discount AS Rabatt, db.Labour AS Lohnkosten, db.Material AS Materialkosten, db.Revenues AS Umsatz, db.SD AS Erlösschmälerungen, db.Volume AS Absatz, 1 AS AnzahlBelegpositionen, -- Info db.InvoiceNo AS BelegNr FROM T_IMPORT_Deckungsbeitragsrechnung db
Mögliche weitere Faktentabellen erweitern Sie analog dem obigen Beispiel.
Tabelle zur Aufnahme der zugriffsberechtigten Nutzer erstellen
Als nächstes wird eine Tabelle in der relationalen Datenbank benötigt, welche den jeweils berechtigten Nutzer für eine Kombination der Schlüssel der beteiligten Dimensionen speichern kann.
CREATE TABLE [T_S_User_StoffgruppeVertrieb]( [VertriebID] [varchar](50) NULL, [StoffgruppeID] [varchar](50) NULL, [UserID] [varchar](50) NULL, [GUID] [uniqueidentifier] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [T_S_User_StoffgruppeVertrieb] ADD DEFAULT (newid()) FOR [GUID] GO
Die Spalte UserID muss den kompletten Nutzernamen im Format <DOMAIN>\<USER> enthalten.
Die GUID wird zur Identifikation eines einzelnen Datensatzes angefügt.
Über die Tabelle T_S_User_StoffgruppeVertrieb wird die View V_S_User_StoffgruppeVertrieb gesetzt. Diese dient (im Sinne einer ganzheitlichen benutzerfreundlichen Anwendung) einer späteren relationalen Eingabeanwendung als Datengrundlage. Alternativ kann sie einer möglichen Modellierung der Kombi-Dimension als Datengrundlage dienen. Sollten Sie diese nicht verwenden wollen, passen Sie bitte die View entsprechend an.
CREATE VIEW V_S_User_StoffgruppeVertrieb AS SELECT t.StoffgruppeID + '_' + t.VertriebID AS StoffgruppeVertriebID ,t.StoffgruppeID ,s.StoffgruppenBEZ_DE StoffgruppeBEZ ,t.VertriebID ,v.VertriebBEZ_DE VertriebBEZ ,t.UserID ,t.[GUID] FROM T_S_User_StoffgruppeVertrieb t LEFT JOIN V_Import_Dim_Stoffgruppen s ON t.stoffgruppeID = s.StoffgruppenID LEFT JOIN V_Import_DIM_Vertrieb v ON t.vertriebid = v.VertriebID
Erweiterung der Kombiview um die berechtigten Nutzer
Aus didaktischen Gründen wurde die Kombi-Dimensionsview in Schritt 4.1 erst erstellt und jetzt nochmals angepasst. Dies kann von erfahren Anwendern auch gleich im ersten Schritt durchgeführt werden.
Damit später im Cube direkt an dem Kombinationselement aus den beiden Dimensionen alle berechtigten Nutzer verfügbar sind, werden diese Komma-separiert aneinander gehängt und in die neue Spalte UserList geschrieben. Dies geschieht in unten stehenden Sub-Select der View V_Import_DIM_StoffgruppeVertrieb.
ALTER VIEW V_Import_DIM_StoffgruppeVertrieb AS
SELECT
s.StoffgruppenID + '_' + v.VertriebID StoffgruppeVertriebID
,s.StoffgruppenBEZ_DE + '_' + v.VertriebBEZ_DE StoffgruppeVertriebBEZ
,s.StoffgruppenID
,s.StoffgruppenBEZ_DE StoffgruppeBEZ
,v.VertriebID
,v.VertriebBEZ_DE VertriebBEZ
,(SELECT UserID + ','
FROM V_S_User_StoffgruppeVertrieb usr
WHERE usr.StoffgruppeVertriebID = (s.StoffgruppenID + '_' + v.VertriebID)
ORDER BY StoffgruppeVertriebID
FOR XML PATH('')
) AS UserList
FROM
V_Import_Dim_Stoffgruppen s,
V_Import_Dim_Vertrieb v
GROUP BY
s.StoffgruppenID + '_' + v.VertriebID
,s.StoffgruppenBEZ_DE + '_' + v.VertriebBEZ_DE
,s.StoffgruppenID
,s.StoffgruppenBEZ_DE
,v.VertriebID
,v.VertriebBEZ_DE
Anlegen und Verknüpfen der Dimension im Modeler
Jetzt muss die neue Dimension StoffgruppeVertrieb noch im Modeler angelegt werden.
Abbildung 2: Modeler, Dimensions
Bei der Modellierung der Levels wird für die neue Dimension lediglich ein Level benötigt. Das Level-Attribute Visible kann, falls gewünscht, sogar deaktiviert werden.
Abbildung 3: Modeler, Levels
Als Datengrundlage für Level source columns dient die View V_Import_DIM_StoffgruppeVertrieb mit dem Schlüssel StoffgruppeVertriebID und der Bezeichnung StoffgruppeVertriebBEZ.
Abbildung 4: Modeler, Level source columns
Wichtig ist, die berechtigten User als Attribut aus der Spalte UserList der V_Import_DIM_StoffgruppeVertrieb zu erstellen, damit diese der Dimension im Cube anschließend zur Verfügung stehen.
Abbildung 5: Modeler, Level attributes
Abbildung 6: Modeler, Level attributes source columns
Die neue Dimension muss nun noch der betroffenen MeasureGroup hinzugefügt werden und mit ihr über die ID in der Fact-View verknüpft werden.
Abbildung 7: Modeler, MeasureGroup dimensions
Abbildung 8: Modeler, MeasureGroup dimension source columns
Anschließend müssen alle vier Schritte im Modeler einmal komplett durchlaufen werden (Create Snowflake -> Transform All -> Deploy OLAP Database -> Process Cube).
Einrichten der Dimensionssicherheit in BIDS bzw. Data Tools
In BIDS bzw. Data Tools muss zunächst eine neue Rolle angelegt werden, welche sich um die Sichtbarkeit der Kennzahlen auf Grund der Berechtigungen in der neuen Dimension kümmert.
Abbildung 9: Data Tools, neue Rolle
Hinweis: Nutzerberechtigungen über mehrere Rollen hinweg sind additiv. Die folgenden Einstellungen müssen in den Registerkarten der Rolle vorgenommen werden:
Auf der Registerkarte Dimensionsdaten sind die eigentlichen Einstellungen zu treffen.
Das Dropdown Menü Dimension enthält oben die Datenbank Dimensionen und darunter die an den jeweiligen Cube angebundenen Dimensionen. In unserem Beispiel wählen wir direkt die an den Cube angebundene Dimension StoffgruppeVertrieb.
Abbildung 10: Dimensionsdaten, neue Dimension ausgewählt
Anschließend wird in der Registerkarte Erweitert darunter der Elementschlüssel für die Attributsicherheit ausgewählt. In unserem Beispiel heißt dieser StoffgruppeVertriebID. Dann ist in dem Textfeld Zulässige Elementgruppe ein MDX Statement einzutragen, welches nur die Elemente der Dimension zurückliefert, für die der aktuelle Nutzer die Berechtigung besitzt.
Abbildung 11: Dimensionsdaten, zulässige Elementgruppe gefiltert
Hinweis: Bei Erstellung des MDX-Statements den Namen der Attributhierarchie verwenden!
Filter( [StoffgruppeVertrieb].[StoffgruppeVertriebID].Members, VBA!InStr( [StoffgruppeVertrieb].[StoffgruppeVertriebID].CurrentMember.Properties( "BerechtigteUser" ), UserName ) > 0 )
In den meisten Fällen werden Sie noch die Option Sichtbare Gesamtwerte aktivieren (engl. Enable Visual Totals) setzen wollen.
Abbildung 12: Dimensionsdaten, Option sichtbare Gesamtwerte aktivieren
Diese Option stellt sicher, dass sich die Aggregate im Cube nur aus den Summen der für den jeweiligen Nutzer sichtbaren Werte zusammensetzen – eine Option, welche im Gegensatz zur Zellsicherheit nur die Dimensionssicherheit besitzt. Um die Änderungen produktiv zu setzen, genügt ein Speichern Ihrer Änderungen.
Relationale Pflegeanwendung der berechtigten Nutzer
Dieser Schritt ist optional und als Erweiterung gedacht.
Wenn die Tabelle T_S_User_StoffgruppeVertrieb nicht manuell gepflegt werden soll, empfiehlt es sich, eine relationale DeltaMaster Eingabeanwendung zur leichteren Pflege hinzuzufügen. Details zum Bau relationaler Pflegeanwendungen entnehmen Sie bitte dem Dokument „Relationale Dateneingabe“ von Bissantz & Company.
Ergebnis anhand des Beispiels
Ein Nutzer ist in der Vertriebsgruppe Baumann für die Stoffgruppe Classic und Alu berechtigt. In der Vertriebsgruppe Hohlmaier besitzt er die Berechtigungen für Aquamarin und Alu. Nach Implementierung der Dimensionssicherheit mit aktivierten sichtbaren Gesamtwerten liefert der eingangs gezeigte DeltaMaster-Bericht folgende Werte (je nach Verschachtelung).
Abbildung 13: Zwei verschachtelte Dimensionen mit Nutzerbeschränkung (vgl. Abbildung 1)
Abbildung 14: Zwei verschachtelte Dimensionen mit Nutzerbeschränkung
Für den betroffenen Nutzer enthält der Bericht nur die Kennzahlen, die mit den Dimensionselementen, für die er auch die Berechtigung besitzt, verbunden sind. Alle anderen Zeilen werden dabei leer dargestellt. Um den Effekt zu veranschaulichen, wurde in DeltaMaster die Option Leere Zeilen ausblenden nicht aktiviert.
Nachteile dieser Methode
Da die Liste der für ein Kombinationselement berechtigten User in eine Spalte geschrieben wird, sind hier bereits Grenzen vorprogrammiert. Dadurch bedingt sind lange Nutzernamen oder viele verschiedene Nutzer mit Berechtigungen ungünstig. Als Alternative für Systeme mit vielen Nutzern könnten diese als eigenständige Dimension modelliert werden und die Rechte in einer eigens dafür zu bauenden MeasureGroup an die Verbindung von Nutzern und den Elementen unserer Kombinationsdimension gehängt werden. Dieses Konzept ist im Blog Rechteverwaltung in SSAS – Teil 2 näher beschrieben. Soll anhand zweier Dimensionen berechtigt werden, welche beide sehr viele Elemente enthalten, kann die Pflege der Nutzerberechtigungen leicht unübersichtlich werden.
Weiterführende Dokumente
Neben den bereits im Text erwähnten Dokumenten beschäftigt sich auch der Blogbeitrag Rechteverwaltung in SSAS – Teil 1 sehr anschaulich mit der Dimensionssicherheit im Allgemeinen.
