# Sql-server – Dimension design and MDX query issue

mdxsql-server-2012ssas

I'm new to MDX and SSAS but trying to learn. I'm on SQL Server 2012.
I have the following table setup.

My dimension relationships look like this:

+-----------+----------+-------------------+------------------------+
| Dimension | Measure: |       Fact        |        FactDimB        |
+-----------+----------+-------------------+------------------------+
| DimA      |          | Regular, A_Key    | Regular, A_Key         |
| FactDimB  |          | M2M with FactDimB | FactRelationship, self |
| DimC      |          | M2M with FactDimB | Regular, C_Key         |
+-----------+----------+-------------------+------------------------+


When I query it like

SELECT [Measures].[Amount] ON COLUMNS,
[DimC].[Hierarchy].members ON ROWS
FROM [MyCube]


I get

+---------+--------+
|  NameC  | Amount |
+---------+--------+
|         |    100 |
|         |    100 |
|         |    100 |
|         |    200 |
+---------+--------+


It seems to sum up fine using the parent child hierarchy. But is it possible to get some of the attributes from DimA into my query without crossjoining DimA with DimC?

I would like to get the following result:

+---------+-------+--------+
|  NameC  | NameA | Amount |
+---------+-------+--------+
| Header1 |       |    200 |
|         | A1    |    100 |
|         | A6    |    100 |
| Header2 |       |    300 |
|         | A3    |    100 |
|         | A9    |    200 |
+---------+-------+--------+


Or, this should be good for a pivot table:

+---------+-------+--------+
|  NameC  | NameA | Amount |
+---------+-------+--------+
| Header1 |       |    200 |
| Header1 | A1    |    100 |
| Header1 | A6    |    100 |
| Header2 |       |    300 |
| Header2 | A3    |    100 |
| Header2 | A9    |    200 |
+---------+-------+--------+


or

+---------+--------+
|  Name   | Amount |
+---------+--------+
| A1      |    100 |
| A6      |    100 |
| A3      |    100 |
| A9      |    200 |
+---------+--------+


If I crossjoin DimA and DimC the numbers still add up, but I get alot of duplicated rows. I've also tried creating calculated measures using WITH MEMBER but that has not worked out.

I can't create a dimension that combines tables DimA, DimC and FactDimB so that i can use attributes from them all due to the foregin key relationship between them. SSAS throws an error about not being able to join the tables together.