AS 2000 Replica dimensions Exceed the 3gb limit

Discussion in 'microsoft.public.sqlserver.olap' started by BigJimSlade, Apr 28, 2006.

  1. BigJimSlade

    BigJimSlade Guest

    Greetings,

    we currently have a fairly simple cube with 3 measures and 10 dimensions

    The cube has about 5 roles defined that use custom mdx to define the allowed
    members.

    The security is implemented using a addtional level for each dimension to
    be secured for example:

    ClientName
    Region
    State
    etc...

    We use the client name to restrict clients from seeing dimension members
    that belong to other customers.


    When we use the analysis manager GUI to test the roles, after clicking test
    role for each of the roles, the memory for analysis services shoots up to
    ~2.7gb and susequently analysis services becomes unrepsonsive; or crashes
    due to an out of memory errror.

    We are running with the /3GB switch enabled and the server has about 10gb
    of physical memory.


    We beleive this is due to replica dimensions being loaded into memory. Is
    there an alternate security apporach that we reduce the amount of memory
    required?

    would simply upgrading to SQL 2005 and porting the cube reduce memory consumption?

    We have heard that 64bit version removes the memory limitation but we are
    hesitiant to upgrade the server hardware without exploring alternate design
    options first.

    any advice?
     
    BigJimSlade, Apr 28, 2006
    #1
    1. Advertisements

  2. BigJimSlade

    Deepak Puri Guest

    Upgrading to AS 2005 should alleviate your memory issue:

    http://www.mosha.com/msolap/articles/yukondimensionsecurity.htmImprovements to dimension security in Analysis Services 2005
    ...
    Scalability of dimension security.

    The way dimension security was implemented in Analysis Services 2000 -
    was by creating special replica dimension (sometimes incorrectly called
    "shadow" dimension. Shadow dimensions actually have nothing to do with
    dimension security - they are simply snapshots done during DSO
    transaction and dimension writeback in order to preserve ACID properties
    of transaction). Replica dimensions weren't the entire portion of the
    dimension, they only contained unsecured members (plus their siblings -
    as empty placeholders if siblings were secured), and the replica
    dimensions were created only if necessary on the fly. Still, when
    dimension to be secured was big (millions of members), and number of
    different roles was big as well (hundreds), and unsecured part was
    relatively big - it could've caused scalability issues, because in
    Analysis Services 2000 all dimensions were always loaded into memory,
    and this includes replicas as well. In Analysis Services 2005 the
    implementation is different. Dimension security is implemented as
    virtual bitmap on the secured attributes. This means, that in the worst
    case, the overhead will be - one bit per attribute member per role. In
    reality the picture is even better, because those bitmaps are not always
    implemented as pure bitmaps. For example, if there are big contiguous
    spaces of attribute members which are secure - they will be compressed
    instead of using bit per member, some important particular cases are
    optimized as well - like when only single member on the attribute is
    allowed or denied etc. But, even if those bitmaps should become very big
    - they can be swapped out to disk - subject of standard Analysis
    Services memory manager algorithm, which knows how to swap caches,
    metadata, dimensions etc - including security bitmaps.
    ...

    - Deepak

    Deepak Puri
    Microsoft MVP - SQL Server
     
    Deepak Puri, Apr 29, 2006
    #2
    1. Advertisements

  3. BigJimSlade

    girtbysea Guest

    i would put clientname in a separate dimension and secure that.
     
    girtbysea, May 2, 2006
    #3
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.