I have one measure group uniquely keyed to an invoice line that indicates a part number and a quantity.
SOLine Part SoldQty 123 abc 1 456 xyx 3
I have another measure group that represents subcomponents sold as part of the top part number, also keyed to the invoice line; a one-to-many relationship.
SOLine Component ShippedQty 123 foo 1 123 bar 2 456 zod 3 456 non 3 456 ursa 6
I have dimensions for Part and Component. I can relate FactSales to DimPart and FactShipments to DimComponent in the DSV successfully. However, what I want to do is relate the two fact tables in a way that allows me to analyze at query time what components were shipped under each sale.
If I apply DimComponent to the SoldQty measure, I get the numeric sum of all rows in every cell.
Row Labels SoldQty foo 17456 bar 17456 zod 17456 non 17456 ursa 17456
If I apply DimPart to ShippedQty, I get no records.
Row Labels ShippedQty Grand Total 123456.12
I have tried relating the two fact tables directly in my dsv as well as creating an intermediate dimension of SOlines that they both relate to.
Linked Measure Groups appear to be for crossing cube definitions, not inside one cube, so I am at a loss as what to try next.