Zero(0) WID: dark horse of DW Concept
Number Zero is nothing, but its yet something without which counting not possible. It does wonders when added to the right side and its eternal.
Co-incidentally its one of dimensional row popularly known as unspecified or 0 ROW_WID. It has its own usage and purpose in designing fact-dimensional models, reports, data representation etc.. But lets explore its usage in defining enterprise level security for reporting.
As Business Intelligence(BI) is evolving as System of Insights, a user centric approach with more focus on self service. As concept is gaining momentum, there is increased demand for Enterprise wide data security. Solution can be implement for row level security (RLS) for many of data insight self service tools like Thought Spot(TS), Tableau, Qlik, OACS etc..
As consultant , one of enterprise wide reporting security requirement:
•Specific user data based on scope
•For admin roles show all data
Technically easiest way to achieve above requirement is through full outer join, but consider scenario when data volume and user base is high. System performance will be highly compromised…..What are other options…lets explore….
Utilizing conditional join is tricky affair as it involves resource intensive operations
Pseudo code:
Select * from FACT F,USER_SEC_D D
where
CASE WHEN <<USER_ROLE>>=‘Admin’ THEN
1 ELSE D.ORDER_NAME END=
CASE WHEN <<USER_ROLE>>=‘Admin’ THEN
1 ELSE F.ORDER_NUMBER END
In practical scenario, consider millions of data in fact table, when any of Admin user login, it create cartesian product between tables and impacts performance
Another approach is to clone complete data of all orders for admin role as additional rows in security table. It will not be efficient design as it will be resource incentive, data duplication and involves manual maintenance. System performance will be impacted.
Best suited and optimized approach , mostly ignored 0 ROW_WID came in handy and solves purpose of showing all data for admin while keeping sort coming of above approach under check
Pseudo Code:
Select * from FACT F,USER_SEC_D D
where
CASE WHEN <<USER_ROLE>>=‘Admin’ THEN
1 ELSE D.ORDER_NAME END=
CASE WHEN <<USER_ROLE>>=‘Admin’ THEN
1 ELSE F.ORDER_NUMBER END
AND
(D.USER_NAME=<< USER_LOGIN>> OR USER_NAME=0)
This will ensure that cartesian product for Admin user happens with only with one row for 0 ROW_WID as well displaying all data from fact table. When normal user login, simple inner join and for admin user its again normal join.
This is how 0 rescued and helped in engineering better performance system… again proving power of zero is eternal
Further similar concept can be implemented at database level utilizing synonyms and logic, thus avoiding need of additional security layer at visualization tool side….concept can be pushed at dB level ….… will cover it later
Thanks you all…
