Scribd
Upload
111 views2 pages

D7

The document discusses using the Lookup function in SAC models to fetch metric values while ignoring specific dimensions. It provides an example of calculating an actual value based on an allocation percentage, which is keyed in at different granularities. The Lookup function is used to ignore the department dimension and fetch the same value for all departments when multiplying by the allocation percentage to get the calculated metric. In summary, the Lookup function allows defined calculations in the model even if account measures use different granularities.

Uploaded by

tanjacabrilo
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
111 views2 pages

D7

The document discusses using the Lookup function in SAC models to fetch metric values while ignoring specific dimensions. It provides an example of calculating an actual value based on an allocation percentage, which is keyed in at different granularities. The Lookup function is used to ignore the department dimension and fetch the same value for all departments when multiplying by the allocation percentage to get the calculated metric. In summary, the Lookup function allows defined calculations in the model even if account measures use different granularities.

Uploaded by

tanjacabrilo
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

SAC Lookup function in Model

3
3
2,214
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.

You might also like