1

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

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Coca'sPP
  • 13
  • 2

1 Answers1

1

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.

ic3
  • 7,917
  • 14
  • 67
  • 115
  • Thank you. It worked, but with my data, so hard to loading. this is my MDX: member [Measures].[Next1] as 'Sum(Descendants([Users], [Users].[ID]), IIf(IsEmpty([Measures].[UU]), 0, IIf(IsEmpty(([Date].[Date].CurrentMember.Lag((- 1)), [Measures].[UU])), 0, 1)))' – Coca'sPP Jul 30 '14 at 13:01
  • I can't help you on performance, maybe ask another question and somebody with more experience with your MDX server might help (you didn't told which one you're using). I could help you with icCube ;-). Not sure descendants is a good idea, if you're getting 600k – ic3 Jul 30 '14 at 13:23