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 Sheet).

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 Sheet, 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

The simplest way to do this could be to use calculated members on
Business Unit Dimension. If you want to split values of a group in
lower levels (Zone/Region/Division/...), you can also add GLTrack and
JobTrack attributes and filter on them (their value could be only a
Yes/No value).
An alternative way could be to use the Multiple Groups scenario I
described in my paper "The many-to-many revolution"
(http://www.sqlbi.eu/manytomany.aspx) that allows more flexibility (you
don't have to modify the cube structure just to add another possible
group, and you can always split values in all original hierarchy
levels). This approach can have lower performance if you have a lot of
members at leave level of Business Unit dimension (a lot means more
than 100.000).

It really depends on what you want to use to navigate and query the
cube. If you have fixed reports, you can rely on MDX calculations with
the traditional approach. The many-to-many dimensions based model is
useful when users want to navigate with a PivotTable tool.

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi


jonathonm (AT) intergen (DOT) co.nz wrote:

Hi Marco,

Thanks for the speedy reply. I had a look at your many-to-many document
and was impressed, it is the first document that I have seen that
covers advanced modelling concepts as well as the Analysis Services
implementation.

The multiple hierarchies section seems to match roughly what I wanted
to achieve. I was able to mock up a proof of concept tonight with a
similar approach, but I think I need to re-read the paper just to get
my understanding straight :S ...

I have uploaded a quick screenshot of the hierarchy layout that I was
able to create that covers the JOB/BIZ/REGION trak concept the client
was after without using MDX (hopefully the URL does not get scrambled):
http://www.imageshare.co.nz/upload1/...ierarchies.JPG

Many thanks! (and have bookmarked your blog)
Jonathon