I am in the process of constructing a SSAS cube for a client and ran into the following issue:

The client is doing organisational analysis and needs to be able to analyse the all relevant measures based of the organisational structure as it was at a certain point in time.

The setup is as follows. The organisational structure is a ragged hierarchy which is stored in a Type 2 fashion with all the relevant effective dates and states. The facts (measures) are linked based of a surrogate key. I have set all the relevant SCD types on the organisational structure dimension attribute type properties in SSAS.

The question is, is there any articles or pointer that can assist in providing the ability via Excel such that a user can specify the date of the organisational structure and have the structure as at that date reflect? While still being able to see and interact with all other information (both current and historic).

The functionality can be provided via SQL by grouping on the business key and filtering the organisational structure based on the given date. I have searched through the documentation of SSAS and various articles but have thus far not been able to find a solution.

Any help or pointers would be appreciated.

Thanks in advance, Jacques Buitendag

With the difficulty in finding information on this, I have to assume that the request cannot be serviced by SSAS.

Can someone please at least confirm this.

Thanks in advance, Jacques Buitendag