January 9, 2007 at 1:19 pm
This seems like such and easy question to answer, I just don't think I know the proper terminology that AS2000 uses. In Essbase, this was called a shared member, meaning that a memmber could appear multiple times in the same dimension.
What's it called and how do you do it elegantly in as2000?
Let's say I have a dimension called SalesOffices. I have 10 offices, each of which has a geographical component, a Manger component and a Size component (I'm making this up.) Picture the heirarchy having 3 main branches right off the root.
Certain users like to drill into the office data by geography: North->Minnesota->St.Paul->Office#4
Others like to go in by manager. Jones--> Brown-->Office#4
Others drill in by size: LargeOffices-->Buildings--> GreaterThan100--Office#4
This makes Office #4 (and all other leaf level offices) show up in three spots in the hierarchy.
When I went to build this in Parent-Child it complained that I didn't have unique member keys or something. I decided then to create a Level style hierarchy. This *does* work, but now due to the nature of the SELECT statement that populates the cube, I have tripled my data load. (it would have been less data to load if i made 3 separate dimensions, one for each hierarchy branch for SalesOffice.
When Office#4 comes in the fact table, it joins to SalesOfficeDimensionTable 3 times because "Office#4" appears 3 times in that table.
There's got to be a better way.. I just can't seem to find it. Somebody please point me to the article or even phrase that best describes what I'm trying to accomplish. Is it custom members? custom rollups? multiple hierarchies, shared dimensions, private dimensions..? I'm at a bit of a loss.
Any help will be appreciated.
thanks
Bruce
January 9, 2007 at 3:22 pm
Hey Bruce,
If I understand you correctly t's multiple hierarchies you're after. In AS2K the way to achieve these is to name the dimension in the pattern of <dim_name>.<hierarchy_name> (yes, that's a period/dot/full stop in there). For e.g. Offices.ByManager , Offices.ByGeography, Offices.BySize.
So, even though their called hierarchies, they will appear (in some places) as if they're seperate dimensions, and you'll need to create them as if they are seperate dimensions. The client tools are then provided the dimension with the hierarchies.
Some general points:
this is handled much better in AS2K5, the hierarchies are built within the dimension (they're called User Hierarchies if you go looking in the doco).
the display of these in AS2K dev enviornment is not as nice as AS2K5 or say Cognos Transformation Server (Transformer) where the hierarchies are shown to exist within the dimension.
some client tools (acutally I think this is dictated by the AS2K engine) won't let you put hierarchy 1 on rows and hierarchy 2 on columns, as really they're treated as a single dimension object (ie can't compare our offices by manager with our offices by size).
HTH,
Steve.
January 9, 2007 at 7:11 pm
Thanks very much Steve!
I'll give this a try ASAP.
At least I know what they're called now! When I had initially come across this subject in my docs, they didn't seem to make the point that this is what they were for.
thanks
Bruce
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply