SSAS -> MDX -> Create a price comparision against resellers I want to write an MDX query that will do a price comparison for a product against each re-seller and return a count which a 'greater', 'lesser' or 'equal' for a given month.    

Measure:
fact_reseller_price

Dimension:
dim_date 
dim_resellers
dim_products

Data in my fact table: 

fact_reseller_price

product      reseller price date_purchase
SSD          AMZ      10.00    01/5/2012
SSD          BB       15.00    01/5/2012
SSD          COSTCO   15.00    01/5/2012
SSD          COSTCO   15.00    01/5/2012
SSD          FRYS     20.00    01/5/2012
SSD          WALMART  10.00    01/5/2012
SSD          EN       5.00     01/5/2012
SSD          EN       8.00     01/5/2012
SSD          EN       4.00     01/5/2012

My resultant table from the MDX query (Price Comparison)

product reseller comparision_reseller count type    date_purchase
SSD     AMZ      BB                   1     GREATER 01/5/2012
SSD     AMZ      COSTCO               2     GREATER 01/5/2012
SSD     AMZ      FRYS                 1     GREATER 01/5/2012
SSD     AMZ      WALMART              1     EQUAL   01/5/2012
SSD     AMZ      EN                   3     LESSER  01/5/2012
SSD     BB       AMZ                  1     LESSER  01/5/2012
SSD     BB       COSTCO               2     EQUAL   01/5/2012
SSD     BB       FRYS                 1     GREATER 01/5/2012
SSD     BB       WALMART              1     LESSER  01/5/2012
SSD     BB        EN                  3     LESSER  01/5/2012
If you have the following database schema with Date, Product and Reseller dimensions and a price fact table:

create table DimProduct (
       ProductID    int not null
      ,Product      varchar (50)
)
go

Create table DimDate (
       DateID    datetime not null
      ,MonthID   int not null
)
go

create table DimReseller (
       ResellerID   int not null
      ,Reseller     varchar (50)
)
go

create table FactPrices (
       ProductID    int not null
      ,ResellerID   int not null
      ,DateID       datetime not null
      ,Price        money
)
go

insert DimProduct (ProductID, Product) values (1, 'Widget')
insert DimProduct (ProductID, Product) values (2, 'Dohickey')
insert DimProduct (ProductID, Product) values (3, 'Whoflungdung')
go


insert DimDate (DateID, MonthID) values ('2012-01-01', 201201)
insert DimDate (DateID, MonthID) values ('2012-02-01', 201202)
insert DimDate (DateID, MonthID) values ('2012-03-01', 201203)
go

insert DimReseller (ResellerID, Reseller) values (1, 'Braise and co')
insert DimReseller (ResellerID, Reseller) values (2, 'Cocost')
insert DimReseller (ResellerID, Reseller) values (3, 'Walls Mart')
go

insert FactPrices (ResellerID, ProductID, DateID, Price) values (1, 1, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (1, 1, '2012-02-01', 9)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (1, 2, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (1, 2, '2012-02-01', 10)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (1, 3, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (1, 3, '2012-02-01', 11)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (2, 1, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (2, 1, '2012-02-01', 10.5)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (2, 2, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (2, 2, '2012-02-01', 9.90)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (2, 3, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (2, 3, '2012-02-01', 11)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (3, 1, '2012-01-01', 12)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (3, 1, '2012-02-01', 10.5)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (3, 2, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (3, 2, '2012-02-01', 11)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (3, 3, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (3, 3, '2012-02-01', 10.5)

go


Create a cube with Date, Product and Reseller dimensions and a SalesTrends fact table with a single metric 'Price'.  The MDX query below will produce counts of products for which the price is increasing, static or decreasing in a given period.

with 
member [Measures].[Delta]
    as [Measures].[Price] -
       (parallelperiod ([Date].[Calendar].[Month]
                       ,1
                       ,[Date].[Calendar].currentmember)
       ,[Measures].[Price])

member [Measures].[Rising]
    as count (filter ([Reseller].[Reseller].Children
                      ,[Measures].[Delta] &gt; 0))
      ,solve_order = 20

member [Measures].[Static]
    as count (filter ([Reseller].[Reseller].Children
                      ,[Measures].[Delta] = 0))
      ,solve_order = 20

member [Measures].[Falling]
    as count (filter ([Reseller].[Reseller].Children
                      ,[Measures].[Delta] &lt; 0))
      ,solve_order = 20

select {[Measures].[Rising]
       ,[Measures].[Static]
       ,[Measures].[Falling]}
    on columns 
      ,[Product].[Product].Children
    on rows
  from [SalesTrends]
 where ([Date].[Calendar].[Month].[201202])


The query calculates the delta from the previous period, and calculates three metrics for rising, static or falling based on counts of resellers where the delta is positive, zero or negative.  Note that the prices will aggregate in strange ways if you to calculate this at anything but the lowest grain of the underlying fact table.