Question

Aggregate vs. Import for Parent/Child Dimension Relationship

  • 4 April 2024
  • 3 replies
  • 49 views
N
Rank
Badge +1

We have two dimensions that have a parent/child relationship - L0 Company and L1 Parent Company. For the exercise, here are some details:

  • L0 has 500,000 items, L1 has 400,000 items
  • This property will be upstream of a fairly complex cohorted revenue model
  • Both dimensions are provided to us from Snowflake
  • The date formatted property “First ARR Date” exists at the L0 Company level

We need First ARR Date in L1 Parent Company in Pigment, what is the preferred way to do this to maximize performance?

  • Aggregate from L0 to L1 in Pigment using a BY MIN
  • Aggregate in Snowflake & import the First ARR Date into both our L0 and L1 dimensions
  • A third option?

Further, would the right way to do this change if the balance of list items between lists changed?

3 replies

BrunoRodriguez
Rank
Userlevel 3
Badge +6

Hello @Not Jeremy Stern ,

 

It seems like you “First ARR Date” will be a property of your L0 dimension and your L1 dimension will have another property that will be the “Parent” L0. As such, you could use the following formula:

  • First ARR Date Metric [BY CONSTANT: P1.P0] 

 

Please let me know if that is not clear.

Cheers!

N
Rank
Badge +1

I think you’ve got it reversed, our Pigment SA Jake Prince set us up where L0 is the most granular level and L1 is the parent level.

 

Our L0 is basically individual subscriptions and our L1 is the parent company. L0 could have LinkedIn, Microsoft, Github, etc. L1 would then be Microsoft. At the L1 level, we want to know the first day that any ARR was generated by an L0. With the goal of optimizing overall performance, the questions end up being:

  1. Generally will it be better to (a) aggregate information in the data warehouse & import to Pigment or (b) to import to Pigment & aggregate in Pigment?
    1. Even better: if there is an inflection point, teach us when option a is better and when option b is better.
  2. If aggregating in Pigment, is there any more efficient way to do this than BY MIN?
BrunoRodriguez
Rank
Userlevel 3
Badge +6

Hello again,

You are completely right. My suggestion resolves the opposite relationship.With regards to this question, any additional calculation will have an impact in the string of calculations.

What I mean by this is that if you do the allocation of dates outside Pigment, your only calculation will be transforming the data from the transactional list into the L1 dimensioned metric. Alternatively, if you bring the data at the L0 dimension, you will have to transform the data from the transactional list into a L0 dimension metric and from there to the L1 dimensioned metric. 

As for the more efficient way than BY MIN, I believe it is a good option since you have to identify what date is the earliest for each specific parent. 

Hope that made sense!

 

Reply


Ask your question here!

Terms & ConditionsCookie settings