Dynamické zabezpečení SSAS s použitím MDX

Minule jsem sepsal jak funguje zabezpečení Multidimenzionálních Analysis Services v základním provedení (http://www.neoral.cz/2016/01/jak-funguje-zabezpeceni-dat-v-ssas.html). Shrnuli jsme si, jak to funguje, co ještě jde vyklikat a co už dost dobře nejde. Dnes to posuneme o kus dál a podíváme se, jak nastavit práva na opravdu velmi granulární úrovni s použitím MDX. Ukážu několik modelových příkladů nad kostkou z MDX Tutorialu (http://www.neoral.cz/2016/01/mdx-tutorail-0-rozcestnik.html), ve kterých chci vypíchnout hlavně principy.

V klasickém zabezpečení definujeme povolený/zakázaný set dimenzionálních členů vyklikáváním. Dynamické zabezpečení vychází z funkce username() a je mu potřeba přizpůsobit design DW, nebo minimálně kostky

Funkce username()

vrátí Windows identitu aktuálně přihlášeného uživatele do Windows .

Jak se to dá využít? To záleží jak vypadá náš DW/naše kostka

Scénář 1 – Každý zaměstnanec uvidí jen své prodeje

Bez ohledu na počet zaměstnanců, vystačím s jednou rolí. O omezení dat se stará vazba mezi dimenzí a faktovou tabukou. V MDX tutorial kostce to vypadá následovoně

Na zaměstnance jsou navázané prodeje obchodníků (reseller sales) a plány. Dimenze zaměstnanců neobsahuje username() ve správném formátu. Tudíž vytvořím v data source view nový pojmenovaný sloupec (new named calculation) EMPLOYEE_LOGIN = @@SERVERNAME + '\' + LastName

Vytvářím zaměstnance Abbas (Je v tabulce Employees) a přidávám ho do role Dynamic. Na záložce dimension data vybírám dimenzi kostky MDX Tutorial -  Employees, přepínám na deselect all members a zaškrtávám NEORAL\abbas

Po přepnutí na záložku Advanced vidím {[Dim Employee].[Login].&[NEORAL\Abbas]}

To je tvar, ke kterému se potřebuji dostat bez ohledu na to, který uživatel je přihlášený.

Výraz v povoleném member setu nahrazuji za

StrToSet("[Dim Employee].[Login].&[" + username() + "]"

Zatrhávám enable visual totals, aby každý zaměstnane viděl i souhrny jen za sebe. Nasazuji. Otvírám excel pod uživatelem Abbas a vidím ve visual studiu

V Excelu

Při přihlášení pod jiným uživatelem bych dostal jiné číslo. Zde se jedná o modelový učebnicový příklad, který se dočtete na SSAS Wiki, nebo v knize MDX Step By Step. Pro praktické použití jsem s tímto ale nikdy nevystačil a musel jsem hledat sám. Scénář byl vždycky složitější.

Scénář 2 – Odpovědnost za konkrétní...

Nejčastěji jsem řešil nastavení práv podle odpovědnosti lidí za konkrétní produktovou skupinu / region / nákladová střediska atd. Princip ukážu na produktové hierarchii. Budu chtít, aby Abbas viděl pouze kategorii Bikes, Jiang (druhý zaměstnanec AdventureWorks) aby viděl kategorie Clothing a Accessories. Pokud by zaměstnanci byli dva, jde to samozřejmně vyklikat, ale pokud by jich bylo 300...

Který zaměstnanec má práva pro přístup k datům ze které produktové kategorie budu řídit konfigurační tabulkou. K tomuto účelu nejraději používám Master Data Services díky jednoduchému uživatelskému rozhranní (o Master Data Services jsem psal zde: http://www.neoral.cz/2015/06/master-data-services-prehled-novinky-v.html). Pro dnešní blog vystačím s obyčejnou tabulkou.

Na databázovém enginu spustím

USE AdventureWorksDW2012;

GO

CREATE TABLE PRAVA

(ID int identity primary key

,[USER_ID] varchar(50) not null

,CATEGORY varchar(50) not null)

GO

INSERT INTO PRAVA

([USER_ID],[CATEGORY])

VALUES

('NEORAL\Abbas','Bikes'),

('NEORAL\Jiang','Accessories'),

('NEORAL\Jiang','Clothing')

;

Čímž naplním tabulku s právy. Do data source view MDX Tutorial projektu přidám onu novu tabulku PRAVA. Přes pravé tlačítko na složku dimenze s kostkovým projektem spustím průvodce vytvořením nové dimenze. Klíč je sloupec ID, dále vytvořím atributové hierarchie ze sloupců USER_ID a CATEGORY. Na závěr úprav přidám novou dimenzi práva do kostky MDX Tutorial. Dimenze práva může zůstat nenavázaná.

A můžeme psát výraz pro omezení práv uživatelů. V prvé řadě si vypíšu seznam, který uživatel má přístup ke kterým kategoriím v konfigurační tabulce a filtruji podle aktuálně přihlášeného uživatele

SELECT

{}

ON COLUMNS,

filter(

{[PRAVA].[USER ID].[USER ID]}*

{[PRAVA].[CATEGORY].[CATEGORY]}

,[PRAVA].[USER ID].currentmember.name = "neoral\jiang"--username()

)

ON ROWS

FROM [MDX Tutorial]

Abych omezil data, musím ale omezit tu správnou hierarchii, která má vazbu na fakta. Tabulka/dimenze práva je nenavázaná. Provedu Crossjoin na dimenzi produkt, atribut category a vyfiltruju jen na související záznamy

SELECT

{}

ON COLUMNS,

filter(

filter(

{[PRAVA].[USER ID].[USER ID]}*

{[PRAVA].[CATEGORY].[CATEGORY]}

,[PRAVA].[USER ID].currentmember.name = "neoral\jiang"--username()

)*

[Dim Product].[Category].[Category]

,[PRAVA].[CATEGORY].currentmember.name=

[Dim Product].[Category].currentmember.name

)

ON ROWS

FROM [MDX Tutorial]

Teď mám crossjoin 3 různých hierarchií, pro omezení práv potřebuji členy hierarchie jedné. Pomocné dimenze PRAVA se zbavím pomocí funkce Extract, která z crossjoinu několika hierarchií vybere jen tu, kterou zadáte jako druhý argument.

SELECT

{}

ON COLUMNS,

extract(

filter(

filter(

{[PRAVA].[USER ID].[USER ID]}*

{[PRAVA].[CATEGORY].[CATEGORY]}

,[PRAVA].[USER ID].currentmember.name = "neoral\jiang"--username()

)*

[Dim Product].[Category].[Category]

,[PRAVA].[CATEGORY].currentmember.name=

[Dim Product].[Category].currentmember.name

)

,[Dim Product].[Category]

)

ON ROWS

FROM [MDX Tutorial]

Takto vypadá pomocný select, teď už se jen zbavím natvrdo zadaného Jinaga, kterého nahradím původně zakomentovanou funkcí username() a výraz z řádků zkopíruju do role

Přihlášen pod Jiangem vidím

Závěr

Dnes jsem Vám ukázal, jak řešit práva pro jednotlivé uživatele za použití MDX díky jednoduché konfigurační tabulce. Tento princip (scénář 2) jsem použil v různých modifikacích. Naposledy tento pátek pro naše řešení směřující do produkce. Pomocí MDX by se taky krásně dala řešit parentchild hierarchie, dnešní článek je už ale poměrně obsáhlý. Tak uvidíme, možná někdy příště. Doufám, že Vám článek pomohl pochopit, na jakých principech dynamické zabezpečení stojí a kdybyste náhodou válčili s tím, jak to napsat... Dejte vědět :)