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:

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.

Analysis Office Components tab - Use Data Source Formula-Optimized
Analysis Office Components tab - Use Data Source 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:

Formula SAPSelectMember
Formula SAPSelectMember

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:

Formula SAPGetData
Formula SAPGetData

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

Formula SAPGetData with value
Formula SAPGetData with value

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.

author.


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.

Analysis Office - The Comprehensive Guide 3rd edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.6 and contains 272 Pages.
49,99 €
Analysis Office - The Comprehensive Guide 2nd edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.4 and contains 243 pages.
39,99 €
Analysis Office - The Comprehensive Guide
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.3 and contains 227 Pages.
9,99 €

Write a comment

Comments: 0