AS 2005 Dimension Modelling Question



Hi guys,

Hopefully this is the right newsgroup for my question :) ...

A client has a cube that is built over JDE. The fact table consists of
Postings, which could be of three different types (Job, General Ledger,
or Balance ***).

There are various dimensions, the most important one being the Business
Unit dimension, consisting of a single hierarchy (top to bottom),
Company->Zone->Region->Division->Department->Job->Sub-Job.

The client is after a dimension for slicing by Posting type. But they
want a slightly more complicated dimension that achieves the following:
1. Slice facts by a type called RegionTrack, which aggregates all
Balance ***, General Ledger and Job postings.
or
2. Slice facts by a type called GLTrack, which aggregates all General
Ledger and Job postings.
or
3. Slice facts by a type called JobTrack, which only aggregates Job
postings.

Additionally, they want to restrict access to each type described
above.
eg: Engineers only see JobTrack while Accountants only see GLTrack ...

I am not sure how to successfully model these aggregations within a
single dimension that can also have security applied easily.

Any suggestions would be much appreciated on modelling this within
Analysis Services 2005.

Many thanks,
Jonathon

.