In association with the nice people at SQLRelay I’ll be presenting an hour-long webinar on advanced SSAS Multidimensional tips and tricks this Thursday July 9th 2015 at 1pm UK time (that’s 8am EDT for you Americans). It’s free to attend and open to anyone, anywhere in the world. You can join the meeting by going to
In the webinar I’ll be covering topics such as:
- The difference between Allowed Sets and Denied Sets in dimension security
- Handling security-related errors in your MDX calculations
- The different ways of implementing dynamic security
- Why you should avoid cell security, and how (in some cases) you can replace it with dimension security
…and lots more.
If you’re in the UK, you should definitely check out SQLRelay, an annual series of one-day SQL Server events that happens at a number of different places around the country each autumn. For more details, see http://www.sqlrelay.co.uk/2015.html
I’m presenting this webinar in my capacity as a sponsor of SQLRelay, so expect me to spend a small amount of time promoting Technitrain’s autum course schedule. There are some cool courses on SSIS, MDX, SQL Server high availability and data science/machine learning coming up, you know…
UPDATE: you can download the slides and demos from the webinar at http://1drv.ms/1LYk1k8 and watch the recording at https://www.youtube.com/watch?v=cB9F6IVo7MA
For whoever was asking about using a measure group to store permissions for dynamic security, this blog post has all the details: http://bifuture.blogspot.co.uk/2011/09/ssas-setup-dynamic-security-in-analysis.html
Thanks, it was great!
I have doubt regarding implementing diemnsional security in cube.
We have different user roles like CEO,CFO,CCO,TAP,CH,a parent child dimension dimuser is build to for implementing security, problem is with relationship between TAP and CH level users which is many to many.
ie one CH can can work under different TAP.
Fact table contains insurance matters created and its amount ie dimension keys of dimTPA,dimCH then measures .
CH can create matter under different TPA, when one TAP logs in he should only see the figures of CH work under him and matters created for him.
could you please help to find correct modeling and soultion for this issue.
Can you explain why normal dimension security won’t work here?
Thanks for your reply, below you can see my sample model.
CHID | Sales Amount
1 300 —-
3 200
3 600
2 800
1 900 ——
This got relation with below CH Dimension using CHID
CHID | CHName
1 MAT–
2 John
3 sebin
Bridge table is connect to above CH Dimension using CHID
UserSurrogateKey | CHID
2(TOM) 1(MAT)
3(Hary) 1(MAT)
7(user MAT under TOM) 1 (MAT)
4 (use MAT under Hary) 1 (MAT)
DimUser is used to store user relationships which got parent child relationship, it is connected to bridge table using UserSurrogateKey.
UserSurrogateKey | Name | SourceSystem | ParentID | Role
1 Allen Ver NULL CEO
2 TOM Ver 1 TPA–
3 Hary Ver 1 TPA
4 MAT Ver 3 CH–
5 John Ver 3 CH
6 sebin Ver 2 CH
7 MAT Ver 2 CH–
With the above design when Hary logs in he can see sales amount of MAT under him and under TOM.
TOM logs in he can see sales figurs of MAT created under him and under Hary which is the problem.
Since our join is only using CHID : FACT Table(CHID)–>CH Dimension(CHID) –> Bridge (CHID).
Actually we Want a combination of both TPAId + CHID to uniquely identify the records but how to bring the concept in to modelling and apply dimension security.
Sorry, I still don’t understand what the problem is here – the example isn’t very clear. Whose figures do you want Hary and Tom to be able to see? Are you using a SSAS many-to-many relationship here?
When Tom logs in he should only see the Sales amount 300,when Hary comes in he should see 900.
when MAT logs in he should see both these amounts since he is the one who done the sales. yes i am using ssas many to many relationship.
It’s hard to say, but I think you need to remove your many-to-many relationship, and instead of using CHID on your fact table use UserSurrogateKey instead so you can join Dim User to it direct?
Hi Chris, thank you for the interesting presentation and for sharing the code (and the slides).
The tips are really useful. Enjoy the summer! 🙂
Cheers, Vlad.
Thanks Chris It worked.
Below mdx statement is used for role based security.
Generate
(
Filter
(
[DimClaimUser].[User Surrogate Key].[User Surrogate Key].MEMBERS
,
[DimClaimUser].[User Surrogate Key].CurrentMember.Name=Str(“” + CustomData() + “”)
)
,{
LinkMember
(
[DimClaimUser].[User Surrogate Key].CurrentMember
,[DimClaimUser].[Parent ID]
)
}
)
SharePoint(Forms authentication) and excel services are used for reporting, because of this reason i used customdata so that we can send logged in username as customdata value in connection string, but we couldn’t find a solution to dynamically changing the connection string in SharePoint for excel services.
Is it possible to dynamically change the custom data value in SharePoint or is it a wrong approach to solve security.
To be honest, I don’t know if it is possible to dynamically change a connection string in SharePoint, sorry. If you can do it, it would be a good approach.