SSAS -> MDX -> Create a price comparision against resellers

Question

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

Answer 1

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] > 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] < 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.