Is it possible to do sub-MX as one can do in SQL?

E.g. selecting a subset of customers who did a specific kind of
transaction, and then query other issues on these customers.

An SQL example could be:
SELECT sum(time_spent) FROM tbl_transaction
WHERE customer_id IN (SELECT customer_id FROM tbl_transaction WHERE
type=' ABC')

i.e. doing the aggregate on all transation time spent for these
customers, and not only where they did the 'ABC' type of
transaction.

How is this done in MDX. A splice seems not to do the trick. My scope
is AS2005

Thanks
JE

http://msdn2.microsoft.com/en-us/lib...9(SQL.90).aspx

Basically you can now do it with a similar syntax to the way it is done
in relational queries.

Cube_Identifier |
(SELECT [
* |
( <SELECT query axis clause> [ , <SELECT query axis clause> ... ]
) ]
FROM <SELECT subcube clause> <SELECT slicer axis clause> )
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1144058797.387599.199460 (AT) j33g2000cwa (DOT) googlegroups.com>,
jesper.ellegaard (AT) gmail (DOT) com says...

Thanks but,
I believe that Darrens suggestion is not the correct solution. Please
try to review the concept of the supplied SQL. It is very important to
note that a customer can do more than one transaction, and that it
selects customers based on a single type of transaction, but it
aggregates on ALL transactions made by these customers, not only the
specific type.
Darrens MDX will, as far as I can see, create a sub cube with only
those transactions that fulfil the transaction type filter. Thus the
result would be quite different from what the SQL returns.

/JE

Sorry, I missed the bit where you said that type was related to the
transaction, not the customer.

Here is an example against the Adventure works sample database that
shows how to select a list of cities that bought a given bike and then
get the total sales across all products. You can do all this with a
calculated member and the filter function.


MEMBER Measures.[All Internet Sales Amount] as ([Measures].
[Internet Sales Amount],[Product].[Product].[All Products])
select
{
[Measures].[Internet Sales Amount]
,Measures.[All Internet Sales Amount]
} ON COLUMNS,
filter([Customer].[Customer Geography].[City],NOT ISEMPTY
((Customer.[Customer Geography].CurrentMember,Measures.[Internet Sales
Amount]))) ON ROWS
FROM [Adventure Works]
WHERE ({[Product].[Product].&[592]})
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1144144021.515252.262810 (AT) t31g2000cwb (DOT) googlegroups.com>,
jesper.ellegaard (AT) gmail (DOT) com says...

Getting a great deal closer.

Would that be the AdventureWorks or AdventureWorksDW base? Your query
does not return anything on DW and we cant build the other one (I was
not told why).

/JE

In article <1144177669.491212.169020 (AT) i40g2000cwc (DOT) googlegroups.com>,
jesper.ellegaard (AT) gmail (DOT) com says...
database.

Do you have an MDX query that was working with the transaction type in
the WHERE clause? I might be able to point you in the right direction
given an example like this.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

Same cube, then I am puzzled by the zero output!

Anyway, I can't supply you with any MDX, as we are just getting
started to learn the syntax here.
But I could give you an expected output from the Adventure Works setup.

Say that we replicate the product dimension, then having two of these
around the internet_sales fact table. Then I would like the following
output, where I count customers buying product combinations, but ONLY
if they once or more have bought product A. The output example is a
symmetric 3x3 matrix, shown here un-pivoted for clearness:

Product; Product; Customer count
------------------------------------
A; A; 4
A; B; 2
A; C; 1
B; A; 2
B; B; 6
B; C; 2
C; A; 1
C; B; 2
C; C; 5

I am aware of that this extends the question a bit!

/JE

Yes, It does expand the question a bit!

I don't know if it is possible to do a cartesian product of two sets of
members like you had in your example. But you can say "for product A,
show me how many people bought these other products".

What we are heading into now is what is known as "basket analysis" there
is a good article on the subject here:

http://www.microsoft.com/technet/pro...in/sste286.msp
x

It was written for Olap Services 7.0, but the concepts still apply.

I have not completely answered your question below, I was experimenting
with the product dimension, but the queries were taking quite a while to
run and I think in AS2005 the type of analysis you are talking about
would be better handled using and many-to-many relationship. Hence I
changed the question to be: Of those people that order products based on
price, how many of them also bought for other reasons?

I hope this helps

WITH
MEMBER Measures.BuyOnPricePre as Iif(IsEmpty(([Measures].[Internet
Order Count], [Sales Reason].[Sales Reason].[Price])) Or IsEmpty
([Measures].[Internet Order Count]), 0, 1)

MEMBER Measures.BuyOnPrice as
sum(Descendants([Customer].[Customer Geography].CurrentMember,
[Customer].[Customer Geography].[Full Name]),
Measures.BuyOnPricePre)

MEMBER Measures.CustomerCount as
count(Descendants([Customer].[Customer Geography].CurrentMember,
[Customer].[Customer Geography].[Full Name]))

MEMBER Measures.PcntBoughtOnPrice as measures.BuyOnPrice /
Measures.CustomerCount, FORMAT_STRING= "0.0%"
SELECT
{
Measures.BuyOnPrice
,Measures.PcntBoughtOnPrice } ON COLUMNS,
[Sales Reason].[Sales Reasons].members ON ROWS
FROM [Adventure Works]


--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1144324194.379471.164360 (AT) z34g2000cwc (DOT) googlegroups.com>,
jesper.ellegaard (AT) gmail (DOT) com says...