3323835 - SAP.GETDATA performance optimization by customizing drill level

• Refresh time is slow with numerous SAP.GETDATA formulas
• Depending on the different SAC data sources, the performances can be improved with drill levels different from the default one.

SAC Analytics Cloud for Microsoft Office

Use lots of SAP.GETDATA formulas in Excel workbook

• SAP.GetData is using SAC table principle to query data from server.

- In a table you can drill dimension members, so you see the member and its children

- there is in Excel add-in the feature to 'drill to level' so members and its descendant are drilled out to this level.

• When SAP.GetData is querying SAC data, by default, a drill level of 1 is set to each member, doing this we expect to send less queries and get expected result in less time.

This drill level of 1 has been chosen by experiencing on many scenarios but we knew in some situation it could be not optimal.

• The advanced setting feature is using an Excel named range to indicate a different drill level.

For example you are using SAP.GetData with DS_1 data source, the named ranged must be named DS_1_DrillLevel. Using Table1 as source it should be named Table1_DrillLevel.

If you set A1 as the DS_1_DrillLevel named range, you can set this kind of values in A1:

• A number => all dimensions will use this number as drill level. If A1 value is 1, you will not see any change as it is the default value.
• *:0|[dimensionname]:1 => generic format to specify a different value by dimension

• The generic format is a list of dimension:number pair separated with pipe sign, dimension and number is separated by colon.

* Is the only wildcard that is recognized, it is applied to any dimension that is not specified

[dimensionname] is the name of a dimension on the data source, the drill level applied to it is the number specified after the colon.

• Let's illustrate this with this example: *:1|AccountNA:0|Date:5|Company:5

AccountNA dimension will use 0 as drill level (AccountNA:0) so no drill level for this one

Date and Company dimensions will use 5 as drill level (Date:5 and Company:5).

All other dimensions will use drill level of 1  (*:1)

"SAP.GETDATA", "drill level", performance , KBA , LOD-ANA-OF-XLA , SAP Analytics Cloud, add-in for Microsoft Office , How To