MDX Query Help - Translating T-SQL to MDX

• rob26r

  SSC Veteran

  Points: 282

  More actions

  November 19, 2018 at 1:58 pm

  #382323

  I'm hoping someone can help me with this query that I have struggled with for a few hours (new MDX user). 

  I can easily get what I need using T-SQL but getting the equivalent using MDX has proven difficult.

  use [AdventureWorksDW2012]

  ------------------------------------------------------------
--Select customers that purchased specific items during specific time period
------------------------------------------------------------
drop table #Customers_Purchased_SelectedProduct
select
distinct
    a.CustomerKey
into #Customers_Purchased_SelectedProduct
from [dbo].[FactInternetSales] a
    inner join [dbo].[DimProduct] b on a.ProductKey = b.ProductKey
    inner join [dbo].[DimProductSubcategory] c on b.ProductSubcategoryKey = c.ProductSubcategoryKey
where
     a.ShipDateKey between 20050101 and 20081215
    and c.ProductSubcategoryKey in (1 , 2)

  ------------------------------------------------------------
--Get sales metrics for customers identified above
------------------------------------------------------------
select
    c.ProductSubcategoryKey
    , b.ProductKey
    , sum(a.SalesAmount) as SalesAmount
    , count(distinct a.CustomerKey) as 'CustomerDistinct_withPurchases'
from [dbo].[FactInternetSales] a
    inner join [dbo].[DimProduct] b on a.ProductKey = b.ProductKey
    inner join [dbo].[DimProductSubcategory] c on b.ProductSubcategoryKey = c.ProductSubcategoryKey
    inner join #Customers_Purchased_SelectedProduct bb on a.CustomerKey = bb.CustomerKey
where
    a.ShipDateKey between 20050101 and 20081215
    and c.ProductSubcategoryKey not in (1 , 2)
group by
    c.ProductSubcategoryKey
    , b.ProductKey

  The code below is what I came up with.  Seems extremely clunky and after 2 minutes it returns data and isn't correct.

  with

  ------------------------------------------------------------
----Select customers that purchased specific items during specific time period
------------------------------------------------------------
set [Cust] as
nonempty(
            [Dim Customer].[Customer Key].[Customer Key].members ,
            (
                ({[Dim Product].[Product Subcategory Key].&[1] ,[Dim Product].[Product Subcategory Key].&[2]}) ,
                ({[Ship Date].[Date Key].&[20050101]: [Ship Date].[Date Key].&[20081215]}) ,
                [Measures].[Sales Amount]
            )
        )

  ------------------------------------------------------------
--Create list of subcategories excluding the ones from above
------------------------------------------------------------

  set [SubCategory Other] as
    except (
                [Dim Product].[Product Subcategory Key].[Product Subcategory Key]
            , ({[Dim Product].[Product Subcategory Key].&[1] ,[Dim Product].[Product Subcategory Key].&[2]})
            )

  member [Sales Amount Selected Customers] as sum([Cust] , [Measures].[Sales Amount])
member [Customer Count] as count(nonempty([Cust],[Sales Amount Selected Customers]))

  select
{[Sales Amount Selected Customers] , [Customer Count]} on 0
, ([SubCategory Other] * [Dim Product].[Product Key].[Product Key]) on 1
from [Adventure Works DW2012]

  Incorrect results set:

  

  Your help is very much appreciated!

  The T-SQL query runs in less than 1 second.  I'm clearly messing something up.

  Thanks.