24. ledna 2016

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 :)

3 komentáře:

  1. Když jsem začínal s SSAS, ta jsem se touto cestou snažil u našeho řešení práva nastavit. Nepovedlo se, zkusím nyní. Díky za (konečně český) popis.

    Použil jsem jiné, asi trochu hardcore řešení, které funguje:
    - kolega mi napsal program, který na základě seznamu uživatelů a vazebních tabulek do dimenzí vygeneruje pro každého uživatele záznam v rolích (1 uživatel = 1 role) s potřebným nastavením (nastavení omezení kostek, dimenzí, ...).
    - v rámci denního spouštění ETL se role a nastavení zkontroluje a případně upraví.
    Ano, není to elegantní, nicméně výkon to snad tak moc nebere - beru to tak, že tak jako tak je to databáze.
    Mimochodem se tak dobře kontroluje, co má kdo za práva.

    OdpovědětSmazat
    Odpovědi
    1. Rádo se stalo :) Tak snad se podaří, kdyby jste narazil na problémy, klidně dejte vědět.

      Smazat