mssqltips logo

SQL Server Analysis Services SSAS Dimension Security Stored Procedures

By:   |   Updated: 2009-09-24   |   Comments (38)   |   Related: > Analysis Services Security

Problem
I have seen the tip Introduction to Dimension Security in SQL Server Analysis Services SSAS 2005 which showed how to define roles in a cube to limit the members of a dimension that are available to the members (Windows users and/or groups) of the role.  In that tip the dimension members were specified by simply selecting them via clicking a checkbox.  In my case I have a security implementation in the data source to my cube where there are a set of constantly changing rules that determine who can see what.  What I need is to be able to leverage the existing security implementation in the data source from the cube.  How can I do that?

Solution
The role-based security model in SSAS provides three ways to specify dimension security; i.e. what members of a dimension are available to members of a particular role.  The tip that you mentioned demonstrated the basic capability where you can specify an allowed set or a denied set of dimension members.  You do this by simply selecting or unselecting the dimension members.  The other two options allow you to define the allowed or denied set via an MDX expression or call out to a stored procedure.  I'll cover the MDX expression option in a future tip; in this one we'll look at how to leverage a stored procedure to dynamically generate the allowed set of dimension members for a role. 

As an example I have created the same SSAS cube used in the tip mentioned above in the problem statement.  The cube structure is as follows:

The data source for this cube is as follows:

This is the same data source that was used in the earlier tip Dynamically Control Data Filtering in SQL Server Reporting Services Reports which showed how to apply the same type of filtering in an SSRS report based on a SQL Server database.  In the above schema the UserOffice table maintains the list of offices that a user can access. 

Using the cube structure and data source as shown above, I'll walk through the following steps to demonstrate how to setup dimension  security in the cube:

  • Create a simple T-SQL stored procedure in the data source that will return the list of offices based on the current user

  • Create the SSAS stored procedure code that invokes our T-SQL stored procedure to return the list of offices based on the current user and compile it into a class library DLL; i.e. an assembly

  • Deploy the assembly to our SSAS cube

  • Setup a role in the cube that will dynamically filter the Office dimension by executing the SSAS stored procedure

  • Test the sample

The key point about this solution is that the role-based security that we define in the cube is enforced in the cube no matter what tool we use to query the cube.  In other words whether we query the cube from Excel, SQL Server Reporting Services, Report Builder, PerformancePoint, etc. the security will be applied and the user will restricted to accessing the dimension members allowed by the role-based security.

Creating a Stored Procedure in the Data Source

The following T-SQL stored procedure will return the list of offices that the user account is allowed to access:

create procedure dbo.FilterOfficeByUser
  @UserAccount nvarchar(50)
as
begin
 set nocount on;
 select o.OfficeKey, o.OfficeName
 from dbo.Office o
 join dbo.UserOffice uo on uo.OfficeKey = o.OfficeKey
 where uo.UserAccount = @UserAccount
end

In the above stored procedure we filter the UserOffice table based on the UserAccount then join to the Office table based on the OfficeKey.  Our result set will be the list of offices that the user can access. 

Creating the SSAS Stored Procedure

The connotation of a stored procedure is a database object that contains T-SQL code.  An SSAS stored procedure is actually a method in a .NET class.  You write the code in the .NET language of your choice, compile it into a class library DLL, then deploy the DLL to SSAS.  The method could execute a T-SQL stored procedure or a T-SQL command, call a web service, etc.  You have the entire .NET framework at your disposal.  In this example we are going to execute the FilterOfficeByUser stored procedure in the cube data source.  We reviewed that stored procedure earlier in this tip.  The C# code for our .NET method is shown below:

public static Set FilterOfficeByUser(string UserAccount)
{
  Expression expr = new Expression();
  SetBuilder sb = new SetBuilder();
  using (SqlConnection cn = new SqlConnection())
  {
    // prepare connection and command
    cn.ConnectionString = 
      string.Format(
        "data source={0};initial catalog={1};integrated security={2};",
        "localhost", 
        "mssqltips_dim_security",
        "sspi"
    );
    cn.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = cn;
    cmd.CommandText = "dbo.FilterOfficeByUser";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@UserAccount", UserAccount);
    // execute stored procedure
    SqlDataReader r = cmd.ExecuteReader(CommandBehavior.SingleResult);
    bool hasRow = r.Read();
    // iterate through the result set
    while (true == hasRow)
    {
      // add each member key to MDX set
      expr.ExpressionText = string.Format(
        "[Office].[Office].&[{0}]", r[0].ToString()
      );
      Member m = expr.CalculateMdxObject(null).ToMember();
      TupleBuilder tb = new TupleBuilder();
      tb.Add(m);
      sb.Add(tb.ToTuple());
      hasRow = r.Read();
    }
    // return MDX set
    return sb.ToSet();
}

The main points about the above method are:

  • It uses standard ADO.NET classes such as SqlConnection, SqlCommand, and SqlDataReader to connect to the mssqltips_dim_security database and execute a stored procedure

  • The connection string is hard-coded; in a production solution you would retrieve it from some sort of configuration

  • Each row returned from the stored procedure contains the OfficeKey column which is added to an MDX set

  • The syntax for specifying the dimension member in the SSAS cube is the string [Office].[Office].&[OffficeKey] where OfficeKey is the member key and is the OfficeKey column in the Office table of the mssqltips_dim_security database

  • In order to add the dimension member to the MDX set, you convert the string to a Member (the CalculateMdxObject method on the Expression class), add the Member to a Tuple (the Add method on the TupleBuilder class), then add the Tuple to the Set (the Add method on the SetBuilder class)

  • The Member, TupleBuilder, Tuple, SetBuilder and Set classes are provided by ADOMD.NET which is essentially ADO.NET for SSAS; you add a reference to the Microsoft.AnalysisServices.AdomdServer assembly to your project in order to use these classes

  • The MDX set is the return value of the method

To sum it up we call a stored procedure in our cube's data source that gives us the list of offices that a user is allowed to access then we return that list as an MDX set.  The MDX set represents the allowed list of members of the Office dimension for a particular user.

Deploying the Assembly to SSAS

After coding the .NET class method as shown above, you compile it into a class library DLL then deploy that DLL to SSAS.  In general .NET terminology a DLL is referred to as an assembly.  You can either use SQL Server Management Studio or an Analysis Services project to deploy the assembly to SSAS.  We'll use the SSAS project in this example. 

You will see an Assemblies node in the Solution Explorer of the SSAS project as shown below:

Right click on the Assemblies node and select New Assembly Reference.  Select the assembly to add to the SSAS project from the dialog.  In my case the SSAS stored procedure code that we just reviewed above is in a class library project in the same solution as the SSAS project.  I click on the Projects tab to add that assembly and the dialog looks like this:

After adding the assembly to the SSAS project, right click on it in the Solution Explorer and edit the properties as shown below:

I set the impersonation mode to Service Account; this is the SSAS service account and those credentials will be used to execute the .NET code in the assembly.  I set the permission set to Unrestricted which places no restrictions on the .NET code.  Make sure that the account used to execute the .NET code has at least read permission on the data source; i.e. the SQL Server database.  For additional information on SSAS stored procedures and the properties noted here, refer to the Books on Line topic Working with Stored Procedures (Analysis Services).

To deploy the SSAS project including the SSAS stored procedure assembly, right click on the project in the Solution Explorer and select Deploy from the menu.  After completing this step you can verify that the assembly has been deployed to the cube by opening SQL Server Management Studio (SSMS), connecting to the cube, and drilling down to the Assemblies node as shown below:

You can also deploy an assembly to the cube using SSMS by right clicking the Assemblies node then selecting New Assembly from the menu.  I like associating the assembly with the SSAS project as I have shown.

Setting Up Role-Based Security in the Cube

In the previous tip Introduction to Dimension Security in SQL Server Analysis Services SSAS 2005 I showed how to setup role-based security in a cube using SSMS.  In this example I'm going to do it in the SSAS project.  It is quite possible that while you are working on building the dimensions and measure groups in the SSAS project you really don't have a handle on the role-based security requirements so you can use SSMS to set it up after you've deployed the cube.  

To begin, right click on Roles in the Solution Explorer of the SSAS project and select New Role from the menu.  Click on the membership tab and add the Authenticated users group as shown below:

In my example I'm working on a Windows 2003 Server that is not a member of a domain.  I selected the Authenticated Users group because the role-based security that I'm setting up is intended to work for any user.

Click on the Cubes tab and allow the role read access to the cube:

Click on the Dimension Data tab to specify the SSAS stored procedure to be invoked to determine the allowed member set as shown below:

In the screen shot above you need to click on the Advanced tab, select the dimension, then enter in the statement to execute the SSAS stored procedure.  The statement can be described as follows:

  • [MSSQLTips_SSAS_Security_SP] is the name of the class library project (discussed earlier) and when you deploy it to SSAS the Assembly name defaults to the project name (or the name of the DLL if you deploy via SSAS)

  • [FilterOfficeByUser] is the name of the method in the .NET class (also discussed above)

  • UserName is a built-in MDX function that returns the current user in the form of DOMAIN\USER and is being passed as a parameter to the FilterOfficeByUser method

Testing Role-Based Security in the Cube

To test our role-based security setup we'll create an Excel 2007 pivot table by connecting to the cube.  We'll launch Excel from a Command Prompt window using the runas command which allows us to specify the user credentials to run Excel.  The following is a sample command:

runas /user:vssqldb02\awilson 
      "C:\Program Files\Microsoft Office\Office12\EXCEL.EXE"

Note that this command needs to by entered on a single line.  The runas command specifies the user, vssqldb02 is the name of my test server, and awilson is the user I want to test.

After executing the above command to launch Excel, I connect to the cube, insert a pivot table, and drag and drop the fields as follows: Office onto the Row Labels, Sales Amount onto the Values, and  Calendar Year onto the Column Labels.  I now have the following pivot table:

The point of this test is that the user AWilson is restricted to just the Baltimore, MD office.  This is exactly what we expect based on the role-based security that we setup.  We can verify the above result by executing the FilterOfficeByUser stored procedure in our data source (this is what the SSAS stored procedure does that we setup in our role-based security).  Execute the stored procedure in an SSMS query window and the results are shown below:

exec dbo.FilterOfficeByUser 'vssqldb02\awilson'
exec dbo.FilterOfficeByUser 'vssqldb02\msmith'

 

I have executed the FilterOfficeByUser stored procedure for two users and shown the query results .  If I repeat the steps above to  launch Excel as the user MSmith and create a pivot table, I get the following:

Again this is what we expect as the query results above show that MSmith is allowed to see the Philadelphia, PA and Pittsburgh, PA offices. 

Next Steps

  • Download the sample code and experiment with it.  The archive includes a backup of the SQL Server database used to build the cube and a solution that contains the class library for the SSAS stored procedure and the SSAS project to build the cube.
  • Dimension security in an SSAS cube is much more robust than what we get with a SQL Server relational database.  This example demonstrates a powerful technique where we can specify dimension security in our cube by leveraging an existing security implementation that lives outside of the cube.
  • Kudos to Teo Lachev who authored the excellent book Applied Microsoft Analysis Services 2005 and Microsoft Business Intelligence Platform which I used to gain an understanding of how to leverage SSAS stored procedures for dimension security.


Last Updated: 2009-09-24


get scripts

next tip button



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, October 03, 2017 - 5:10:56 AM - Madjid Back To Top

 If you cannot get the data to be filtered, make sure that you don't have full rights on the data at a higher level. In my case, although I was member of a role with restricted access to the cube dimension data using this method, I was administrator of the server, which gave me full rights on the dimension data and made the filtering useless... Once removed from the server's administrator role, the filtering worked like a charm!

 


Saturday, September 16, 2017 - 8:00:39 AM - Abid Ali Back To Top

Hi,

i have deployed your code as it. but not getting filtered data at excel. can you help me?

 


Tuesday, June 02, 2015 - 8:01:52 AM - Eduardo Back To Top

 

Really nice post, this is what i was looking for but i have a problem with the number of tuples selected when the stored procedure returns more than 50 results to filter into the Dimension Data.

Someone have the same problem?

Thanks


Thursday, February 19, 2015 - 1:28:49 AM - Akshay Rane Back To Top
Hi Ray,
 
We cannot use this approach for implementing Dynamic Dimension Security, can we?
As this approach takes the Allowed Member Set from a relational database using an assembly function, it isn't as dynamic as we wish it to be. The reason behind this is when a user logs in the first time, the assembly function - FilterOfficeByUser() is called and the returned set is cached in the cube. Now, every subsequent time this particular user logs in, we have the same cached Allowed Member Set that was calculated the first time, even if the data in relational database changes. This means that there are no subsequent calls to the function in assembly to recalculate the Allowed Member Set each time a user logs in.
 
Regards,
Akshay
 

Saturday, August 23, 2014 - 6:10:14 AM - Prasad Back To Top

A really nice post !

 

I was looking in the net to find out a nice article to implement data level security using custom c# code. I ended up with this article, it gives me a clear picture from scratch to end on how to implement it.


Monday, January 27, 2014 - 8:09:34 PM - Alex Back To Top

OMG !! this is exactly what I was looking for, thank you so much!


Sunday, April 21, 2013 - 4:07:49 AM - Omar Zyadat Back To Top

 

Hi Ray,

 

I have tried the solution and it is working fine. My dimension contains a hierarchy of Geo Zone, Country, and entity. Each user is assigned to one or more entities. However, when I’m browsing the cube the dimension is not being filtered according to the user’s assigned entities. When I implicitly call the assembly using an MDX query the filter works fine without any issue. Here is the MDX query I’m using:

SELECT
{
 [Measures].[Performance]
} ON COLUMNS,
{
 [DimSecurity].[FilterEntityByUser](UserName)
} ON ROWS
FROM
 [Dim Security]

 

When I execute the below MDX query the filter does not work:

 SELECT
{
 [Measures].[Performance]
} ON COLUMNS,
{
 [Dim Entities].[Entities].[Entity]
} ON ROWS
FROM
 [Dim Security]

 

Regards,

Omar

 


Thursday, March 07, 2013 - 9:44:53 AM - Raymond Barley Back To Top

You should be able to test from the cube browser or Excel.  Since you are actually running a query against a SQL Server database you can run the SQL Server Profiler to see what the query looks like.  That may shed some light on why it's not working.  


Wednesday, March 06, 2013 - 1:42:49 PM - hmai Back To Top

Hi Ray,

Yes. It did work well when I added the reference.

But I am not getting filtered result while testing. Getting data for all users. My stored procedure is as below

ALTER PROCEDURE [dbo].[FilterCategoryByBuyer] @BuyerName nvarchar(50)

AS

BEGIN

SET NOCOUNT ON;

SELECT C.Category_Id

FROM [Books].Category C

JOIN [Books].[Buyers] B ON B.Category_Id = C.Category_Id

WHERE B.BuyerName IN (@BuyerName)

END

I am using above same dll and SP . Created a role where dimension data security is implemented for cube. I have also mentioned [ClassLibrary1].[FilterCategoryByBuyer](UserName) in the allowed member set and checked mdx syntax. It is fine. Processed the cube. Also opened excel from command prompt passingmy username.

What can be the reason for not getting filtered data in Excel. Also can I test it in the cube browser itself instead of testing in Excel?

 

Thanks,

Hmai


Tuesday, March 05, 2013 - 9:07:43 AM - Raymond Barley Back To Top

I don't know anything about Visual Studio Express editions; I use Visual Studio Ultimate.

You need to add a reference to a Microsoft.AnalysisServices DLL.  When I did this tip the name of the DLL was Microsoft.AnalysisServices.AdomdServer.  I think this tip was done with SQL Server 2005.

Take a look at the DLLs that are named Microsoft.AnalysisService.* in c:\windows\assembly

On my system I see Microsoft.AnalysisServices.AdomdClient but not Microsoft.AnalysisServices.AdomdServer.  I'm using SQL Server 2012


Tuesday, March 05, 2013 - 4:12:58 AM - hmai Back To Top
Hi, For the above post, I have pasted the same code again in a better readable format. The code still gives the same errors.
using System;
using System.Collections.Generic;
using System.Text;
// add namespaces
using System.Data;
using System.Data.SqlClient;
using Microsoft.AnalysisServices.AdomdServer;
namespace SSASLibrary
{
    public class SSASLibrary
    {
      public static Set FilterCategoryByBuyer(string BuyerName)
{
  Expression expr = new Expression();
  SetBuilder sb = new SetBuilder();
  using (SqlConnection cn = new SqlConnection())
  {
    // prepare connection and command
    cn.ConnectionString = 
      string.Format(
        "data source={0};initial catalog={1};integrated security={2};",
        "localhost", 
        "Books",
        "sspi"
    );
    cn.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = cn;
    cmd.CommandText = "dbo.FilterCategoryByBuyer";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@BuyerName", BuyerName);
    // execute stored procedure
    SqlDataReader r = cmd.ExecuteReader(CommandBehavior.SingleResult);
    bool hasRow = r.Read();
    // iterate through the result set
    while (true == hasRow)
    {
      // add each member key to MDX set
      expr.ExpressionText = string.Format(
        "[Category].[Category Id].&[{0}]", r[0].ToString()
      );
      Member m = expr.CalculateMdxObject(null).ToMember();
      TupleBuilder tb = new TupleBuilder();
      tb.Add(m);
      sb.Add(tb.ToTuple());
      hasRow = r.Read();
    }
    // return MDX set
    return sb.ToSet();
         }
      }
    }
}
 
Thanks,
Maithri

Tuesday, March 05, 2013 - 4:05:51 AM - hmai Back To Top

Hi,

I am stuck creating dll from .net code. I am using SqlServer 2008 and I have installed Visual C# 2010 express edition and Sql Server 2008 ADOMD.Net. Changed the settings in properties of c sharp file to follow .Net 3.5 framework.

After all this I am getting below errors

1. The type or namespace name 'AnalysisServices' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)

2. The type or namespace name 'Set' could not be found (are you missing a using directive or an assembly reference?)

I am new to .net and clueless. Please let me know how I can create dll for the code pasted below.

using System; using System.Collections.Generic; using System.Text; // add namespaces using System.Data; using System.Data.SqlClient; using Microsoft.AnalysisServices.AdomdServer; namespace SSASLibrary { public class SSASLibrary { public static Set FilterCategoryByBuyer(string BuyerName) { Expression expr = new Expression(); SetBuilder sb = new SetBuilder(); using (SqlConnection cn = new SqlConnection()) { // prepare connection and command cn.ConnectionString = string.Format( "data source={0};initial catalog={1};integrated security={2};", "localhost", "Books", "sspi" ); cn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = "dbo.FilterCategoryByBuyer"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@BuyerName", BuyerName); // execute stored procedure SqlDataReader r = cmd.ExecuteReader(CommandBehavior.SingleResult); bool hasRow = r.Read(); // iterate through the result set while (true == hasRow) { // add each member key to MDX set expr.ExpressionText = string.Format( "[Category].[Category Id].&[{0}]", r[0].ToString() ); Member m = expr.CalculateMdxObject(null).ToMember(); TupleBuilder tb = new TupleBuilder(); tb.Add(m); sb.Add(tb.ToTuple()); hasRow = r.Read(); } // return MDX set return sb.ToSet(); } } } }

 

Thanks,
hmai

Tuesday, April 19, 2011 - 1:21:36 PM - Ray Barley Back To Top

I meant to add in my previous post that I think when you deploy an assembly to SSAS that it gets copied somewhere and the config file can't be located.  There may be a way but I've not seen it.


Tuesday, April 19, 2011 - 1:18:17 PM - Ray Barley Back To Top

When you specify the Allowed Member Set for the role,

you invoke the stored procedure (actually it's C# code) with this expression:

[MSSQLTips_SSAS_Security_SP].[FilterOfficeByUser](UserName)

 

When you connect to the cube, you could pass in your

server name for the connection string to the relational database by adding the CustomData parameter to

the SSAS connection string; e.g. 

 

Data Source=your_servername;Initial Catalog=your_ssasdb;CustomData=xxxx

 

where xxxx is the server name portion that you need for the relational database connection string

 

Then you could pass in the server name as an additional parameter

to the FilterOfficeByUser stored proc in the Allowed members et expression; e.g.

 

[FilterOfficeByUser](UserName, CUSTOMDATA)

 

CUSTOMDATA is an MDX function that returns whatever you passed in to the SSA connection string's CustomData

 

Inside the FilterOfficeByUser C# code you can add the servername parameter 

and put the servername into the connection string (replace localhost with the 

servername variable)


Tuesday, April 19, 2011 - 12:43:42 PM - Patrick Fung Back To Top

Thanks a lot for your post. I try it and it works great. However, I get problems with the hardcoded db connection string in the dll code. I try to use a config file to store to the connection string so i can re-use the dll for different cubes. But I am not able to get it work after I add the .dll to as assembley in the cube onto SSAS. 

The config file in the .dll project contains only 2 settings : ServerName & DatabaseName, I then use the following 2 lines in the .dll code to retrieve the setting and build the connection string after.

string ServerName = ConfigurationManager.AppSettings["ServerName"].ToString();
string DatabaseName = ConfigurationManager.AppSettings["DatabaseName"].ToString();

I build the .dll and it seems fine. The .dll and the .dll config are in my project folder. Then I add the .dll as assembley to the cube and redeploy the cube. I can found the .dll created on the SSAS server but see no .dll.config there. Then I copy the .dll.config from my pc to folder that contains my .dll but it's still not working
any idea?


Thursday, February 17, 2011 - 7:41:31 AM - Ray Barley Back To Top

I have done this by specifying an MDX expression for the Allowed Member Set for a dimension; i.e. you want to filter the dimension members based on who is executing the report.  By filtering the dimension the facts that join to it are also filtered.

The way to do that is to use the MDX function EXISTS.  There is also an MDX function StrToMember which takes a string and returns a member that you can use.  Use StrToMemeber to get the dimension member based on the user (using the USERNAME MDX function).

For example I have a dimension that is connected to a fact, I'll call it Demographics, a User dimension which has info on users, and finally a dimension called DemographricsBridge which has the combination of what users can access what members in the Dmeographics dimension (which is connected to my facts thereby filtering the facts as well).

Here is a sample EXISTS MDX function that you put in the Allowed Member Set:

 

Exists(

  [Demographics].[DemographicsID].MEMBERS

  , StrToMember("[User].[LoginName].[LoginName].[" + Username + "]")

  , "User Demographics Role Bridge"

)

General Notes on Role-Based Security in a Cube

·         It’s based on Windows security; i.e. users and groups

·         You create a role and specify members; e.g. users and/or groups

·         If a user is a member of more than one role, they get the union of permissions for all roles

·         Denied permissions are only effective in a single role; if you are a member of another role that allows those permissions, denied permissions are then allowed

·         In order for role-based security to work, the credentials of the caller must be used when connecting to SSAS

SSAS Connection String Properties

EffectiveUserName=DOMAIN\ACCOUNT can be used to impersonate

Roles=RoleNameCommaDelimitedList can be used to pass role(s) to check; the user must be a member of the role.  This one may be able to be used to handle the situation where basic search doesn’t enforce any security on sources but advanced search does.


Wednesday, February 16, 2011 - 11:34:32 PM - Jai Sidhu Back To Top

Hi ray..Thanks for the reply.But I have one more query.

I want to create SSRS reports where my data source is SSAS cubes and these reports will have the filtered data based on the user who has logged in.
My requirement is that : I do have a mapping table where I have mapped the username with the userid.
My fact table contains the userid's of various persons and the respective data allocated to them.

1)How should I capture the username of the user who has logged in to the reports and pass it to the SSAS cubes?
2)How to filter the data from the cubes(dimension tables and fact table) based on the user login from cubes?


Its urgent. Pls help


Wednesday, February 16, 2011 - 8:31:19 AM - Ray Barley Back To Top

First a bit of advice - if you can implement dimension data security in the cube do it rather than taking this approach.  However I had a situation where it wasn't possible so I had to struggle through this myself.

In the tip there are 2 projects - the stored proc code and an SSAS project.  Did you add the stored proc project as an Assembly reference to the SSAS project?  You may have to dpeloy the SSAS project with the Assembly reference before you can set dimension data security (I'm not sure but it wouldn't hurt).  You can check the cube in SQL Server Management Studio to see if the cube knows about the assembly.

When I added the assembly reference to the SSAS project, I set Impersonation Mode to Service Account and Permission Set to Unrestricted.

When you click on the Dimension Data tab in your Analysis Services project and select the Office dimension you will see a treeview that has nodes for the SSAS database (first one) and the cube (second one).  The dimensions are shown under both nodes.  I selected the Office dimension under the cube node, not under the SSAS database node.  The SSAS database node makes more sense because you are implementing the filtering at the database level rather than the cube level (if you had multiple cubes filtering at the database level would filter them all).  However I couldn't get that to work (don't remember why) so I chose the Office dimension under the cube node.

After you enter the [MSSQLTips_SSAS_Security_SP].[FilterOfficeByUser](UserName) in the Allowed Member Set did you click the Check button and then Save?  The Check button validates the MDX.

 


Wednesday, February 16, 2011 - 1:02:00 AM - Jai Sidhu Back To Top

Hi Ray..I just tried your solution .It was of great help.But I am stuck at a point.

 I click on the Dimension Data tab to specify the SSAS stored procedure to be invoked to determine the allowed member set and  enter  the statement to execute the SSAS stored procedure as [MSSQLTips_SSAS_Security_SP].[FilterOfficeByUser](UserName) and then I click the Ok button to save the above statement.But when I reopen the Roles section and go to the Dimension Data section, all my settings are lost and the statement [MSSQLTips_SSAS_Security_SP].[FilterOfficeByUser](UserName) is not there.hence when I exceute the excel I am able to see the whole data intstead of the filtered result based on Office.

Do I need to add something else?

Pls Help.

Thanks and Regards

Jai Sidhu

 


Tuesday, September 07, 2010 - 3:03:12 PM - jerry e Back To Top
worked like a charm..thanks for your assistance.


Tuesday, September 07, 2010 - 2:07:23 PM - Ray Barley Back To Top
By default the grand total isn't filtered.  You can change that by going to the Advanced tab of the Dimension Data security window and checking the option Enable Visual Totals; it's all the way at the bottom of the window.

This window is shown in the tip; it's where the Allowed Member Set is entered.  However, the screen shot doesn't show the whole window.

 


Tuesday, September 07, 2010 - 11:07:05 AM - Jerry E Back To Top
Hi Ray..we would need a way to only show the Total as Philadelphia + Pittsburgh.  How can that be accomplished? Thanks in advance..Jerry


Tuesday, September 07, 2010 - 10:44:45 AM - indigenica Back To Top
thank you i've been searching it a entire week! thank you again


Friday, September 03, 2010 - 7:25:40 AM - Ray Barley Back To Top
You can use an expression for the SSAS connection string and set the user that way; add EffectiveUserName=DOMAIN\ACCOUNT to the connection string.  I'm not sure how you can actually get the DOMAIN\ACCOUNT but if it's available somehow then you can just add it to the connection string.


Thursday, September 02, 2010 - 11:11:00 PM - mark seidelin Back To Top
ray

nice article - and have implemented in our cube - works well

but we have a problem when we are calling the cube from Reporting Services - we are using FormsAuthentication in Reporting Services - and havent found a way of getting theUserID from Reporting Services over to  SSAS.

do you know of a way (apart from Kerberos)


Monday, March 01, 2010 - 9:06:11 AM - teodorom Back To Top

Tried. NO way ! NOTHING works !


Monday, March 01, 2010 - 8:17:45 AM - raybarley Back To Top

I think this means that you're going to have to make sure you have correctly deployed your DLL to Analysis Services.  In particular what I have done to make this work is:

Deploy the assembly to the cube rather than the SSAS server

Set the Impersonation Mode to Service Account

Set the Permission Set to Unrestricted

You can review these things in the Teo Lachev book as well.  I have that one too and it's a good one.

As for me personally I would rather find an MDX solution than rely on calling out to external .NET code.  There are just too many things that go wrong and ther error messages are useless.


Monday, March 01, 2010 - 7:58:18 AM - teodorom Back To Top

Same with

WITH MEMBER [Measures].[PctDiff] AS
 MyNetAssembly.PctDiff(3, 4)
SELECT [Measures].[PctDiff] ON 0
FROM [Adventure Works]
;

It seems that the two environments are unable to exchange any metadata. But why ?


Monday, March 01, 2010 - 7:51:40 AM - teodorom Back To Top

Thank you, but this is not the problem.
I started exploring the problem affording a more complex application (a sample for dimension security in the Teo Lacev book).
Then I was able to reproduce the same problem with a much simpler application.
Anyway the same proble happens even if I write
public static double PctDiff(int a, int b)
Thanks
Teodoro Marinucci


Monday, March 01, 2010 - 7:43:48 AM - raybarley Back To Top

Your function takes 2 parameters of type double.  It looks like you're passing in 2 integers (I'm assuming [Date].[Fiscal].[Fiscal Year].&[2003] would be an integer in your date dimension). 

Another point is that you really don't need a stored procedure to perform this calculation.  For example you could do something like this:

with
member measures.fy02sales as
([Measures].[Reseller Sales Amount], [Date].[Fiscal].[Fiscal Year].&[2002])
member measures.fy03sales as
([Measures].[Reseller Sales Amount], [Date].[Fiscal].[Fiscal Year].&[2003])
member measures.pctdiff as
(measures.fy03sales - measures.fy02sales) / measures.fy02sales
, format_string="PERCENT"

select
  { measures.fy02sales, measures.fy03sales, measures.pctdiff } on 0
from [Step-by-Step]

This is NOT from the AdventureWorks cube, but a similar one.  The results from the above MDX query are:

fy02sales           fy03sales            pctdiff

$16,288,441.77  $27,921,670.52   71.42%

 

If you like books, I've found Microsoft SQL Server 2008 MDX Step by Step to be a good one and that's the sampel cube I used for the above MDX.

 

 


Monday, March 01, 2010 - 5:31:58 AM - teodorom Back To Top

I post my question in this thread because I have a related problem: my AS2008 Stored Procedures do not work at all !
I defined a (very simple) Stored Procedure by:
using System;
using System.Collections.Generic;
using System.Text;

namespace MyNetAssembly
{
    public class StoredProcedureExample
    {
        public static double PctDiff(double a, double b)
        {
            return (a - b) / b;
        }
    }
}
Compiled, registered and tested using:
WITH MEMBER [Date].[Fiscal].[PctDiff] AS
 MyNetAssembly.PctDiff([Date].[Fiscal].[Fiscal Year].&[2003],
  [Date].[Fiscal].[Fiscal Year].&[2004])
SELECT [Date].[Fiscal].[PctDiff] ON 0
FROM [Adventure Works]
WHERE [Measures].[Internet Sales Amount]
Doing this I get in return:
Executing the query ...
Internal error: An unexpected exception occurred.
Internal error: An unexpected exception occurred.
Internal error: An unexpected exception occurred.

Execution complete
Please help ! Iím becoming crazy !
My system is WS 2008 EE 64bit, AS2008, VS2008.
Many Thanks
 Teodoro Marinucci

 


Friday, January 08, 2010 - 6:43:43 AM - raybarley Back To Top

I don't have any insight on that error. 

The approach in the tip is good where the logic for determining what dimension rows people can access is more complicated than what you can do in the cube itself. 

There are two alternatives: if you have a small number of people you could accomplish the same thing by just mapping the allowed or not allowed list by user or group - see this tip for an example: http://www.mssqltips.com/tip.asp?tip=1834

Another approach is to use an MDX expression to specify who can see what dimensions.  Take a look at this article: http://bisqlserver.blogspot.com/2009/01/advanced-dimension-data-security-with.html

My preference using MDX expressions; it eliminates relying on stuff outside of the cube; i.e. stored proc, C# code, assembly, etc.


Friday, January 08, 2010 - 5:58:41 AM - mamtams Back To Top

Hi Ray ,

 I am retreiving ProjectID from SP.I have set my expression text as

expr.ExpressionText = string.Format("[User_Access].[ProjectID].&[{0}]", r[0].ToString());

Still I am not able to get the filtered data.

First Test case:

I tried to call the class library through a console application which has all the required references, I try to debug my C# code I get an error as

AccessViolationException unhandled on Expression Text

The other point of discussion is

Basically my project contains

Dimensions :

D_Project (It has project ID and other details)

D_Employee(It has username and employeeid)

I have a table called as User_Acess that contains

UserID Identity column,EmpID, ProjectID

Now using this table I am trying to filter out my Project data.

Please guide me if I am wrong anywhere.

I have to complete this task by Monday(IST). Please help me 

 

 

 

 

 

 

 


Thursday, January 07, 2010 - 7:48:26 AM - raybarley Back To Top

I think what's wrong is this line:

expr.ExpressionText = string.Format("[Project].[Project].&[{0}]", r[0].ToString()

);

Your stored proc returns  EmpID and ProjectID  so you want the 2nd column not the first.  You have r[0].ToString() which is the first column.  Try r[1].ToString()

 


Thursday, January 07, 2010 - 7:19:57 AM - mamtams Back To Top

Hi,

Please ignore the earlier post.

I am not getting any errors now . But when I am launching excel I am not getting the filter data

C# function

using System;

using System.Collections.Generic;

using System.Text;

 

// add namespaces

using System.Data;

using System.Data.SqlClient;

using Microsoft.AnalysisServices.AdomdServer;

namespace SSASLibrary

{

public class SSASLibrary

{

public static Set FilterProjectByUser(string UserAccount)

{

Expression expr = new Expression();

SetBuilder sb = new SetBuilder();

using (SqlConnection cn = new SqlConnection())

{

// prepare connection and command

cn.ConnectionString =

string.Format(

"data source={0};initial catalog={1};integrated security={2};",

"IND-MHP1W3S0031",

"Productivity_DM",

"sspi"

);

cn.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = cn;

cmd.CommandText =
"dbo.FilterProjectByUser";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@UserAccount", UserAccount);

// execute stored procedure

SqlDataReader r = cmd.ExecuteReader(CommandBehavior.SingleResult);

bool hasRow = r.Read();

// iterate through the result set

while (true == hasRow)

{

// add each member key to MDX set

expr.ExpressionText = string.Format("[Project].[Project].&[{0}]", r[0].ToString()

);

Member m = expr.CalculateMdxObject(null).ToMember();

TupleBuilder tb = new TupleBuilder();

tb.Add(m);

sb.Add(tb.ToTuple());

hasRow = r.Read();

}

// return MDX set

return sb.ToSet();

}

}

}

}

My Stored Proc is

CREATE PROCEDURE dbo.FilterProjectByUser

@UserAccount nvarchar(50)

AS

BEGIN

SET NOCOUNT ON

SELECT EmpID,ProjectID FROM dbo.User_Access A JOIN

dbo.D_Employee B ON A.EmpID= B.EmployeeID

WHERE B.USERNAME=@UserAccount

END

 

 

D_Employee contains  empid,username

User_Access contain empid,projectid,

D_project contains empid, projectid : I am trying to filter data on D_project

Pls advice.

Thanks in advance. 

 

 

 


Wednesday, January 06, 2010 - 11:32:45 PM - mamtams Back To Top

Hi ,

I have covered all the steps mentioned in your article.

I have also added the reference to that dll in my SSAS project with

properties for security settings as impersonation as "Service

 Account" and permission set as "Unrestricted".

 


Wednesday, January 06, 2010 - 8:26:03 AM - raybarley Back To Top

You said you copied the DLL to your machine.  In addition to putting the DLL on your machine you have to add a reference to the DLL to your SSAS project; see the section of the tip "Deploying the Assembly to SSAS"


Wednesday, January 06, 2010 - 7:00:51 AM - mamtams Back To Top

I have written the C# code in my colleague machine and copied that dll in my machine. In my machine I opened an analysis project and I called this dll.

At the time of creating role in Dimension data-Advanced tab : I am call this function  for eg as

 testlibrary.filterprojectbyuser(UserName).

 I am getting an error as the above function does not exist.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools