Analysis Office Data Source for Defining Formulas

A new function in Analysis Office 2.7 is "Select Data Source for Defining Formulas". But what does this mean? First the prerequisites:

• BW/4HANA SP8 and apply the following notes: 2624495 and 2600508
• BW 7.50 SP12 (or >= SP8 and apply the following notes: 2579842, 2627315, 2624495 and 2600508)

So I just logged into my BW 7.50 test system and what I have to see, we only have a BW 7.5 SP11. So I have applied the notes and each note need further notes to implemented. I just want to test something and now I have to implement more than 30 notes.

After this was done I could start finally to test this function. Alexander Peter showed in the latest DSAG webcast an example so I had a slight idea what to do.

First I logged to my BW 7.5 and selected my query. You have to make sure that the option Use Data Source Formula-Optimized is active. If not, you cannot use it. If it is not active you can in return create a Crosstab which you cannot do if it is formula optimized.

So now just create an example. First I create a simple SAPSelectMember to use it for the next steps.

=SAPSelectMember("DS_1";"0CALMONTH{pres=INTERNAL_KEY}=201705";"0CALMONTH";"Text")

This is how it looks like:

Now I build a little compley formula to get my data.

=SAPGetData("DS_1";F3;"0CALMONTH="&SAPGetMember("DS_1";"0CALMONTH="&I3;"KEY"))

This is how it looks like:

As you might wonder, you see now #RFR. This mean you need to refresh your data source and now you see the result.

After you change the Filter Component you always see the #RFR. This could be better, I hope SAP will make improvements here. Now you can use this "new" function to build beautiful dashboards.

But what is now the thing, why I should use the new function? You sure now the SAPGetData formula from earlier versions. But the SAPGetData formula in older versions need a query view  that contains all cells which you want to query. That means you have to drill down hierarchies and add dimensions if you want to query these characteristic combinations.

This can mean, that you have to quest 100.000 cells to represent two values. In the new handling since the actual version it is the other way around. This mean you write the combination you need and Analysis Office generates the best possible queries.

Thanks to Alexander Peter for clearing this up.

These posts might also be interesting:

• Analysis Office Launching Workbooks from BIP with variables
• Analysis Office Restrictions
• Analysis Office Filter by Member Attribute
• Analysis Office 2.5 Scaling Factor

I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.