Creating a Webi doc off of an unmanaged Excel data source on a network share

[former_member59613]

In support, we get a lot of questions and feature requests.

One request that has passed my desk a few times is the ability for a webi document to use an unmanaged Excel spreadsheet as a data source.

You might not be familiar with the term "Unmanaged" so I'll explain a little.

BI 4.1 SP02 introduced a new feature that allowed you to base a Webi Document off of an Excel spreadsheet that was published/saved to the BI Launchpad environment. This is referred to as a "Managed" object within the BI Platform.  A Managed object is something that is imported into the BI repository and that is managed by the CMS.   This is great if your data is static in the Excel sheet but lacks functionality if the data is being updated frequently.

So, the question has come to me a few times now:  Can we have a Webi document use an Excel data source that is "Unmanaged"?  Meaning it is not published within the BI Platform and exists on a network share.

I didn't know if this was possible at first but after about 10 minutes of playing around, I was easily able to accomplish this using the below steps.

Keep in mind this is just a simple walk-through and doesn't cover all scenarios, but this should give you the basics on creating a Webi document that will refresh against an Excel data source that exists outside the BI Platform environment.

So, here we go:

1. First, place your Excel file on a network share.  You can see below, mine is on a share called:  \\bipw08r2\dataSource\data.xlsx
   
2. The data is simple in this example.  3 columns and 4 rows of data to start. 
   
3. Open Webi Rich Client (WRC) and create a new doc.  Choose Excel as the data source
   
4. Type in your network share name in the File Name box.  Select your xlsx sheet as I did below
   
5. The Custom Data Provider - Excel dialog will allow you to choose a source path, sheet, range, etc... 
   
6. After you select your options, you will see the Query Panel.  Note the Source Path shows the network share and the Result Objects/Data Sample reflect what is in my sample xlsx.
   
7. I click Run Query and see my Webi document with the data from my spreadsheet.
8. To ensure my Webi Doc is refreshing, I am add a new row to my spreadsheet and save it to my network share location
   
9. I then refresh my Webi Document and see the new row appear.
   
10. I save my Document out to my BI Platform using the "Save to Enterprise" option under the Save icon in WRC.
11. Then I open up a browser and login to BI Launchpad.  I navigate to the folder where I saved my document.
12. To test that my BI Launchpad report is refreshing against my network data source, I update the Excel spreadsheet and add a row. 
    
13. Then I refresh my document in BI Launchpad to ensure the new row is there
    
14. I now have a report that will use an Excel sheet off of a network share to get data.   This gives me some more flexibility around my data for my report.

There are a few things that you will want to watch out for with this option.  I've documented the ones that come to mind below:

• In most cases, the SIA (Server Intelligence Agent) will need to run as a network account that has rights to this network location.
• You will need to ensure you have an Adaptive Processing Server that has the Excel Data Access Service running on it (and enabled)
• Only the Applet Interface and WRC interface support modifying/creating Excel data sources for Webi Docs
• The "refreshable" option will need to be selected for the Data Provider in order to refresh a document
• The "editable" option has been removed in later versions of BI 4.1 because it was not needed for Excel data sources and did not do anything except cause confusion to end users.
• IMPORTANT: Excel is not a database and does not handle concurrent access well.  In fact, the file can get locked when using an unmanaged network share as a data source for multiple users.  So, although the method above is possible, it would be better to use a Managed Excel document that is updated through the RESTFul SDKs to accomplish this.  The above method may be useful if you want to schedule a report instance to run daily and fetch updated information from an unmanaged XLS but for adhoc reporting, there is a very real chance that it could result in an error due to file locking.
• It is also important to note that on Linux/UNIX operating systems that this will not work.  The network path is stored in the report metadata as text and for Windows this would be something like "\\<server>\<share>" but on UNIX, this type of network path will not be valid.  For UNIX the managed document would be best.  KBA 2166390 talks more about this.

That's it!  I'd love to hear from anyone using this in the real world.  Are there any gotchas?

Thanks!

Jb

[christopher_vozella]

Breezytastic!!! This is an oldie and a goodie! Well done JB!

[wmarcy]

Great Webi trick !

You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !

William

[ted_ueda]

That's a cool trick!

Personally, I like "Managed" Excel reporting.  The advantages of having the Excel managed by BI Platform are the following:

1. The managed Excel is tagged as a "dependency" for any WebI document that reports off of it.  So it's included in the Promotion, Upgrade, and Version Control of the WebI doc.  It's nice having the BI Platform tools keep refrenential integrity between WebI and Excel.
2. If you have a backup plan for the CMS DB database and FRS files, the Excel file will be backed up along with the WebI document.
3. Access to the Excel file is available anywhere your User has access to BI launchpad.  No need to expose the file share to end users.
4. Access to the Excel file is controlled via the BI Platform security layer.
5. Access consistency to the file - when the APS requests a copy of the Excel file, it goes through the File Repository Server, ensuring you're getting the correct version of the file, and that it's not being modified during the request.  No need to manage file access rights, for each APS.
6. Any User with the rights can update the Excel file via BI launchpad (right-click the Excel object and select Organize -> Replace File).  Refreshing the WebI doc after will show the new data. Conversely, you can restrict access using the BI Platform security framework.
7. If you want to list out all the WebI document that's reporting off the managed Excel file, find its SI_ID value in BI launchpad, and in the "Query Builder" tool (http://<host>:<port>/AdminTools), execute the query (below I'm assuming the Excel is SI_ID=7015):
   
   Select SI_ID, SI_NAME from CI_INFOOBJECTS WHERE PARENTS('SI_NAME=''Document-Excel''', 'SI_ID=7015')
   
   and that'll give you a list (so you won't inadvertantly delete a Excel file that's still being used by a WebI document.  If you do, then you'll just retrieve from Version Control, but only if "managed").

The "Managed Excel" reporting feature was introduced in BI 4.1 SP02 for the above advantages.  Check it out to see if it suite your purposes!

Regards,

Ted Ueda

[Former Member]

HI,

did you try this approach in a Linux Server?

we ran BO 3.1 in a Windows Server and it worked like a charm, but now we're running BO 4.1 in a Linux Server and it stopped working. Do you, by any chance, worked it out in a Linux Server? You would save my day (or better my whole year !!!)

CHeers,

ROgerio

[former_member59613]

Hi Roger,

I haven't tried that myself.  Is it failing with an error of some sort?  Doing an End to End trace would be the best way to get the details around why it is not working.  Most likely the issue is with finding the file on the Linux Server.  Could be permissions or something.

thanks

Jb

[former_member59613]

For sure Ted, managed is a great new feature indeed!  I was surprised that people were asking about the unmanaged option during my sessions at ASUG but there seems to be different use cases for one vs another.  The big one being it is less overhead to update an Excel file on a network share vs republishing it to the BI Platform.  A lot of people use 3rd party applications to generate excel output that they would like to format using Webi.

It seemed there were quite a few people that wanted the unmanaged functionality but weren't aware it was available.  That's the main reason I put this info together.  There is some good info out there already on the managed feature.  Patrick Perrier does a bang-up job walking through that feature here: http://scn.sap.com/docs/DOC-49324

Thanks,

Jb

[david_bartuli]

Hi Roger,

do you have found a solution for this issue? We are facing the same problem

Regards

David

[Former Member]

HI David, unfortunately no, we didn't.

IN fact we are QAaWS (web services) intead.

WE got 2 reports, the 1st generates the WS, and the second consumes it.

REgards,

ROgerio

[Former Member]

Hi Jonathan,

We have recently upgraded to BO4.1

We have a few Webi reports which use excel spreadsheets on a network drive as a data source. Since the upgrade, we are experiecning 2 issues which seem to be related. The first issue is that BO seems to leave the spreadsheet locked after refreshing. So when users go to update the spreadsheet, when they try to save, excel says that someone is locking the spreadsheet. We then find the user svcBOPROD is locking the spreadsheet. Once this occurs the Webi itself can no longer refresh, we get this error:

Unable to create or update the Excel personal data provider: cannot open the workbook. (IES10872)

The strange thing is that if I open the webi report using the Rich Client tool, it is able to refresh without any problem. But it still leaves the spreadsheet locked so that users cannot edit the data.

Have you come across this problem with BO4.1?            

[ted_ueda]

That's a problem with unmanaged Excel reporting, where WebI is directly opening the Excel.  It needs to lock it to ensure no updates are done while reading the file, and it locks till the session closes or times out.

Managed reporting - where the Excel is uploaded to BI Platform, and WebI is reporting off that, doesn't have this issue, since the File Repository Service manages the file, and WebI merely asks for a copy of the Excel file from the FRS.

In my opinion, the managed Excel reporting is a better solution.

Regards,

Ted Ueda

[Former Member]

Hi Ted,

The problem is that webi is not releasing the locks even when the session ends.

Do you know of any technical documentation which describes the locking process? Specifically I would like to understand when the lock should be released. In BO4.0 it seems that the lock was released as soon as the Webi finished refreshing.

[Former Member]

Hi  Ted,

I do agree with you that the managed is a better solution, but there are a main issue that , at least for me, is a huge problem.

You can´t schedule, a report to XLS on a daily basis for instance, and then use it as a PDP. The WEBI report that reads the Excel don´t , automatically, recognizes the new excel as the PDP.If you use unmanaged Excel you do.

I´m working on a script , with REST API to automatically change the Dataproviders so the document could always gets the fresh Excel. As soon as I finish it I will post it.

Regards,

Rogerio

[ted_ueda]

That's interesting!

So you're scheduling WebI A to Excel format.  You have a different WebI, WebI B, reporting off the Excel.

Previously, you schedule WebI A to Excel to unmanaged disk. You have WebI B report off of unmanaged Excel.

That would break because of file locking issues.

If instead you have WebI B report off of managed Excel, then you need to have means to update the managed Excel file automatically whenever WebI A schedule completes.

Create an Event off of the WebI A schedule + Program Object to script the managed Excel update?

That seems the natural thing to do - it would scale better than updating WebI B to point to a different Excel. WebI B, or any other WebI reporting off the Excel, shouldn't have to change.

Regards,

Ted Ueda

[Former Member]

Hi Ted,

I´m not completely sure I understood your proposed solution. Snyway, I came up with a script that re-maps WEBI B to read the Webi A scheduled Excel.

It´s posted here A Script to Change a Managed Excel Datasource of a WEBI Document

Regards,

Rogerio

[former_member203955]

We are on BO 4.1 service pack 5 . I've followed your article which works fine on the Rich client but in webi the refresh button is greyed out and if I try to refresh by edit data source run query get the error you do not have authorisation to access the data on this universe . I've got our basis team to run the SIA as a domain server but dont know how to check if the APS has the excel service enabled . We are using SAP authentication if that is relevant .

[former_member59613]

Hi Jim,

Sounds like it is more related to Enterprise security.  Can you try with an Administrator account to see if that allows for a refresh?

It is likely a right that is denied for your user. 

thanks

Jonathan

[bulent_ozgul]

I've faced with the same problem. It isn't relevant with your SAP auth. or your BO security.

My first scenario was to use the excel file source via "map network drive". I opened the file on Webi Rich Client via my mapped network drive. The report runs fine on Rich Client but if i export it to my enterprise the refresh button is disabled.

On  the second scenario i shared the folder where my Excel file is and set its setting to "turn off password protect sharing". After this i created my Webi Rich Client report via this sharing folder not my mapped drive and now i'm able to refresh the report on my BO environment.

[Paul1202]

@ Ted Ueda,

I agree with you that “Managed” XLS is better. The big issue is that one has to update (“Replace”) the XLS in Infoview (so stored in the Input-Repository) from the changed XLS which is stored “somewhere else”.

In practice, those kind of XLS-Files can change monthly, weekly, daily. So if an organization/department has multiple of these XLS’es, it would be a manual (so time-consuming and annoying) job to “replace” all these XLS-files (daily) on the BIP with the ones maintained externally.

So it would be nice to have a kind of Program Object which can be scheduled, to execute this “replaces”. Any idea of this exists somewhere ?

Or (to all) : maybe somebody ever created this kind of Program Object ?

Or, is there another possibility ?

Technically, it should be possible to physically replace the XLS-file on the FRS (as, example, frs://Input/x_xxx/yyy/?zzzz.XLS with another file. By, for example, a Windows-script/schedule. But this is surely not a good idea (as we want to keep integrity with CMS-Database).

[daniel_paulsen]

This will be possible in BI4.2 with a simple Restful call:

      PUT   /spreadsheets/<spreadsheetID>

Where the body of the request is the excel file as a binary stream.

BI4.2 documentation is now available and documentation for this Restful call can be found here in the  "SAP BusinessObjects RESTful Web Service SDK User Guide for Web Intelligence and the BI Semantic Layer 4.2" guide, section 8.7.4

Dan

[Former Member]

Hello community,

Is there any change to control how the excel documents are uploaded to the BO platform? Such as a limit over the size, or over the number of excel documents a user could upload.

By the way, could we control via security policies where a user could upload a local document, or which users could have to do it?

We have worried about how users will use this feature to use it in webi reports. We don't want BO platform becomes an Excel repository.

Thank very much in advance,

Best regards.

[jenszulinski]

Hi,

Wondering if there have been updates allowing unmanaged files on a UNIX platform?  We are on 4.1 SP5.

[former_member536749]

Following for any insights on creating universes on excel files in  Linux platform. We are on SAP BusinessObjects BI Platform 4.1 Support Pack 6 Patch 3.

[amitrathi239]

Universe on excel file at Linux platform is not possible.you have two options.

Directly upload the excel file in BI launchpad and use as data provider.

or load the excel file in database table and then use in the universe

 

 

[former_member536749]

Thank you Amit,

I tried 1st option but my requirement is to link this excel file (Detail View) to a summary table in webI.  which I don't see option other than Universe or BEx query.

2nd option: Have to talk to the Admin team.

Thanks,

Jothi

[Joe_Peters]

Does this work in BI4.2?  I tried but it will not refresh in BI launchpad.

[amitrathi239]

I haven't tested in BI 4.2 but yes it's working properly in BI 4.1 SP08.

[former_member398199]

Hi there,

We need to be able to use unmanaged Excel files as a data source as there are several times that the business needs to edit data before it is reported on. They don’t have the knowledge, nor the inclination to upload files to the server.

Testing this exact issue today we discovered two things when creating Rich Client reports from 4.1 sp5 to run through WebI on a scheduled basis.

1. You need the full UNC name of the folder the Excel file is stored in
2. The BO server needs to be able to access the source file
3. If the file is stored as xlsx it gets locked up, if it is stored as xls, it doesn’t!

We are in the process of setting up a new 4.2 system and I tested the report that didn’t work in 4.1 (with the xls extension) in 4.2 and it did work, without locking up!

Our users are excited as this will give them greater flexibility over their reports going forward.

I just wish we didn’t have to go through Rich Client first and could access an unmanaged Excel doc straight from WebI! Maybe in it’s coming in a newer version???