I have a pretty standard sales transaction table, with ordernumber/line_number primary key (two columns) and a variety of measures and dimensions.
I'm trying to add a new measure and dimension, which is the shipping method used by the shipper to deliver the product. The measure is the shipping charges that we incurred, and the dimension is the shipping method used by the shipper.
This issue is that in rare circumstances the shipper might use two different shipping methods (e.g., the package has a bad address, was returned to us, we fixed the address, and sent it next day air).
So, this measure is on the ordernumber/line number/shipping method grain.
I'd like to cube to let us view the charges by the different shipping methods (sliced by any of the other sales dimensions, of course), while summing the charges when the shipper shipping method is not chosen.
I have a table in SQL Server containing the ordernumber/line number/shipping method and shipping charges.
I have added the shipping charges as a new measure group in the cube.
But when trying to link the new measure group to the rest of the cube in the Dimension Usage tab, I am only offered the granularity attribute of shipping method, and am not afforded any way to link the new measure group to the fact table (shipping methods is a dimension which I added which otherwise has no connection to the fact table).
What am I doing wrong?
