Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everybody...
Im not sure i quite get this topic fully..
Lets take an example:
1. I want my ADMIN account to see all - Thats easy enough.
2. I want one of my shops only to see data about it self - How do i do that?
3. What defines where my "security is" Is it my tablename, or listbox name, or my "selcted field to make the listbox" (I have used the last one as you can see)
Example:
STAR is ALL;
Section Access;
LOAD * INLINE [
ACCESS, USERID, PASSWORD, USERKEY, SHOPNAME
ADMIN, ADMIN, ADMIN, ALL, ALL
USER, Customer1, Customer1, ALL, ALDI
USER, Customer2, Customer2, ALL, COLES
];
USERKEY is my UsersName (I know that key is dumb, but i dont have any names in data yet, so i use my key)
SHOPNAME contains all my shops
So if i want Customer1 to only see ALDI what should i do?
Should i make a secction application looking like this;
Load * Inline [
SHOPNAME,
ALDI,
COLES
????
Thank you in advanced
You need to create/modify three of the tables you Load in QlikView
See also attachments.
HIC
First, I strongly suggest you use NTNAME instead of USERID and PASSWORD. NTNAME is mapped against Windows users and groups, or against the authenticated user if you use ticketing on a web server. So Section Access then becomes a tool for authorization only.
Secondly, you should use one single reducing field, in your case SHOPNAME. This field should exists in both Section Access and in the data. But you need to map the 'ALL' symbol to all shops. This you can do using generic keys. See more on
http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/02/complex-authorization
HIC
Surely it can be done a lot easier than that - That looks complicated and to much backend development in frontend tool.
I have Keys from all dimensions in my fact.
But basically i could just use an conditional on every sheet (i have to make it twice though then) but that is a easy solution..
But i simply cannot believe that u just cannot say ShopName = Aldi - and then it only shows aldi???
Theres gotta be a way
btw.
My SHOPNAME in Section Application is linked to my DimShop - where i have a key called ShopKey which is linked to my fact. So it should work.......
But this is easy!
You write "But i simply cannot believe that u just cannot say ShopName = Aldi - and then it only shows aldi???". That is exactly what you should do. The twist is to make the admin see everything. For that, you need the generic keys and a bridge table that looks like:
Now you link %SHOPNAME to the authorization table in Section Access and SHOPNAME to the data. And you're done.
The blog post describes a situation with two reducing fields. And this thread describes a case with only one reducing field, so this is less complex.
HIC
But the admin is always able to see ALL ??
I did like this:
Star is ALL;
Section Access;
LOAD * INLINE [
ACCESS, USERID, PASSWORD
ADMIN, ADMIN, ADMIN
USER, ALDI, ALDI
USER, COLES, COLES
];
Section Application;
LOAD * INLINE [
USERID, ShopName
ALDI, Aldi
COLES, ALL
];
This allows only Aldi to see Aldi related data, and shows only Coles related data..So i dont see the point in using your idea ?? Unless yes u have more date you need to reduct, then i can see the point in having it more easy to edit and overview over which tables(data) you have given access to see
Furthermore, i am not sure i totally understand you.
If I have these access account for an example:
Star is ALL;
Section Access;
LOAD * INLINE [
ACCESS, USERID, PASSWORD
ADMIN, ADMIN, ADMIN
USER, ALDI, ALDI
USER, COLES, COLES
];
Section Application;
Then i have some questions for you:
1. Should i then make the Section Access as a table ? For instance call it AccessDimension
If i want a User, for instance ALDI, to only see ALDI in my DimSHOP and a certain AreaName in DimProduct
Attributes: ShopName (DimShop) and AreaName (DimProduct) (It could be householding goods)
What do i do then??
Should i make table in excel for instance which contrains
%ShopName, ShopName , %AreaName, AreaName
Or should i make a table for both?
And where should i link %ShopName and %AreaName too? Cause you want ShopName and AreaName to link to my dimensions DimShop and DimProduct right??
Or could i just do like this:
Star is ALL;
Section Access;
LOAD * INLINE [
ACCESS, USERID, PASSWORD
ADMIN, ADMIN, ADMIN
USER, ALDI, ALDI
USER, COLES, COLES
];
Section Application;
DimAccess:
LOAD * INLINE [
USERID, ShopName, AreaName
ADMIN, ALL, ALL
ALDI, Aldi, Kioskvarer
COLES, ALL, Husholdning
];
But i think it will be to complicated or cause to many problems???
Hope you understand, and im sorry that i dont totally understand, im pretty new at qlikview
That was many questions...
First, don't use the "Star is" statement. It doesn't do what you want. It will expand the star symbol to all values in that table only. But you may have shops that are not listed in the Section Access table. So, my suggested solution contains a symbol 'ALL' that I expand myself.
But to expand that, you need the bridge table that I mentioned earlier.
"Should i then make the Section Access as a table"
It is a table, but hidden. If you mean if you should label it "AccessDimension", then the answer is that it doesn't matter. But I think it is good practice to label all tables. It is easier to read...
"If i want a User, for instance ALDI, to only see ALDI in my DimSHOP and a certain AreaName in DimProduct"
Ahh, this is when it becomes interesting. If you have several reducing fields (fields that link Section Access with the data) then I strongly suggest you do what I suggest in my blog http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/02/complex-authorization.
If you don't, you will encounter cases where you think QlikView doesn't work the way it should. Example:
This table you would interpret as "A can see the data if (Shop=X and Product=ANY) or (Shop=ANY and Product=Y)". But QlikView interprets it the other way around: "A can see data if (Shop=X or Shop=ANY) and (Product=ANY or Product=Y)". So it won't work. A will be able to see everything.
Instead you should have a concatenated field %AUTHORIZATIONKEY that is a combination of the other two.
Bottom line: If you want to avoid problems, you should only have one reducing field.
Read more on http://community.qlik.com/docs/DOC-3451
HIC
Thank you, i think i understand, but could you maybe give an example with my data??
Is it like this?
If i have ShopName and AreaName from DimShop and DimProduct and my FactTable called FactAll has ShopKey and ProductKey
First i create a new AuthTable with the values i want to reduce on, so it would kinda like this
SourceAuthTable:
LOAD
USERID,
ShopName,
AreaName
FROM ????? Cause ShopName is in my DimShop and AreaNAme in DimProduct and USERID is a hidden table?? or should i resident or concatenate? Or is it the applymap as you describe in your link??
And then i do like this
AuthTable:
LOAD
USERID,
ShopName &'|'& AreaName as %AuthID
And then i make a bridge table which is kinda like this:
AuthBridgeTable:
Load ShopName&'|'& AreaNAme as AuthID, ShopNAme&'|'& AreaNameas %AuthID From ????;
Load ShopName&'|'& AreaNAme as AuthID, ShopName&'|'&'<ANY>' as %AuthID From ????;
Load ShopName&'|'& AreaName as AuthID, '<ANY>'&'|'& AreaName as %AuthID From ????? ;
Load ShopName&'|'& AreaName as AuthID, '<ANY>'&'|'&'<ANY>' as %AuthID From ?????;
or am i totally wrong?
You need to create/modify three of the tables you Load in QlikView
See also attachments.
HIC
Wow amazing with so much help, that is just awesome - Thank you very much!!!
Can you confirm this then?
Section Access;
Load
ACCESS | USERID | Upper(ShopName &'|'& AreaName)as%AUTHID | |
USER | ADAM | A|ANY | |
USER | BETTY | ANY|Y | |
ADMIN | XERXES | ANY|ANY |
];
Section Application;
Data:
Load
ShopName,
AreaNAme,
ShopName &'|'& AreaName as AuthID
APPLYMAP blabla (havent used applymap before, but i can investigate in it)
AuthBridgeTable:
Load ShopName&'|'& AreaName as AuthID, ShopName&'|'& AreaName as %AUTHID Resident Data;
Load ShopName&'|'& AreaName as AuthID, ShopName&'|'&'<ANY>' as %AUTHID Resident Data;
Load ShopName&'|'& AreaName as AuthID, '<ANY>'&'|'& AreaName as %AUTHID Resident Data;
Load ShopName&'|'& AreaName as AuthID, '<ANY>'&'|'&'<ANY>' as %AUTHID Resident Data;
Or do i also need to use map in Section Access, or even create a brand new table with userid, access and stuff in, like you have done in excel in the authoraztion sheet? Cause i dont have want data form excel, and it is from 2 different tables
Iguess ANY is = ALL?