Joining Measures and Dimensions

In order to retrieve measures per specific level value, issue a join between the Measure view and any Dimension or set of dimensions. For example, issuing the following will retrieve the number of customers in each city:

SELECT m.[Customer Count], c.[City] 
FROM [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Customer AS c 
INNER JOIN [AdventureWorksDW2012Multidimensional-SE].[Adventure Works].Measures AS m

Note that there is no ON condition necessary. That is because tables are already related appropriately in SQL Server Analysis Services. If you are using a tool that requires ON conditions, set IncludeJoinColumns to true. This will append a number of foreign key columns to each view which will relate them to one another another. These columns will not return data on their own, but may be picked up on automatically with tools to construct the ON conditions for joins where needed.

For more information on other types of join, see Retrieving Analysis Services Data