none
an MDX question:: RRS feed

  • Question

  • We have a table as the following

    product store day visit
    p1     s1      d1  1
    p2     s1      d1  0
    p3     s1      d1  0

    p1     s1      d2  0
    p2     s1      d2  0
    p3     s1      d2  0

    Since if we visit a store, actually we will look at each product. So we'd like to flag the visit as 1 for ALL the products in a store that day if we visit that store that day for ANY product.

     

    The expected result should be like

     

    product store day visit
    p1     s1      d1  1
    p2     s1      d1  0   ==> flag visit to 1 as p1 was visited
    p3     s1      d1  0   ==> flag visit to 1 as p1 was visited.

    p1     s1      d2  0
    p2     s1      d2  0
    p3     s1      d2  0

    I tried use the following 2 way, but neither works.

    ([PRODUCT].[PRODUCT].[PRODUCT].members*[Store].[Store].[Store].members*[Calendar].[Date].[Date].members
    ,[measures].[new visit]) = IIF(([Store].[Store].currentmember,[Calendar].[Date].currentmember,visit) > 0,1,0 );

    ([PRODUCT].[PRODUCT].[PRODUCT].members*[Store].[Store].[Store].members*[Calendar].[Date].[Date].members
    ,[measures].[new visit]) = IIF((root(product),[Store].[Store].currentmember,[Calendar].[Date].currentmember,visit) > 0,1,0 );

    Friday, September 30, 2011 3:15 AM

Answers

  • This is how you can do it. 

    You can use the MAX function and get to what you want.

    In Adventure works what i have done is considered the Internet Sales value by Product Category and the Month. Now Set the Internet sales value across all month of the year per product category to its Max value.

    In your case since values are only 0s and 1s, and if there is a visit any one record will be marked as 1, Max would return 1 and you use this to set across that store for the day. You can solve your problem too similarly. 

    WITH MEMBER MAX_InternetSales As 
    GENERATE
    (
    		ANCESTOR( [Date].[Calendar].CURRENTMEMBER ,[Date].[Calendar].[Calendar Year]),
    		MAX
    		(
    			(
    				[Product].[Product Categories].CURRENTMEMBER,
    				([Date].[Calendar Year].CURRENTMEMBER,[Date].[Calendar].[Month])
    			)
    			,
    		[Measures].[Internet Sales Amount]
    		)
    ),FORMAT_STRING='CURRENCY'
    
    SELECT 
    	{[Measures].[Internet Sales Amount],MAX_InternetSales} on 0,
    	NONEMPTY(
    		[Product].[Product Categories].[Category]*
    		[Date].[Calendar].[Month],
    		[Measures].[Internet Sales Amount]
    	) On 1
    FROM
    	[Adventure Works]
    	

    Hope it was helpful


    vinu
    Friday, September 30, 2011 2:52 PM
  • Try the following equivalent in Adventure Works

    WITH MEMBER [Measures].[Visit] AS 
    	IIF((	[Customer].[Customer Geography].CurrentMember, 
    			[Date].[Calendar].CurrentMember,
    			[Product].[Product Categories].[All Products], 
    			[Measures].[Internet Sales Amount]) > 0, 1, 0) 
    
    SELECT {[Measures].[Internet Sales Amount], [Measures].[Visit]} on 0,
    [Customer].[Customer Geography].[City].&[Berkshire]&[ENG] *
    [Date].[Calendar].[Calendar Year].Members * 
    [Product].[Product Categories].[Category].Members  ON 1
    FROM [Adventure Works]
    

    regards

    Gautham

     

    Friday, September 30, 2011 3:38 PM
    Answerer

All replies

  • I think it is easier if the flags are appropriately set in the ETL layer itself. You do not have to do anything at SSAS calculation.

    Something like this should work

    Create table #tmp (Product varchar(2),Store varchar(2), VisitDay varchar(2), VisitFlag varchar(1))
    Insert into #tmp values('p1','s1','d1','1')
    Insert into #tmp values('p2','s1','d1','0')
    Insert into #tmp values('p3','s1','d1','0')
    Insert into #tmp values('p1','s1','d2','0')
    Insert into #tmp values('p2','s1','d2','0')
    Insert into #tmp values('p3','s1','d2','0')
    
    With StoreMaxVisitFlag as (
    Select MAX(VisitFlag) VisitFlag,VisitDay,Store from #tmp group by VisitDay,Store)
    
    Select * from #tmp t1 inner join StoreMaxVisitFlag s1 on t1.Store=s1.Store and t1.VisitDay=s1.VisitDay
    
    With StoreMaxVisitFlag as (
    Select MAX(VisitFlag) VisitFlag,VisitDay,Store from #tmp group by VisitDay,Store)
    
    Update t1 set t1.VisitFlag=s1.VisitFlag from #tmp t1
     inner join StoreMaxVisitFlag s1 on t1.Store=s1.Store and t1.VisitDay=s1.VisitDay



    vinu
    Friday, September 30, 2011 4:34 AM
  • Thanks Vinu,

    I also though about that way, and it should be performance much better than MDX. But just curious, how to do it in MDX?

    Friday, September 30, 2011 4:54 AM
  • This is how you can do it. 

    You can use the MAX function and get to what you want.

    In Adventure works what i have done is considered the Internet Sales value by Product Category and the Month. Now Set the Internet sales value across all month of the year per product category to its Max value.

    In your case since values are only 0s and 1s, and if there is a visit any one record will be marked as 1, Max would return 1 and you use this to set across that store for the day. You can solve your problem too similarly. 

    WITH MEMBER MAX_InternetSales As 
    GENERATE
    (
    		ANCESTOR( [Date].[Calendar].CURRENTMEMBER ,[Date].[Calendar].[Calendar Year]),
    		MAX
    		(
    			(
    				[Product].[Product Categories].CURRENTMEMBER,
    				([Date].[Calendar Year].CURRENTMEMBER,[Date].[Calendar].[Month])
    			)
    			,
    		[Measures].[Internet Sales Amount]
    		)
    ),FORMAT_STRING='CURRENCY'
    
    SELECT 
    	{[Measures].[Internet Sales Amount],MAX_InternetSales} on 0,
    	NONEMPTY(
    		[Product].[Product Categories].[Category]*
    		[Date].[Calendar].[Month],
    		[Measures].[Internet Sales Amount]
    	) On 1
    FROM
    	[Adventure Works]
    	

    Hope it was helpful


    vinu
    Friday, September 30, 2011 2:52 PM
  • Try the following equivalent in Adventure Works

    WITH MEMBER [Measures].[Visit] AS 
    	IIF((	[Customer].[Customer Geography].CurrentMember, 
    			[Date].[Calendar].CurrentMember,
    			[Product].[Product Categories].[All Products], 
    			[Measures].[Internet Sales Amount]) > 0, 1, 0) 
    
    SELECT {[Measures].[Internet Sales Amount], [Measures].[Visit]} on 0,
    [Customer].[Customer Geography].[City].&[Berkshire]&[ENG] *
    [Date].[Calendar].[Calendar Year].Members * 
    [Product].[Product Categories].[Category].Members  ON 1
    FROM [Adventure Works]
    

    regards

    Gautham

     

    Friday, September 30, 2011 3:38 PM
    Answerer