Post reply

Representing Security Hierarchies is OLAP

  • September 11, 2007 at 8:18 am

    #180820

     

    Hi All,

     

    I’ve had a good hunt around the internet on this one but haven’t found quite what I need. 

     

    The company I’m working for is doing a feasibility study/proof of concept for a data warehouse utilising SQL Server 2005/2008 analysis services and reporting services for report delivery.

     

    A security hierarchy will exist based upon organisation level/cost centre etc. where if you exist at a certain level you will be able to see descendant nodes.  I’ve represented this type of hierarchy before in oltp apps but not in OLAP, is this possible? Could anyone point me at a source of information as to how to implement this granularity?

     

     

    Many Thanks

     

  • September 11, 2007 at 10:14 pm

    #732998

    Yes, it's generally quite straight forward. However, remember that security in MSAS is based either on the dimensional characteritcs or on item data. (As opposed to a hieararchy definition).

    You'll first need to layout your business rules arround the security model (i.e. in most security models, you can get access at multiple levels and multiple types). However, I will suggest the following:

    • At it simplest, you can grant access (via roles) to specific nodes.
    • For a simpler model, you should be able to use the user function at the data points to turn access on / off dynamically.
    • You'll probably need to implement a many to many dimension.
    • Take a look at the parent-child dimension. But remember, it's a one to many relationship (i.e. can a user id exist in more than one location?)

    Some of this may depend on how many data rows you're planning and the type of hardware you'll run on.

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

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