Unfortunately, I didn't make it to publish the post in the last month. There were several reasons that I didn't make it, like the internal BI days or to prepare the next deep dive for Data Warehouse Cloud. But back to topic. SAP published Analysis for Office 2.8 SP14. Maybe there is something with the SP14 because Analysis Office 1.4 had also a SP14 before Analysis office 2.0 was released. So perhaps we see some new feature in the future?
But back to Analysis for Office 2.8 SP14. Now you are able to connect to Data warehouse Cloud and consume the analytical data sets directly in Excel. This is the biggest update with for a long time with features. The last updates were mostly bug fixing and some technical setting parameter, but nothing what is fascinating.
Besides the function in Analysis Office 2.8 SP12 repeat titles of a crosstab. The latest version also offers a new API method called SaveBwComments and some new technical settings like
But for me the best part is now the Data Warehouse Cloud connection. For this, you have to create a connection in the Insert Data Source dialog.
Since last week Analysis for Office 2.8 SP8 is available, and you can download it with your S-User. I just got a question if I could write about it, so here are the notes which fixes some bugs:
Since last Friday the latest version of SAP Analysis for Office is now available. You can download it in the SAP Launchpad. As the last version we have some bugs which are now hopefully fixed:
Since a couple of days the latest version of Analysis for Office 2.8 is available. Michael was a little faster and notice it on Twitter before me. So let's look into it. SAP fixes a bunch of bugs with this service pack. Here is an overview:
My video course about Analysis for Office is done. It will be released on 06.07.2020 @ 8:00 am MESZ. You find it under video.analysisoffice.net and you get more than 4 hours of 4k content for only 199€. The content will be extended if it is necessary. Here is a overview of the curriulum.
Every chapter has a lot of videos to explain certain functions in detail. You can also view some letures as a preview for the course so have a look into it. Here are some examples with to show you what you can expect.
I know its been a while since the last blog post. But I am currently working on the Analysis Office Video course to get it done. When you follow me on Twitter or Linkedin you get an update all the time. But back to topic. Analysis Office 2.8 SP3 is available almost over one week so let's look into it.
These bugs are fixed now:
So its not much but after SAP released the standalone addin for SAP Analytics Cloud, they still working on Analysis for Office.
Currently I am working on my Analysis Office Video Course. It will be a full discover of Analysis for Office. So if you are interested in it, you can join the list to get a special price when it will be launched.
Thank you for all your support.
After the latest service pack of Analysis Office 2.8 was released this week, the online help is now available. Analysis Office now offers a button for creating and deleting of comments in the ribbon menu. In former versions, SAP used the text of Microsoft Excel for comments. As Microsoft renamed the comments to notes, it was inconsistent in the ribbon and the context menu. Now it is consistently again.
Also new is the file system setting UseLegacyModeForFormulaContext which allows you to specify the mode for creating formulas with Table Design. And in the planning area there is a new setting called PlanningFunctionUploadFolder, which allows you to upload the values for executing planning objects from a file.
So it is nothing big, but Analysis Office is still developing and not dead, even if there is a new SAC Addin. I hope there will be some feature I submitted.
If you want the latest edition of Analysis Office - The Comprehensive Guide, you can get it now with a 20% discount until easter 2020. Use the code Easter2020 in the online shop.
If you have any questions or need any help, don't hesitate to contact me.
Until now, we speak of Analysis Office 2.8, if you have different text maintained for an infoobject, Analysis Office shows only the short text in the prompt dialog. The maintenance view of the InfoObject shows all different texts.
It is done, the latest version of my book Analysis for Office - The Comprehensive Guide is available. It was a lot to do and so the book has now 346 pages. The 4th edition had only 299 pages. So there is some new stuff inside. The first 5 who contact me will get a promo discount code. Also I am happy if you want to write a review you get a review discount code.
So you can buy it in the store. In future I will create more videos if there is any interest. So please leave a comment.
Yeah finally is Analysis Office 2.8 available. It took a long time. Here is a overview what's new.
This are some sweet features even not everything is running smoothly.
Since last week the latest version of Analysis Office is available. I installed it right away and want to test it. And what I have to see? You cann't add hierarchies into the rows or columns of your crosstab. I tried to record it maybe you can see it in the video below.
This week I discussed with a colleague. He ask if there is any way to filter multiple entries in a hierarchy because there is no Paste or Upload function in the hierarchy filter dialog.
The Analysis Office Workbook saves a lot of information in it. The Microsoft Excel format xlsx is a container which can save a lot of other information besides the normal data. When you rename a xlsx file to a zip file you can extract it with for example 7-Zip. Now you see the following folder structure.
Now the book Analysis Office - The Comprehensive Guide 4th edition is now available via digistore24.com. You can use credit card, paypal and other payment options.
The latest version of Analysis Office is now available. It was released on 27.09.2019 and after the go live of my project is now done, I may have time to look more into Analysis Office again.
As you can see in this version there is not many things fixed or maybe they found not more? But the What's new Guide lists some new file system settings (technical parameter)
The admin guide now documented now all these parameters. A nice parameter is UseKeyForHierarchySelection, which allows you to define if you want to see the keys of a hierarchy in the first coloumn or second column of your prompt.
Until the end of the month you can get my book with 30% off. Just use the discount code Summer2019. You can buy it here.
It's a kind of summer break at the moment and I haven't done a lot for this blog in the last time. But SAP now release the 7th service pack of Analysis Office.
You can now use the filtering data access mode with SAP Analytics Cloud data sources, analytic and planning models. There are also 3 new file system settings:
The setting ShowCutCopyPasteInGridContextMenu allows you to sepcify whether the cut, copy and paste entries should be available in the context menu for a crosstab cell. The setting EnableImprovedAddInCompatibility should improve the compatibility with other Microsoft Office add-ins. How this work we will see.
The setting UseSeparateDisplaySettingsForFilterDialog you can define if the display settings defined in the filter dialog should be saved with the workbook.
So as you can see nothing spectecularit fixes also a little bit:
I am still looking for Analysis Office 2.8, but now I am first enyoing my vacation.
It has been a quite while since the last post. But with a go live and a reorganization of my current project I hadn't time to look into some topic. But here is a new kind of interessting bug. We had an open task from a user of the controlling department, that a query display the message:
"Size Limit of result set exceeded."
Since 24.05.2019 the latest service pack of Analysis Office is available. At the moment I am working on a test tool to automate testing in BW so I can easy check if any change of mappings and so on has an impact on my ADSO or query result. But I found time to install it. A "bug" what I found was when I want to insert a data source from the recent list, nothing happend. The solution was to reinstall Analysis Office and now it works. Here is the list of bug fixes:
Since last week SAP published the latest version of Analysis Office 2.7 SP5. I don't know if any other version ever had a service pack so high like the actual version. At this time SAP doesn't offer the newst help file under help.sap.com/boaa. So stay tuned if there is something happening. Here is an overview about what they fixed:
Since last week the latest SP of Analysis Office is available. You can download it here, but you need a S-User. I know I am a little bit late but here here is the overview of what is now fixed:
It is done, the newest edtion of Analysis Office - The Comprehensive Guide is available. It took me this time a little bit longer to update it. There are several reasons like my project workload or the the private stuff which make it sometimes difficult to write on a book and tests functions in peace without noise. You can now find it in the store and here is the table of content. It covers Analysis Office 2.7 SP3 and contains 299 Pages. It is only available as PDF but it is as always DRM free. Also is the 3rd edition now available on amazon.
Since the 21.12.2018 the latest service pack of Analysis Office 2.7 is available. I looked in the morning into the Support Portal but it wasn't released and in the afternoon Patrick wrote on Twitter, that Analysis Office 2.7 SP3 is released. I thought it wouldn't released anymore in 2018 but here are the fixed problems:
I received an email from Jean-Pierre, who tried the new function Data Source for Defining Formulas of Analysis Office 2.7 - but it didn't work as he mentioned in his email. The option Select Data Soure for Defining Formulas was gray out for BEx Queries. It worked when he insert the Multiprovider or the Composite Provider. So I was very confused and tried to find a solution.
It is a little late for Analysis Office 2.7 SP2 but SAP just delivered Patch 1 so I can write a short note about it. Finaly SAP give you the option to Create a Web Application in Lumira Designer. But if you installed SAP Design Studio an SAP Lumira Designer, it always opens Design Studio. Analysis Office doesn't care if you first install Lumira or Design Studio. Design Studio always wins. At the moment there is no setting like the DefaultBWQueryDesigner for the old BEx Query Designer or the new Eclipse Query Designer.
Patch 1 also fixes some little bugs.
I think it is nothing spectacular but it is maintenance.
It's been a while since the last blog post and I am also late for the news that Analysis Office 2.7 SP2 is available. As Patrick told me on 05.10.2018 Analysis Office 2.7 SP2 is GA. But I was on vaction so I didn't find the time to write about it. So here we are:
This is fixed in Service Pack 2
When I have more time, I will look deeper into Analysis Office 2.7 SP2 and write a short overview.
Since last week Analysis Office 2.7 SP1 is available. Here is a short overview what SAP fixed in this verison.
Since Analysis Office 2.6 you are able to launch workbooks from BIP with variables. The biggest problem is to have a BI Platform 4.2 SP5 and time to test this feature. In case of writing an updated version of Analysis Office - The Comprehensive Guide, I now have a BI Platform which fulfilled the conditions. According to SAP slides, the command looks like this:
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.
Today Analysis Office 2.7 was released. Thanks to Zisi1990, who pointed me seconds after it was released that it is available. You can download it with a S-User. Here is a short overview what's new in Analysis Office 2.7:
After a short vacation over Pentecost I have now time to write "What's new in Analysis Office 2.6 SP3". A short overview yo find in this post. The user guide shows some new things:
The admin guide has 20 new pages to the Analysis Office 2.6 guide. The settings chapter is new organized and in my opinion a little bit better organized than before. Also new is the topic how to use the BI Platform with hyperlinks and variables like the old BI Portal. This is described in section 5.9.4 in the admin guide.
Since yesterday Analysis Office 2.6 SP3 is available. You need as always a S-User to download the latest version. Here is a short overview what it might fix.
Since last week the new version of my book SAP Analysis Office - The Comprehensive Guide is now available. You can find it only here in my shop. The book is based on Analysis Office 2.6 and covers now all about:
The table of content can be found here. If you have any questions feel free to write them into the comments.
One question from me:
Is it ok, that there is only Paypal as payment method or do you need more? Just write it in the comments and I will see what I can do.
Analysis Office 2.6 SP2 is available since two weeks. There are only some slight changes to Analysis Office 2.6 SP1. You are now able to set the selection type for the formula SAPSetFilterComponent to MEMBERSELECTORBYRANGE. So you can use it to define the filter dialog with range option as standard.
Also a new selection type for the VBA API SAPCallMemberSelector is now available. It is called MULTIPLEBYRANGE, which allows you to select a range of members for filtering. So if you found new functions, feel free to write them down in the comments.
Since two days the new version of Analysis Office 2.6 is available. You need as always a S-User to download the latest version. Here is a short overview what it might fix:
A overview what's new will be available next week. If you find something interesting, please feel free to post a comment.
Analysis Office 2.6 SP1 is available since last week. When you look at the What's new guide from SAP, you see nothing spectacular new. You have now a new file system setting called AlwaysDoApplicationSteps. You can use this setting to write entries into the table RSDDSTAT_OLAP afer each action without the need to close Analysis Office.
Since today Analysis Office 2.6 SP1 is available. You need as always a S-User to download the latest version. Here is a short overview what it might fix.
A overview what's new will be available next week. If you find something interesting, please feel free to post a comment.
As you might know, Analysis Office 2.6 is available now. To download it, you need a S-User with download credentials. First SAP BusinessObjects Analysis for Microsoft Office is now called, SAP Analysis for Microsoft Office. Damn I have to rewrite my book, just kidding. Here is a short overview of What's new in Analysis Office 2.6:
So I just downloaded Analysis Office 2.6 and maybe I can write a little bit more in the next days.
In the last month I was very busy with my thesis and my project at work, so I had no time to write a lot of blog posts. So here is a short overview what Analysis Office 2.5 SP2 fix. But keep in mind, Analysis Office 2.6 is up coming.
A really cool feature of Analysis Office 2.5 can you see in the "backend" under File >> Analysis. The function Customize Analysis offers now new options. Besides the Customize User Interface from Analysis Office 2.4 which allows you to change the ribbon menu of Analysis Office, you now can directly modify the technical settings of Analysis Office.
This week is very short in case of the day of German unity and we have sprint change next week, I haven't time to write a lot so here is just one goodie. When you insert a DataSource in Analysis Office 2.5 you see the recent inserted DataSource. Now in Analysis Office 2.5 you also see the technical name of the query and the system. I think this is nice if you are testing a query on different systems.
In my current project I work a lot in the backend of a BW 7.4 on HANA but I finally found time to look into Analysis Office 2.5. Here is a short explanation about the feature that the scaling factor works now with conditional formatting. If you configure your conditional formatting like the following screenshot and apply after that your favourite scaling factor. It automatically adjusts.
Analysis Office 2.5 is finally available since the end of August for everyone. After I submit my diploma thesis last week, I have now time to see whats new in Analysis Office 2.5. Here is the first short overview of the new feature:
At the moment it is very quiet about new blog post. This is maybe the summer break everybody talks about. ;)
I am at the final phase of my diploma thesis and have no time to do cool stuff and also SAP doesn't release Analysis Office 2.5. It is still in ramp up and nothing new at the moment.
So I could only say, my current project is a BW on HANA and I am testing how HANA SQL Script (AMDP) works and how fast we could build some stuff. First tests show a transformation with routines for 800.000 records about 1:55 minutes. The same transformation with AMDP (ABAP Managed Database Procedures) only need between 15 - 23 seconds. This is really impressive.
So stay tuned, if SAP will release Analysis Office 2.5 and some examples with AMDP.
Last week the Analysis Office 2.4 SP3 was released. Here is a short overview of notes, which may solves some problems.
After I had time in my parental leave to read proof the second edition of my book, I can say it is now finished. The second edition is now available in the store. You can download the table of content here. This version is only available here and at the moment not on Amazon.
If you have any questions feel free to ask and send me either an email or a comment. I hope to publish regularly in the next weeks so stay tuned.
In this blogpost I want to know if there is an interesting about an Analysis Office Video Course. Thanks for your time and your answers. Open the survey.
On 10.03.2017 a new service pack for Analysis Office was released. The main reason why you should update from SP0 or SP1 is that they fixed a lot of bugs in it (and maybe they build some new). Here is a short overview of the fixed problems:
There are also some changes in the User Interface Customization.
A new feature of Analysis for Office 2.4 is workbook or sheet protection. It is similar to the Microsoft Excel Protect Sheet / Workbook function, but it is also different. When you need to protect your workbook or sheet and you use the Excel functionality, you can not refresh or drill down your Crosstab. But when you want to refresh your data or give the users the opportunity to drill down or filter, you have to use the workbook / sheet protection from Analysis for Office.
And also these parameters:
Only then AppBuilderCompanyProfileDirectory is a PerMachine parameter, the others are UserRoaming. How the other parameters exactly work has to be tested. The Ao_app.config also provides new parameters:
So it can be possible that the user can choose between the old BEx Query Designer and the new BW Modeling Tools. The default parameter is "0", so at the moment I cannot say what exactly has to be the value for other query designers. The parameter DoSelectorHierarchyExplicitSelection should select only the child nodes of a node and the parent node is not automatically checked. But I haven't got a chance to test this. So I can't confirm this. I don't know at the moment how the parameter AutoCompleteFetchMemberLimit works, I only know the default value is 30. But as soon as possible I add these information to this post.
A new feature of Analysis for Office 2.4 is that you have a function called Customize User Interface. On the first view it looks similar to the Excel customize ribbon function. But it has more flexiblity to switch between different interfaces. First you have to open the Customize User Interface dialog via File >> Analysis. The following dialog appears.
Since yesterday Analysis for Office 2.4 is available. You can download it with a S-User under Support Packages and Patches >> By Alphabetical Index (A-Z) >> A >> SBOP Analysis Office >> SBOP Analysis MS Office 2.4.
The SAP help page isn't updated yet. But I think it would be updated in the next days.
This week I don't have much time to write an article about Analysis Office, but I found an interesting article from Xavier about Migrating Analysis for Office workbooks from BW to BI Platform last week.
The topic came up yesterday as a collegue asked me if I know why he can't save a workbook to the BI platform. The workbook was opened from a NetWeaver system and than the option save to BI Platform isn't available.
So read this article if you have the same problem.
You can define the RuleID, if you do not define an ID, the system generates one automatically. As Data Source Alias, you have to enter the data source alias, e.g. DS_1. As Position you can choose between Before, After, BelowHeader or BesideHeader. For PositionBy you can enter one of the following elements:
Here is the post Analysis for Office: How to work with Excel formulas as a short video.
Since Analysis for Office 2.3 was released, you are able to cancel long-running queries. In the Ao_app.config you can define the parameter CancelPopupDelay. The default value is 5 seconds. You can change this setting to specify after how many seconds the cancel dialog should be displayed. The dialog could appear when you inserting a data source or navigating through the data and it took more than 5 seconds.
If the dialog is displayed, you could press "cancel" to cancel the server request. In the following message dialog, you can select restart to go back to the workbook and the data source is active. Or you select close to go back to the workbook and the data source is offline. If you don't cancel the request, the dialog will disappear automatically when the server request is completed.
It is done. My Analysis for Office book is finished. You can now download the table of content and the release of the book is next week. Have fun and if you have feedback send it to me.
Have a nice weekend.
In Analysis for Office 2.2 SP3 some new interesting parameter for Ao_app.config were delivered. The following parameter are interesting and in some case maybe very useful.
Use this setting to define the maximum number of parallel threads that can be used to open the SAP HANA DataSources of a workbook. This only works with SAP HANA.
Use this setting to specify the mode of the prompting dialog when you open a workbook from the NetWeaver that contains exactly one DataSource.
The function SAPGetVariable returns the value for a specific SAP Business Warehouse variable. For example:
Analysis for Office has different two kinds of variants. One is a workbook variant and the other is a DataSource variant. So what is exactly the difference?
If you have a workbook you can save your variants in the prompt dialog.
I just got access to a NetWeaver 7.5 SP2 and I want to test it with Analysis for Office 2.3. So I open Excel and insert a query. And here we go first error: "unable to open data source", so I thought maybe the query is broken and I developed a new query and insert it. Here we go, same error. Maybe queries don't work, so I insert a InfoCube directly. Same error...
Now I refresh the insert query and got an Analysis for Office message: Error while executing function module: BICS_PROV_OPEN
In the explanation was one line with the hint "wrong parameter type in an rfc call", so I looked into st22 and saw a dump which the message: CALL_FUNCTION_ILLEGAL_P_TYPE
A new feature of Analysis for Office 2.3 is a macro called SAPOpenWorkbook. Some of you maybe know the old sapbex.xla!SAPBExReadWorkbook or the BExAnalyzer.xla!runWorkbook command.
The user guide explanation for this macro is:
You can use this API method to open an Analysis workbook. The workbook that should be opened can be stored on a SAP NetWeaver server or on a BI platform.
The workbook is opened in the same Microsoft Excel instance. After the command execution, the opened workbook is active.
As you know the sapbex.xla!SAPBExReadWorkbook respectively BExAnalyzer.xla!runWorkbook command needed either an established connection from the addin or you created your own connection with the following code:
Here is a short overview about the new features of Analysis for Office 2.3
I think there is some cool new stuff in SAP Analysis for Office 2.3 available. Can't wait to see the next version.
I just download the version and hope to write more in the next days.
Since Analysis Office 2.1 you have a formula editor. You can find this function in the group Data Analysis >> Calculations >> Add Advanced Calculation.
Since three days a new service pack of Analysis Office is available. Now you can use Analysis Office and Excel 2016.
Here is a summary of new functions
Detail information can be find in the What's new Guide.
If you want to delete or rename an Analysis for Office Workbook, you have to right click on the Workbook Opendialog.
The most important thing when you are working with VBA in Analysis for Office, is the refresh of the DataSource(s). Otherwise nothing works. Once a connection to the Business Warehouse is established, each DataSource needs to be refreshed.
If you have only one DataSource, that is the source code:
If you have more than one DataSource, this is the source code:
So that you know if the refresh was successful, you should use the following source code:
Therefore you can check lResult to 1 or 0. If you refresh the DataSource(s) each time, a lot of time is wasted. To counteract this, there is the following source code:
This command checks if the DataSource is already active. Here an example source code:
After this you can excute your own code.
So far is nothing in the help file, but it is called in the SCN Forum under "Whats new in Analysis for Office 2.1".
The command is:
In BEx Analyzer you could jump into another query from a query / workbook. The GoTo-Function make sense if you have one query for overview and one for detail.
When you have macros in your Analysis for Office Workbook and you remove the Crosstab of a DataSource, Analysis for Office maybe crashes. Therefore, if you need the DataSource no longer, you should remove it completely and not only the Crosstab.
In Analysis for Office 2.0, SAP has changed the name of the addin. The command is now:
Now you can continue working with the SAP Analysis commands.
Update: Since Analysis for Office 2.3 you also can filter your data by uploading a file.
As in BEx Analyzer, there is important information that you would like to represent in a Workbook. If you have worked with BEx Analyzer, this screenshot should be familiar.
A limiting factor in SAP BusinessObjects Analysis for Office is the restriction to 500,000 cells per DataSource. The limitation is calculated from rows * columns. This limitation can be increased by the following parameter value in the registry:
HKEY_CURRENT_USER\Software\SAP\AdvancedAnalysis\Settings\DataSource\ResultSetSizeLimit = -1
The Value “0” means that the default value is defined by 500,000 cells. Values greater than zero will return the entered value as number of cells. If you want to use the Business Warehouse settings you have to set the Value “-1”.
The corresponding parameter value for this can be found in the Business Warehouse table RSADMIN. The Object is “BICS_DA_RESULT_SET_LIMIT_MAXHKEY”.
There are two ways to define a default workbook in Analysis for Office (AO). Either every user can define autonomously a Workbook as default Workbook or the IT sets this globally for all users permanently. The first way is very time-consuming because you have to define the default workbook for every Analysis Office User manually. The second way is much more comfortable.