Search code examples
mysqlcountmdxretention

MDX how to count visited users in yesterday exist visit today


I have a fact table to store user login

date_key   | user_key
20140701   |     1
20140701   |     2
20140701   |     3
20140701   |     4
20140702   |     1
20140702   |     2
20140702   |     3
20140702   |     8
20140703   |     1
20140703   |     4
20140703   |     8
20140703   |     9

I would like to make a MDX to count visitors visited a day, how many go back in next some days EX:

Date     DAU  Next1  Next 2
20140701 4    3      2
20140702 4    2      -
20140703 4    -      - 

How can I make a MDX to count


Solution

  • Back, sorry. Funny I was discussing DAU concept last Friday. There is no direct way to calculate this, so we need to do a bit of MDX calculated measures.

    I don't see another way as to iterate through all users, something like :

     MEMBER [Next1] AS SUM( [Users].[Users Level], 
                           IIF( isEmpty( [Measures].[MyEmpty] ), 
                                0, 
                                IIF( isEmpty( ([Time].currentmember.next,[Measures].[MyEmpty]) ), 
                                  0,
                                  1
                                )
                           )
    
     [Time].currentmember.next -> move to next day (assumes currentmember is day)
    

    For each user we're checking if the users did connect today and if it's the case we check this for the next day.

    The empty should be on a measure that quickly checks if the user was there for a given day. Not sure how a distinct count works on SSAS in this scenario (in icCube we don't care). A pity in icCube we could think extending aggregation to support vectors, so we could perform a faster version.