Skip to Content
Technical Articles
Author's profile photo Sukanya Krishnan

SAC Lookup function in Model

Lookup Function in SAC Model : Lookup function can be leveraged to fetch metric value restricted on dimension values. The function also provides option to ignore specific dimensions.

  • If ignored dimension is part of drill state, function would return aggregated value considering ignored dimensions in the group by clause.
  • If ignored dimension is not part of drill state, same account value gets placed in all members of ignored dimension

Syntax: LOOKUP([Account], Filters , Ignored Dimensions)

Use case: Requirement is to calculate the actual value based on allocation %. Allocation % is keyed in by business users at different granularity. In below example, “Value” metric is available at Project, Domain, Location, Date granularity.

Figure%201%3A%20Table%20with%20account%20measure

Figure 1: Table with “Value” account measure

Allocation% – input provided by user for each project across different departments. This input is irrespective of dates.

Figure%202%3A%20Table%20with%20Allocation%25

Figure 2: Table with Allocation%

In such scenarios, where we input metric without date, value automatically gets distributed across dates ( based on the planning date range or based on date range filter if applied)

Figure%203

Figure 3

In such scenario, where we do not want values to be distributed, one option is to set the aggregation type to “None”.

Figure%204%3A%20Measure%20aggregation%20type

Figure%204%3A%20Aggregation%20type%20None

Figure 4: Aggregation type None

So far,

  • “Value” metric is at Project–>Domain–>Location granularity
  • “Dept_Alloc” is at Project–>Domain–>Location–>Department granularity

Requirement is to find the calculated “Value” based on the allocation%. We can use the lookup function for this requirement. Below is the output of calculated measure.

  • VAL_LKP : LOOKUP([Value] , , [d/Department]) – By ignoring the department dimension, same “Value” gets fetched for all departments
  • CALC_METRIC : [VAL_LKP] * [Dept_Alloc] – Above value is then multiplied by allocation % to get the  output

Figure%205%3A%20Calculated%20Measure%20with%20Lookup%20function

Figure 5: Calculated Measure with Lookup function

In summary , calculated metrics can be define in model level even if account measures used in calculations are at different granularity. This option helps to define calculations dynamically in real time. Based on the data volume and report performance, we can also think about persisting the intermediate calculation values in model leveraging the advanced scripting. But in such case, changes in input will not reflect the calculations rather we need to execute the data actions to get the modified calculated values.

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Vaibhav Lokhande
      Vaibhav Lokhande

      Nice blog on a tricky topic

      Author's profile photo Mayumi Lacerda Blak
      Mayumi Lacerda Blak

      Hello Sukanya,

      thanks for the post.

      I have a question: what if I have one dimension in common for the value, but all of the others are different. LOOKUP formula only let us ignore 1 dimension....do you have any suggestions to ignore more then 1?

       

      kind regards

      Mayumi

      Author's profile photo Sukanya Krishnan
      Sukanya Krishnan
      Blog Post Author

      Hi Mayumi,

      Sorry, missed your message. We can ignore more than one dimension in LOOKUP formula. Hope you got the syntax by now.

       

      Regards,

      Sukanya Krishnan