Analysis Services | CSoPro BI Blog

Aufgabenstellung

In einem aktuellen Projekt hatten wir die Aufgabe, in mehreren Cubes insgesamt ca. 250 Rollen anzulegen bzw. zu aktualisieren.
Ausgangspunkt war eine interne Umstrukturierung der Vertriebsstruktur, was dann in Rollen abgebildet werden musste.

[Nebenbemerkung: Natürlich gibt es andere Optionen wie z.B. dynamische Rechtevergabe mittels USERNAME() oder CUSTOMDATA(). In diesem Projekt war aber eine hart kodierte Lösung sinnvoll, da die Rechtevergabe nicht dynamisch erfolgen soll und die Benutzer eh zu AD-Gruppen zugeordnet sind.]

Somit war die Anforderung, ca. 250 Sätze mit folgenden Informationen automatisch zu Rollen umzusetzen:

Name des Cubes
Name der Rolle
Beschreibung
Filtereinstellungen für bestimmte Kunden-Attribute (Hub, Market, Sales Country) und ggf. Element-Attribute
Zuordnung einer oder mehrerer AD-Gruppen zu dieser Rolle

Deswegen haben wir zunächst eine Tabelle angelegt, in der wir diese Metadaten speichern konnten:

Das CREATE-Statement sieht so aus:

CREATE TABLE [Config].[CubeRollen](
	[Cube] [nvarchar](100) NOT NULL,
	[CubeRolle] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](1000) NULL,
	[Filter_SalesCountry] [nvarchar](3) NULL,
	[Filter_HubId] [int] NULL,
	[Filter_RegionId] [int] NULL,
	[Filter_ElementNo] [int] NULL,
	[Bemerkung] [nvarchar](1000) NULL,
	[ADGruppe] [nvarchar](100) NULL,
	[AdminRights] [tinyint] NOT NULL,
	[ProcessPermission] [tinyint] NOT NULL,
	[ProcessAndReadPermission] [tinyint] NOT NULL,
 CONSTRAINT [PK_Config_CubeRollen] PRIMARY KEY CLUSTERED 
(
	[Cube] ASC,
	[CubeRolle] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [Config].[CubeRollen] ADD  DEFAULT ((0)) FOR [AdminRights]
GO

ALTER TABLE [Config].[CubeRollen] ADD  DEFAULT ((0)) FOR [ProcessPermission]
GO

ALTER TABLE [Config].[CubeRollen] ADD  DEFAULT ((0)) FOR [ProcessAndReadPermission]
GO

Jetzt betrachten wir mal, wie wir die Rollen automatisch anlegen wollen. Dazu gibt es JSON-Befehler (früher XMLA), die der Analysis Services (ab SQL 2016) ausführt. Für die 4.Zeile des obigen Screen Shots sieht der Befehl so aus: (wenn man die Syntax nicht kennt, kann man sich so einen Befehl über die Skript-Funktionalität im SQL Server Management Studio erstellen lassen)

{
  "createOrReplace": {
    "object": {
      "database": "AFD Order Entry Reporting",
      "role": "Andorra"
    },
    "role": {
      "name": "Andorra",
      "description": "Role for Andorra",
      "modelPermission": "read",
      "members": [ { "memberName": "DOMÄNE\\BI_Andorra" } ],
      "tablePermissions": [
        {
          "name": "Customer",
          "filterExpression": "Customer[Hub ID]=\"20\" && Customer[Sales country Id]=\"AD\""
        }
      ]
    }
  }
}

Wir sehen, dass die Bedingung [Filter_HubId]=20 und [Filter_SalesCountry]=AD sich zu folgendem Block übersetzt:

"filterExpression": "Customer[Hub ID]=\"20\" && Customer[Sales country Id]=\"AD\""

Im übrigen kann man auch mehrere solche Rollen-Anlage-Skripte verknüpfen – mit:

{
  "sequence": {
    "operations": [
      {
        "createOrReplace": { ... }
      },
      {
        "createOrReplace": { ... }
      },
      {
        "createOrReplace": { ... }
      }
    ]
  }
}

Umsetzung mittels SQL

Damit wir diese JSONs erstellen können, brauchen wir noch eine Konfigurationstabelle, in der wir definieren, wie sich die Einträge in den einzelnen [Filter_…]-Spalten in diese Bedingungen übersetzen. Diese Übersetzung kann für jeden Cube unterschiedlich sein. Dazu haben wir folgende Tabelle angelegt:

CREATE TABLE [Config].[CubeFilterDefinition](
	[Cube] [nvarchar](100) NOT NULL,
	[Filter_SalesCountry] [nvarchar](100) NULL,
	[Filter_HubId] [nvarchar](100) NULL,
	[Filter_RegionId] [nvarchar](100) NULL,
	[Filter_ElementNo] [nvarchar](100) NULL,
 CONSTRAINT [PK_Config_CubeFilterDefinition] PRIMARY KEY CLUSTERED 
(
	[Cube] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [Config].[CubeFilterDefinition]
SELECT 'AFD Order Entry Reporting', 
'Customer[Sales country Id]=\"##Wert##\"',
'Customer[Hub ID]=\"##Wert##\"',
'Customer[Region ID]=##Wert##',
NULL
INSERT INTO [Config].[CubeFilterDefinition]
SELECT 'DCH Sales Margin Analysis', 
'Customer[Sales country Id]=\"##Wert##\"',
'Customer[Hub ID]=\"##Wert##\"',
'Customer[RegionID]=##Wert##',
'Element[Element No]=\"##Wert##\"'

Man sieht, dass die Einschränkung aufs Element-Attribut nur beim 2. Cube möglich ist (weil der erste Cube gar nicht diese Dimension hat).

Für eine Verallgemeinerung muss man natürlich die Filter_…-Spalten an die jeweilige Situation anpassen.

Nun bauen wir das SQL-Statement für die Erstellung des JSON schrittweise zusammen. Für jeden Schritt verwende ich eine eigene CommonTableExpression, damit man die Schritte einfach einzeln bauen kann.

with piv as
(
select r.[Cube], r.CubeRolle, 
r.Filter_SalesCountry as FilterWert, f.Filter_SalesCountry as FilterCondition from config.CubeRollen r
inner join config.CubeFilterDefinition f
on r.Cube=f.Cube
UNION 
select r.[Cube], r.CubeRolle, 
convert(nvarchar(10), r.Filter_HubId) as FilterWert, f.Filter_HubID as FilterCondition from config.CubeRollen r
inner join config.CubeFilterDefinition f
on r.Cube=f.Cube
UNION 
select r.[Cube], r.CubeRolle, 
convert(nvarchar(10), r.Filter_RegionId) as FilterWert, f.Filter_RegionID as FilterCondition from config.CubeRollen r
inner join config.CubeFilterDefinition f
on r.Cube=f.Cube
UNION 
select r.[Cube], r.CubeRolle, 
convert(nvarchar(10), r.Filter_ElementNo) as FilterWert, f.Filter_ElementNo as FilterCondition from config.CubeRollen r
inner join config.CubeFilterDefinition f
on r.Cube=f.Cube
)
select * from piv

Dieses SQL liefert die Spalten:

Cube
CubeRolle
FilterWert: der jeweilige Wert, auf den gefiltert werden soll (also in unserem Andorra-Beispiel von oben: 20 bzw. AD
FilterCondition: die jeweilige Definition aus der Meta-Tabelle, wie dieser Filterwert anzuwenden ist (z.B. Customer[Hub ID]=\“##Wert##\“ bzw. Customer[Sales country Id]=\“##Wert##\“)

Im nächsten Schritt ermitteln wir aus der FilterCondition die Tabelle (im Beispiel Customer). Das geht mit der SQL-Funktion charindex zum Suchen des Texts „[„:

,
pivMitFilteredTable as (
select *, left(FilterCondition, charindex('[', FilterCondition, 1)-1) as FilteredTable  from piv
)
select * from pivMitFilteredTable

Im nächsten Schritt gruppieren wir nach FilteredTable und bauen die Bedingung pro Tabelle zusammen (in unserem Beispiel Customer[Hub ID]=\“20\“ && Customer[Sales country Id]=\“AD\“):

,
RollenFilter as (
select [Cube], CubeRolle, FilteredTable , string_agg(convert(nvarchar(max), replace(FilterCondition, '##Wert##', FilterWert)), ' && ') as Filter from pivMitFilteredTable
group by [Cube], CubeRolle, FilteredTable
)
select * from RollenFilter

Nun ergänzen wir diese Tabelle um die Description, die ADGruppe und die modelPermission (hier werden Admin- und processor-Rechte separat vergeben):

,
FDef as (
select rf.*, r.Description, ADGruppe, 
case when AdminRights=1 then N'administrator'
when ProcessPermission=1 then N'refresh'
when ProcessAndReadPermission=1 then N'readRefresh'
else N'read' end as modelPermission from RollenFilter rf
left join Config.CubeRollen r
on rf.[cube] = r.[Cube] and rf.CubeRolle = r.CubeRolle
)
select * from FDef

Jetzt haben wir alles, um die ersten JSON-Fragmente zusammenzubauen.

Außerdem ist es erlaubt, in der Definition in dem Feld ADGruppe mehrere AD-Gruppen zu spezifizieren – indem sie durch , getrennt sind. Zusätzlich wird die Domäne vor die AD-Gruppe geschrieben. Somit entsteht aus

BI_All,BI_DOCH_All

das JSON-Fragment

[
  { "memberName": "DOMÄNE\\BI_All" },
  { "memberName": "DOMÄNE\\BI_DOCH_All" }
]

Dazu verwenden wir die Funktionen STRING_AGG und STRING_SPLIT (die es ab SQL 2017 gibt). Dieser Schritt sieht dann so aus:

, ff as (
select [cube], CubeRolle,
'{
  "createOrReplace": {
    "object": {
      "database": "' + [Cube] + '",
      "role": "' + CubeRolle + '"
    },
    "role": {
      "name": "' + CubeRolle + '",
      "description": "' + isnull(Description, '') + '",
      "modelPermission": "' + modelPermission + '", ' + isnull('
      "members": [' 
+ (select string_agg('{
          "memberName": "' + replace(case when value like '%\%' then value else 'DOMÄNE\'+ value end, '\', '\\') + '"
        }', ',') from ( Select value from string_split(ADGruppe, ',')) as x) + '
      ]', '') as Rumpf_Anfang, '
    }
  }
}' as Rumpf_ende, FilteredTable, Filter, description, modelPermission, ADGruppe
from FDef
)
select * from ff

Als nächstes bauen wir das Filter-JSON-Fragment pro Tabelle erstellt, für obiges Beispiel also

{
  "name": "Customer",
  "filterExpression": "Customer[Hub ID]=\"20\" && Customer[Sales country Id]=\"AD\""
}

Dazu verwenden wir dieses SQL-Fragment:

,f2 as (
select [cube], CubeRolle, Rumpf_Anfang, Rumpf_ende,  '{
          "name": "' + FilteredTable + '",
          "filterExpression": "' + Filter + '"
        }' as  FilterZeile, FilteredTable, Filter from ff
)
select * from f2

Dann müssen wir nur noch mit einem Group By Cube + CubeRolle das JSON pro Rolle bauen und konkatenieren:

,
erg as (
select [cube], CubeRolle, Rumpf_Anfang + 
isnull(
', "tablePermissions": [' + 
string_agg(convert(nvarchar(max), FilterZeile), ', ') 
+ ']', '')
+ Rumpf_ende xmla from f2
group by [cube], CubeRolle, Rumpf_Anfang , Rumpf_ende 
)
select '{
"sequence":
{
"operations": [
' + string_agg(xmla, ',') 
+ ']}}'
from erg

Somit siehr das fertige SQL so aus:

with piv as
(
select r.[Cube], r.CubeRolle, 
r.Filter_SalesCountry as FilterWert, f.Filter_SalesCountry as FilterCondition from config.CubeRollen r
inner join config.CubeFilterDefinition f
on r.Cube=f.Cube
UNION 
select r.[Cube], r.CubeRolle, 
convert(nvarchar(10), r.Filter_HubId) as FilterWert, f.Filter_HubID as FilterCondition from config.CubeRollen r
inner join config.CubeFilterDefinition f
on r.Cube=f.Cube
UNION 
select r.[Cube], r.CubeRolle, 
convert(nvarchar(10), r.Filter_RegionId) as FilterWert, f.Filter_RegionID as FilterCondition from config.CubeRollen r
inner join config.CubeFilterDefinition f
on r.Cube=f.Cube
UNION 
select r.[Cube], r.CubeRolle, 
convert(nvarchar(10), r.Filter_ElementNo) as FilterWert, f.Filter_ElementNo as FilterCondition from config.CubeRollen r
inner join config.CubeFilterDefinition f
on r.Cube=f.Cube
),
pivMitFilteredTable as (
select *, left(FilterCondition, charindex('[', FilterCondition, 1)-1) as FilteredTable  from piv
),
RollenFilter as (
select [Cube], CubeRolle, FilteredTable , string_agg(convert(nvarchar(max), replace(FilterCondition, '##Wert##', FilterWert)), ' && ') as Filter from pivMitFilteredTable
group by [Cube], CubeRolle, FilteredTable
)
,
FDef as (
select rf.*, r.Description, ADGruppe, 
case when AdminRights=1 then N'administrator'
when ProcessPermission=1 then N'refresh'
when ProcessAndReadPermission=1 then N'readRefresh'
else N'read' end as modelPermission from RollenFilter rf
left join Config.CubeRollen r
on rf.[cube] = r.[Cube] and rf.CubeRolle = r.CubeRolle
)
, ff as (
select [cube], CubeRolle,
'{
  "createOrReplace": {
    "object": {
      "database": "' + [Cube] + '",
      "role": "' + CubeRolle + '"
    },
    "role": {
      "name": "' + CubeRolle + '",
      "description": "' + isnull(Description, '') + '",
      "modelPermission": "' + modelPermission + '", ' + isnull('
      "members": [' 
+ (select string_agg('{
          "memberName": "' + replace(case when value like '%\%' then value else 'DOMÄNE\'+ value end, '\', '\\') + '"
        }', ',') from ( Select value from string_split(ADGruppe, ',')) as x) + '
      ]', '') as Rumpf_Anfang, '
    }
  }
}' as Rumpf_ende, FilteredTable, Filter, description, modelPermission, ADGruppe
from FDef
)
,f2 as (
select [cube], CubeRolle, Rumpf_Anfang, Rumpf_ende,  '{
          "name": "' + FilteredTable + '",
          "filterExpression": "' + Filter + '"
        }' as  FilterZeile, FilteredTable, Filter from ff
)
,
erg as (
select [cube], CubeRolle, Rumpf_Anfang + 
isnull(
', "tablePermissions": [' + 
string_agg(convert(nvarchar(max), FilterZeile), ', ') 
+ ']', '')
+ Rumpf_ende xmla from f2
group by [cube], CubeRolle, Rumpf_Anfang , Rumpf_ende 
)
select '{
"sequence":
{
"operations": [
' + string_agg(xmla, ',') 
+ ']}}'
from erg

Anpassungen

Theoretisch könnte man dieses JSON auch automatisch an den Analysis Services schicken (z.B. via ETL). In unserem Projekt haben wir es einfach mit Copy&Paste in das SQL Server Management Studio kopiert und dort ausgeführt.

Für jeden Anwendungsfall muss man natürlich die unterschiedlichen gefilterten Felder spezifizieren. Das bedeutet – wie oben angedeutet – die Anpassung der Datenbank-Struktur. Natürlich könnte man das Datenmodell auch so wählen, dass für unterschiedliche Filter keine Strukturänderung erforderlich wäre. Wir haben uns aber bewusst für die gezeigte Lösung entschieden, weil dadurch die Tabelle auch für Nicht-IT-ler verständlich bleibt und so die Meta-Daten von den Fachbereichen gepflegt werden können.

In einem Projekt, in dem die gesamte Datenhaltung und Datenverarbeitung on premise war (SQL + SSIS), haben wir einen Azure Analysis Services-Cube benutzt.

Für die Verarbeitung dieses Cubes haben wir nach einfachen Wegen gesucht, wie wir die Cube-Verarbeitung von SSIS / SQL Agent aus anstarten könnten.

Im Web findet man ja einiges zu Cube-Verarbeitung von Azure Analysis Services-Cubes (z.B. Process Azure Analysis Services Models with Azure Data Factory v2 oder Automating Azure Analysis Services processing with Azure Functions). Die erste Methode haben wir übrigens in einem anderen Projekt (das Azure Data Factory verwendete) verwendet. Allerdings hat dies den Nachteil, dass die ADF Managed Service Identity als Administrator des Analysis Services eingetragen werden muss – also mehr Rechte braucht als eigentlich nötig.

Aber für unseren Fall wollten wir eine möglichst einfache Lösung innerhalb SSIS.

Zunächst schauen wir uns an, wie wir einen On-Prem-Analysis-Services-Cube verarbeiten würden. Dazu reicht im SSIS eine Execute-SQL-Task mit folgenden Einstellungen:

Connection ist eine OLE DB-Connection, z.B. mit folgendem Connection-String: Data Source=<ServerName>;Initial Catalog=<Cube-Datenbank-Name>;Provider=MSOLAP.7;Integrated Security=SSPI;

Beispiel für eine OLE DB Connection auf den Cube — Connection auf SSAS

Im Feld „SQL-Statement“ trägt man den SSAS-Befehl ein (den man sich z.B. im SQL Server Management Studio skripten lassen kann):

{"refresh": 
{"type": "full",
"objects": [
{"database": "NetzwerkControlling"}
]
}
}

Für eine Verarbeitung eines Azure-Cubes können wir ganz genauso vorgehen. Nur der ConnectionString ist zu ändern:

Als Data Source ist der Azure Analysis Service einzutragen, also irgendwie so: asazure://westeurope.asazure.windows.net/<Server>
Wir müssen einen User angeben, da wir nicht den User verwenden können, unter dem der Prozess on prem läuft. Also sieht unser ConnectionString so aus:
Data Source=asazure://westeurope.asazure.windows.net/<Server>;User ID=<Username>@<Azure Active Directory>;Initial Catalog=<Cube-Datenbank-Name>;Provider=MSOLAP.8;Persist Security Info=False;
Dann müssen wir in dem Attribut Password der Connection das Passwort dieses Users mitgeben.

Natürlich wollen wir das Passwort nicht im plain text irgendwo stehen haben. Deswegen reichen wir das Passwort über einen Paket-Parameter herein, den wir als sensitive (vertraulich) definieren. Dann können wir bei der Konfiguration im Katalog oder im SQL Server Agent das Passwort eintippen und es ist sicher in der SSISDB gespeichert:

Im Paket sieht das so aus:

Paket-Parameter „Passwort“ – als vertraulich definiert

Eigenschaften der SSAS-Connection in SSIS

In diesem Fall habe ich sogar den Connection-String auch noch von außen hereingereicht.

Bei der Konfiguration im SQL Agent stellt man dann das Passwort ein:

Konfiguration des Paket-Aufrufs im SQL Server Agent mit Passwort-Eingabe

Damit können wir mit dem gleichen Code lokale und Azure-Analysis-Services-Cubes verarbeiten – lediglich den Connectionstring mussten wir anpassen und im Azure-Analysis-Services-Fall das Passwort mit angeben.

Dies hat folgende Vorteile:

einheitliche Code-Basis – ein Code für beides
Passwort sicher gespeichert
im Gegensatz zu obiger Azure Data Factory-Variante
- der User benötigt nur process-Rechte auf dem Cube
- Der Aufruf ist automatisch synchron, so dass man Fehler einfach mitbekommt.

CSoPro BI Blog

Schlagwort-Archive: Analysis Services

Automatisierte Anlage von Rollen in Analysis Services

Aufgabenstellung

Umsetzung mittels SQL

Anpassungen

Azure Cube-Verarbeitung von on premise

Meine Erfahrungen in der Business Intelligence Welt