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