In SSAS is there a way to have a dimension relate to a fact table based on two columns in the fact table?
We have two tables: Location (Dimension) and Sales (Fact). The Location dimension has one column: "state". The Sales table has three columns: "saleAmount", "customerState" and "billingState" (because our customer can be in California but wants us to bill a company or branch in New York).
In SQL, if we want to see all the sales in California, we write our SQL query as:
select sum(saleAmount) from Sales where customerState = 'California' or billingState = 'California'
Is there a way to accomplish this in SSDT when building my cube so that when I'm using Excel as an end user tool and I select the state attribute from the Location dimension and the saleAmount measure, the saleAmount will be based on customerState or billingState? (I do not want to have role playing dimensions here - where one Location dimension is based on customerState and another Location dimension is based on billingState. I want one dimension matching up with both columns at once.)
Not the way you're thinking, but you can achieve what you want by doing this:
Create a view on your fact table, that is a UNION of facts related to customerState and facts related to billingState. This means the view will only have 1 State column, and if a fact has different values for customerState and billingState, then it will have two rows in the view.
Use the view instead of your table to populate your measure group in the cube.
Link the measure group to the Location dimension on the single State column in the Fact view.
Builder beware, this results in duplicate counting of facts when rolling up states where a single fact is in two different states.