Many-to-Many MDX

I have a measure called MyMeasure.

There is a dimension called Account

There is a dimension called AccountGroup that groups many accounts so it references the cube through a Many-to-Many relationship. The account group has a field called Group Level. If for example, I want to know the number of accounts for a region I would do something like this:

SELECT {[Account Group].[Account Group].[New South Wales] } ON ROWS,

{[Measures].[MyMeasure]} ON COLUMNS

FROM [MyCube]

WHERE ([Account Group].[Group Level].[Region])

This will give me:

--

| Account Group | MyMeasure |

--

| New South Wales | 35 |

--

I need the MDX query to get MyMeasure by grade which is another Account Group with Group Level 'Grade' for a particular region (Another Account Group with Group Level 'Region'). The result I am looking for, will look something like this

--

| Account Group | Account Group | MyMeasure |

--

| New South Wales | Grade A | 15 |

| New South Wales | Grade B | 4 |

| New South Wales | Grade C | 6 |

| New South Wales | Grade D | 10 |

-

It is like getting the measures for all accounts that belong to both group levels (one with a known name) grouping by accounts. I appreciate any help ...

Any ideas?...|||

Hi Mario,

Could you explain the AccountGroup and the intermediate measure group source data a bit more - I still can't understand the purpose of this many-many dimension, from a data modelling perspective?

|||

In the DW: every fact table has an AccountKey:

DimAccount (AccountKey, Name,...)

BridgeAccountToAccountGroup (AccountKey, AccountGroupKey)

DimAccountGroup (AccountGroupKey, Name,...)

In the cube, I have DimAccountGroup as a dimension but "behind the scenes" it is referencing DimAccount as a Many-to-Many dimensions through the BridgeAccountToAccountGroup "Measure Group".

|||

That's the technical detail, but from your example, it seemed like the members of AccountGroup were so diverse (eg: New South Wales, Grade A) that they normally would belong in different dimensions. Any insight or reason for that?

An easier option might be to translate your requirement into Adventure Works - for example, using the SalesReason dimension, which has many-many relations. You did mention this in your new post, but can you give a more detailed scenario, using actual data from Adventure Works?

|||

Deepak,

I know attributes like region and all that should be part of the Account dimension, the thing is that some accounts can belong to many region groups or grades. Groups are created by users in the OLTP system so we don't have any control over these, this is why I had to create a Many-to-Many relationship.

For example the DimAccountGroup has the following attributes.

DimAccountGroup (AccountGroupKey, Name, Level)

This dimension could have the following attributes:

1, New South Wales, Region

2, Queensland, Region

3, Grade A, Grade

4, Grade B, Grade

The bridge table will tell me the relationships between Accounts and Account Groups. So what I am trying to do is to get some measures for a set of accounts that belong to two or more (intersection?) predefined account groups but I don't want to see the measures by account, just by account group.

If I wanted to do the same thing with different dimensions it would be very easy:

SELECT
NON EMPTY {
[Product Group].[Product Group].[All].Children
} ON ROWS,
{
[Measures].[Sales]
} ON COLUMNS
FROM [MyCube]
WHERE (
[Account Group].[Group Level Identifier].[Region],
[Account Group].[Account Group].[New South Wales],
[Product Group].[Group Level Identifier].[Brand]
)

The problem is I am using the same dimension twice and it doesn't have a direct relation to the fact table, it is indirectly referenced through the bridge table and the account table (which is another dimension). It is like trying to do something like this (I know this query is wrong):

SELECT
NON EMPTY {
[Account Group].[Account Group].[All].Children //Account group "2"
} ON ROWS,
{
[Measures].[Sales]
} ON COLUMNS
FROM [MyCube]
WHERE (
[Account Group].[Group Level Identifier].[Region], //Account Group "1"
[Account Group].[Account Group].[New South Wales], //Account Group "1"
[Account Group].[Group Level Identifier].[Grade] //Account Group "2"
)

|||

Mario,

Here are sample SQL and MDX queries from Adventure Works, using the SalesReason many-many dimension. These compute Internet Sales Amount for all orders which have both a [Quality] and a [Manufacturer] reason. Hopefully, it is similar to what you want to do with [AccountGroup]:

SQL:

>>

select count(*) as SalesOrders,

sum(fs.SalesAmount) as SalesAmount

from dbo.FactInternetSalesReason fr1

join dbo.FactInternetSalesReason fr2

on fr1.SalesOrderNumber = fr2.SalesOrderNumber

and fr1.SalesOrderLineNumber = fr2.SalesOrderLineNumber

join dbo.FactInternetSales fs

on fr1.SalesOrderNumber = fs.SalesOrderNumber

and fr1.SalesOrderLineNumber = fs.SalesOrderLineNumber

where fr1.SalesReasonKey = 5

and fr2.SalesReasonKey = 9

-

1551 5549896.77

>>

MDX:

>>

select {[Measures].[Internet Sales Amount],
[Measures].[Internet Order Quantity]} on 0
from [Adventure Works]
where Intersect(Exists(
[Internet Sales Order Details].[Internet Sales Orders].[Internet Sales Order],
{[Sales Reason].[Sales Reason].[Quality]}, "Internet Sales"),
Exists(
[Internet Sales Order Details].[Internet Sales Orders].[Internet Sales Order],
{[Sales Reason].[Sales Reason].[Manufacturer]}, "Internet Sales"))

Internet Sales Amount Internet Order Quantity
$5,549,896.77 1,551

>>

|||

Thank you very much Deepak,

I knew I had to use Intersect but I had no idea where to start. Here is the query I needed:

SELECT
NON EMPTY {
[Account Group].[Account Group].[All].Children
} ON ROWS,
{
[Measures].[MyMeasure]
} ON COLUMNS
FROM [MyCube]
WHERE
(
[Date].[Calendar Month Year].[Jul 2005],
Intersect
(
Exists
(
[Account].[Account].[Account],
{
(
[Account Group].[Account Group].[New South Wales],
[Account Group].[Group Level Identifier].[Region]
)
},
"Account To Account Group"
)
,
Exists
(
[Account].[Account].[Account],
{
(
[Account Group].[Account Group].[All],
[Account Group].[Group Level Identifier].[Grade]
)
},
"Account To Account Group"
)
),
[Account Group].[Group Level Identifier].[Grade]
)

In the DW the Bridge table is called BridgeAccountToAccountGroup which is a measure group in the cube called "Account to Account Group".

|||

Mario, I'm glad you found a solution - just wanted to add a nested approach, which might be more efficient:

SELECT
NON EMPTY {
[Account Group].[Account Group].[All].Children
} ON ROWS,
{
[Measures].[MyMeasure]
} ON COLUMNS
FROM [MyCube]
WHERE
(
[Date].[Calendar Month Year].[Jul 2005],
Exists
(
Exists
(
[Account].[Account].[Account],
{
(
[Account Group].[Account Group].[New South Wales],
[Account Group].[Group Level Identifier].[Region]
)
},
"Account To Account Group"
),
{
(
[Account Group].[Account Group].[All],
[Account Group].[Group Level Identifier].[Grade]
)
},
"Account To Account Group"
),
[Account Group].[Group Level Identifier].[Grade]
)