Skip to Content

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:

  1. it is still little known albeit possible since BW 1.2,
  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,
  3. 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
Excel 2007 Example Currencies: 2003 vs 2007 Hierarchies: 2003 vs 2007 Filter menus fully enabled in Excel 2007
To report this post you need to login first.

37 Comments

You must be Logged on to comment or reply to a post.

  1. Deepu Sasidharan
    Thanks for the great blog!

    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.

    (0) 
  2. Alexander Kästner
    hi there, actually i do not see any values (key figures). dimensions (characters) are displayed in my pivot table – but no values of key figures. can anyone help me?
    (0) 
  3. Jeri McNeany
    Thanks for this blog! If we can get this to work, it will be much appreciated.

    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

    (0) 
    1. Thomas Zurek Post author
      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

      (0) 
      1. Chung-Ching Chiang
        Do user to run query on Excel need SAP gui??  I presume only the developer need all these SAP BI 2007 SPS 14 and GUI 7100 to create an URL for user to execute>  Am I correct??

        chung-ching chiang

        (0) 
    2. Frank Ernst
      Hello!

      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

      (0) 
    3. Mark Zuchowski
      I am also getting this RFC unicode error message on the SAP 7.10 GUI.  What is name of the backend RFC destination that is used for the ODBO connection?  I would like to make sure the unicode settings are correct for this RFC destination in SM59.
      (0) 
  4. Burkhard Drews
    This functionality looks very promising and would satisfy the need of our finance users.
    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
    (0) 
    1. Thomas Zurek Post author
      Sorry, I can’t follow your comment on the memory consumption. We have run extensive internal benchmarks that show that our MDX interface performs as well as the internal interface used by our BEx suite. The same applies to resource consumption.

      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

      (0) 
  5. Hello 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).

    (0) 
  6. Ming Zhou
    anyone got this error?

    “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.

    (0) 
    1. Thomas Zurek Post author
      Hi Ming,

      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

      (0) 
    2. Rama Shankar
      Hi Guys,

        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

      (0) 
  7. Debra Masdea
    Thomas, it seems that the biggest gap with Pivot Table services is the missing RKF, CKF, and structures that are critical to the end user’s understanding and easy usage of the data.  Is this an area that is actively being worked by SAP/MS?
    (0) 
    1. Thomas Zurek Post author
      Hi Debra,

      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

      (0) 
  8. Victor Figueroa
    Hi, in BPS for input using Office Web Component, this is compiled in BSP where call Excel Layout.

    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 ?

    (0) 
  9. Jody Chassereau
    Any ideas what would be necessary to correct the following message when trying to access a data target with Excel 2007?

    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…

    (0) 
  10. Mustafa Aydogdu
    Hi,

    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

    (0) 
  11. Samuel Palaparthi
    Hi Thomas, can we use the drivers that come attached with note 1134226 mentioned in your blog? It is mentioned in it that they are Simba files. will there be any licensing issue?
    (0) 
    1. Thomas Zurek Post author
      Hi Samuel,
      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

      (0) 
      1. Samuel Palaparthi
        Hi Thomas, are BEx variables supported at this time? can you please provide some info on user prompts. is there any way to calculate MTD and YTD values in one report using ODBO?
        (0) 
        1. Thomas Zurek Post author
          Hi,

          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

          (0) 
          1. Samuel Palaparthi
            Hi Thomas, Is it possible to model MTD and YTDs based on user input? I mean if current month is 10/2010, user wants to enter/select any month in the past for example 05/2010 and get Monthly KFs(MTD) of 5th month and YTD of 5/2010?
            (0) 
            1. Thomas Zurek Post author
              Hi Samuel,
              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
              (0) 
              1. ryan mussa
                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?
                (0) 
                1. Thomas Zurek Post author
                  Hi Ryan,
                  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
                  (0) 
  12. Martin Søgaard
    Hi 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

    (0) 
    1. Thomas Zurek Post author
      Hi Martin,

      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

      (0) 
  13. Seema Chandran
    Hello Thomas,

    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,      

    (0) 
    1. Roman Moehl
      Hello Seema Chandran,

      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

      (0) 

Leave a Reply