Native Excel 2007 On Top Of Netweaver BI 7.0
Using Excel in the context of BW* is nothing new: (1) there is BEx Analyzer and (2) there is the native pivot tables inside Excel that connect via OLE DB for OLAP (ODBO) to BW. While (1) has mostly been the option of choice, option (2) has tended to be sidelined for the following reasons:
- it is still little known albeit possible since BW 1.2,
- Excel’s pivot tables were really very basic and not a viable alternative to Excel add-ins – be it BEx Analyzer or add-ins created by our competitors,
- meta data integration between BW and Excel’s pivot tables was poor: e.g. external hierarchies showed up as separate dimensions or units and currencies of key figure values were not displayed (see figures 2, 3 for examples of the situation with Excel 2003).
Interesting things have happened in the meantime and with Excel 2007 together with NW BI 7.0 SPS14 problems 2. and 3. have vanished. The goal of this blog is to make you aware of that (and thus to tackle 1.) and to show you that using native Excel 2007 on top of NW BI 7.0 is now a viable and additional alternative. See figure 1 for a simple example.
So what has changed? First of all, Microsoft has heavily invested in reworking pivot tables in Excel 2007: UIs and a large number of features that make it a viable BI client overall. When Excel 2007 connects to an ODBO provider it asks for the latter’s abilities which it then exposes to the end user. Actually, this mechanism worked only with Microsoft’s Analysis Services. With BW’s old ODBO provider (and the associated MDX counterpart on the server side) abilities did exist but were simply not exposed at all so that a basic default offering applied (see example in figure 4).
Now, here is a list of things you should consider when you want to test this new offering:
- Make sure that the BW system that you want to use is on NW 7.0 SPS14.
- Make sure that you have the right SAP ODBO driver installed on your PC. Check OSS note 1134226 for details.
- Check the step-by-step guide Connecting to SAP BW with Excel 2007 PivotTables and ODBO provided by our friends from Simba. You find it in SDN or via Simba’s homepage (www.simba.com).
- Check more details around the topic in this presentation.
Finally, I like to invite you to provide us with feedback. This blog has been kept short on purpose and there many more interesting little details to talk about. Overall, the nice message is that is now really hard to notice whether Excel 2007 is connected to MSAS or to BW. Let us know what kind of technical information you would like to see in SDN on this topic.
PS: Regarding the many questions on the non-unicode problem (see comments below) please check OSS note 1173537.
* BW is now officially labeled NetWeaver BI (NW BI). As BI is also frequently used in its general, non-SAP-specific sense we use BW in this blog.
Figures referred in the text. Click on them to enlarge.
| Figure 1 | Figure 2 | Figure 3 | Figure 4 |
|---|---|---|---|
 |
 |
 |
 |
Will there be an advanced correction provided by OSS note or SPS for BW 3.X and older SPS for BI 7.0 to implement this functionality.
We attempted to do this using our BWT system (BI 7.0) which is not Unicode and we get the message Cannot process unicode RFC in non-unicode system when we attempt to log into our BWT system through Excel 2007 as described through this blog and the related documents. There was no mention that Unicode was a requirement. Is there a way to use this technique with a non-Unicode SAP BI 7.0 system?
Thanks,
Jeri
the ODBO connection uses some libraries that ship with SAP GUI. I've had the same problem as you a few months ago and it disappeared when I updated the SAP GUI to a recent version. The one that I'm currently using is 7100.1.6.1038.
Regards
Thomas
chung-ching chiang
I am on the same SAPGUI version as you are and still get the Unicode error. Any other ideas??
thx
Katrina
Did anyone resolve this Problem?
Ist there a way to set the OLE DB provider to non-unicode?
May this problem be related with the librfc32u.dll, the newer unicode version of the librfc32.dll?
Any new Ideas?
Thanks.
Frank
I found a Workaround: The OLE DB provider distributed with the SAP GUI 6.40 CD works.
Frank
My only concern is the memory usage on the SAP Server Side which (from earlier experience) is tremendous once ODBO interface is used and the MDX processor is needed. Is there a possibility to control the memory size and why does the MDX processor consume all the memory?
Burkhard Drews
There are certainly queries that consume lots of memory despite showing only a few lines as a result. The prototypical example for that is a top-n query, e.g. the top-10 customers according to sales. Then you need to look at each and everyone of them to see if he/she qualifies for the top-10. If the list of customers is long (e.g. telco company) then this consumes a lot of resources albeit showing only 10 lines as a result.
But that does not depend on MDX or any other client interface.
Regards
Thomas
I apologize for being late in responding to your blog, but I see a lot of potential in the native Excel Pivot Table integration with Netweaver BI and would like to replicate your test in a landscape that is easy to understand and deploy without basis support.
I'm somewhat familiar with the BEx Analyzer 3.5, Essbase 5.0, and Crystal Reports 9.0, apart from being a BPX user and configurator in R/3 3.1, 4.0.
4.6, and 6.0.
The native Excel seems to be in the direct competition with the BEx Analyzer and BO, but that's just my impression as an end user, not backed by any concept or end user acceptance testing.
Before I can dive deeper into your proposed solution I would like to you confirm the basics of the required installation.
My first impression is that for a concept setup the following software is needed on a Windows XP platform?:
1. SAP Netweaver 7100.1.6.1038 (The highest I could find is this
trial download).
2. Microsoft Excel 2007 (I'm not very familiar with Microsoft's end-user support but I have found this Excel version).
3. Implementation of Notes: 1134226 (cannot read it with only public access to SDN)
If I can replicate successfully your test, I stand a better chance of converting accountants and financial analysts out there to use this solution
over the plethora of "dead" vendors and custom tools out in the financial reporting and planning world (at least here in the US).
latest ABAP download in my prior message.
I apologize for
the redundant spaces,
too
😉
"test connection failed because of an error in initializing provider. unspecified error".
when I follow simba's instruction in excel data source connection wizard I got this error.
I'm not sure but I think you met the problem that Simba describes in their document Connecting to SAP BW with Excel 2007 PivotTables and ODBO (for URL see blog) on pages 9 and 13. Check there for details.
Regards
Thomas
Here is the solution for SPS13.
Symptom
When trying to set up a data link in Excel 2007 using the SAP BI ODBO connector, a message appears saying that the tool “Cannot process Unicode RFC in non-unicode system”, and advises that the user read OSS note 875767:
OSS Note 875767
Note 875767 - ABAP runtime error
The error is caused by inconsistencies between SAP backend patch level SPS13 and front-end DLL and OCX files which are delivered as part of SAP GUI 710 (refer to version information below).
Information
The ABAP runtime error CALL_FUNCTION_NO_UNICODE_SERVER is returned to the client by non-Unicode systems as of the following patch levels:
Backend SAP BI 70 system with SPS13
Front-end SAP GUI 710: Patch level 05
Solution Summary
The solution requires that the SAP BI backend and front-end environment be maintained at a particular state with certain OSS notes applied and certain OSS notes not applied thereby making this a volatile solution. Refer to the below solution details.
The ideal solution would be to wait until you upgrade the SAP BI backend system to SPS14 as recommended by SAP. However, due to project priorities if you can not wait until SPS14 upgrade the below solution will work as long as backend and front-end components, patches, notes and changes are maintained without conflicting with Excel 2007 connectivity.
If you decide to go-ahead with the below solution any future changes or notes applied to the backend or front-end BI components will have to be tested to make sure no impact is caused to this functionality in our sandbox before moving forward to our Dev, Test and Prod SAP BI environments.
This solution does not fix the known ODBO silent connection warning message boxes (see below) and also refer to page # 11 in Simba ODBO how-to document (Connecting to SAP BW with Microsoft Excel 2007 PivotTables and ODBO.pdf ) attached to original email. This error message will happen every time when the end-user tries to execute a SAP BW report within MS excel 2007.
Solution Details
1) Confirm that OSS note 1134226 (New SAP BW OLE DB for OLAP files delivery - Version 3) is not applied in the local PC where SAP GUI is installed. One way of confirming that the note is not installed is to validate the below folder snap shot with the current PC folder contents “C:\Program Files\Common Files\SAP Shared\BW\OleOlap”.
If OSS note 1134226 is not installed the folder contents should match the below screen snap shot for the mdrmsap.dll, scerrlkp.dll, and mdrmdlg.dll files. The “xx-new.dll” files shown below are files from OSS note 1134226 which are not used. They are shown below just as reference.
If you register the “xx-new.dll” files provided as per OSS note 1134226 shown above with the newly provided wdtlogu.ocx version (7100.1.0.77) file provided by SAP as discussed below you will receive an “internal communication error “ (also refer to screen snapshot below).
2) Register new version of wdtlogu.ocx provided by Stephan Kalweit
a. Un-register using regsvr32 – u in DOS prompt the original wdtlogu.ocx provided with the SAP GUI from the folder “c:\Program Files\SAP\FrontEnd\SAPgui”
b. Copy the newly provided version (7100.1.0.77) of wdtlogu.ocx into folder “c:\Program Files\SAP\FrontEnd\SAPgui” and register using regsvr32 in DOS prompt
Regards,
Rama Shankar
you can use those objects with native Excel, i.e. with any MDX client. As native Excel has no environment to define, e.g., RKF and CKF you have to use BW's query designer to define a BW query using those objects. Simply release that BW query to be used with ODBO. That BW query is exposed as a cube to native Excel and can consequently be used with PTS.
Regards
Thomas
Is posible in Portal, Visual Composer or BI Web show Excel file inside web via URL or IVIEW ?
Maybe via WebDynpro ?
We have Iview displaying files inside KM folder,here is xls file, but is opened in Excel not in Web using OWC.
Any idea for the future ?
Excel cannot find OLAP cube xxxxxx. Either the OLAP database has been changed or you don't have permissions to connect to the cube.
Consult your database administrator.
We are on NW2004s - BW patch level 16. The SAPGui is BI 7.10 FEP 3. I download and registered the 3 .dll files mentioned in note 1134226 for OLE DB for OLAP.
Thanks...
thanks for the useful information. I get the following error when there are a lot of elements in the query.
"the query did not run, or the database could not be opened"
Is there a way to solve this problem.
Thanks
yes. SAP's ODBO driver is owned by and licensed via SAP. Still, it is created by Simba. Those files simply constitute an update of the driver shipped with a SAPGUI and BEX installation.
Regards
Thomas
BEX exit variables are supported. So you can model MTD and YTD inside the BEX query. Simply expose that BEX query as a querycube to ODBO (or MDX) in order to consume it via native Excel.
A pointer on how to model YTD, ... in a BEX query is here.
Regards
Thomas
probably a I cannot provide an exhaustive answer but here are a few thoughts that might help:
1. Input variables from BEX are not supported by this, simply because (native) Excel has no infrastructure to generate the necessary MDX extensions.
2. I have not tried this out myself but maybe there is somehow a way to manually inject this via VB. Just a thought ...
I hope this helps a bit.
Regards
Thomas
Can you elaborate on how you would use VB (I assume you mean VBA) to change the MDX extensions? Where can I see the MDX code generated?
that question is solely related to Excel 2007 and how to extend and enhance that via VBA. I've seen a VBA script that tracked MDX code generated by Excel but I'm no expert here. I suggest that you check sites/forums focusing on Excel and VBA.
Best
Thomas
Could you please outline exactly what can be achieved with this integration that can not be achieved using the BEx Analyzer plugin? I don't think that distinction is totally clear in the blog.
Furthermore I'd like to comment on the password handling in this native excel 2007 integration. If the data is sensitive, I really don't think it is a good idea that the user's password is stored locally on his/her PC without encryption. Because the user will have to store the password in the local files in order to avoid too many authorization prompts, there will also be double maintenance of the password once every three months when it has to be changed by the user (in most cases).
Best regards,
Martin Søgaard
regarding the comparison to BEX Analyzer: well, consider the native Excel just as a good alternative. Microsoft has heavily invested into the pivot table functionality in native Excel and will likely continue to do so. Whoever likes that or wants to use it because he/she is familiar with it can happily do so. BEX Analyzer offers a variety of features that are not available via native Excel; with variables or the planning stuff being the most prominent ones. Vice versa, native Excel offers functionality within the same UI which would require both, BEX Analyzer and BEX Query Designer. An example is top-n or similar.
Regarding your password concerns: I think your concerns are valid. Still, they are documented in Simba's white paper.
In summary, I consider the native Excel integration as an alternative, maybe to users who are more familiar with that tool.
Regards
Thomas
It asks me for a user and password.
Thank you for your help.
We have sucessfuly implemented OBDO connection on top of SAP BW and users are happy with XCEL 2007 feature they can avail using OLEDB connection strings.
When we implemented this conection our BI sytem was on 7.0 and all reports was workning fine.We just upgraded BI sytem from 7.0 to 7.01. Now some of MDX statement generated by Excel 07/Report BW is not able to decode and output is blank e.g is while selecting two nodes of hierarchy from 5 nodes and displaying associated cost against it. It works fine if I select single node.
Also same query is working fine with current BW system if I use 2003 XLS file i.e the MDX generated by 2003 xls it is able to understand and by 2007 xlsx it is not able to.
Do we have to refer some notes,simba drivers or patch?
Thanks,
I guess that this is a problem in the MDX engine. In order to analyze this problem in more detail, please raise a support message for component BW-BEX-OT-MDX.
Thanks and best regards,
Roman