Dynamic Security to Cube

I have the requiremrnt that when a User loggs in He would be able to see only his and his Ancestores Details(means he ll be able to see the users deatils those are reporting him)

The MDX Query i wrote in Roles--> Dimension Data-->Advanced-->Allowed Memberset is.

FILTER( [DIMUSERS].[DIMUSERS].Members,

'POLARIS\'+ [DIMUSERS].[DIMUSERS].Currentmember.Name=username)

When After execuing the above MDX Its doing hardcode as the login of the Current User

which is not affecting for other Users.

So that Who ever user login to the system everybody getting all the User deatils

This is Urgent plz help me in this regards

Thank U

Check that your users are not setup in the Administrators group, or are not part of a windows group that is in the admin role. This is definitely true for the server admins and possibly also for database admins, but I have not double checked that.|||

Thanks Daren

what you gav solution I did the same still its comming the same

|||

I'm not entirely sure I understand your issue. Are you possibly saying that the members are restricted at the lower level, but the parent levels still have the total for all members? If this is your problem, go into the advanced tab and turn on the "Visual Totals" option. This will make the value for all the parent members only show a total which is the sum of the values to which the user has access.

If this is not your issue maybe an example of what you are seeing and what you would like to see would help.

|||

I want the cube data to be filtered on the basis of the current domain user. Is there any way to get that filter implemented on analysis cube or through MDX query as said above.....please its urgent...

|||You can either do what the first poster was doing and setup a "users" dimension and setup the allowed set in your role to filter using the username function. or you can setup a series of different roles with different allowed sets and allocate users to the different roles - it really depends on your specific security needs as to which is the better approach.

Dynamic Security to Cube

I have the requiremrnt that when a User loggs in He would be able to see only his and his Ancestores Details(means he ll be able to see the users deatils those are reporting him)

The MDX Query i wrote in Roles--> Dimension Data-->Advanced-->Allowed Memberset is.

FILTER( [DIMUSERS].[DIMUSERS].Members,

'POLARIS\'+ [DIMUSERS].[DIMUSERS].Currentmember.Name=username)

When After execuing the above MDX Its doing hardcode as the login of the Current User

which is not affecting for other Users.

So that Who ever user login to the system everybody getting all the User deatils

This is Urgent plz help me in this regards

Thank U

Check that your users are not setup in the Administrators group, or are not part of a windows group that is in the admin role. This is definitely true for the server admins and possibly also for database admins, but I have not double checked that.|||

Thanks Daren

what you gav solution I did the same still its comming the same

|||

I'm not entirely sure I understand your issue. Are you possibly saying that the members are restricted at the lower level, but the parent levels still have the total for all members? If this is your problem, go into the advanced tab and turn on the "Visual Totals" option. This will make the value for all the parent members only show a total which is the sum of the values to which the user has access.

If this is not your issue maybe an example of what you are seeing and what you would like to see would help.

|||

I want the cube data to be filtered on the basis of the current domain user. Is there any way to get that filter implemented on analysis cube or through MDX query as said above.....please its urgent...

|||You can either do what the first poster was doing and setup a "users" dimension and setup the allowed set in your role to filter using the username function. or you can setup a series of different roles with different allowed sets and allocate users to the different roles - it really depends on your specific security needs as to which is the better approach.

Dynamic Security Stored Procedure Repeatedly Called

I have implemented an SSAS stored procedure for dynamic security and I call this stored procedure to obtain the allowed set filter. To my supprise, the stored procedure is being called repeatedly many times (more than 10) upon establishing the user session. Why is this happening?Do you have more than 10 attributes in this dimension ? (i.e. number of calls to sproc should be the same as number of attributes in dimension).|||

Thank you. I don't quite follow. The dimension is wide (it has some 30 attributes). However, only one attribute hierarchy has an allowed set filter defined. Why does this need to be evaluated many times?

In addition, the dimension is large (some 2 mil plus members). Even with allowed set caching inside the stored procedure, it takes an enormous amount of time (some 10 min) for the filter to be applied, e.g. if the filter returns some 40,000 allowed members. Any optimization tips?

|||10 minutes to filter 40,000 members out of 2 million is way too much. I've written sprocs which were able to do comparable filtering in a matter of seconds. Of course, it depends on the logic inside sproc. Would you share more details please - what is the criteria for the filtering ?|||

Thank you for helping out.

As I mentioned, the dimension has some 2 mil members. I has a Security Filter attribute with some 50,000 members on which the allowed set is applied. The allowed set expression is StrToSet(<call to stored procedure here>). The stored procedure returns a comma-seperated list of the allowed members in the format [DimensionName].[Security Filter].&[key].

The stored procedure queries a database to get the set but this is very fast. Then, it caches the set to avoid repetative calls.

|||

> The allowed set expression is StrToSet(<call to stored procedure here>). The stored procedure returns a comma-seperated list of the allowed members in the format [DimensionName].[Security Filter].&[key].

This is not a good practice. Much better approach is to use Server Adomd.NET object model and return AdomdServer.Set from sproc instead of giant string. But since you query database table, I wonder whether you can make this table part of UDM and build joins inside UDM and avoid sproc altogether...

|||

OK, I will test both approaches (StrToSet and server-side Set) and post the results here.

-update

Thanks for the tip. Server Set resulted in much better performance. The first column shows the number of members in the allowed set. Time is in seconds.

Filer #	StrToSet	Set
5000	52	35
10000	90	58
20000	171	108
40000	272	144

end update

Would you mind eleborating more about the table idea? Do you mean a new dimension table that will slice the fact table or do you refer to a many-to-many dimension?

|||

> Would you mind eleborating more about the table idea? Do you mean a new dimension table that will slice the fact table or do you refer to a many-to-many dimension?

Since it seems that the information about which users can see which members is already stored in the table, you could build an aux measure group with User dimension and your dimension included, and then use something like

Exists(MyDimension.MyAttribute.MyAttribute.MEMBERS, StrToMember("[User].[" + Username + "]", "securitymeasuregroup")

as expression for the allowed set.

|||Thank you. But this will result in a very large fact table which will hold the allowed members for each user, corrrect? So, if I have 100 users and each user is allowed to see 100,000 members on average, the table will have 10,000,000 rows. I wonder how both approaches compare from a performance standpoint. Do you have any performance test results to share?|||Before we can compare the approaches - can you explain how the database table used by sproc looks like ? Doesn't it have the same structure (i.e. for user you need to be able to get list of allowed members), and therefore same size ?|||

To make the things simpler, let's assume that that in both cases the security policy will be materialized into the same table (dimension surrogate key, employee surrogate key). In the case of dynamic security, the stored procedure will query this table by user and apply the allowed set but the allowed set will not exceed the maximum number of members that the user can access. In the case of the security measure group, EXISTS can be potentially applied over millions of records. As the securiy table gets larger, I'd expect the benefits of EXISTS to level off. Or, will it?

|||Please note, that the call to Exists is always filtered by the current User, therefore there will never be a full scan of this measure group. I.e. if this measuregroup was implemented as pure ROLAP - it would generate exactly same SQL queries as you are likely doing from your sproc already. And, of course, in case of MOLAP it will be much more efficient. Therefore I beleive that the approach with EXISTS will always outperform approach with sproc no matter what sizes of tables will be.|||Thank you so much for you help, Mosha. I will post to this thread if I find otherwise.|||I think it will be very interesting to everybody to see results of your testing in either scenario. While I have seen approach that I recommended working well in number of installations, your data volumes (100 users each one having access to different set of 100,000 members out of 2 million) are interesting enough to see how well it will perform in the real world. My prediction is that it should perform well, but it is only a feeling without hard data behind it.|||

Got results. The materialized results are impressive! For the sake of testing, I generated all permutations of 250 employees with 52,000 securable items resulting in a fact table with some 13 mil rows. I compared this against the dynamic security approach where 52,000 securable items are converted to a Set. I tested the connect time from Excel and noted the query log events in the Profiler.

Dynamic Security Materialized Security (non-partitioned)

30-35 sec 8-12 sec

Not to mention that the dynamic security test currently excludes the application latency (records are read directly from a table). In real life, it would take additional time for stored procedure/service to prepare the security filter.

Thank you so much for the tip, Mosha! We will go probably with materialized security.

Do you think that partitioning the Security Filter measure group (if there is a way to logically group users and minimize the number of partitions) would decrease the query time even further?

Dynamic Security Stored Procedure Repeatedly Called

I have implemented an SSAS stored procedure for dynamic security and I call this stored procedure to obtain the allowed set filter. To my supprise, the stored procedure is being called repeatedly many times (more than 10) upon establishing the user session. Why is this happening?Do you have more than 10 attributes in this dimension ? (i.e. number of calls to sproc should be the same as number of attributes in dimension).|||

Thank you. I don't quite follow. The dimension is wide (it has some 30 attributes). However, only one attribute hierarchy has an allowed set filter defined. Why does this need to be evaluated many times?

In addition, the dimension is large (some 2 mil plus members). Even with allowed set caching inside the stored procedure, it takes an enormous amount of time (some 10 min) for the filter to be applied, e.g. if the filter returns some 40,000 allowed members. Any optimization tips?

|||10 minutes to filter 40,000 members out of 2 million is way too much. I've written sprocs which were able to do comparable filtering in a matter of seconds. Of course, it depends on the logic inside sproc. Would you share more details please - what is the criteria for the filtering ?|||

Thank you for helping out.

As I mentioned, the dimension has some 2 mil members. I has a Security Filter attribute with some 50,000 members on which the allowed set is applied. The allowed set expression is StrToSet(<call to stored procedure here>). The stored procedure returns a comma-seperated list of the allowed members in the format [DimensionName].[Security Filter].&[key].

The stored procedure queries a database to get the set but this is very fast. Then, it caches the set to avoid repetative calls.

|||

> The allowed set expression is StrToSet(<call to stored procedure here>). The stored procedure returns a comma-seperated list of the allowed members in the format [DimensionName].[Security Filter].&[key].

This is not a good practice. Much better approach is to use Server Adomd.NET object model and return AdomdServer.Set from sproc instead of giant string. But since you query database table, I wonder whether you can make this table part of UDM and build joins inside UDM and avoid sproc altogether...

|||

OK, I will test both approaches (StrToSet and server-side Set) and post the results here.

-update

Thanks for the tip. Server Set resulted in much better performance. The first column shows the number of members in the allowed set. Time is in seconds.

Filer #	StrToSet	Set
5000	52	35
10000	90	58
20000	171	108
40000	272	144

end update

Would you mind eleborating more about the table idea? Do you mean a new dimension table that will slice the fact table or do you refer to a many-to-many dimension?

|||

> Would you mind eleborating more about the table idea? Do you mean a new dimension table that will slice the fact table or do you refer to a many-to-many dimension?

Since it seems that the information about which users can see which members is already stored in the table, you could build an aux measure group with User dimension and your dimension included, and then use something like

Exists(MyDimension.MyAttribute.MyAttribute.MEMBERS, StrToMember("[User].[" + Username + "]", "securitymeasuregroup")

as expression for the allowed set.

|||Thank you. But this will result in a very large fact table which will hold the allowed members for each user, corrrect? So, if I have 100 users and each user is allowed to see 100,000 members on average, the table will have 10,000,000 rows. I wonder how both approaches compare from a performance standpoint. Do you have any performance test results to share?|||Before we can compare the approaches - can you explain how the database table used by sproc looks like ? Doesn't it have the same structure (i.e. for user you need to be able to get list of allowed members), and therefore same size ?|||

To make the things simpler, let's assume that that in both cases the security policy will be materialized into the same table (dimension surrogate key, employee surrogate key). In the case of dynamic security, the stored procedure will query this table by user and apply the allowed set but the allowed set will not exceed the maximum number of members that the user can access. In the case of the security measure group, EXISTS can be potentially applied over millions of records. As the securiy table gets larger, I'd expect the benefits of EXISTS to level off. Or, will it?

|||Please note, that the call to Exists is always filtered by the current User, therefore there will never be a full scan of this measure group. I.e. if this measuregroup was implemented as pure ROLAP - it would generate exactly same SQL queries as you are likely doing from your sproc already. And, of course, in case of MOLAP it will be much more efficient. Therefore I beleive that the approach with EXISTS will always outperform approach with sproc no matter what sizes of tables will be.|||Thank you so much for you help, Mosha. I will post to this thread if I find otherwise.|||I think it will be very interesting to everybody to see results of your testing in either scenario. While I have seen approach that I recommended working well in number of installations, your data volumes (100 users each one having access to different set of 100,000 members out of 2 million) are interesting enough to see how well it will perform in the real world. My prediction is that it should perform well, but it is only a feeling without hard data behind it.|||

Got results. The materialized results are impressive! For the sake of testing, I generated all permutations of 250 employees with 52,000 securable items resulting in a fact table with some 13 mil rows. I compared this against the dynamic security approach where 52,000 securable items are converted to a Set. I tested the connect time from Excel and noted the query log events in the Profiler.

Dynamic Security Materialized Security (non-partitioned)

30-35 sec 8-12 sec

Not to mention that the dynamic security test currently excludes the application latency (records are read directly from a table). In real life, it would take additional time for stored procedure/service to prepare the security filter.

Thank you so much for the tip, Mosha! We will go probably with materialized security.

Do you think that partitioning the Security Filter measure group (if there is a way to logically group users and minimize the number of partitions) would decrease the query time even further?

Dynamic Security Stored Procedure Repeatedly Called

I have implemented an SSAS stored procedure for dynamic security and I call this stored procedure to obtain the allowed set filter. To my supprise, the stored procedure is being called repeatedly many times (more than 10) upon establishing the user session. Why is this happening?Do you have more than 10 attributes in this dimension ? (i.e. number of calls to sproc should be the same as number of attributes in dimension).|||

Thank you. I don't quite follow. The dimension is wide (it has some 30 attributes). However, only one attribute hierarchy has an allowed set filter defined. Why does this need to be evaluated many times?

In addition, the dimension is large (some 2 mil plus members). Even with allowed set caching inside the stored procedure, it takes an enormous amount of time (some 10 min) for the filter to be applied, e.g. if the filter returns some 40,000 allowed members. Any optimization tips?

|||10 minutes to filter 40,000 members out of 2 million is way too much. I've written sprocs which were able to do comparable filtering in a matter of seconds. Of course, it depends on the logic inside sproc. Would you share more details please - what is the criteria for the filtering ?|||

Thank you for helping out.

As I mentioned, the dimension has some 2 mil members. I has a Security Filter attribute with some 50,000 members on which the allowed set is applied. The allowed set expression is StrToSet(<call to stored procedure here>). The stored procedure returns a comma-seperated list of the allowed members in the format [DimensionName].[Security Filter].&[key].

The stored procedure queries a database to get the set but this is very fast. Then, it caches the set to avoid repetative calls.

|||

> The allowed set expression is StrToSet(<call to stored procedure here>). The stored procedure returns a comma-seperated list of the allowed members in the format [DimensionName].[Security Filter].&[key].

This is not a good practice. Much better approach is to use Server Adomd.NET object model and return AdomdServer.Set from sproc instead of giant string. But since you query database table, I wonder whether you can make this table part of UDM and build joins inside UDM and avoid sproc altogether...

|||

OK, I will test both approaches (StrToSet and server-side Set) and post the results here.

-update

Thanks for the tip. Server Set resulted in much better performance. The first column shows the number of members in the allowed set. Time is in seconds.

Filer #	StrToSet	Set
5000	52	35
10000	90	58
20000	171	108
40000	272	144

end update

Would you mind eleborating more about the table idea? Do you mean a new dimension table that will slice the fact table or do you refer to a many-to-many dimension?

|||

> Would you mind eleborating more about the table idea? Do you mean a new dimension table that will slice the fact table or do you refer to a many-to-many dimension?

Since it seems that the information about which users can see which members is already stored in the table, you could build an aux measure group with User dimension and your dimension included, and then use something like

Exists(MyDimension.MyAttribute.MyAttribute.MEMBERS, StrToMember("[User].[" + Username + "]", "securitymeasuregroup")

as expression for the allowed set.

|||Thank you. But this will result in a very large fact table which will hold the allowed members for each user, corrrect? So, if I have 100 users and each user is allowed to see 100,000 members on average, the table will have 10,000,000 rows. I wonder how both approaches compare from a performance standpoint. Do you have any performance test results to share?|||Before we can compare the approaches - can you explain how the database table used by sproc looks like ? Doesn't it have the same structure (i.e. for user you need to be able to get list of allowed members), and therefore same size ?|||

To make the things simpler, let's assume that that in both cases the security policy will be materialized into the same table (dimension surrogate key, employee surrogate key). In the case of dynamic security, the stored procedure will query this table by user and apply the allowed set but the allowed set will not exceed the maximum number of members that the user can access. In the case of the security measure group, EXISTS can be potentially applied over millions of records. As the securiy table gets larger, I'd expect the benefits of EXISTS to level off. Or, will it?

|||Please note, that the call to Exists is always filtered by the current User, therefore there will never be a full scan of this measure group. I.e. if this measuregroup was implemented as pure ROLAP - it would generate exactly same SQL queries as you are likely doing from your sproc already. And, of course, in case of MOLAP it will be much more efficient. Therefore I beleive that the approach with EXISTS will always outperform approach with sproc no matter what sizes of tables will be.|||Thank you so much for you help, Mosha. I will post to this thread if I find otherwise.|||I think it will be very interesting to everybody to see results of your testing in either scenario. While I have seen approach that I recommended working well in number of installations, your data volumes (100 users each one having access to different set of 100,000 members out of 2 million) are interesting enough to see how well it will perform in the real world. My prediction is that it should perform well, but it is only a feeling without hard data behind it.|||

Got results. The materialized results are impressive! For the sake of testing, I generated all permutations of 250 employees with 52,000 securable items resulting in a fact table with some 13 mil rows. I compared this against the dynamic security approach where 52,000 securable items are converted to a Set. I tested the connect time from Excel and noted the query log events in the Profiler.

Dynamic Security Materialized Security (non-partitioned)

30-35 sec 8-12 sec

Not to mention that the dynamic security test currently excludes the application latency (records are read directly from a table). In real life, it would take additional time for stored procedure/service to prepare the security filter.

Thank you so much for the tip, Mosha! We will go probably with materialized security.

Do you think that partitioning the Security Filter measure group (if there is a way to logically group users and minimize the number of partitions) would decrease the query time even further?

sql

Dynamic Security in SSAS 2005

I am trying to create dynamic security and was hoping to use a named query to link the fact table to a dimension that contains Active Directory users and companies - a user can have access to multiple companies. The problem is that I cannot find out how to get the username of the current user.

Anyone have any ideas?

thanks in advance.

Does the standard MDX keyword: UserName return what you want, like:

>>

With member [Measures].[CurrentUser] as UserName

select {[Measures].[CurrentUser]} on 0

from [Adventure Works]

>>

|||

thanks for the feedback - however, I am relatively new to Analysis Services (and 2005) - and cannot see where this would be created. Are you saying that this is a calculated Dimension (or measure?)

Many thanks

|||

The MDX keyword: UserName will return the name of the current user in an MDX expression, which could be used in many different contexts:

http://msdn2.microsoft.com/en-us/library/ms144884.aspx#expressions_statements_scripts

>>

SQL Server 2005 Books Online

Key Concepts in MDX (MDX)

In MDX, an expression is a combination of identifiers, values, functions, and operators that Analysis Services can evaluate to retrieve an object, such as a set or a member, or a scalar value like a string or a number.

>>

http://msdn2.microsoft.com/en-us/library/ms146016.aspx

>>

SQL Server 2005 Books Online

UserName (MDX)

Returns the domain name and user name of the current connection.

>>

|||Thanks for the feedback.

Dynamic Security and Rool ups

Hi

I thought I had this dynamic security worked out but I guess not.

this is on AS 2000.

I have a Fact table - one of the columns is a companyid, this is joined to the company dimension.

For security I have another table "empcompany" which lists all the users (ntusername) and the company codes they can access. I created a member property against the company code and used MDX similar to this:

filter([Companycode].[Companycode].members,([Companycode].CurrentMember.Properties("ntusername") = username))

The problem is that when I join the "empcompany" table to either the fact or to the company dimension instead of getting 1000 rows I get 80000+ rows and my numbers are wrong. Most of the users have access to more than 1 company.

Any one have any ideas?

Thanks

Steve

Hi Steve,

My suggestion would be to try the "Security Fact Table" approach with the "empcompany" table, as discussed in slides 18-32 of this webcast deck (you're using the "Member Property" approach above). In the 2nd approach, you don't need to join "empcompany" to the fact table - rather, it becomes the fact table for a "Permissions" cube, which is combined in a virtual cube:

http://support.microsoft.com/kb/828343/

>>

Support WebCast: Dynamic Dimension Security in Microsoft SQL Server 2000 Analysis Services

...

Dynamic Security
The Three Basic Approaches

Member property approach

Permission data (e.g. UserName) is stored at the desired dimension level

Security fact table approach

A permissions cube is combined with original source cube using a virtual cube

Filter members at the leaf level

Filter members at the non-leaf level

User-defined function callout

Call a user-defined function to reference an outside source (e.g. external RDBMS)

Dynamic Security

Hi,

I am a little concerned about the recent discussion of “Dynamic Security” and SSAS 2005.

http://blogs.proclarity.com/blogs/dgustafson/archive/2006/3/21/54.aspx

Is the “Dynamic Security” approach really scalable?

Christian

Don't take my post at blogs.proclarity.com the wrong way. This is not so much a performance problem in SSAS 2005. Rather, using dynamic security makes external OLAP query caching difficult for our product, ProClarity Analytics Server. My advice would be to think twice before using dynamic security to simply bypass the management of OLAP roles or AD groups.

Dynamic Row Level Security

Hi,
Is it possible to configure Reporting Services 2005 so that the same report
will apply different data filters depending on the user running the report ?
i.e A German user will only see German data and an English user will only
see English data even if they enter a parameter for 'All Europe'.
Thanks.RS supports a property User!UserID which returns the identity of the
interactive user (assuming Windows authentication). You can pass the user
identity to the data source as a query parameter to implement data filtering
at the data source.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
"Duncan Allen" <DuncanAllen@.discussions.microsoft.com> wrote in message
news:B76E2612-99F1-490B-AFBF-E11B5280BE43@.microsoft.com...
> Hi,
> Is it possible to configure Reporting Services 2005 so that the same
> report
> will apply different data filters depending on the user running the report
> ?
> i.e A German user will only see German data and an English user will only
> see English data even if they enter a parameter for 'All Europe'.
> Thanks.

Dynamic Members Security

Hi All,
I have a working solution with user-defined security. When user connects to a cube, a list with allowed members is created(retrieved from table) and returned as set to AS. Everything works fine.

Problem is that members are retrieved only at connection time. When I change my table with allowed members later, I would like to force AS to re-read this information again. Of course I can restart AS service or do full process of cube.

Does anybody have an idea?

Thanks,
Radim

Have you tried just reprocessing the dimension affected? What are the specifics of the solution?

|||That's very correct. Processing is possible solution but since I have many concurent users I didn't want to disrupt them. But yesterday I played with it more and I discovered that it's only necessary to do Process index. That's enough to force re-read of allowed members.

Thanks for contribution.

Radim

Dynamic dimenssion security

I am trying to pass the username to a vb function in the allowed members set of a dimenssion. For some reason it is not getting to the function. All of the other parameters are being received as I am logging them in the event log.

Any ideas on why it looks like the username is not being picked up at all would be great.

Thanks in advance.

Chris

Chris,

I'm assuming you are using the UserName() function? Can you post the MDX expression you have in the allowed members set definition?

You might try creating a test calculated member using some or all of the MDX expression you are using for that parameter just to make sure it is resolving in the manner you think it should...

Dave F.

|||

Thanks for the reply Dave. In the allowed members set I am doing a call to a vb strored procedure which takes in as parameter a username and level in the hierarchy it is providing security for. The stored procedure then executes a sql query on a view which returns an mdx string providing a set of members for a specific level.

If you execute the stored procedure from a query in management studio for example

SecurtiySP.GetOrgMembers(USERNAME, 7)

This then does return correctly. But when reporting services executes a query, it doesnt seem to pass the users username at all. I believe it may be to do with the account RS is running as and that it isnt part of AD and cant impersonate other accounts, so I will have a look next tuesday and see what I can find.

|||

Yes, the problem could be with having Reporting Services in the mix (or any other mid-tier application). If Reporting Services is running on a separate server, you would have to set up Kerberos in order for Reporting Services to impersonate the authenticated users in relation to Analysis Services. Also the server running Reporting Services will have to be trusted for delegation. More on this here:

http://sqljunkies.com/WebLog/mosha/archive/2005/01/25/6905.aspx

|||

Hi Michael,

Thanks for the reply. I have since found the problem. It seems accoring to this article http://support.microsoft.com/kb/913667 that we needed to run sp1 and the hotfixes. It is now passing the correct username :)

Chris

Dynamic Dimension Security using something else than ROLES

We are incorporating some OLAP cubes into our secure website and what to restrict the cubes information base on the person or client logged in. We are using Dundas OLAP as the front end for displaying the cubes. The Roles approach is not a viable solution since it is impossible for us to gather and keep track of our client’s windows usernames.

Is there a way to somehow pass a value through the control or the connection string and later use this value to filter dimension members?

> Is there a way to somehow pass a value through the control or the connection string and later use this value to filter dimension members?

Yes, there is a connection string property called "Roles", where you can specify which Roles should be used.

|||

I did a little bit of digging on connection string properties and came across this in one of your blogs.

There is a new MDX function for SSAS 2005 called “CustomData”.

http://sqljunkies.com/WebLog/mosha/archive/2005/10/11/mdx_functions_as2005.aspx

I am using the “Roles” and “CustomData” properties of the connection string to achieve my purpose.

Thanks Mosha

Dynamic Dimension security options

Hi

I want to filter members in the dimension based on the the access an application user has access to. Which will be stored in the applicaion database table.

Questions

1. Can somebody post the example of how to read this database table to retreive members so that I could set the allowed member section of the dimension security for the application user (not on windows domain)

2. Is this the good way off doing it or there is a better way to provide dimension member security for the application.

3. In my case none of the cubes data, tables or data sources has any application user inforamation stored aginast them. What I building is separate user repository and want to provide facility where administrator will be able to configure dimesion securty in the ASP.NET web application (using forms authentication). and then when user logs into the system he wshould be able to see only those dimesion members to which he has access to. Please let me know how the diffenten ways to acheive this.

Thanks

Milind

Bryan

Have you experienced performance issues with this solution? I implemented a solution similar to this but for some reason my performance has slowed significantly on MDX queries for users in my dynamic security role.

Thanks

|||

What are you using to define your allowed set?

B.

|||

A simple way of approaching this problem is to create a measure group with the user-dimension key combination. The measure group would have a single measure, a count, which you would make not visible to users in order to hide that measure group.

In the cube dimension you want to restrict, you defined an allowed set something like this:

NONEMPTYCROSSJOIN(

[My Dimension].[My Attribute].Members,

STRTOMEMBER("[User].[User Name].[" + USERNAME + "]"),

[Measure].[MySecurityMeasureGroupCount],

1)

It's important to note the USERNAME function returns the a string in the format of domain\user. You can use many of the common VB-type string parsing functions to alter the format if you need it. (Also, the MySecurityMeasureGroupCount is the count you hid above.

Bryan

|||

Hi Bryan,

In the scenario, I am trying. no user information is stored in the cube data. what I have is a screen where administrators will configure access to dimension member for the application users and this information will be stored into application database. the table structure will be like this

User Id, Dimension, Members

User123 Location [Location].[Some level member].(member].value

This table will be real time meaning administrator will be updating this table as and when required for any application user. I want to have this changes in effect imeddiately whenever administrators makes this changes.

Can you please let me know how would you acheive this with performance taking into consideration.

Cheeeers

Milind

|||Hello!

I have similar scenario with such table and real-time change propagation. I'm processing the cube to force re-read of user's permission. This is only necessary when user with changed permission set was already connected before that change. You can use "Process script cache" type, or Default, which wouldn't do the real process if cube is in processed state. However, it will cut off all the other users, so they will be also forced to connect again.

Radim

|||

I would still recommend the approach outlined above. This is a standard way of implementing user-specific allowed sets. If you need real-time updates, try building that one measure group as Rolap or as Holap with ProActive Caching.

You potentially could do a realtime lookup with a custom assembly. (See Books Online page titled "Working with Stored Procedures (Analysis Services)".) Still, this is not a commonly used technique so I wouldn't recommend this at this time.

Bryan

|||

Bryan C. Smith wrote:

I would still recommend the approach outlined above. This is a standard way of implementing user-specific allowed sets. If you need real-time updates, try building that one measure group as Rolap or as Holap with ProActive Caching.

You potentially could do a realtime lookup with a custom assembly. (See Books Online page titled "Working with Stored Procedures (Analysis Services)".) Still, this is not a commonly used technique so I wouldn't recommend this at this time.

Bryan

Bryan,

1. Can you please post the example of acheiving this...i mean some link to some help or some code.

2. Are you storing the uniquename of members in this user dimension member security table? Basically I will be building a screen wher application administrator will be able to browse dimesion members for any dimension and then they will select the members a particular user has access to. Could yuo please let me know is a good idear to store unique name of members and then is it possible to uses some what similat MDX expresssion to filter the dimesion mber in the allowed members set section of the dimension. ( it would be great if you could provide the example of the expresion in this case)

I appreciate your suggestion on this, this will give me comprehensive idea to go with the design.

Thanks in advance.

Waiting for your response.

Milind

|||

Let's say you have a dimension called MyDimension that you want to secure on a user-by-user basis. Connect to the relational database and create the following:

Code Snippet

create table MyUser (
UserID int not null,
ADAccount varchar(256) not null
)
alter table MyUser add
constraint PK_MyUser primary key (userid),
constraint AK_MyUser unique (adaccount)

create table MyDimensionSecurity (
MyDimensionID int not null,
MyUserID int not null
)
alter table MyDimensionSecurity add
constraint PK_MyDimensionSecurity primary key (mydimensionid, myuserid),
constraint FK_MyDimensionSecurity_MyDimensionID foriegn key (mydimensionid) references MyDimension (MyDimensionID),
constraint FK_MyDimensionSecurity_MyUserID foriegn key (myuserid) references MyUser (MyUserID)

Add your list of users to the MyUser table. For this example, be sure you store the ADAccount value in the DOMAIN\user format. Use your interface to control which users get to see which MyDimensionRecords. This data is of course stored in the MyDimensionSecurity table.

Now, connect to SSAS. Add the MyUser and MyDimensionSecurity tables to the DSV.

Create a new dimension for MyUser. It will likely have a single attribute, MyUser, with UserID as the key and ADAccount as the name.

Open the cube designer window. Create a new measure group for the MyDimensionSecurity table. It will have a single measure, MyDimensionSecurity Count. Select the measure in this measure group, right-click, select properties, and set visible = false.

Go to your dimension usage tab. Make sure the MyUser dimension was added to the cube. If not, add it. Make sure the MyDimensionSecurity measure group has a relationship with the MyDimension and MyUser dimensions.

Now we're ready to set up security.

Create a role (or alter an existing role). Select the Dimension Data tab and select the MyDimension cube dimension. We will be exploiting a relationship that exists within the cube so be certain to NOT select the database dimension version.

Once you've selected the MyDimension CUBE dimension, set the attribute to the leaf-level (key) attribute of that dimension and go to the Advanced tab. In the Allowed Set text box, enter the following code:

Code Snippet

{
NONEMPTYCROSSJOIN(
[My Dimension].[My Dimension Key].Children,
STRTOMEMBER( "[My User].[My User]."+USERNAME+"]"),
[Measures].[My Dimension Security Count],
1
)
}

This will give the affect you want. It does leave a security hole in that the My User dimension is visible. Even if you make it not visible, users who know it's there can write MDX to access the list of users within it. It is adviseable you set an Allowed Set on the User dimension based on the USERNAME function as well. (It's syntax will not look like the code example above.)

Now, you have to figure out how to get updates in the SQL Server tables reflected in real-time. Start by reading the Books Online sections on HOLAP, ROLAP, and proactive caching.

Hope that helps get you started.

Bryan

|||

Hi Bryan,

Thanks for your reply, it does helps. it has given me an ideaon how measure can be used to provide dimension security.

But solution you have provided still doesn't statisfy the requirement I have.

As per your example, I have understood following. please let me know if I have misunderstood something, may be because of little exp. on olap cubes.

1. This way I can protect members of only one diimension. it does require to have relation ship setup on the two tables (MyDimensionSecurity and MyDimension) making it the constraint for having same data type for the key columns. This means to secure members of other dimension in the cue MyOtherDimension i will have to create another table MyOtherDimensionSecurity and create one more measure based on it and setup a relationship between key columns of this new table and source table of MyOtherDimension dimension. Please confirm.

2. how can you extend this to show/hide members of any dimension attribute to one particular user using allowed members set expression. Please have a look at the table structure in my previos post for configuring dimension security for user using my asp.net application screen.

3. You ave mentioned ...."Go to your dimension usage tab. Make sure the MyUser dimension was added to the cube. If not, add it. Make sure the MyDimensionSecurity measure group has a relationship with the MyDimension and MyUser dimensions." can you please provide information on what kind of relation needs to be created and how to create. (This will be a great help)

I am after more generic way of configuring and securing dimension members of any dimension in the cube through asp.net application screen with help of "Allowed member set" section of advance tab of dimension data in role configuration.

Thanks,

Milind

|||

Sorry for the delay in the response. Long weekend here.

Regarding item #1, you are correct. Every dimensions you wish to secure with user-specific allowed sets, you will need to set up this way. In most real-world situations where we use this, there are 1 or 2 sensitive dimensions that are user-specific. The rest we secure at a group level. In other words, just because you secure one dimension with this technique doesn't mean every dimension must not employee this technique. Use it only where required to minimize complexity and maintenance burden).

Regarding item #2, I'm not seeing the table structure you reference. Still, you can apply this technique wherever you have table that lists dimension key and user key combos.

Regarding item #3, the relationship-type you should employ between the security fact table and the user and the MyDimension dimensions would be "Regular". You use this because your fact table has the key of both the User and the MyDimension dimensions. (You could technically build a many-to-many relationship between your other fact tables and the User dimension using the security fact table (measure group) as the intermediate measure group. Still, I'd stick with just the simple relationships and the security approach outlined in the previous posts.)

Regarding an overall security strategy, keep your initial approach very simple. SSAS security is different from more traditional security. Try implementing something basic, verify it works, and then add one more element to the mix.

Good luck,
Bryan

Dynamic Dimension security options

Hi

I want to filter members in the dimension based on the the access an application user has access to. Which will be stored in the applicaion database table.

Questions

1. Can somebody post the example of how to read this database table to retreive members so that I could set the allowed member section of the dimension security for the application user (not on windows domain)

2. Is this the good way off doing it or there is a better way to provide dimension member security for the application.

3. In my case none of the cubes data, tables or data sources has any application user inforamation stored aginast them. What I building is separate user repository and want to provide facility where administrator will be able to configure dimesion securty in the ASP.NET web application (using forms authentication). and then when user logs into the system he wshould be able to see only those dimesion members to which he has access to. Please let me know how the diffenten ways to acheive this.

Thanks

Milind

A simple way of approaching this problem is to create a measure group with the user-dimension key combination. The measure group would have a single measure, a count, which you would make not visible to users in order to hide that measure group.

In the cube dimension you want to restrict, you defined an allowed set something like this:

NONEMPTYCROSSJOIN(

[My Dimension].[My Attribute].Members,

STRTOMEMBER("[User].[User Name].[" + USERNAME + "]"),

[Measure].[MySecurityMeasureGroupCount],

1)

It's important to note the USERNAME function returns the a string in the format of domain\user. You can use many of the common VB-type string parsing functions to alter the format if you need it. (Also, the MySecurityMeasureGroupCount is the count you hid above.

Bryan

|||

Hi Bryan,

In the scenario, I am trying. no user information is stored in the cube data. what I have is a screen where administrators will configure access to dimension member for the application users and this information will be stored into application database. the table structure will be like this

User Id, Dimension, Members

User123 Location [Location].[Some level member].(member].value

This table will be real time meaning administrator will be updating this table as and when required for any application user. I want to have this changes in effect imeddiately whenever administrators makes this changes.

Can you please let me know how would you acheive this with performance taking into consideration.

Cheeeers

Milind

|||Hello!

I have similar scenario with such table and real-time change propagation. I'm processing the cube to force re-read of user's permission. This is only necessary when user with changed permission set was already connected before that change. You can use "Process script cache" type, or Default, which wouldn't do the real process if cube is in processed state. However, it will cut off all the other users, so they will be also forced to connect again.

Radim

|||

I would still recommend the approach outlined above. This is a standard way of implementing user-specific allowed sets. If you need real-time updates, try building that one measure group as Rolap or as Holap with ProActive Caching.

You potentially could do a realtime lookup with a custom assembly. (See Books Online page titled "Working with Stored Procedures (Analysis Services)".) Still, this is not a commonly used technique so I wouldn't recommend this at this time.

Bryan

|||

Bryan C. Smith wrote:

I would still recommend the approach outlined above. This is a standard way of implementing user-specific allowed sets. If you need real-time updates, try building that one measure group as Rolap or as Holap with ProActive Caching.

You potentially could do a realtime lookup with a custom assembly. (See Books Online page titled "Working with Stored Procedures (Analysis Services)".) Still, this is not a commonly used technique so I wouldn't recommend this at this time.

Bryan

Bryan,

1. Can you please post the example of acheiving this...i mean some link to some help or some code.

2. Are you storing the uniquename of members in this user dimension member security table? Basically I will be building a screen wher application administrator will be able to browse dimesion members for any dimension and then they will select the members a particular user has access to. Could yuo please let me know is a good idear to store unique name of members and then is it possible to uses some what similat MDX expresssion to filter the dimesion mber in the allowed members set section of the dimension. ( it would be great if you could provide the example of the expresion in this case)

I appreciate your suggestion on this, this will give me comprehensive idea to go with the design.

Thanks in advance.

Waiting for your response.

Milind

|||

Let's say you have a dimension called MyDimension that you want to secure on a user-by-user basis. Connect to the relational database and create the following:

Code Snippet

create table MyUser (
UserID int not null,
ADAccount varchar(256) not null
)
alter table MyUser add
constraint PK_MyUser primary key (userid),
constraint AK_MyUser unique (adaccount)

create table MyDimensionSecurity (
MyDimensionID int not null,
MyUserID int not null
)
alter table MyDimensionSecurity add
constraint PK_MyDimensionSecurity primary key (mydimensionid, myuserid),
constraint FK_MyDimensionSecurity_MyDimensionID foriegn key (mydimensionid) references MyDimension (MyDimensionID),
constraint FK_MyDimensionSecurity_MyUserID foriegn key (myuserid) references MyUser (MyUserID)

Add your list of users to the MyUser table. For this example, be sure you store the ADAccount value in the DOMAIN\user format. Use your interface to control which users get to see which MyDimensionRecords. This data is of course stored in the MyDimensionSecurity table.

Now, connect to SSAS. Add the MyUser and MyDimensionSecurity tables to the DSV.

Create a new dimension for MyUser. It will likely have a single attribute, MyUser, with UserID as the key and ADAccount as the name.

Open the cube designer window. Create a new measure group for the MyDimensionSecurity table. It will have a single measure, MyDimensionSecurity Count. Select the measure in this measure group, right-click, select properties, and set visible = false.

Go to your dimension usage tab. Make sure the MyUser dimension was added to the cube. If not, add it. Make sure the MyDimensionSecurity measure group has a relationship with the MyDimension and MyUser dimensions.

Now we're ready to set up security.

Create a role (or alter an existing role). Select the Dimension Data tab and select the MyDimension cube dimension. We will be exploiting a relationship that exists within the cube so be certain to NOT select the database dimension version.

Once you've selected the MyDimension CUBE dimension, set the attribute to the leaf-level (key) attribute of that dimension and go to the Advanced tab. In the Allowed Set text box, enter the following code:

Code Snippet

{
NONEMPTYCROSSJOIN(
[My Dimension].[My Dimension Key].Children,
STRTOMEMBER( "[My User].[My User]."+USERNAME+"]"),
[Measures].[My Dimension Security Count],
1
)
}

This will give the affect you want. It does leave a security hole in that the My User dimension is visible. Even if you make it not visible, users who know it's there can write MDX to access the list of users within it. It is adviseable you set an Allowed Set on the User dimension based on the USERNAME function as well. (It's syntax will not look like the code example above.)

Now, you have to figure out how to get updates in the SQL Server tables reflected in real-time. Start by reading the Books Online sections on HOLAP, ROLAP, and proactive caching.

Hope that helps get you started.

Bryan

|||

Hi Bryan,

Thanks for your reply, it does helps. it has given me an ideaon how measure can be used to provide dimension security.

But solution you have provided still doesn't statisfy the requirement I have.

As per your example, I have understood following. please let me know if I have misunderstood something, may be because of little exp. on olap cubes.

1. This way I can protect members of only one diimension. it does require to have relation ship setup on the two tables (MyDimensionSecurity and MyDimension) making it the constraint for having same data type for the key columns. This means to secure members of other dimension in the cue MyOtherDimension i will have to create another table MyOtherDimensionSecurity and create one more measure based on it and setup a relationship between key columns of this new table and source table of MyOtherDimension dimension. Please confirm.

2. how can you extend this to show/hide members of any dimension attribute to one particular user using allowed members set expression. Please have a look at the table structure in my previos post for configuring dimension security for user using my asp.net application screen.

3. You ave mentioned ...."Go to your dimension usage tab. Make sure the MyUser dimension was added to the cube. If not, add it. Make sure the MyDimensionSecurity measure group has a relationship with the MyDimension and MyUser dimensions." can you please provide information on what kind of relation needs to be created and how to create. (This will be a great help)

I am after more generic way of configuring and securing dimension members of any dimension in the cube through asp.net application screen with help of "Allowed member set" section of advance tab of dimension data in role configuration.

Thanks,

Milind

|||

Sorry for the delay in the response. Long weekend here.

Regarding item #1, you are correct. Every dimensions you wish to secure with user-specific allowed sets, you will need to set up this way. In most real-world situations where we use this, there are 1 or 2 sensitive dimensions that are user-specific. The rest we secure at a group level. In other words, just because you secure one dimension with this technique doesn't mean every dimension must not employee this technique. Use it only where required to minimize complexity and maintenance burden).

Regarding item #2, I'm not seeing the table structure you reference. Still, you can apply this technique wherever you have table that lists dimension key and user key combos.

Regarding item #3, the relationship-type you should employ between the security fact table and the user and the MyDimension dimensions would be "Regular". You use this because your fact table has the key of both the User and the MyDimension dimensions. (You could technically build a many-to-many relationship between your other fact tables and the User dimension using the security fact table (measure group) as the intermediate measure group. Still, I'd stick with just the simple relationships and the security approach outlined in the previous posts.)

Regarding an overall security strategy, keep your initial approach very simple. SSAS security is different from more traditional security. Try implementing something basic, verify it works, and then add one more element to the mix.

Good luck,
Bryan

|||

Bryan

Have you experienced performance issues with this solution? I implemented a solution similar to this but for some reason my performance has slowed significantly on MDX queries for users in my dynamic security role.

Thanks

|||

What are you using to define your allowed set?

B.

dynamic dimension security - user and secretary

Hi

I have defined a dimension with a role with dynamic security like filter({set},CurrentMember.Name=UserName)

This works well while I have only the users.

Does someone have an Idea how this could be handled if the user has a secretary or an assistent who should see the same figures.

Thanks

Hannes

One thing that's not clear is which dimension hierarchy "CurrentMember.Name" refers to - is it like an Employee dimension? If so, maybe this same dimension could be added in a different "DelegateEmployee" role, which has a many-many relation to the original measure group. Each row of the intermediate measure group fact table (DelegateFact) would relate a Delegate to an Employee. This way, each employee could have multiple (0-N) delegates, and these delegates could be determined via the intermediate measure group, like:

Filter(({set, Generate(Exists([DelegateEmployee].[Employee].[Employee], set, "DelegateFact")},

{LinkMember([DelegateEmployee].[Employee].CurrentMember, [Employee].[Employee])}),

[Employee].[Employee].CurrentMember.Name = UserName)

|||

Thats a good idea - thanks.