June 21, 2018 at 4:46 am
Hello there,
This is my first post, however i have been a reader of the forums for a while! I've seen various posts that are similar however they don't seem to cover all my requirements, however apologies if there is one that i have missed!
We have an SSAS cube that users connect to via Excel, we have a Customers dimension and a Depot's dimension. Customers are members of specific depots. So for example:
Customers
Customer Account / Depot
CUS001 - Depot 1
CUS002 - Depot 3
CUS004 - Depot 2
Depots
Depot / Depot Name
Depot 1 / England
Depot 2 / America
Depot 3 / France
We have a requirement to stop users seeing details for customers that don't belong to their Depot (set up using AD Security Groups). I've managed to get it set up so that when filtering users cannot select anything other than the Depot that we specify, however there is still the option for "All". So for example if a user is displaying a list of Customer Accounts in the Rows, and they have the filter either not set, or set to All - it does show a list of all customer accounts regardless of depot. There is also a 'Depot Name' value - which if selected alongside customer accounts does only show values for the specified depot, and is blank for the customers that aren't in that depot.
What we would like, is for the cube to not show Customer details that belong to any other depot other than the one we specify in the Dimension Data for the role. Is this achievable? (We cannot use perspectives as our edition of SQL doesn't support it apparently)
Many thanks
Callum
June 21, 2018 at 9:09 am
Hi Callum,
Let's start with some base information...could you please let us know which version/edition of SSAS you are using, as well as whether it is Multidimensional or Tabular. Also post the details of at least one of the roles (filter code).
June 22, 2018 at 1:58 am
Hi Martin,
Thanks for your reply. We are using SQL Server 2016 Service Pack 2, with SSAS in Multidimensional mode.
We have attribute security defined on both the Database and Cube Dimensions for 'Company', 'Customers' and 'Depot Actual'. Company is set to 'Deselect all members', as this is being picked up by another AD Group.
In the 'Customers' dimension we have attribute security set on 'Depot Customer' and 'Depot Name', which are both set to 'Select all members', and the Depots that are not relevant are not selected, here is the MDX code for the 'Denied Member Set':
{[Customers].[Depot Name Customer].&[10]&[C10 - 3], [Customers].[Depot Name Customer].&[10]&[C10 - 9], [Customers].[Depot Name Customer].&[10]&[C10 - 8], [Customers].[Depot Name Customer].&[10]&[C10 - 7], [Customers].[Depot Name Customer].&[2]&[C2 - 1], [Customers].[Depot Name Customer].&[2]&[C2 - 5], [Customers].[Depot Name Customer].&[10]&[C10 - 6], [Customers].[Depot Name Customer].&[10]&[C10 - 5], [Customers].[Depot Name Customer].&[10]&[C10 - 10], [Customers].[Depot Name Customer].&[10]&[C10 - 4], [Customers].[Depot Name Customer].&[10]&[C10 - 2]}
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply