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