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

19. ledna 2016

Power BI – Doplněk do Excelu

Kdo už jste přišli s Power BI do styku (Ti co ne si asi říkáte co to je), možná si říkáte o kterém doplňku bude řeč. PowerQuery, PowerPivot, PowerView, PowerMap... Ani jedno. Jedná se o separátní doplněk PowerBI služby do Excelu. Zatím toho moc neumí, ale to před rokem neuměl ani PowerBI designer.
Takže jedna taková rychlovka pro dnešní večer s výhledem pro večery další :)
V prvé řadě:
Kde to stáhnete?
To proto, abyste mohli hned zkoušet.
V druhé řadě:
Co to umí?
Po instalaci přibyde na pásu karet záložka PowerBI s následujícími možnostmi
Pin
Nejedná se o PIN pro výběr z bankomatu, ale o přišpendlení Excelového obsahu do PowerBI dashboardu. Můžete si zvolit, zda chcete dashboard nový, nebo stávající
Pin Manager
Po přišpendlení se nabízí otázka, když se mi změní data v Excelu, promítne se to do PowerBI služby, kde to zatím vypadá po přišpendlení tabulky a grafu takto?
Odpověď zní, zatím ne.
A k tomu je právě druhá ikonka Pin Manager. Zde můžete objekty z Excelu manuálně aktualizovat, nebo případně smazat provázanost PowerBI službou.
Open PowerBI – ikona pro lenochy, která otevře prohlížeč s webem PowerBI.
Profile – řekne Vám kdo jste a umožní Vám být někým jiným (aspoň co se týče identity v ráci PowerBI)
Update – ikona je zatím šedá a tudíž nefunkční
About – Opravdu na tohle někdo kliká? To je jako zůstat v kině po filmu na titulky.
Závěr

Srdce Excelového uživatele zaplesalo, dočkali jsme se nějaké integrace mezi Power BI službou a Excelem co se týká sdílení obsahu. Dá se očekávat, že se v budoucnu dočkáme lepší integrace s Excelem.

16. ledna 2016

Jak funguje zabezpečení dat v SSAS

Cca dva roky zpátky jsem měl na TechEdu v Praze přednášku na téma „Dynamické zabezpečení“. Je to také téma, které mě opět čeká v práci na aktuálním projektu. O co se jedná? V podstatě o nastavení individuálních práv jednotlivým uživatelům za použití MDX výrazů. K onomu zabezpečení dynamickému se dostanu v dalším článku, nejprve jako předvoj projdu zabezpečení základní, abychom si ukázali jak to funguje, ale také limity. Budu se opět soustředit na multidimenzionální modely analysis services, protože multidimenzionální nasazení v Česku a na Slovensku jsou stále v drtivé většině.
Zabezpečení Analysis Services
Předtím, než se pustíme do zabezpečení dynamického, chtělo by to projít, jak funguje kalsické zabezpečení SSAS. Proti SSAS se můžeme ověřovat pomocí Windows autentizace. Buď pomocí doménových Windows účtů, nebo pomocí Windows účtů lokálních. SSAS rozeznávají 2 typy rolí. Role serverová je pouze jedna, nastavuje se na instanci a sem spadají všichni správci instance (ekvivalent sysadmina z DB enginu). Mohou všechno ve všech databázích na dané instanci, stejně jako měnit nastavení instance. Dále můžeme tvořit databázové role uživatelské svázané s jednou konkrétní databází. Role se dají tvořt buď ve Visual studiu, nebo přes Management studio. Pokud budete přidávat členy role přes management studio a následně budete chtít databázi aktualizovat z Visual Studia, dejte si pozor, ať si nastavení rolí nepřepíšete. Visual studio se neptá a přelívá. Použijte místo toho deployment wizard pro SSAS.
Designer role
Projďeme si jednotlivé záložky. Budu popisovat jen to co je většinou potřeba
General – týká se databáze. Full control = admin dané databáze (nikoliv celé instance). Process database – uživatel většinou nepotřebuje, ale hodí se servisnímu účtu, pod kterým procesujeme kostku. View definition – uživatel, který prochází kostky nepotřebuje, vývojář, který chce psát MDX dotazy ano. Jinak neuvidí databázi v Management studiu.
Membership – sem patří členové role. Většinou se práva nastavují na úrovni AD skupin.
Data sources – většinou není potřeba
Cubes – vždycky je potřeba. Zde definujete přístupy k jednotlivým kostkám ve stejné databázi. Access se dá zvolit none/read/read-write. Read-write se týká funkce Writeback (samo o sobě nestačí, je toho potřeba nastavit poněkud více).
Local cube/drill through access – lokální kostka znamená, že můžete data z SSAS stáhnout lokálně do souboru .cub a lokální kostku procházet bez konektivity na zdroj (přežité, použijte raději PowerPivot, který je v podstatě taky lokální kostkou a umí toho nesrovnatelně více). Drill through se naopak hodí. Možná znáte dvojklik na buňku v Excelu při procházení přes kontingenční tabulku. Detaily se vysypou na nový list a to je přesně ono. Drill through. SSAS také podporují detailnější Drillthrough akci (je možno vyjmenovat, které dimenze, measures atd použít. Ve výchozím chování je jak lokální kostka, tak drill through zakázáno a můžete je jednotlivě povolit.
Cell data – zabezpečení se dá řešit na úrovni jednotlivých buněk pomocí MDX výrazu. Když mají ale analysis services testovat práva pro individuální buňky je to problém, protože buněk je hodně. Jedná se o spolehlivého zabijáka výkonu, kterého navíc zpravidla nepotřebujete (jde to jinak, čtěte dál). Na záložce cell data můžete s výhodou použít funkci test cube security. Testováni co vidí členové role i když jste admin bez nutnosti logovat se pod jiným uživatelem.
Dimensions – týká se sdílených dimenzí mezi různými kostkami, zde nastavíte akorát read, read/write na celé dimenzi. Writeback dimenzí jsem zatím nepotřeboval, stejně chybí nějaká normální klientská aplikace (Excel 2010 a vyšší umí pouze writeback faktů, nikoliv dimenzí)
Poslední záložka Mining structures se týká datamingu. Nás bude nejvíce zajímat záložka předposlední
Dimension data
Zde se děje veškerá magie s řízením přístupu kdo co uvidí/neuvidí. Dialog si můžete prohlédnout na obrázku
Rozbalovací menu dimensions umožňuje vybrat, zda chcete práva omezit na úrovni dimenzí sdílených a ovlivnit všechny kostky, nebo zda chcete v konkrétní kostce omezit měřítka, které daný uživatel uvidí, případně jestli chcete omezit dimenzionální záznamy v dané kostce.
Pokud bych v MDX Tutorial kostce zvolil dim Product, mám dvě možnosti jak postupovat. Buď můžu explicitně zakazovat položky, které nemá uživatel vidět, nebo vyberu deselect all members a naopak explicitně zatrhávám jen položky, které daný uživatel má vidět. Na pozadí se generuje buď denied member set, nebo allowed member set (zobrazíte na záložce advanced).
Vyberu, že uživatelé mají vidět pouze kategorii bikes
Přepínám na záložku Advanced a vidím následující obrazovku
Generoval se mi allowed member set (z povolených/zakázaných setů bude vycházet dynamické zabezpečení). Kromě toho můžu nastavit default membera. Může být užitečné například v situaci, že firma působí jak v Česku, tak na Slovensku. Češi můžou vidět výkony Slováků a obráceně. Výchozí řez ale uvidí pouze pro svůj region.
Také vidíme nenápadné, byť důležité zatržítko: “enable visual totals” Které znamená, sčítám jen to na co mám práva do celkové sumy. Pokud jsme explicitně zatrhli, že uživatel uvidí jen “Bikes”, celkovou sumu vidí za celou firmu. Pokud mu tedy nezatrhneme enable visual totals, což by viděl celkový souhrn jen za “Bikes”. Hodí se občas oba přístupy. Někdy kvůli výpočtu % podílu prodejů daného prodejce umožníme vidět i celkové příjmy, nebo naopak dost často chceme, aby každý viděl jen tu část, za kterou je odpovědný i v celkové sumě.
Omezení
Nevím o žádném omezení fyzickém (počet rolí), když ale chcete řešit granulárně práva kdo má k čemu přístup. Dojdete dříve či později do bodu zlomu. Vyklikávání povolených/zakázaných setů je udržitelné jen do určité míry. Pokud bychom chtěli rozsekat Českou republiku geograficky a omezit práva na úrovni krajů. Tímto přístupem bychom museli vytvořit 14 rolí (jedna pro každý kraj pokud dobře počítám). Vyklikávat 14 rolí je už pro mne na hranici udržitelnosti/přehlednosti. Nebylo by jednodušší mít roli pouze jednu a konfiguraci brát z databázové tabulky podle toho kdo je přihlášen? Za mě ano a to je právě ten případ, kdy sáhneme po dynamickém zabezpečení a budeme práva nastavovat MDX výrazem. To je téma pro článek někdy v snad blízké budoucnosti :)
Závěr

V dnešním článku jsme shrnuli jak funguje nastavení oprávnění v analytických službách SQL Serveru. Došli jsme až na hranici udržitelnosti při potřebě definovat práva hodně granulárně. Připravili jsme si půdu pro článek jak nastavit a řídit práva pomocí MDX výrazů.