Web Intelligence documents using a Microsoft Excel personal data provider is a feature no longer only possible in the Rich Client (Desktop).

I’d like to welcome WILLIAM MARCY as a collaborator to this article!

/wp-content/uploads/2013/11/image_png_331824.png

Thanks to SAP BusinessObjects Business Intelligence Suite 4.1 SP02, this is now also possible with the Web Intelligence Applet (aka Rich Internet Application aka Rich Internet Application aka Java Report Panel).


SAP BI 4.1 SP01 SAP BI 4.1 SP02
SAP BI 4.1 SP01 - Data source - small.png SAP BI 4.1 SP02 - Data source - small.png


Note: This is still not possible with the HTML client.

SAP BI 4.1 SP02 - Data source (HTML) - small.png

Uploading a new Microsoft Excel Spreadsheet

1. Logon to the SAP BI Launch pad

2. Navigate to the folder where you want to save the Excel spreadsheet

3. Open the menu “New” and select Local Document

SAP BI 4.1 SP02 - New Local Document.png

4. Browse to your .xls or .xlsx file

5. Click Add

SAP BI 4.1 SP02 - New Local Document.png

Maintaining your Microsoft Excel Spreadsheet

New in SAP BI 4.1 is the ability to (finally) manually update your spreadsheets directly in the BI Launch pad.

1. Right-click the Microsoft Excel

2. Select Organise > Replace File

SAP BI 4.1 SP02 - Replace File - small.png

3. Browse to your .xls or .xlsx file

4. Click Replace

5. Confirm if you want to replace the file or not

SAP BI 4.1 SP02 - Confirm Replace - small.png

Note: Previous copies of the spreadsheet are kept in the File Store location on the server and the file name is different.  This will not affect the link with the Web Intelligence document as it uses the CUID stored in the repository.

SAP BI 4.1 SP02 - History - small.png

Extra Note: If you wanted a process to automatically refresh a spreadsheet in the BI Launch pad, this would require some level of unsupported “hacking”.  For this you’ll need to locate the location of the spreadsheet in the Input File Store and overwrite the file.

1. In the BI Launch pad, right-click the Excel spreadsheet

2. Select Properties

SAP BI 4.1 SP02 - Excel File Location - small.png

3. This would translate to something like: E:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\FileStore\Input\a_140\088\000\22668

Important: Changing the columns or other elements of the spreadsheet could/will break the Web Intelligence document.

Alternatively you should use the SDK which is fully supported.

Creating a Web Intelligence document using an Excel spreadsheet as data source

The process is very similar to the Web Intelligence Rich Client (Desktop:

1. Logon to the SAP BI Launch pad

2. Click Applications > Web Intelligence

3. Click New (Ctrl N)

4. Select Excel > OK

5. Open a document from the servere > Open

SAP BI 4.1 SP02 - Open a document - small.png

Limitation: Unfortunately it is not possible to use an instance of a Web Intelligence document saved as Excel format.

6. Provide data source interpretation parameters > OK

SAP BI 4.1 SP02 - Custom Data Provider - small.png

7. Preview data in the Query Panel > Run Query

SAP BI 4.1 SP02 - Preview Data - small.png

Troubleshooting

Trying to refresh the Web Intelligence document where you either don’t not have enough rights to the spreadsheet or it has been deleted will return the following error:

SAP BI 4.1 SP02 - Excel Error.png

Extra information:

SAP BI 4.1 SP02 Release Announcement: http://scn.sap.com/community/bi-platform/blog/2013/11/22/sap-businessobjects-business-intelligence-suite-41-sp02-released

Installation notes: http://scn.sap.com/community/bi-platform/blog/2013/11/22/installing-sap-businessobjects-bi-41-sp02

Hope this was helpful.  Any comments or suggestions?

To report this post you need to login first.

47 Comments

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

      1. Erik Murk

         

        Trying to refresh the Web Intelligence document where you either don’t not have enough rights to the spreadsheet or it has been deleted will return the following error:

        I used to ” refresh”  the excel datasource and quite suddenly i cant refresh them anymore;

        Tried about everything and its driving me nuts..

        I dont understand the rights to the spreadsheet part; you choose an excel as a source and it works, but you refresh with this file and you get the error?

        It almost looks as these are other “routes” to the file?

        (0) 
  1. Satyam Ravada

    Thanks Patrick. Very useful information for my new requirement. I have one question. If the BOE platform is on Linux/Unix servers, Can we use excel as data source using the above method?

    (0) 
    1. Patrick Perrier Post author

      Hi Satyam, thanks for the feedback.

      That’s a great question!  I’d hope so as I can’t see really why not but I always like to back my answers with facts 🙂

      Unless anybody here knows the answer already, I have a colleague at work building a test SAP BI 4.2 in Red Hat this week.  Give me couple days to answer you at 100%.

      (0) 
      1. Satyam Ravada

        Thank you Patrick. I will wait for the answer. I have the same requirement and i need to deliver before december 31st. But, it is really a good feature.Happy to know this 🙂

        (0) 
        1. Patrick Perrier Post author

          As WILLIAM MARCY mentioned: This requires “Excel Data Access Service ” service that belongs to the class of service “Web Intelligence Services.  You’ll find it inside your Adaptive Processing Server (APS).

          I suppose you have that one on Linux/Unix also?

          (0) 
          1. Satyam Ravada

            I need to check this. As per the plan, this will be installed on 9th december. By any chance, if you get any additional info, please share.  Thank you again.

            (0) 
            1. Gregory BOTTICCHIO

              Hi,

              Yes: it is fully supported if the BI4.1 SP2 is on Linux.
              You will be able to access the Excel stored into the BILaunchPad and create a WebI report on top of it from the web.

              Kind regards,

              Gregory

              (0) 
          2. Ragnar Pall Arnason

            Excellent tips!

            Was having problems with creating a Webi report based on Excel as ds in BI4.1 and the part about – This requires “Excel Data Access Service ” service that belongs to the class of service “Web Intelligence Services” – solved it for me.

            Thank you.

            (0) 
  2. Geert Van Der Streeck

    I don’t understand why the Excelfile has to be ‘uploaded’ in the Filestore/CMS.  The CMS contains a pointer to a location on the filestore.  Why can’t the pointer in the CMS, link to a filelocation on ‘a external location’, next to the Filestore?

    It is possible to schedule reports to a filelocation, using the UNC-format for the target location.  Using the UNC-format, it should be possible for the CMS to ‘find’ the excel file on a external location.

    The ‘upload’ is a blocking issue for our users.

    Note: The concept/idea to make this possible for users that want to work 100% web-based, is very good.

    (0) 
  3. Kevin Brendel

    Thank you for the step by step instructions.

    I am having trouble modifying the “Source Path” once the Excel Data Provider has been created and saved. ie, when I go into “Edit Settings” , the pop-up dialog shows the “Source Path” that I had initially selected, but there is no “Browse” button or other means of changing the path..

    Help ! Is it a feature / user security setting that needs to be enabled, the permissions on the folder itself, or a bug ?

    Thanks in Advance for your help !

    (0) 
    1. Saundra Manning

      Hi Kevin,

      It’s definitely not a permissions problem, as I tried to edit the data source while logged in with administrator rights.  I would like to know if editing this setting is even possible.  Seems like the usefulness of this feature is greatly reduced without the ability to edit the connection to the spreadsheet.  So I’m hopeful it’s just not obvious but the capability does exist.

      Hopefully someone knows and will clarify for us!

      (0) 
  4. Jackie Dunn

    I have a couple of questions…. 1)  Does anyone have a feel for the impact on the File Repository / Storage that this can have if you have, all of a sudden, a bunch of users uploading big Excel data sets. and 2) are there ways to limit the amount of “space” that users can have for uploaded local documents?

    We have end users very excited about this feature and we are very hesitant about what the impact on our infrastructure would be by introducing it to the masses.

    (0) 
    1. Saundra Manning

      Our users were excited too. Until we found out that you can’t run a scheduled report and have it automatically pick up the updated data. The user would have to upload an updated spreadsheet to the repository for every report run (monthly, etc.).  This took away the ‘wow factor’ for us.  Currently, we host all of our Rich Client Excel data providers on a network drive that the BO server can access.  This way, it automatically picks up updated spreadsheets with each scheduled run.  We can’t see how to replicate that level of automation using the Webi option.

      (0) 
      1. WILLIAM MARCY

        Hi Saundra,

        Maybe you can create an universe based on this Excel and schedule your Web document based on this universe.

        Your data will be updated each time your Excel changes.

        Universe still be the best way to create Webi document in my opinion.

        (0) 
  5. Jackie Dunn

    Anyone have any feedback to my question above about concerns for allowing uploads of Excel documents?  We are concerned about impact / management of file repository and if there’s a way we can limit how much “space” each user has to upload these types of documents….

    (0) 
    1. WILLIAM MARCY

      Jackie,

      Unfortunatelly it seems that you can’t control this kind of limitation. The only way would be to create a batch file which scan your FRS with “.xlsx” file format and check the size. If the size is too high, you delete the file and then you run “reposcan” to fix the inconsistency between FRS and CMS.

      It means that your users are aware of this limitation in order to avoid “Where is my file ???”.

      (0) 
      1. Jackie Dunn

        Thanks for your comment – this is what I thought.  Do you know if you can disable the feature altogether?  In other words, only allow the local document upload for certain users / approved uses??

        Thanks for your input!!

        (0) 
  6. Johannes Nissen

    Hi everyone!

    Thanks for this helpful introduction and discussion on this new feature which many user’s may have been waiting for… – But for me the “wow” has gone as well.

    It would have been cool to have the opportunity to base a report on an instance from a scheduled report (like already mentioned), but I really was shocked to finding out that there doesn’t seem to be a way of updating the data of the Source-Excel even manually.

    When uploading a new version of the spreadsheet i get

    /wp-content/uploads/2014/06/replace_465525.png

    If I let it do a copy of the Document I get a new file “[1]” (with a new internal ID I believe). So far so good, but how would I get this data into the Report? I can’t edit the source information in the data provider to switch the report over to the 2nd file.

    I tried to delete the original Excel and upload afterwards, so the name stays the same, but after reopening the report the data-provider is broken (I believe due to the changed internal ID of the Report)

    So, How can I update the data in the Excel in the launchpad? what did I miss? A “replace” Button in the dialogue above would have been great.

    Thanks for any hint and sunny regards from northern Germany.

    Johannes

    (0) 
    1. Gregory BOTTICCHIO

      Hi Johannes,

      Are you trying to update the Excel file previously uploaded into the BILaunchPad?

      If yes, the answer is above, in this Wiki, at the step “Maintaining your Microsoft Excel Spreadsheet” .

      We have added a new menu item “Replace File”.

      Hope thishelps,

      GB

      (0) 
      1. Johannes Nissen

        Hey Gregory,

        yes, that helps- Thanks…

        Need to clean my glasses… 😉

        But I keep my statement that a replace button on the dialogue above would have been great.

        Cheers Johannes

        (0) 
  7. Wale Ilori

    So does anyone know why I get an error message that says

    “Unable to create or update the Excel personal data provider : an internal error occurred. (IES 10884) (WIS 10884).”

    The file I was uploading is about 29MB and it’s an XLSX file. I’ve had better experience with .XLS files but the 65,000 row limitiation is an issue.

    (0) 
  8. Patrick Delage

    Hi,

    What about restricting the users from Using this feature. I would be more than happy to say that it’s already tough to restrict users from having multiple version of a report doing exactly the samething, if they start addind external file…ouf!

    I want my user to be able to use excel for their output by not for creating report on it.

    Can’t find how. Any help?

    Pat

    (0) 
    1. Saundra Manning

      I see a right called “Desktop Interface – enable local data providers” under Webintelligence Application rights. Did you try that?  It might only be for Rich Client.  “Save as Excel” is a separate right.

      (0) 
      1. Hans RENS

        Tried that … right this last hour.

        Yes, it is LABELED “Desktop Interface – enable local data providers”

        That SUGGESTS it is for “rich client” only.

        Turns out to be mislabeled, because it works for “excel in repository folder” too if enabled.

        I copied a reporter access level, and tried to add only

        “Desktop Interface – enable local data providers”

        Which seemed not to work. Closing this CAL2 and re-opening, the right was not “added”.

        So I added “Enable desktop interface” AND this one: that seemed to stick.

        But I did NOT want to give out RichClient possibilities at random.

        It was easy to disable that right though, and the “local DP” stuck.

        Applied to a test user: check, he can now make a webi query on his excel-in-repository.

        Take away the CAL2 : he gets the error “this query cannot be edited” , even before choosing the content.

        Will re-test it later, but I thought I might as well leave the hint.

        I THINK now that :

        SAP got the functionality working, but forgot to re-lablel the right. This way it suggests you need “desktop Interface”, but you don’t

        (well, you do for LOCAL excel on your desktop PC, external to the repository, unless you have web services … )

        Am I thinking/searching in the right direction ?

        Hans R

        (0) 
  9. Ian Pye

    Hi, Has anyone had any success using the unsupport ‘hack’ described in this article relating to updating the Excel source file directly in the File Store, rather than through the infoview front end using Replace File?

    We have done some testing around this. We can locate the relevant Excel file in the file store and update it. In Infoview we can then open the file and it shows the updates made, all good so far. However… any reports (both existing and brand new ones created after the update was made) that are using the Excel file as a data source, continue to pull the original data. If we go back to using the ‘Replace File’ method everything comes back into line.

    So my guess is when the the Excel file is first loaded and when the Replace File method is used, the data is taken and cached somewhere else, thus making any changes to the Excel files in the file store redundant.

    Any one else had this experience, appreciate this is unsupported functionality, but it would really open up some options for us.

    Thanks

    ian.

    (0) 
    1. Paul Touloukian

      After you swap out the XLS on the file repository, you need to do something to update the last updated date on the Excel object.  Otherwise the WebI document that references it will use the cached data.

      We are doing this in a Java SDK program that replaces the file on the FRS and then updates the InfoObject,

      (0) 
      1. Ian Pye

        A colleague found just building the report in Desktop Client and adding the excel file as a data source at that point does the job, as the excel file is then not ‘managed’. You can then Edit the report in Web Client and as long as you don’t mess with the Excel Data Provider Query, all future updates to the Excel file are always picked up.

        (0) 
        1. Paul Touloukian

          Agreed, that is much easier.  But we are trying to maintain an environment where users do not have to use the Desktop Client.  I’m currently trying to see if we can convert a managed Excel data provider into an Excel data source that references a UNC path (like it would be had it been created in the desktop client).

          (0) 
      2. Ilyas Mohammed

        Hi Paul,

        We are facing the same issue. Could you please tell me how you were able to overcome this challenge, i.e. overwrite the XLS file on the file repository and also update its last updated date on the excel object?

        I appreciate the help.

        Thank you,
        Ilyas

        (0) 
  10. Juan Elías Rivero Domínguez

    Thank you very much for this useful tutorial, but i have a question about an error:

    Clipboard01.jpg

    Before that i run the query with the excel file, the page show me the table with the data, and put this error message. If i choose the close option, let it the page empty. How can i solve this?

    Thanks.

    (0) 
  11. Erik Murk

    Trying to refresh the Web Intelligence document where you either don’t not have enough rights to the spreadsheet or it has been deleted will return the following error:

    I used to ” refresh”  the excel datasource and quite suddenly i cant refresh them anymore;

    Tried about everything and its driving me nuts..

    I dont understand the rights to the spreadsheet part; you choose an excel as a source and it works, but you refresh with this file and you get the error?

    It almost looks as these are other “routes” to the file?

    Anyone please?

     

    (0) 
  12. Javier Martí

    Hi,

    I have already installed BO 4.2 sp2 patch 7 in windows server.

    I want to use the datasource Excel in LaunchPad, but an error appears

    I test all kind of excel file : xls and xsls

    Someone knows what happens?

    thank you very much

    (0) 
  13. Brian Nakid

    Hello Patrick,

     

    That tutorial helped quite a lot. Thanks.

     

    Any update on how we can automate that excel upload on the launchpad? I get my excel in my mailbox and the upload is done manually on a daily basis 🙁

     

    Any information you can share is most welcomed.

     

    Regards,

    Brian

    (0) 

Leave a Reply