MDX Query Help...Please

Hi,

I have a fact table containing the following columns:

CustomerID

RegionID

CategoryID

ItemID

Price

Relationships:

A customer belongs to only one particular region and category

Each customer can buy the same item at different prices

The measures are LowPrice and AveragePrice

Query

For a particular customer, what was the customer price, average price and low price for some other combination of region and category ( this not the region and category,the customer belongs to)

For ex:

CutomerId RegionID CategoryID ItemID Price

1 1 2 1 2.00

2 2 3 1 6.00

3 2 3 1 4.00

Report should display for Customer 1 , Region 2 and Category 3 as

ItemID Price LowPrice AveragePrice

1 2.00 4.00 5.00

Iam stuck with this query for a long time now...Any help on the query will be appreciated.

Also, how would find the above result for all combinations of (regions and categories)?

Thanks,

Prash

Could you explain how you derive this result from the 3 rows of data - only 1 of which is associated with CustomerID 1?

ItemID Price LowPrice AveragePrice

1 2.00 4.00 5.00

|||

Thanks Deepak for looking into the query.

Here we are looking at report for (customer 1, region 2, category 3, item 1).

As shown in the fact table there are two records for (item 1 ,region 2, category 3) combination and therefore Iam taking an average and minimum of the prices from those two records for Item 1.

CusomerID RegionID CategoryID ItemID Price

1 1 2 1 2.00

2 2 3 1 6.00

3 2 3 1 4.00

So when customer 1 logs in and runs the report for (region 2,category3, item1), he should be able to see the following data

ItemID Price LowPrice AveragePrice

1 2.00 4.00 5.00

The 'price' is Customer's Price from the facttable, 'LowPrice' is the lowestprice for Item1 in (region2, category3) and 'AvgPrice' is the averageprice for the Item1 in (region2, category3) combination.

As explained before:

Customers belong to one region and category. But the reports should be able to display the low price and average price for an item for any combination of region and category.

Hope I have explained it properly. Please let me know, if you need any other details

Thanks,

Prash

|||

Hi Prash,

Assuming that there is a cube "sum" measure like [FPrice], a "min" measure like [LPrice] and a "count" measure like [FCount], then you can create these calculated measures (dimensions are [Customer], [Region], [Category] and [Item]):

[GlbPrice]: [FPrice] / [FCount]

[CustPrice]: ([GlbPrice], [Region].[Region].[All], [Category].[Category].[All])

[LowPrice]: ([LPrice], [Customer].[Customer].[All])

[AvgPrice]: ([GlbPrice], [Customer].[Customer].[All])

|||Thanks Deepak. That was helpful.