XMLA | 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.

CSoPro BI Blog

Schlagwort-Archive: XMLA

Automatisierte Anlage von Rollen in Analysis Services

Aufgabenstellung

Umsetzung mittels SQL

Anpassungen

Meine Erfahrungen in der Business Intelligence Welt