MDX Question

Post Reply
Posts: 1
Joined: Fri Jul 16, 2010 3:25 pm
OLAP Product: MSAS
Version: Cognos Transformer
Excel Version: 2007

MDX Question

Post by P1Daniels » Fri Jul 16, 2010 4:55 pm


This may be a straight MDX question or it may end up being a forcing a design change. Either suggestion is fine, as long as the response is relevant.

I have a cube with some shopping activity in it. The measures contains a CustomerID with the aggregation set to Distinct Count, along with Sales$'s and ItemQty. I also have all the standard dimensions present: Time, Stores, Products. I do not currently have a customer dimension.

What I need to do is create a view into the data where I can report on "cross-shopping" between stores. That is, I need to show a grid with a list of Stores on both axes and the intersection showing me how many customers shopped in both stores.

I'm thinking I might need to put Customers in a dimension, but would like your ideas.

Thanks, in advance...

Marcus Scherer
Community Contributor
Posts: 125
Joined: Sun Jun 29, 2008 9:33 am
OLAP Product: TM1
Version: 9.5 10.1 10.2
Excel Version: 2007
Location: Karlsruhe

Re: MDX Question

Post by Marcus Scherer » Sat Jul 17, 2010 4:31 pm

welcome to the forum!
some thoughts:
- you probably need to duplicate your dim_store to be able to display it on two axis.
- this leads you to the "many-to-many dimension" concept in SSAS. Search for examples on the web if this fits your need.
- defining dim_store as "role playing" dimension will probably not fit your problem since cusotmers can buy in many shops.
- setting up a dim_customer gives you one more degree of freedom. May be useful for further requirements anyway.

Post Reply