New in SAP BI 4.1 SP02: Microsoft Excel as data source for Web Intelligence
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!
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 |
|---|---|
 |
 |
Note: This is still not possible with the HTML client.
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
4. Browse to your .xls or .xlsx file
5. Click Add
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
3. Browse to your .xls or .xlsx file
4. Click Replace
5. Confirm if you want to replace the file or not
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.
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
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
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
7. Preview data in the Query Panel > Run Query
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:
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?
Thanks, Patrick! Very clear and precise instructions. Keep em coming!
Very nice write up. The screen shots are very helpful.
Thanks!
Thank you for the feedback. I have couple extra ideas, hopefully as helpful!
Cheers.
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?
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?
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%.
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 🙂
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?
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.
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
Hi Gregory,
Thank you 🙂
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.
Nice share ! Thanks 🙂
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.
Thanks, Patrick!
I like the cartoon presentation which is informative and funny.
Screen shots helps me a lot.
Keep posting.
Good Luck!
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 !
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!
I am facing the same problem in WEBI. Once saved I am not able to change the source path of Excel file?is it by design or am I missing something obvious?
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.
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.
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.
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....
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 ???".
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!!
Maybe yes.
You have to test with your security matrix on CMC. If I can check on my side, I'll upload the solution.
Great! Leave it to SAP to provide a cool feature without a cool way to control it....No surprise of course....
I am not concerned about the fact that you have to "refresh" the excel data source as Saundra is.....
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
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
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
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
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.
I'm seeing the same behavior with 4.1 SP4.3. were you able to get this resolved?
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
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.
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
I am facing the same problem in WEBI. Once saved I am not able to change the source path of Excel file?is it by design or am I missing something obvious?
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.
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,
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.
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).
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
very useful functionality about Replace file
Thank you very much for this useful tutorial, but i have a question about an error:
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.
I solved my problem. I gave compatibility to IE 11 and the error ends.
Regards.
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?
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
Hello we are also experiencing the 30270 error. The error is sporadic in our case and happens with webi's run in the background. Our excel files are stored on a NAS so we use a UNC path to point to the excel file.
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