Can anyone help with a basic Analysis Services cube question?
Here's the issue...
We're running SQL Server 2005 w/ all services.
We have a table called 'PartProduct' which has 3 columns: PartNumber, PartQty, and Product. The combination of PartNumber+Product is the key; a PartNumber can appear on many products, and a Product has many PartNumbers.
I can create a cube which shows me a pivottable with PartNumbers down the Y axis and Products across the X axis, so that I can compare how many of each Part is used on each Product. What I NEED, though, is a comparison how many Parts each Product has in common with other products, whether by the number of different parts or the sum of different parts:
Source Table:
PartNo PartQty Product
- -
PartA 4.0 ProductA
PartA 3.0 ProductB
PartA 4.0 ProductC
PartB 1.0 ProductA
PartB 1.0 ProductB
PartB 1.0 ProductC
PartC .5 ProductB
PartC .5 ProductA
PivotTable from a cube I've created:
PartNo ProductA ProductB ProductC
-- -- --
PartA 4.0 3.0 4.0
PartB 1.0 1.0 1.0
PartC .5 .5
What I need (#1 - total parts used between a pair of products; note that where a product crosses itself, the number of parts is doubled, since it's a pair of products):
Product ProductA ProductB ProductC
- -- -- --
ProductA 11.0 10.0 10.5
ProductB 10.0 9.0 9.5
ProductC 10.5 9.5 10.0
What I need (#2 - Number of different PartNumbers in a pair of products):
Product ProductA ProductB ProductC
- -- -- --
ProductA 3 3 2
ProductB 3 3 2
ProductC 2 2 2
I need a few other stats, but I think if someone can help me figure out how to generate these two tables, I can figure out the rest.
Any suggestions are DEEPLY appreciated. I'm an Analysis Services neophyte and I'm learning as fast as I can.
Thanks, all!
- Tom
