Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.