Analysis Services Default Member Through Stored Procedure

Today, I had to dynamically assign default member value to an analysis services dimension. Scenario: Each user (AD login) has a preferred or default value assigned on time basis. So depending on which date data is sliced it always comes up with the value default of the current user.

For this we would need to create a mapping table in Relational database with date, user and default value.

Create a basic User Stored Procedure (uspGetUserDefaultValue) which takes username (AD Login) as a parameter, selects on the mapping table based on username passed and returns a single value.

Now we create a solution in Visual Studio 2008. This can be of class project template. We would be creating a custom DLL and register with SQL Server Analysis Services solution. Where SSAS solution would use function calls exposed by custom DLL. I created the project as part of my SSAS solution for better manageability.

We would need to add:

Microsoft.AnalysisServices

Microsoft.AnalysisServices.AdomdClient

as reference to our custom dll project. These can be of version 10 (SQL 2008) or version 9 (SQL 2005). Works fine with either, seamlessly interchangeable.

Now we create/rename our namespace and class to like

Project/Assembly: AnalysisServicesCustomLibrary

Namespace: CustomMethodCollection

Class: CustomMethods

or whatever you like.

Create a public static function like:

public static string GetUserDefaultValue(string userName)

{

OleDbConnection connection;

        try

        {

         connectionString ="provider=sqloledb;server=;database=;trusted_connection=yes";

           connection= new OleDbConnection(connectionString);

           connection.Open();

           OleDbCommand command= new OleDbCommand("uspGetUserDefaultValue", connection);

               command.CommandTimeout = 0;

               command.CommandType = CommandType.StoredProcedure;

               command.Parameters.Add(new OleDbParameter("@UserLogin", OleDbType.VarChar, 50));

               command.Parameters[0].Value = userName;

               

               DataTable dataTable = new DataTable("DefaultValue");

               OleDbDataAdapter dataAdapter= new OleDbDataAdapter(command);

               dataAdapter.Fill(dataTable);

               connection.Close();

               if (dataTable.Rows.Count == 0)

                   return "empty";

               string defaultValue = dataTable.Rows[0][0].ToString();

               return "[Dimension].[Dimension Attribute].&[" + defaultValue + "]";

           }

           catch (Exception ex)

           {

               errorMessage = "Some error: " + ex.Source + " Message: " + ex.Message;

               throw new Exception(errorMessage, ex);

           }

       }

The return part is critical since here we would be formatting the return value into a MemberUniqueName for the associated dimension attribute. Else is basic stuff, feel free to add better exception handling etc.

Do a quick test to see if you are getting the desired output from Stored Procedure and Class Function.

Now we move to Analysis Services solution. First step is to add reference to AnalysisServicesCustomLibrary project under Assemblies folder.

Next move to the dimension where you would like to have a default value. Go to properties of the attribute that would have the default value. Under DefaultMember enter:

STRTOMEMBER( AnalysisServicesCustomLibrary.CustomMethodCollection.CustomMethods.GetUserDefaultValue(UserName))

Here UserName is an Analysis Services keyword to get current logged in user’s account.

STRTOMEMBER converts the string passed by custom library’s function in a member.

Build/Deploy/Process cube. Run a query or browse the cube and you should have the default value against logged in user.

DEBUG

To debug your code, move to your custom library project. Go to Debug-->Attach to Process and select Msmdsrv.exe process. Add a breakpoint and try querying the dimension using the function.

References:

Debugging Stored Procedures