Excel Data Sources for Webi and Xcelsius

Excel Data Sources for Webi and Xcelsius

Although using Excel as a data source is not ideal, sometimes there is no other option. In this post I’ll go through what I believe are the best options for getting data from an Excel spreadsheet into Webi and Xcelsius (and Universes and Crystal).

I’m Using

• Excel 2010 32-bit
• BOBJ XI3.1 SP4
• InfoView/Webi
• Webi Rich Client
• (Universe) Designer
• Xcelsius SP4

I’m Assuming

• You know your way around the tools listed above.
• You have Excel installed. You can have Excel 2003 or 2007. If you have SP4, you can use Excel 2010 32-bit. Xcelsius is not compatible with 2010 64-bit.
• You can also follow some of these steps in BI4.0. However, since BI4.0 is a 64-bit architecture, some steps (e.g. setting up an ODBC data source) will require 64-bit versions of the Excel ODBC driver.

The Disclaimer

Working with Excel as a data source should be avoided wherever possible. Before following any of the methods outlined below, you should be sure that there’s no way to get to a more robust source of data. The reasons are probably obvious, but here they are:

• Once you have a report working on an Excel data source, any change to the columns in the Excel file will probably break the report.
• There is an extra concern of security on the Excel file, if it is available centrally.
• There is a limitation of 64,000 rows in Excel 2003 and below.

The Options

Depending on your situation, the following are you main options for getting data out of Excel into the Business Objects front end tools:

• Universe: creating a universe that connects to your Excel file.
• Webi Rich Client: create a Webi report in the rich client that uses Excel as a data source.
• Data Services: using Data Services (previously Data Integrator, or some other ETL tool) to load your Excel data into a database.
• XML Maps: use XML maps to keep an Xcelsius model bound to an external source.
• Crystal: Selecting an Excel file as a data source for a Crystal Report.
• Explorer: Select an Excel file as a data source for Explorer.

Option 1: The Universe

Pros:

• This method is useful if you need to use an Excel file for many reports.
• You can do the hard work and have your users access the Excel file like they would any data source (through a universe).
• You can access to the data from Webi and Crystal in the same format.
• You can access the data without the BOBJ Client Tools.

Cons:

• When you refresh a Webi report that uses an Excel data source, that Excel file will be locked for editing.
• You need access to the server to place the Excel file somewhere visible to the server components.
• It’s difficult to find instructions on how to set it all up and troubleshoot.
• The below steps work in XI3.1. Since BI4.0 is 64-bit architecture, you will need to find the 64-bit ODBC Excel drivers, which I haven’t tested. Leave a note in the comments if you’ve had luck with this.

Excel File
So that I have some data to work with, I’ll just export an existing Webi report I have. Remember that when you export to Excel, Webi cleverly re-creates your layout. I’ll export to CSV since this ignores the layout and just exports the query results.
Make sure you save the file in a central location (visible by the Business Objects server), with the file type .xls, then close the file.
If you already have an Excel file, make sure that the layout has each ‘object’ as a column, and the values of that object in the rows below. You can’t use pivot tables.


ODBC Connection

1. Open up your 32-bit ODBC Administrator. On 64-bit systems you need to be careful, since the default Data Sources (ODBC) start menu entry is for 64-bit drivers, which BusinessObjects XI3.1 doesn’t use. If in doubt, the exact file is C:\Windows\SysWOW64\odbcad32.exe
2. Go to the System DSN tab.
3. Click Add…
   
4. Select Microsoft Excel Driver (*.xls) from the list.
5. Click Finish.
6. Name the connection (best to use the name of the file).
7. Click Select Workbook…
8. Navigate through the directories to find your file. You can copy the file path from Windows Explorer if you like. Then paste it into the Database Name: field and hit enter.
9. Select the file and click OK.
   
10. Click OK and OK to close the ODBC Data Source Administrator window.

Universe and Connection

1. Open Universe Designer and connect to a repository.
2. Click File > New.
3. Name the universe, I’ll stick with the Excel file name.
4. In the Connection box at the bottom of the window, click New…
   
5. Click Next.
6. Type a name for the connection.
7. Select Microsoft > MS Excel 2003 > ODBC Drivers
   
8. Click Next
9. Leave the Authentication Mode as Use specified username and password, and leave the two fields below blank.
10. In the Data source name box, select the connection you just created.
11. Click Test Connection. You should get a positive message. If not, check that you have followed each of the steps above.
12. Click OK to the Test the connection window.
13. Click Next.
14. Change the Array fetch size to 1 (it will be 10 by default).
    
15. Click Next > then Finish then OK.
    
    Your universe is now created.
16. Click Insert > Tables… (or double-click the white space).
17. Right click the ‘table’ and select Insert
    
18. Drag the table over into the universe pane to create the objects as you would with any universe. You can add, move, rename and rearrange as you normally would. You will need to manually switch your objects to measures.
    
19. Click File > Export…
20. Save the file and export it to the repository as per the prompts.

This universe can now be used in Webi just like any other universe.

Except… If you now try and edit the original Excel file, you will see this:

A quick search in Microsoft’s brilliant Process Explorer shows that the file is locked by WIReportServer.exe.

To release the file, you can either go into the CMC and restart the WebIntelligenceProcessingServer, or just kill the WIReportServer.exe process (it will be started again immediately by the SIA). If you have more than one Webi Processing Server, the PID will tell you which to restart.
Obviously either of these actions are call for concern in a busy production environment, and this is one of the big issues with this method. I would like to think there is a way to avoid this, but I haven’t found it.

Option 2: Web Intelligence Rich Client

This option is much simpler, and likely more familiar than using a universe. I won’t go into as much detail here as this process is well documented in the manual.
Pros:

• It’s quick, and can be done without access to the Business Objects server.
• There is no locking of the Excel file as described above.
• It can be used off-line (note, for the Webi Rich Client to work in standalone mode, it must have been connected to a CMS at least once).

Cons:

• It only applies to one Webi report. If you want multiple Webi reports using the same Excel file you must recreate each one.
• You need to have the Webi Rich Client installed.

The Excel File
As with the universe method, your Excel file must have each object (dimension or measure) as a column.


The Webi Report

1. Open the Webi Intelligence Rich Client.
2. Login (or select Standalone) and click the New Document icon.
   
3. Select Other Data Sources and click Next > >.
4. Click Browse and select your Excel file.
5. Select the sheet and range, and select the option of First row contains column names as appropriate.
   
   Note: If you’re going to deploy this Excel file to a central location, so that your Webi users can refresh the report, change the path so that it will work from any machine, like so:
   
6. Click Next >>
7. Configure your dimensions and measures and run the query.

The Others

Data Services
Data services can take an Excel file and Extract, Transform and Load it into a relational database. This means that you have a robust data source to report off of. However the actual ETL job still relies on the structure of the Excel file not changing. For large user bases or long-term needs, this is arguably the best option.


XML Maps
This option is mostly carried out in Excel, and only applies when using an Excel spreadsheet as a source for Xcelsius. The documentation from Microsoft is pretty good. Once you have XML maps set up, import the spreadsheet into Xcelsius. Then go to Connections and click Add > XML Maps.

Xcelsius will recognise the XML map that was in the spreadsheet you imported and create a connection mapped to the appropriate range of cells. Specify the location of the XML file that you created when setting up the XML map in Excel.

Note that the standard options for refreshing the connection are available for XML maps too.
Now the spreadsheet embedded in the Xcelsius model has a link to the XML file referenced in the original Excel file. Set the connection to refresh on open and your dashboard will remain in sync with the XML file.
The XML file can then either be updated directly, or the original Excel file can be opened, modified, and the XML re-exported. Whilst the setup is finicky, the result is that access to the Xcelsius client is not required for an Excel data source to be updated.


Crystal Reports
This option is very similar to using Webi Rich Client to access the Excel sheet. When creating your Crystal report, under Create New Connection, select Access/Excel (DAO), then select the file and set the Database Type to Excel 8.0.


Explorer
In Explorer, you can base an Information Space on a local Excel file. If super-fast analysis and very nice graphics is what you’re after, then this is an excellent option.


That’s it!

- See more at: http://blog.davidg.com.au/2011/10/excel-data-sources-for-webi-and.html#sthash.0BCaWfJ1.dpuf