Purpose of Document
This document will illustrate steps to source data for IBM Cognos Insight from a macro-enabled Microsoft Excel spreadsheet.
IBM Cognos Insight 10.x.
This document assumes that the IBM Cognos Insight client machine has Microsoft Excel 2007 or 2010 installed.
Most organizations use macros heavily in their Microsoft Excel spreadsheets. When such spreadsheets, with the .xlsm file extension, are dropped into an IBM Cognos Insight workspace, a message saying “Unable to open the file because either the file format is not supported or Cube Import is not enabled” appears as shown in Figure 1.
Figure 1: IBM Cognos Insight error when importing an xlsm file
This is because IBM Cognos Insight does not support macro-enabled Excel spreadsheets (*.xlsm) as a data source. In such cases, one option is to save the workbook as an .xls or .csv file and import that into IBM Cognos Insight. Another option is to create a name range in the spreadsheet (similar to defining a table structure) and then import the data in the name range through an ODBC connection. This second option is what we will look at in this document.
Define Name Range(s) in Microsoft Excel Spreadsheets
The steps in this section specify the steps to create one defined name range, however any number of name ranges can be defined. Please refer to the Microsoft Excel documentation for version specific instructions on defining name ranges. In this example, Microsoft Excel 2007 is used.
- Open the Microsoft Excel spreadsheet containing the macros.
- Select the cell range that you want to import data from, select the
Formulas tab, and then click Define Name in the Defined Names group as illustrated in Figure 2.
Figure 2: Select data for name range
- In the Name box, type All_Data (this can be any name you would like)
as depicted in Figure 3, and then click OK.
Note: The first character must be a letter, an underscore, or a backslash. No spaces are allowed in a range name. Also, the name should not be similar to a cell address (i.e. A11, AB10 etc.).
Figure 3: New Name range dialog
- To ensure that the name range is defined, click the name drop-down
box on the left end of the formula bar as illustrated in Figure 4, and verify
that ALL_Data is one of the entries in the list.
Figure 4: Verify name range creation
- Save the file.
Create ODBC Connection for Microsoft Excel Spreadsheet
This section describes step-by-step instructions for defining an ODBC DSN for the Microsoft Excel spreadsheet in the previous task.
- In Windows, open Control Panel, click Administrative
Tools, click Data Sources (ODBC) to open the ODBC Data Source Administrator as illustrated in Figure 5.
Figure 5: The ODBC Data Source Administrator
If you are on a 64-bit version of Microsoft Windows and are using a 32-bit version of Microsoft Office, by default, you will not be able to connect to Excel through ODBC. This is because the IBM Cognos TM1 portion of IBM Cognos Insight will, by default, run in 64-bit mode on a 64-bit OS and a 64-bit application cannot connect to a 32-bit ODBC driver. Currently, there are two remedies to this situation. The first is to use a 64-bit version of Microsoft Office, which was introduced in Office 2010. You can also configure the IBM Cognos TM1 portion of IBM Cognos Insight to run in 32-bit mode thereby allowing you to connect to 32-bit ODBC drivers. To do this:
- Go to the IBM Cognos Insight install location (can be located by looking at the Target property in the Properties dialog of the IBM Cognos Insight shortcut located on your desktop).
- Open the configurations folder and then open the config_10.2.xxxx.x folder.
- Open config.ini in a text editor such as Notepad and add the
following line to the end of the file:
- Save and close the file.
- In the ODBC Data Source Administrator, click the User DSN tab if you
want the data source to be visible only to you on this machine, or click the
System DSN tab if you want the data source to be visible to others on
this machine and on the network. Click Add.
The Create New Data Source dialog box appears as shown in Figure 6.
Figure 6: Create New Data Source dialog
- In the Create New Data Source dialog box, click Microsoft Excel Driver (*.xls, *.xlsx,
*.xlsm, *.xlsb), and then click Finish.
The ODBC Microsoft Excel Setup dialog box is displayed.
- In the Data Source Name: field, type a data source name, in this case
we will use ExcelODBCForCI as shown in Figure 7, and then click Select Workbook to browse to and select the required workbook.
Figure 7: ODBC Microsoft Excel Setup dialog
- From the Version drop down list, select the appropriate Microsoft Excel version, and then click OK.
- Ensure that the new DSN is created, and then click OK to close the ODBC Data Source Administrator dialog.
Import ODBC Microsoft Excel Data into IBM Cognos Insight
This section describes the step-by-step process of importing data from a macro-enabled Microsoft Excel spreadsheet into IBM Cognos Insight through ODBC.
Once name range(s) are defined in the spreadsheet and the ODBC DSN pointing to the spreadsheet is created, users can import data from the spreadsheet into IBM Cognos Insight with the following steps.
- Launch IBM Cognos Insight.
- Click Get Data, and then click Import Data..., as shown in Figure 8, to open Import Data wizard.
Figure 8: Import Data option under Get Data menu item
- From the Type drop-down list, select Relational Data Source (ODBC) as shown in Figure 9.
Figure 9: Import Data wizard with Relational Data Source (ODBC) selected
The Connection Editor dialog appears.
- From the Data Source Name drop-down list, select the ODBC DSN created
in the previous section, in this case ExcelODBCForCI, as shown in Figure 10.
Figure 10: Connection Editor dialog with ExcelODBCForCI DSN selected
- You may test the connection using the Test Connection button, and
then click OK to close the Connection Editor dialog.
Please note, Query Builder cannot be used for such ODBC sources, and the SQL Query needs to be written manually.
- For our example, enter the SQL query
SELECT * FROM ALL_Dataand then click the Refresh button to preview the data.
The data appears in the Data Preview pane as shown in Figure 11.
Figure 11: Data Preview pane showing data retrieved from the macro-enabled Microsoft Excel workbook
One can also import selected columns as well. For example, the SQL query
SELECT Year, "Product line", Revenue FROM ALL_Datawill return data for the specified columns only, as shown in Figure 12.
Figure 12: SQL Query returning only Year, Product line and Revenue in the Data Preview pane
Once the data has been previewed, you can proceed with modeling and importing data into the IBM Cognos Insight workspace as usual.
Leveraging the ODBC connection to macro-enabled excel spreadsheet (*.xlsm), IBM Cognos Insight users can not only import data from such unsupported spreadsheets, but can define multiple name ranges in Microsoft Excel to simply import different sets of data from the same spreadsheet.
The steps described in this document are not restricted to the macro-enabled spreadsheets (*.xlsm). The same can also be leveraged with standard Excel spreadsheets (*.xls, *.xlsx) as well.