How to model changing Membership Types when calculating Historical Data

[satyamarigold]

I have a user table with a property MembershipType. I also have a second table with documentations that our staff are filling for each user over time The common key here is "username". I need to create a breakdown of # of documents filled per membership type. The problem comes when trying to get sisense and it's Dim Dates Table to pick start dates and end dates for memberships. 

Eg: if a user John is of membershipType "Alpha" till March 23 and "Beta" afterwards, documentation for John should count towards Alpha before March 23 and Beta after March 23.

[Benji_PaldiTeam · Accepted solution]

Hi @satyamarigold ,

If I understand correctly this use case is known as "slowly changing dimension" , this is a well knows use case in BI modeling.
Here is a post I found about slowly changing dimension in Sisense:
https://community.sisense.com/t5/knowledge/slowly-changing-dimension/ta-p/8925
Keep in mind that there are few types of slowly changing dimensions, so I recommend reading this article as well.

Feel free to reach out if you have further questions, we're always happy to help (: 

[email protected]

Paldi Solutions - Number #1 Sisense Plugins Developer 

[satyamarigold]

Thank you! This helped solve that issue though I have a separate related issue now. Suppose for the example above - 
the user John is of membershipType "Alpha" till March 23 and "Beta" & "Gamma" afterwards, documentation for John should count towards both when breaking down by membership. But I don't want to create multiple rows in the dimension table, with documentation table linking to both. One solution I can think of is to create a second duplicate row in the documentation table (linking it to the second membership) but I want to avoid such an approach if possible.