BI Publisher - Excel data source Shared (Server) and Local

We plan a local Oracle BI Self Service forum in couple of weeks, so I'm writing few relevant posts. This one will cover the connection to Excel data source in BI Publisher data model using both local and shared (server based) excel files. The next one will involve the Excel files in data mashup with other sources.

When creating a new data model in BI Publisher

I can select Microsoft Excel file as data source. It has to be Excel 1997 - 2003 format (xls):

 

The file can be shared or local:

There is a major deference between them. 

In the Shared case we point to the file while in the local case we upload the file. It influences the refresh, the shared case will show the latest version of the excel (excluding cashing, as any other data source). In the local case, for refresh we need to reload the local file.

There is reason here. The local case is designed to enable a user to create a report using local file and share with other Publisher users that don't have the same local file.

In both cases the Excel file can have multiple sheets. If we have more than one table in a sheet, we need to name the table (right click - Name a Range) in Excel with "BIP_" prefix so BI Publisher recognizes it. We don't have to use names for a single table in a sheet.

 

For example I have 2 excel files:

1. few_tables.xls: With 3 tables, one BIP_Table1, the others, BIP_Years and Years (no BIP_ prefix).         
2. One_table.xls: With only the first table and no names.  

The last preparation step for this experiment will explain how do I create a shared file location. 

Under Administrator/Manage BI Publisher

 We can define data Sources. Select "File".

If we want a new location on the server, we should press "Add Data Source" and specify the location of the files on the server. They can be of various types such as XML, XLS....

 We can always use an existing location.

The existing location - demo_files, is located at    ${xdo.server.config.dir}/repository/DemoFiles.

The ${xdo.server.config.dir} translates to :

MWHOME/user_projects/domains/bifoundation_domain1/config/bipublisher/

I used a full path to server location and named it "files". One can also create a virtual drive for shared location.

Now we are all set, I will import an Excel file in both methods and create a basic report with the combined model, just to show I can work with both types.

I created a new data model and in the "Data Sets" use the pull down menu and selected "Microsoft Excel File". I have to give the new data set a name. I selected a Data Source and pressed the magnifier glass for a list of Excel files. I'll select one_table.xls.

Since there is only default sheet in this file and no names definition there is very little to select here.
If you wonder about the parameter section; To use parameter, you need to create them in another part of the data model. Excel parameters are limited to one value.

Next I will import a table from the other Excel. In this case lets do it "locally".

 I selected Local -> File Name -> Browse and selected a file. After I pressed Upload, it was uploaded to my user (weblogic).

I can select one of 2 tables in the sheet. You might remember I created 3 names in this sheet, but only 2 of them have BIP prefix. Those are the only tables I see.

I selected the BIP_Years table. I have 2 data sources. In the Data tab, I can see data from each. But I actually want to join them, so I can have the year description in with the table data.

To join the data sources, I clicked on "Per_Name_Year", selected "Create Link". 

Next selected Year from the other data source.

The result is:

In Data tab I see they are joined.

I might want to select better column names in the Structure tab. Next I can create the report:

Last step: I'll update both excel files (change 2007 to 2008 and the description for the year 2008) and refresh. As expected, only the change in the shared excel is seen in the report.

In case I insist on updating the local file data, I have to:

Return to data model and refresh the local excel file.

I'm required to select the file again, but I don't have to select the table or sheet in the Excel.

 Now the report is updated with both changes:

Few more issues:

if your version is 11.1.1.7.0 and above, you might have noticed the "Manage Private Data Source" option:

With it, private connections for OLAP, JDBC, Web Service, and HTTP data sources are supported in BI Publisher and can be created by users with data model creation privileges.(described here). So it's interesting, but not relevant to our excel case.

When creating a new report there is a "Upload Spreadsheet" option. For simplicity, it creates a data model, with Local Excel, just without the table selection option.