We have an InfoCube that has sales data at the transaction level. We sell paint so the key figures are Gallons Sold, Taxes, Sale Amount, etc. Characteristics are Color (like red, blue, yellow, purple, green), Store, etc.
For one specific report the user wants to see the sales data broken down to the primary colors sold. He gave me a chart that has each of the colors we sell, what primary colors make up the color sold, and the amount of the primary color. Think of it like a recipe.
Color, Primary Color, Factor
Red,Red,1
Blue,Blue,1
Yellow,Yellow,1
Purple,Red,.5
Purple,Blue,.5
Green,Blue,.5
Green,Yellow,.5
What I thought about doing was bringing in this table as an infocube. Then creating a multiprovider between the sales infocube and the new infocube. I thought that since color was in both cubes it would join them together. Then what I had planned on doing was whenever I use Primary Color in a query I would multiply the Key Figures by my factor so that it wouldn't double count.
The new infocube looks fine, the problem is when I look at the multiprovider it doesn't join correctly. The key figures have empty values for Primary Color and vice versa.
Am I doing something wrong in my multiprovider? Or am I going about this totally wrong thinking that I can add more granularity by using multiproviders? What would you suggest?