SQL subselect, group by, sum equivalent in MDX(SSAS)

0
votes

I have a problem with creation of calculated member in SSAS. Fact table:

  Date       Store       Product        Sales_in_USD
    --------------------------------------------------
    2016-07-01 Store1      Product1       50
    2016-07-01 Store1      Product2       100
    2016-07-01 Store2      Product3       70
    2016-07-01 Store2      Product2       85

Dimensions: Dates,Stores,Products

I want to get something like that: If I filter by some product I want to get all sales by this store and date that include sales by filtered product+another products, for example I want filter by Product1:

SQL code:
select sum(Sales_in_USD)
from [Fact table]
where Store in (select Store from [Fact table] where Product="Product1")

Executing this sql code I get all Sales by Store1.

How I can create it with MDX when I want create a calculated member?

Output of calculated member must be the next:

Product   Total_Sales_By_Store
------------------------------
Product1  50+100=150
Product2  50+100+70+85=305
Product3  70+85=155
3
ssasmdx

3 Answers

0
votes

1 - Determine the valid stores for the selected product.

NonEmpty(
    [Store].[Store Name].MEMBERS,
    ([Product].[Product].Currentmember,[Measures].[Sales_in_USD])
)

2 - Once you have the stores for the current product, you want to calculate the sum of values for the possible tuples(cross join).

with member Measures.[Total Sum of Stores] as
sum(
[Product].[Product].Currentmember *
NonEmpty
   (
    [Store].[Store Name].MEMBERS,
    ([Product].[Product].Currentmember,[Measures].[Sales_in_USD])
   )    
,
[Measures].[Sales_in_USD]
)

select Measures.[Total Sum of Stores] on 0
from [YourCube]
where [Product].[Product].[Product1]
0
votes
SELECT 
  [Measures].[Sales_in_USD] ON 0,
  {[Store].[Store Name].MEMBERS,
   [Product].[Product].MEMBERS} ON 1
 FROM [Cube Name];

For your requirement, you really don't need a calculated member, since the measures will do the job for you

For Particular Product Name Product1, then

SELECT 
  [Measures].[Sales_in_USD] ON 0,
  {[Product].[Product].&[Product1],
   [Store].[Store Name].MEMBERS
  } ON 1
FROM [Cube Name];
0
votes

I think Sourav has pretty much nailed it but it could be more general?

WITH 
MEMBER [Measures].[Total Sum of Stores] AS
SUM(
  NONEMPTY(
    [Store].[Store Name].MEMBERS,
    (
      [Product].[Product].Currentmember
     ,[Measures].[Sales_in_USD]
    )
  )    
  ,[Measures].[Sales_in_USD]
)
SELECT
 [Measures].[Total Sum of Stores] on 0,
 [Product].[Product].[Product].MEMBERS ON 1
FROM [YourCube];