Post reply

Question about Dynamic Dimension Security through SSAS Stored Procedure

  • July 26, 2010 at 10:46 am

    #224625

    We are trying to use dynamic dimension security as documented in various articles on the web but hit a little snag...

    Through a C# DLL, we build a set of allowed members from data returned by a SQL Server stored procedure.

    We identify these by their business key.

    For various reasons (cube looking at a subset of a dimension, cube not processed since last DW Dimension update) we may return some business keys/dimension members to the cube that actually do not exit in the cube.

    The cube seems very unhappy about this and returns nothing to the end user.

    Our C# code looks like this

    Expression expr = new Expression();

    foreach (string tupleString in businessKeys)

    {

    expr.ExpressionText = tupleString;

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

    tupleBuilder = new TupleBuilder(member);

    Tuple tuple = tupleBuilder.ToTuple();

    resultSetBuilder.Add(tuple);

    }

    I was hoping that expr.CalculateMdxObject(...) would have done some kind of validation but it does not seem to be the case...

    Question: how could we make sure than we only return valid members to the cube?

  • July 26, 2010 at 2:46 pm

    #1198447

    I just discovered that I might get away with generating a string in my SSAS Procedure instead of a set.

    Then I can use the MDX function STRTOSET() to return a set... and it does not blow up if mentioning a member that does not exist as long as the dimension is defined with MdxMissingMemberMode set to Ignore.

    I would make sure to specify the full path of each member for performance reason.

    How cool is that? 😀

    Any feeling?

  • July 29, 2010 at 5:31 am

    #1199717

    For those interested, this article explains the solution I found in details

    http://dwbijourney.blogspot.com/2008/10/dynamic-dimension-security-in-ssas.html

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply