I have an application for which AS2005 with a pivot table front-end
would be IDEAL, except that my dimension hierarchies are many-to-many
(ie products may include "Phones" with children "Landline phones" and
"Mobile phones", but "Mobile phones" may also be a child of "Mobile
devices"). I was told that AS2005 can support such many-to-many
hierarchies, but I haven't been able to figure out how. Can you
provide any leads or explanations? Thanks.

Yes it can.

If memory serves, the analysis services tutorial that is part of books
online walks through an example.

Good luck

Yes the documentation says it does, though it may be a bit misleading.

In fact, to create a many-to-many relationship requires an intermediate
fact table and an intermediate dimension table.

Join the intermediate fact table to the intermediate dimension table
and the original fact table.
Join the intermediate dimension table to the fact table.

This creates a circular reference and allows multiple many-to-many
relationships to be defined.

You set to the dimension to many-to-many on the dimension usage tab.

Hope this helps

Steve Muise
Neudesic LLC

Thanks for your advice. I already did try to follow the books online
pages regarding many-to-many, but they did not clarify how to do this.
They appear to illustrate how to establish a many-to-many relationship
ACROSS two different dimensions (for example, many Customers to many
Products), but I need to establish such WITHIN ONE dimension (for
example, within Products, "Mobile phones" may have many parents and
many children, and each of its children may have many parents and many
children, and so on for every level within this parent-child dimension.
Since I don't even know how many levels this dimension may have
tomorrow, I don't think that I can manually create intermediate
dimension tables to support each link. Besides the woefully unclear
books online, is there another comprehensive explanation of how to do
this? Thanks again.

It is possible to post some sample records to illustrate your situation.
I am wondering if may you could use multiple hierarchies or make use of
attributes to achieve a similar goal.

One of my concerns with the situation you are describing is how the
users would navigate around. You might end up creating something so
technically elegant that the users cannot comprehend it

If you can come up with a small sample it would help the group provide
some more suggestions.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Darren, I'd be grateful if you or anyone can respond despite my slow
reply. I have built something relatively complete using SQL queries to
the relational database, so I know how to provide a good UI, and now I
want to duplicate this in Analysis Services to achieve better
performance. What I have are numerous dimensions that are each
parent-child dimensions with many levels and with lots of many-to-many
relationships within each parent-child dimension (not across them).
Some examples: There is one dimension called Product that has a lot of
levels, and some records include Phones, which is subcategorized into
children called Wireline Phones and Wireless Phones. Wireless Phones
is further subcategorized into children called 2.5G Wireless Phones and
3G Wireless Phones, but Wireless Phones is also a subcategory (a child)
of Handheld electronic devices, whose other children include PDAs and
handheld video games. So Wireless Phones has
many-to-many-relationships. I need to enable that kind of many-to-many
relationships within the Product dimension (among others) while
maintaining the ability to continue adding more members at any levels
that I want. And I need the user (this app is ONLY for a very small
group of power users) to be able to edit these hierarchies easily in
real time, adding and modifying members and their relationships to one
another. Any suggestions?

Peter,

Sorry for not replying sooner, I think I have a method that may achieve
what you are after. It is a little involved and I noticed someone else
asking a similar question, so I put an article up on my blog about it.

http://www.geekswithblogs.net/darren...0/24/57812.asp
x

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Try here:

http://www.sqlserveranalysisservices.com

I wrote up something a while ago -might help.

Cheers,
RIchard

--
_______________
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.


"Peter" <psmoore (AT) gmail (DOT) com> wrote