Forum Discussion

satyamarigold's avatar
satyamarigold
Cloud Apps
06-22-2023
Solved

How to model changing Membership Types when calculating Historical Data

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.

Data Models

2 Replies

Replies have been turned off for this discussion
  • Benji_PaldiTeam's avatar
    Benji_PaldiTeam
    Data Pipeline
    06-23-2023

    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's avatar
      satyamarigold
      Cloud Apps
      06-23-2023

      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.