0 Replies Latest reply on Oct 22, 2018 2:02 PM by Jalal Hassan

    Different Aggregation Levels on a record

    Jalal Hassan

      Hi,

       

      So this is something more conceptual that I am wondering about rather than something I am having an issue with. I'd love to have a better understanding of how to handled data structured in the format below.

       

       

      So on any given day an accounts has a certain amount of active PR IDs per region,city,state,country ect... So for every ID or record in that location the number of active PR IDs is appended to the ID record. If there are no ID records on that day the data will have nulls where the  ID/PR ID would be. As there was no ID/PR ID record to join on that day.

       

      So the data has ID records at the most granular level and then aggregated summary data appended to the granular record. What would be the best way to go about "disassembling" the aggregated data.

       

      For example Texas Gulf only had 152 active PR records on 10/30, so if I wanted to de-aggregate the data would I want to use a FIXED LOD calculation at the Date,Account,and regional data level? I:E

       

      {FIXED Account Name,Date,PR Country,PR State,PR City,PR Region: SUM(PR Active Records)}/{FIXED Account Name,Date,PR Country,PR State,PR City,PR Region: COUNT(ID)}

       

      I guess my question is I am still trying to learn how LODs work in actuality, and how I can incorporate them into my data to bring out more insights. I know this is more theoretical, but would love to see/hear some discussion on best methodologies.

       

      ID       Date              Account Name     PRID           PR Country        PR State          PR City           PR Region         PR Active Records

      1234   10/30/2018    AccountX              93576            US                   Texas              Galveston         Texas Gulf              152

       

      ID       Date              Account Name     PRID           PR Country        PR State          PR City           PR Region         PR Active Records

      1231  10/30/2018    AccountX              93576            US                   Texas              Galveston         Texas Gulf              152

       

      ID       Date              Account Name     PRID           PR Country        PR State          PR City           PR Region         PR Active Records

      1212   10/30/2018    AccountX              93576            US                   Texas              Galveston         Texas Gulf              152

       

      ID       Date              Account Name     PRID           PR Country        PR State          PR City           PR Region         PR Active Records

      12334   10/31/2018    AccountX              93575            US                   Texas              Galveston         Texas Gulf              132

       

      ID       Date              Account Name     PRID           PR Country        PR State          PR City           PR Region         PR Active Records

      12980   10/30/2018    AccountX              93525            US                   Texas              Houston         Other                        25

       

      ID       Date              Account Name     PRID           PR Country        PR State          PR City           PR Region         PR Active Records

      12888   10/31/2018    AccountX              93532           US                   Texas              Houston         Other                       36

       

      ID       Date              Account Name     PRID           PR Country        PR State          PR City           PR Region         PR Active Records

      12478  10/30/2018    AccountX              93545            US                   Texas             Austin               Austin                  532

       

      ID       Date              Account Name     PRID           PR Country        PR State          PR City           PR Region         PR Active Records

      12309   10/31/2018    AccountX              93514            US                   Texas             Austin               Austin                  550

       

      ID       Date              Account Name     PRID           PR Country        PR State          PR City           PR Region         PR Active Records

      null   11/01/2018         AccountX              null            US                            Texas         Galveston         Texas Gulf                 122

       

      ID       Date              Account Name     PRID           PR Country        PR State          PR City           PR Region         PR Active Records

      null   11/01/2018         AccountX              null            US                            Texas           Houston              Other                  35

       

      ID       Date              Account Name     PRID           PR Country        PR State          PR City           PR Region         PR Active Records

      null   11/02/2018         AccountX              null            US                            Texas             Austin               Austin                  567