MDX SELECT conditionally

Question

I am new to MDX querying and working with multidimensional data, though a project requires me to learn.

We have a cube with a KPI which performs a Lag() that sums up the number of transactions that have met a requirement, based on a date.

A query would be something like

SELECT
{} on 0,
[Transaction].[Trader].Members HAVING
KPIStatus("REQ") < 0 on 1
FROM
[Cube]
WHERE
[Transaction].[Trade Date].[2014-07-02]

This will output all the traders that fulfill the criteria of the KPI.

From this, I would like to select all the [Transaction].[Trade ID] for a trader in the result from the above query. I only want the [Transaction].[Trade ID] in the same date range as earlier, since they are a part of what triggers the KPIGoal.

I've tried something like

SELECT
[Transaction].[Trader].&[trader_id] HAVING
KPIStatus("REQ") < 0 on 0,
[Transaction].[Trade ID].Members on 1
FROM
[Cube]
WHERE
[Transaction].[Trade Date].[2014-07-02]

but I get a System.OutOfMemoryException. This might be because of low RAM on my computer that SSMS eats up, but is there a better way to execute the query than the above example? Also, I believe this would result in a badly formated result. The trader can have very many trades with unique [Transaction].[Trade ID]'s. What could be a better approach for it?

---

Bonus question:

What is the difference of getting the results as row/column "headears" instead of values. The first query gives me one column with the result as "headers" such as how the categories below are encapsulated. What does it really mean?

Answer 1

Just use a cross product on the rows. Analysis services Autoexists should take care that you only see trade IDs belonging to their trader, as both hierarchies are in the same dimension:

SELECT
{} on 0,
[Transaction].[Trader].Members
*
[Transaction].[Trade ID].Members
HAVING KPIStatus("REQ") < 0 on 1
FROM
[Cube]
WHERE
[Transaction].[Trade Date].[2014-07-02]

I am not sure how your KPI is built, and if it will be affected by the additional hierarchy in the rows. If that would be the case, you could also use Filter:

SELECT
{} on 0,
Filter([Transaction].[Trader].Members, KPIStatus("REQ") < 0)
*
[Transaction].[Trade ID].Members
on 1
FROM
[Cube]
WHERE
[Transaction].[Trade Date].[2014-07-02]

With regard to your bonus question: An MDX query can have zero, one, two, or even more axes, which build the headers and span the cell space for the result cells (normally numbers). Each axis can have zero, one, two, or more hierarchies from which the member names for the labeling are taken. If you e. g. have three hierarchies on the rows axis, you get three header entries on each row, which are members of the three hierarchies. Most reports use two dimensions. And SQL Server Management Studio complains if you use more than two axes, as it does not know how to display the result, but this is not a restriction of MDX.

In your first query, you state that you want to have zero hierarchies on the columns, as you put the empty set on axis number zero, which is the columns axis. Then you put one hierarchy on the rows (axis number one). If you wanted to put a result into the cells, you could do so with calculated measures like this:

WITH Member Measures.[Id as measure] AS
[Transaction].[Trade ID].CurrentMember.Name
SELECT
{ Measures.[Id as measure] } on 0,
Filter([Transaction].[Trader].Members, KPIStatus("REQ") < 0)
*
[Transaction].[Trade ID].Members
on 1
FROM
[Cube]
WHERE
[Transaction].[Trade Date].[2014-07-02]

Of course, you can do much more with calculated members, which - in contrast to physical measures - can be strings and not only numbers, but this would be another question.