Hi,

I have a GroupsAndProducts dimension with following characteristics:

1. 4 attributes (GroupID, ProductID, GroupName, and ProductName).
2. Each group can have multiple products and each products can be in
multiple groups.
3. Primary key - (GroupID + ProductID).
4. Hierarchy - (GroupID --> ProductID).
5. Linked with Sales fact table through ProductID (which is not its
primary key).

The requirement is to find out the sales for each group.
This is working fine in SQL 2000 but SQL 2005 is giving errors.

The sales of each group is shown the total sales in the fact table
instead of aggregating the sales of only those products that are under
it. When the group is drill down to product level, the sales for each
product is shown right.

Any help will be very much appreciated.

Thanks
Mahesh







*** Sent via Developersdex http://www.developersdex.com ***

One other possibility to consider is that this sounds like it may be a
many-to-many relationship.

ie. One product can be in many groups and one group can have many
products. If this is the case, in AS2k, the "All" amount would have more
than the sum of the facts for each product.

In AS2k5 you could model this using a "true" many-to-many relationship,
using a bridging table to hold the GroupId, ProductId relationship and
then another Product dimension table holding one row for each product
and its attributes.

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

In article <#HPE9YYMGHA.500 (AT) TK2MSFTNGP15 (DOT) phx.gbl>, maheshnp (AT) hotmail (DOT) com
says...

Thank you very much for the responses.

Many to many relationship solved the problem.

As Darren said AS2K was showing higher sales amount than the sum of the
facts for each product. I think if a product is in two groups, it was
aggregated twice and so on. This problem is solved in AS2K5 with many to
many relationship.

Thanks
Mahesh






*** Sent via Developersdex http://www.developersdex.com ***