All,

Sorry if this is a question that is already answered, but
I have searched for a few hours without finding a good
answer. This Question involves SQL Server 2000 Analysis
Services, and A Type II Slowly Changing Dimension.

I have an employee parent-child dimension that is a type
II SCD. I have no problems with updating the dimension,
fact table or anything else along those lines, but am
having trouble Aggregating the data across time. For
example if a supervisor is chosen for a time period, I
need to show a total line of the supervisor, aggreagted
across time, and a line for each child of the supervisor
aggregated across time. If there is more than one record
for the supervisor for the time period than there is more
than one record for the children that have physically
reported to that person during the time period.

I have come up with a couple of solutions that require
aggregation of each data point individually. What I want
though is a solution that is more general. For example in
the Article OLAP Services: Managing Slowly Changing
Dimensions, Amir Netz
(http://msdn.microsoft.com/library/en-
us/dnolap/html/slowly2.asp)suggests a general solution in
the section Generalizing the Calculations: The
Consolidation Dimension, that involves a "[Fragmented]"
Dimension, with a single level and a single member.
Unfortunatly I cannot figure out how to implement this.

Any suggestions as to how to implement the solution in
the article or other suggestions on how to implement a
report that behaves as described would be greatly
appreciated.

If you are asking about how to create the Consolidation Dimension
itself, it should be along the lines of an analytical Utility Dimension,
commonly used for Time Series calculations. This group has past posts
describing how to create a Utility Dimension, such as below from 2002:

http://groups.google.com/groups?q=sp...on&hl=en&lr=&i
e=UTF-8&oe=UTF-8&selm=3D66285F.F69BE1E5%40dsslab.com&rnum=1
A time analysis utility dimension has no all level and 1 real member in
a dimension table named something
like "Current" with a key value like 0 or 1. You can create and process
the dimension as usual. Bring the
dimension into the cube, join it to the fact table on any column of the
fact table, and set the member
key in the cube the constant 0 or 1 (matching Current's key).

Add calculated members to the cube on this dimension:
...

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!