Deswegen haben wir zun\u00e4chst eine Tabelle angelegt, in der wir diese Metadaten speichern konnten:<\/p>\n\n\n\n Das CREATE-Statement sieht so aus:<\/p>\n\n\n\n Jetzt betrachten wir mal, wie wir die Rollen automatisch anlegen wollen. Dazu gibt es JSON-Befehler (fr\u00fcher XMLA), die der Analysis Services (ab SQL 2016) ausf\u00fchrt. F\u00fcr die 4.Zeile des obigen Screen Shots sieht der Befehl so aus: (wenn man die Syntax nicht kennt, kann man sich so einen Befehl \u00fcber die Skript-Funktionalit\u00e4t im SQL Server Management Studio erstellen lassen)<\/p>\n\n\n\n Wir sehen, dass die Bedingung [Filter_HubId]=20 und [Filter_SalesCountry]=AD sich zu folgendem Block \u00fcbersetzt:<\/p>\n\n\n\n Im \u00fcbrigen kann man auch mehrere solche Rollen-Anlage-Skripte verkn\u00fcpfen – mit:<\/p>\n\n\n\n Damit wir diese JSONs erstellen k\u00f6nnen, brauchen wir noch eine Konfigurationstabelle, in der wir definieren, wie sich die Eintr\u00e4ge in den einzelnen [Filter_…]-Spalten in diese Bedingungen \u00fcbersetzen. Diese \u00dcbersetzung kann f\u00fcr jeden Cube unterschiedlich sein. Dazu haben wir folgende Tabelle angelegt:<\/p>\n\n\n\n Man sieht, dass die Einschr\u00e4nkung aufs Element-Attribut nur beim 2. Cube m\u00f6glich ist (weil der erste Cube gar nicht diese Dimension hat).<\/p>\n\n\n\n F\u00fcr eine Verallgemeinerung muss man nat\u00fcrlich die Filter_…-Spalten an die jeweilige Situation anpassen.<\/p>\n\n\n\n Nun bauen wir das SQL-Statement f\u00fcr die Erstellung des JSON schrittweise zusammen. F\u00fcr jeden Schritt verwende ich eine eigene CommonTableExpression, damit man die Schritte einfach einzeln bauen kann.<\/p>\n\n\n\n Dieses SQL liefert die Spalten:<\/p>\n\n\n\n Im n\u00e4chsten Schritt ermitteln wir aus der FilterCondition die Tabelle (im Beispiel Customer<\/em>). Das geht mit der SQL-Funktion charindex zum Suchen des Texts „[„:<\/p>\n\n\n\n Im n\u00e4chsten Schritt gruppieren wir nach FilteredTable und bauen die Bedingung pro Tabelle zusammen (in unserem Beispiel Customer[Hub ID]=\\“20\\“ && Customer[Sales country Id]=\\“AD\\“<\/em>):<\/p>\n\n\n\n Nun erg\u00e4nzen wir diese Tabelle um die Description, die ADGruppe und die modelPermission (hier werden Admin- und processor-Rechte separat vergeben):<\/p>\n\n\n\n Jetzt haben wir alles, um die ersten JSON-Fragmente zusammenzubauen. <\/p>\n\n\n\n Au\u00dferdem ist es erlaubt, in der Definition in dem Feld ADGruppe mehrere AD-Gruppen zu spezifizieren – indem sie durch , getrennt sind. Zus\u00e4tzlich wird die Dom\u00e4ne vor die AD-Gruppe geschrieben. Somit entsteht aus <\/p>\n\n\n\n BI_All,BI_DOCH_All<\/p>\n\n\n\n das JSON-Fragment<\/p>\n\n\n\n Dazu verwenden wir die Funktionen STRING_AGG und STRING_SPLIT (die es ab SQL 2017 gibt). Dieser Schritt sieht dann so aus:<\/p>\n\n\n\n Als n\u00e4chstes bauen wir das Filter-JSON-Fragment pro Tabelle erstellt, f\u00fcr obiges Beispiel also<\/p>\n\n\n\n Dazu verwenden wir dieses SQL-Fragment:<\/p>\n\n\n\n Dann m\u00fcssen wir nur noch mit einem Group By Cube + CubeRolle das JSON pro Rolle bauen und konkatenieren:<\/p>\n\n\n\n Somit siehr das fertige SQL so aus:<\/p>\n\n\n\n Theoretisch k\u00f6nnte 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\u00fchrt.<\/p>\n\n\n\n F\u00fcr jeden Anwendungsfall muss man nat\u00fcrlich die unterschiedlichen gefilterten Felder spezifizieren. Das bedeutet – wie oben angedeutet – die Anpassung der Datenbank-Struktur. Nat\u00fcrlich k\u00f6nnte man das Datenmodell auch so w\u00e4hlen, dass f\u00fcr unterschiedliche Filter keine Struktur\u00e4nderung erforderlich w\u00e4re. Wir haben uns aber bewusst f\u00fcr die gezeigte L\u00f6sung entschieden, weil dadurch die Tabelle auch f\u00fcr Nicht-IT-ler verst\u00e4ndlich bleibt und so die Meta-Daten von den Fachbereichen gepflegt werden k\u00f6nnen.<\/p>\n","protected":false},"excerpt":{"rendered":" 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\u00fcrlich gibt es andere Optionen wie z.B. dynamische Rechtevergabe mittels USERNAME() oder CUSTOMDATA(). In diesem Projekt war aber eine hart … Automatisierte Anlage von Rollen in Analysis Services<\/span> weiterlesen ![\"\"](\"https:\/\/www.csopro.de\/biblog\/wp-content\/uploads\/2022\/02\/grafik.png\")
<\/a>CREATE TABLE [Config].[CubeRollen](\n\t[Cube] [nvarchar](100) NOT NULL,\n\t[CubeRolle] [nvarchar](50) NOT NULL,\n\t[Description] [nvarchar](1000) NULL,\n\t[Filter_SalesCountry] [nvarchar](3) NULL,\n\t[Filter_HubId] [int] NULL,\n\t[Filter_RegionId] [int] NULL,\n\t[Filter_ElementNo] [int] NULL,\n\t[Bemerkung] [nvarchar](1000) NULL,\n\t[ADGruppe] [nvarchar](100) NULL,\n\t[AdminRights] [tinyint] NOT NULL,\n\t[ProcessPermission] [tinyint] NOT NULL,\n\t[ProcessAndReadPermission] [tinyint] NOT NULL,\n CONSTRAINT [PK_Config_CubeRollen] PRIMARY KEY CLUSTERED \n(\n\t[Cube] ASC,\n\t[CubeRolle] ASC\n)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]\n) ON [PRIMARY]\nGO\n\nALTER TABLE [Config].[CubeRollen] ADD DEFAULT ((0)) FOR [AdminRights]\nGO\n\nALTER TABLE [Config].[CubeRollen] ADD DEFAULT ((0)) FOR [ProcessPermission]\nGO\n\nALTER TABLE [Config].[CubeRollen] ADD DEFAULT ((0)) FOR [ProcessAndReadPermission]\nGO<\/pre>\n\n\n\n
{\n \"createOrReplace\": {\n \"object\": {\n \"database\": \"AFD Order Entry Reporting\",\n \"role\": \"Andorra\"\n },\n \"role\": {\n \"name\": \"Andorra\",\n \"description\": \"Role for Andorra\",\n \"modelPermission\": \"read\",\n \"members\": [ { \"memberName\": \"DOM\u00c4NE\\\\BI_Andorra\" } ],\n \"tablePermissions\": [\n {\n \"name\": \"Customer\",\n \"filterExpression\": \"Customer[Hub ID]=\\\"20\\\" && Customer[Sales country Id]=\\\"AD\\\"\"\n }\n ]\n }\n }\n}<\/pre>\n\n\n\n\"filterExpression\": \"Customer[Hub ID]=\\\"20\\\" && Customer[Sales country Id]=\\\"AD\\\"\"<\/pre>\n\n\n\n
{\n \"sequence\": {\n \"operations\": [\n {\n \"createOrReplace\": { ... }\n },\n {\n \"createOrReplace\": { ... }\n },\n {\n \"createOrReplace\": { ... }\n }\n ]\n }\n}<\/pre>\n\n\n\nUmsetzung mittels SQL<\/h2>\n\n\n\n
CREATE TABLE [Config].[CubeFilterDefinition](\n\t[Cube] [nvarchar](100) NOT NULL,\n\t[Filter_SalesCountry] [nvarchar](100) NULL,\n\t[Filter_HubId] [nvarchar](100) NULL,\n\t[Filter_RegionId] [nvarchar](100) NULL,\n\t[Filter_ElementNo] [nvarchar](100) NULL,\n CONSTRAINT [PK_Config_CubeFilterDefinition] PRIMARY KEY CLUSTERED \n(\n\t[Cube] ASC\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\n) ON [PRIMARY]\nGO\nINSERT INTO [Config].[CubeFilterDefinition]\nSELECT 'AFD Order Entry Reporting', \n'Customer[Sales country Id]=\\\"##Wert##\\\"',\n'Customer[Hub ID]=\\\"##Wert##\\\"',\n'Customer[Region ID]=##Wert##',\nNULL\nINSERT INTO [Config].[CubeFilterDefinition]\nSELECT 'DCH Sales Margin Analysis', \n'Customer[Sales country Id]=\\\"##Wert##\\\"',\n'Customer[Hub ID]=\\\"##Wert##\\\"',\n'Customer[RegionID]=##Wert##',\n'Element[Element No]=\\\"##Wert##\\\"'<\/pre>\n\n\n\n
with piv as\n(\nselect r.[Cube], r.CubeRolle, \nr.Filter_SalesCountry as FilterWert, f.Filter_SalesCountry as FilterCondition from config.CubeRollen r\ninner join config.CubeFilterDefinition f\non r.Cube=f.Cube\nUNION \nselect r.[Cube], r.CubeRolle, \nconvert(nvarchar(10), r.Filter_HubId) as FilterWert, f.Filter_HubID as FilterCondition from config.CubeRollen r\ninner join config.CubeFilterDefinition f\non r.Cube=f.Cube\nUNION \nselect r.[Cube], r.CubeRolle, \nconvert(nvarchar(10), r.Filter_RegionId) as FilterWert, f.Filter_RegionID as FilterCondition from config.CubeRollen r\ninner join config.CubeFilterDefinition f\non r.Cube=f.Cube\nUNION \nselect r.[Cube], r.CubeRolle, \nconvert(nvarchar(10), r.Filter_ElementNo) as FilterWert, f.Filter_ElementNo as FilterCondition from config.CubeRollen r\ninner join config.CubeFilterDefinition f\non r.Cube=f.Cube\n)\nselect * from piv<\/pre>\n\n\n\n
,\npivMitFilteredTable as (\nselect *, left(FilterCondition, charindex('[', FilterCondition, 1)-1) as FilteredTable from piv\n)\nselect * from pivMitFilteredTable<\/pre>\n\n\n\n,\nRollenFilter as (\nselect [Cube], CubeRolle, FilteredTable , string_agg(convert(nvarchar(max), replace(FilterCondition, '##Wert##', FilterWert)), ' && ') as Filter from pivMitFilteredTable\ngroup by [Cube], CubeRolle, FilteredTable\n)\nselect * from RollenFilter<\/pre>\n\n\n\n
,\nFDef as (\nselect rf.*, r.Description, ADGruppe, \ncase when AdminRights=1 then N'administrator'\nwhen ProcessPermission=1 then N'refresh'\nwhen ProcessAndReadPermission=1 then N'readRefresh'\nelse N'read' end as modelPermission from RollenFilter rf\nleft join Config.CubeRollen r\non rf.[cube] = r.[Cube] and rf.CubeRolle = r.CubeRolle\n)\nselect * from FDef<\/pre>\n\n\n\n
[\n { \"memberName\": \"DOM\u00c4NE\\\\BI_All\" },\n { \"memberName\": \"DOM\u00c4NE\\\\BI_DOCH_All\" }\n]<\/pre>\n\n\n\n, ff as (\nselect [cube], CubeRolle,\n'{\n \"createOrReplace\": {\n \"object\": {\n \"database\": \"' + [Cube] + '\",\n \"role\": \"' + CubeRolle + '\"\n },\n \"role\": {\n \"name\": \"' + CubeRolle + '\",\n \"description\": \"' + isnull(Description, '') + '\",\n \"modelPermission\": \"' + modelPermission + '\", ' + isnull('\n \"members\": [' \n+ (select string_agg('{\n \"memberName\": \"' + replace(case when value like '%\\%' then value else 'DOM\u00c4NE\\'+ value end, '\\', '\\\\') + '\"\n }', ',') from ( Select value from string_split(ADGruppe, ',')) as x) + '\n ]', '') as Rumpf_Anfang, '\n }\n }\n}' as Rumpf_ende, FilteredTable, Filter, description, modelPermission, ADGruppe\nfrom FDef\n)\nselect * from ff<\/pre>\n\n\n\n{\n \"name\": \"Customer\",\n \"filterExpression\": \"Customer[Hub ID]=\\\"20\\\" && Customer[Sales country Id]=\\\"AD\\\"\"\n}<\/pre>\n\n\n\n,f2 as (\nselect [cube], CubeRolle, Rumpf_Anfang, Rumpf_ende, '{\n \"name\": \"' + FilteredTable + '\",\n \"filterExpression\": \"' + Filter + '\"\n }' as FilterZeile, FilteredTable, Filter from ff\n)\nselect * from f2<\/pre>\n\n\n\n,\nerg as (\nselect [cube], CubeRolle, Rumpf_Anfang + \nisnull(\n', \"tablePermissions\": [' + \nstring_agg(convert(nvarchar(max), FilterZeile), ', ') \n+ ']', '')\n+ Rumpf_ende xmla from f2\ngroup by [cube], CubeRolle, Rumpf_Anfang , Rumpf_ende \n)\nselect '{\n\"sequence\":\n{\n\"operations\": [\n' + string_agg(xmla, ',') \n+ ']}}'\nfrom erg<\/pre>\n\n\n\nwith piv as\n(\nselect r.[Cube], r.CubeRolle, \nr.Filter_SalesCountry as FilterWert, f.Filter_SalesCountry as FilterCondition from config.CubeRollen r\ninner join config.CubeFilterDefinition f\non r.Cube=f.Cube\nUNION \nselect r.[Cube], r.CubeRolle, \nconvert(nvarchar(10), r.Filter_HubId) as FilterWert, f.Filter_HubID as FilterCondition from config.CubeRollen r\ninner join config.CubeFilterDefinition f\non r.Cube=f.Cube\nUNION \nselect r.[Cube], r.CubeRolle, \nconvert(nvarchar(10), r.Filter_RegionId) as FilterWert, f.Filter_RegionID as FilterCondition from config.CubeRollen r\ninner join config.CubeFilterDefinition f\non r.Cube=f.Cube\nUNION \nselect r.[Cube], r.CubeRolle, \nconvert(nvarchar(10), r.Filter_ElementNo) as FilterWert, f.Filter_ElementNo as FilterCondition from config.CubeRollen r\ninner join config.CubeFilterDefinition f\non r.Cube=f.Cube\n),\npivMitFilteredTable as (\nselect *, left(FilterCondition, charindex('[', FilterCondition, 1)-1) as FilteredTable from piv\n),\nRollenFilter as (\nselect [Cube], CubeRolle, FilteredTable , string_agg(convert(nvarchar(max), replace(FilterCondition, '##Wert##', FilterWert)), ' && ') as Filter from pivMitFilteredTable\ngroup by [Cube], CubeRolle, FilteredTable\n)\n,\nFDef as (\nselect rf.*, r.Description, ADGruppe, \ncase when AdminRights=1 then N'administrator'\nwhen ProcessPermission=1 then N'refresh'\nwhen ProcessAndReadPermission=1 then N'readRefresh'\nelse N'read' end as modelPermission from RollenFilter rf\nleft join Config.CubeRollen r\non rf.[cube] = r.[Cube] and rf.CubeRolle = r.CubeRolle\n)\n, ff as (\nselect [cube], CubeRolle,\n'{\n \"createOrReplace\": {\n \"object\": {\n \"database\": \"' + [Cube] + '\",\n \"role\": \"' + CubeRolle + '\"\n },\n \"role\": {\n \"name\": \"' + CubeRolle + '\",\n \"description\": \"' + isnull(Description, '') + '\",\n \"modelPermission\": \"' + modelPermission + '\", ' + isnull('\n \"members\": [' \n+ (select string_agg('{\n \"memberName\": \"' + replace(case when value like '%\\%' then value else 'DOM\u00c4NE\\'+ value end, '\\', '\\\\') + '\"\n }', ',') from ( Select value from string_split(ADGruppe, ',')) as x) + '\n ]', '') as Rumpf_Anfang, '\n }\n }\n}' as Rumpf_ende, FilteredTable, Filter, description, modelPermission, ADGruppe\nfrom FDef\n)\n,f2 as (\nselect [cube], CubeRolle, Rumpf_Anfang, Rumpf_ende, '{\n \"name\": \"' + FilteredTable + '\",\n \"filterExpression\": \"' + Filter + '\"\n }' as FilterZeile, FilteredTable, Filter from ff\n)\n,\nerg as (\nselect [cube], CubeRolle, Rumpf_Anfang + \nisnull(\n', \"tablePermissions\": [' + \nstring_agg(convert(nvarchar(max), FilterZeile), ', ') \n+ ']', '')\n+ Rumpf_ende xmla from f2\ngroup by [cube], CubeRolle, Rumpf_Anfang , Rumpf_ende \n)\nselect '{\n\"sequence\":\n{\n\"operations\": [\n' + string_agg(xmla, ',') \n+ ']}}'\nfrom erg<\/pre>\n\n\n\nAnpassungen<\/h2>\n\n\n\n