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.

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 |
+---------+--------+
| Header1 |    200 |
|         |    100 |
|         |    100 |
| Header2 |    300 |
|         |    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 |
+---------+--------+
| Header1 |    200 |
| A1      |    100 |
| A6      |    100 |
| Header2 |    300 |
| 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.

Best Answer

I've come to the conclusion that what i wan't is not possible in MDX. But it don't need to be. I also realized MDX is not SQL. SQL returns a dataset. MDX returns a dataset and metadata. Combine the two and I get what I wanted.

Pulling up my original query into an Excel PivotTable it was exactly what I was after. So it came down to SSMS limitations at displying MDX queries, and my own lack of understanding what MDX queries actually return. And what one really needs is some kind of cube analysis client to really benefit from MDX.

So the result of an MDX query is much more then just the dataset it returns but also all the properties you specify to get with it as metadata.

Related Question